Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
When a materialized derived table is joined using an equality predicate such that one of his parts is a field of the materialized table then hash join algorithm should not be used, because in this case a hash key is created for the table by which it is supposed to be accessed.
No we see that is not so, for example, for the following test case:
CREATE TABLE t1 ( |
id int NOT NULL, |
amount decimal DEFAULT NULL, |
PRIMARY KEY (id) |
) ENGINE=MyISAM;
|
|
|
CREATE TABLE t2 ( |
id int NOT NULL, |
name varchar(50) DEFAULT NULL, |
PRIMARY KEY (id) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO t1 VALUES |
(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
|
(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); |
|
|
INSERT INTO t2 VALUES |
(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), |
(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); |
|
|
set join_cache_level=4; |
|
|
EXPLAIN
|
SELECT t2.id,t2.name,t.total_amt |
FROM t2 |
LEFT JOIN |
(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t |
ON t2.id=t.id |
WHERE t2.id < 3; |
j
We see that hash join algorithm is applied for this query:
MariaDB [test]> EXPLAIN
|
-> SELECT t2.id,t2.name,t.total_amt
|
-> FROM t2
|
-> LEFT JOIN
|
-> (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
|
-> ON t2.id=t.id
|
-> WHERE t2.id < 3;
|
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
|
| 1 | PRIMARY | t2 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
|
| 1 | PRIMARY | <derived2> | hash_ALL | key0 | #hash#key0 | 5 | test.t2.id | 8 | Using join buffer (flat, BNLH join) |
|
| 2 | DERIVED | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | |
|
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
|
Attachments
Issue Links
- blocks
-
MDEV-17381 Wrong query result with LATERAL DERIVED optimization and join_cache_level=6
-
- Closed
-