[MDEV-25558] UPDATE on multiple tables won't use the hashed join Created: 2021-04-28  Updated: 2021-04-28

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.3.23
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Mike Ilin Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: innodb

Attachments: File fill_test_table.sql    

 Description   

System settings:
join_cache_level = 8
optimizer_switch join_cache_hashed=on

Tables definition:

CREATE TABLE IMPTest1 (id INT NOT NULL, is_ready INT NOT NULL)
 
ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
 
 
 
CREATE TABLE IMPTest2 (id INT NOT NULL, is_ready INT NOT NULL)
 
ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;

Queries:

EXPLAIN FORMAT=JSON
 
SELECT * FROM IMPTest1 INNER JOIN IMPTest2 ON IMPTest1.id = IMPTest2.id;

Result:

{
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "IMPTest2",
 
      "access_type": "ALL",
 
      "rows": 8,
 
      "filtered": 100
 
    },
 
    "block-nl-join": {
 
      "table": {
 
        "table_name": "IMPTest1",
 
        "access_type": "hash_ALL",
 
        "key": "#hash#$hj",
 
        "key_length": "4",
 
        "used_key_parts": ["id"],
 
        "ref": ["kav_ilin.IMPTest2.id"],
 
        "rows": 16,
 
        "filtered": 100
 
      },
 
      "buffer_type": "flat",
 
      "buffer_size": "100Mb",
 
      "join_type": "BNLH",
 
      "attached_condition": "imptest1.`id` = imptest2.`id`"
 
    }
 
  }
 
}

Update query

EXPLAIN FORMAT=JSON
 
UPDATE IMPTest1, IMPTest2 SET IMPtest1.is_ready = 1
 
WHERE IMPTest1.id = IMPTest2.id;

Result:

{
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "IMPTest2",
 
      "access_type": "ALL",
 
      "rows": 8,
 
      "filtered": 100
 
    },
 
    "table": {
 
      "table_name": "IMPTest1",
 
      "access_type": "ALL",
 
      "rows": 16,
 
      "filtered": 100,
 
      "attached_condition": "imptest1.`id` = imptest2.`id`"
 
    }
 
  }
 
}

I expected that the database engine would use hashed join on running the second query.
Generated at Thu Feb 08 09:38:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.