diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2019-08-20 07:31:44 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-08-20 07:31:44 +0300 |
commit | a02dd7e614c1f59d537005b1f5d80084a615f276 (patch) | |
tree | 9f168ddd6c48da6d2537ad13151634f7c619e8b1 /mysql-test | |
parent | a7e2cd55ab3e8bc1d658c3b6ac11c7834783acd5 (diff) | |
parent | e746f451d57def4be679caafc29976741b3e89f7 (diff) | |
download | mariadb-git-a02dd7e614c1f59d537005b1f5d80084a615f276.tar.gz |
Merge 5.5 into 10.1
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/join.result | 326 | ||||
-rw-r--r-- | mysql-test/t/join.test | 59 |
2 files changed, 385 insertions, 0 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index ff5d72eec74..68a2254b15c 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -2926,6 +2926,332 @@ NULL NULL NULL 9 NULL NULL NULL 5 drop table t1,t2,t3,t4,s1,s2; # +# MDEV-20265: Mix of comma joins with JOIN expressions +# (correction of the fix for MDEV-19421) +# MDEV-20330: duplicate +# +create table t1 (a int); +insert into t1 values (7), (5), (3); +create table t2 (a int); +insert into t2 values (5), (1), (7); +create table t3 (a int); +insert into t3 values (2), (7), (3); +create table t4 (a int); +insert into t4 values (4), (7), (9), (5); +create table t5 (a int); +insert into t5 values (3), (7), (9), (2); +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 left join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 left join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 5 2 NULL +5 5 2 NULL +3 5 2 NULL +7 1 2 NULL +5 1 2 NULL +3 1 2 NULL +7 7 2 NULL +5 7 2 NULL +3 7 2 NULL +7 5 3 NULL +5 5 3 NULL +3 5 3 NULL +7 1 3 NULL +5 1 3 NULL +3 1 3 NULL +7 7 3 NULL +5 7 3 NULL +3 7 3 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 right join t4 on t3.a=t4.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t4` left join (`test`.`t2` join `test`.`t3`) on((`test`.`t3`.`a` = `test`.`t4`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 right join t4 on t3.a=t4.a; +t1_a t2_a t3_a t4_a +7 5 7 7 +5 5 7 7 +3 5 7 7 +7 1 7 7 +5 1 7 7 +3 1 7 7 +7 7 7 7 +5 7 7 7 +3 7 7 7 +7 NULL NULL 4 +5 NULL NULL 4 +3 NULL NULL 4 +7 NULL NULL 9 +5 NULL NULL 9 +3 NULL NULL 9 +7 NULL NULL 5 +5 NULL NULL 5 +3 NULL NULL 5 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` left join `test`.`t5` on((`test`.`t5`.`a` = `test`.`t4`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a; +t1_a t2_a t3_a t4_a t5_a +7 5 2 7 7 +5 5 2 7 7 +3 5 2 7 7 +7 1 2 7 7 +5 1 2 7 7 +3 1 2 7 7 +7 7 2 7 7 +5 7 2 7 7 +3 7 2 7 7 +7 5 7 7 7 +5 5 7 7 7 +3 5 7 7 7 +7 1 7 7 7 +5 1 7 7 7 +3 1 7 7 7 +7 7 7 7 7 +5 7 7 7 7 +3 7 7 7 7 +7 5 3 7 7 +5 5 3 7 7 +3 5 3 7 7 +7 1 3 7 7 +5 1 3 7 7 +3 1 3 7 7 +7 7 3 7 7 +5 7 3 7 7 +3 7 3 7 7 +7 5 2 9 9 +5 5 2 9 9 +3 5 2 9 9 +7 1 2 9 9 +5 1 2 9 9 +3 1 2 9 9 +7 7 2 9 9 +5 7 2 9 9 +3 7 2 9 9 +7 5 7 9 9 +5 5 7 9 9 +3 5 7 9 9 +7 1 7 9 9 +5 1 7 9 9 +3 1 7 9 9 +7 7 7 9 9 +5 7 7 9 9 +3 7 7 9 9 +7 5 3 9 9 +5 5 3 9 9 +3 5 3 9 9 +7 1 3 9 9 +5 1 3 9 9 +3 1 3 9 9 +7 7 3 9 9 +5 7 3 9 9 +3 7 3 9 9 +7 5 2 4 NULL +5 5 2 4 NULL +3 5 2 4 NULL +7 1 2 4 NULL +5 1 2 4 NULL +3 1 2 4 NULL +7 7 2 4 NULL +5 7 2 4 NULL +3 7 2 4 NULL +7 5 7 4 NULL +5 5 7 4 NULL +3 5 7 4 NULL +7 1 7 4 NULL +5 1 7 4 NULL +3 1 7 4 NULL +7 7 7 4 NULL +5 7 7 4 NULL +3 7 7 4 NULL +7 5 3 4 NULL +5 5 3 4 NULL +3 5 3 4 NULL +7 1 3 4 NULL +5 1 3 4 NULL +3 1 3 4 NULL +7 7 3 4 NULL +5 7 3 4 NULL +3 7 3 4 NULL +7 5 2 5 NULL +5 5 2 5 NULL +3 5 2 5 NULL +7 1 2 5 NULL +5 1 2 5 NULL +3 1 2 5 NULL +7 7 2 5 NULL +5 7 2 5 NULL +3 7 2 5 NULL +7 5 7 5 NULL +5 5 7 5 NULL +3 5 7 5 NULL +7 1 7 5 NULL +5 1 7 5 NULL +3 1 7 5 NULL +7 7 7 5 NULL +5 7 7 5 NULL +3 7 7 5 NULL +7 5 3 5 NULL +5 5 3 5 NULL +3 5 3 5 NULL +7 1 3 5 NULL +5 1 3 5 NULL +3 1 3 5 NULL +7 7 3 5 NULL +5 7 3 5 NULL +3 7 3 5 NULL +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t5 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` join `test`.`t5` left join (`test`.`t2` join `test`.`t3` join `test`.`t4`) on((`test`.`t4`.`a` = `test`.`t5`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a; +t1_a t2_a t3_a t4_a t5_a +7 5 2 7 7 +5 5 2 7 7 +3 5 2 7 7 +7 1 2 7 7 +5 1 2 7 7 +3 1 2 7 7 +7 7 2 7 7 +5 7 2 7 7 +3 7 2 7 7 +7 5 7 7 7 +5 5 7 7 7 +3 5 7 7 7 +7 1 7 7 7 +5 1 7 7 7 +3 1 7 7 7 +7 7 7 7 7 +5 7 7 7 7 +3 7 7 7 7 +7 5 3 7 7 +5 5 3 7 7 +3 5 3 7 7 +7 1 3 7 7 +5 1 3 7 7 +3 1 3 7 7 +7 7 3 7 7 +5 7 3 7 7 +3 7 3 7 7 +7 5 2 9 9 +5 5 2 9 9 +3 5 2 9 9 +7 1 2 9 9 +5 1 2 9 9 +3 1 2 9 9 +7 7 2 9 9 +5 7 2 9 9 +3 7 2 9 9 +7 5 7 9 9 +5 5 7 9 9 +3 5 7 9 9 +7 1 7 9 9 +5 1 7 9 9 +3 1 7 9 9 +7 7 7 9 9 +5 7 7 9 9 +3 7 7 9 9 +7 5 3 9 9 +5 5 3 9 9 +3 5 3 9 9 +7 1 3 9 9 +5 1 3 9 9 +3 1 3 9 9 +7 7 3 9 9 +5 7 3 9 9 +3 7 3 9 9 +7 NULL NULL NULL 3 +5 NULL NULL NULL 3 +3 NULL NULL NULL 3 +7 NULL NULL NULL 2 +5 NULL NULL NULL 2 +3 NULL NULL NULL 2 +explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) join `test`.`t5` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`a` = `test`.`t5`.`a`)) where 1 +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a; +t1_a t2_a t3_a t4_a t5_a +5 5 2 7 7 +7 7 2 7 7 +3 NULL 2 7 7 +5 5 7 7 7 +7 7 7 7 7 +3 NULL 7 7 7 +5 5 3 7 7 +7 7 3 7 7 +3 NULL 3 7 7 +5 5 2 9 9 +7 7 2 9 9 +3 NULL 2 9 9 +5 5 7 9 9 +7 7 7 9 9 +3 NULL 7 9 9 +5 5 3 9 9 +7 7 3 9 9 +3 NULL 3 9 9 +5 5 NULL NULL 3 +7 7 NULL NULL 3 +3 NULL NULL NULL 3 +5 5 NULL NULL 2 +7 7 NULL NULL 2 +3 NULL NULL NULL 2 +drop table t1,t2,t3,t4,t5; +select a.a +from (select 1 as a) a, +(select 2 as b) b +cross join +(select 3 as c) c +left join +(select 4 as d) d +on 1; +a +1 +# # End of MariaDB 5.5 tests # # diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 632ee947940..4ea6b56bc0b 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1614,6 +1614,65 @@ eval $q; drop table t1,t2,t3,t4,s1,s2; --echo # +--echo # MDEV-20265: Mix of comma joins with JOIN expressions +--echo # (correction of the fix for MDEV-19421) +--echo # MDEV-20330: duplicate +--echo # + +create table t1 (a int); +insert into t1 values (7), (5), (3); +create table t2 (a int); +insert into t2 values (5), (1), (7); +create table t3 (a int); +insert into t3 values (2), (7), (3); +create table t4 (a int); +insert into t4 values (4), (7), (9), (5); +create table t5 (a int); +insert into t5 values (3), (7), (9), (2); + + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 left join t4 on t3.a=t4.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a +from t1, t2 join t3 right join t4 on t3.a=t4.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a; +eval explain extended $q; +eval $q; + +let $q= +select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a +from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a; +eval explain extended $q; +eval $q; + +drop table t1,t2,t3,t4,t5; + +select a.a +from (select 1 as a) a, + (select 2 as b) b + cross join + (select 3 as c) c + left join + (select 4 as d) d + on 1; + +--echo # --echo # End of MariaDB 5.5 tests --echo # |