revision-id: 859536176897305f2e2f089eeba77871aefdb79c (mariadb-10.3.10-10-g8595361) parent(s): e2535dcc04b5ecc15575b878ebeb0cc589cd23fe author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-08 06:55:48 -0700 message: MDEV-17381 Wrong query result with LATERAL DERIVED optimization and join_cache_level=6 This bug was fixed by the patch for mdev-17382 applied to 5.5. --- mysql-test/main/derived_split_innodb.result | 41 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 41 +++++++++++++++++++++++++++++ 2 files changed, 82 insertions(+) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 7e4ba8e..21dbd49 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -58,3 +58,44 @@ WHERE t2.id2=t.id2; id3 1 DROP TABLE t1,t2,t3; +# +# Bug mdev-17381: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; +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; +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 name total_amt +1 A 10 +2 B 20 +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> ref key0 key0 5 test.t2.id 2 +2 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 +set join_cache_level=default; +DROP TABLE t1,t2; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 5e5e3d5..c3b3bca 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -53,3 +53,44 @@ eval EXPLAIN $q; eval $q; DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug mdev-17381: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; + +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; + +let $q= +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; + +eval $q; +eval EXPLAIN $q; + +set join_cache_level=default; + +DROP TABLE t1,t2;