revision-id: 62edf4ed553963fc40eef7087689e9d64d174538 (mariadb-10.1.39-31-g62edf4e) parent(s): 47637a3dd13d19e897a7cbfd1499f1bf3b2fdb2a author: Igor Babaev committer: Igor Babaev timestamp: 2019-05-14 23:50:08 -0700 message: MDEV-19258 RIGHT JOIN hangs in MariaDB This patch corrects the patch for the bug 10006. The latter incorrectly calculates the attribute TABLE_LIST::dep_tables for inner tables of outer joins that are to be converted into inner joins. As a result after the patch some valid join orders were not evaluated and the optimizer could choose an execution plan that was far from being optimal. --- mysql-test/r/join_outer.result | 139 +++++++++++++++++++++++++++++++++++- mysql-test/r/join_outer_jcl6.result | 139 +++++++++++++++++++++++++++++++++++- mysql-test/t/join_outer.test | 127 +++++++++++++++++++++++++++++++- sql/sql_select.cc | 16 ++++- 4 files changed, 416 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 6bd12db..3120a6d 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2447,7 +2447,7 @@ t1.b1+'0' t2.b2 + '0' 0 0 1 1 DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; # # MDEV-14779: using left join causes incorrect results with materialization and derived tables # @@ -2513,4 +2513,141 @@ ORDER BY tb1.i1; v2 DROP TABLE t1,t2; # end of 5.5 tests +# +# MDEV-19258: chained right joins all converted to inner joins +# +CREATE TABLE t1 ( +id int NOT NULL AUTO_INCREMENT, +timestamp bigint NOT NULL, +modifiedBy varchar(255) DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +REV int NOT NULL, +REVTYPE tinyint DEFAULT NULL, +profile_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t3 ( +id int NOT NULL, +REV int NOT NULL, +person_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t4 ( +id int NOT NULL, +REV int NOT NULL, +PRIMARY KEY (id,REV) +); +INSERT INTO t1 VALUES +(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), +(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), +(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), +(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), +(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), +(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), +(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), +(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), +(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), +(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), +(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), +(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), +(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), +(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), +(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), +(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), +(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), +(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), +(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), +(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), +(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), +(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), +(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), +(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), +(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), +(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), +(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), +(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), +(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), +(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); +INSERT INTO t2 VALUES +(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), +(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), +(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), +(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), +(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), +(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), +(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), +(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), +(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), +(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), +(12,605892,2,10219),(13,1,0,10220); +INSERT INTO t3 VALUES +(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), +(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), +(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), +(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), +(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), +(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), +(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), +(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); +INSERT INTO t4 VALUES +(300000,1),(300001,1),(300003,1),(300004,1), +(300005,1),(300005,688796),(300006,1),(300006,97697), +(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), +(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), +(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), +(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); +# This should have join order of t2,t3,t4,t1 +EXPLAIN EXTENDED SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t2`.`REVTYPE` = 2) and (`test`.`t4`.`id` = `test`.`t3`.`person_id`) and (`test`.`t3`.`id` = `test`.`t2`.`profile_id`) and (`test`.`t1`.`id` = `test`.`t2`.`REV`) and (`test`.`t1`.`timestamp` < 1294664900039) and (`test`.`t1`.`timestamp` > 1294644616416)) +SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +# This should have join order of t2,t3,t4,t1 with the same plan as above +# because all RIGHT JOIN operations are converted into INNER JOIN +EXPLAIN EXTENDED SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`REVTYPE` = 2) and (`test`.`t1`.`id` = `test`.`t2`.`REV`) and (`test`.`t3`.`id` = `test`.`t2`.`profile_id`) and (`test`.`t4`.`id` = `test`.`t3`.`person_id`) and (`test`.`t1`.`timestamp` < 1294664900039) and (`test`.`t1`.`timestamp` > 1294644616416)) +SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +DROP TABLE t1,t2,t3,t4; +# end of 10.1 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index faad874..44250a9 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2458,7 +2458,7 @@ t1.b1+'0' t2.b2 + '0' 0 0 1 1 DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; # # MDEV-14779: using left join causes incorrect results with materialization and derived tables # @@ -2524,6 +2524,143 @@ ORDER BY tb1.i1; v2 DROP TABLE t1,t2; # end of 5.5 tests +# +# MDEV-19258: chained right joins all converted to inner joins +# +CREATE TABLE t1 ( +id int NOT NULL AUTO_INCREMENT, +timestamp bigint NOT NULL, +modifiedBy varchar(255) DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +REV int NOT NULL, +REVTYPE tinyint DEFAULT NULL, +profile_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t3 ( +id int NOT NULL, +REV int NOT NULL, +person_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t4 ( +id int NOT NULL, +REV int NOT NULL, +PRIMARY KEY (id,REV) +); +INSERT INTO t1 VALUES +(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), +(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), +(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), +(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), +(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), +(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), +(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), +(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), +(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), +(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), +(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), +(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), +(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), +(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), +(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), +(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), +(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), +(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), +(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), +(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), +(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), +(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), +(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), +(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), +(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), +(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), +(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), +(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), +(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), +(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); +INSERT INTO t2 VALUES +(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), +(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), +(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), +(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), +(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), +(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), +(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), +(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), +(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), +(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), +(12,605892,2,10219),(13,1,0,10220); +INSERT INTO t3 VALUES +(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), +(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), +(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), +(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), +(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), +(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), +(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), +(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); +INSERT INTO t4 VALUES +(300000,1),(300001,1),(300003,1),(300004,1), +(300005,1),(300005,688796),(300006,1),(300006,97697), +(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), +(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), +(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), +(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); +# This should have join order of t2,t3,t4,t1 +EXPLAIN EXTENDED SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t2`.`REVTYPE` = 2) and (`test`.`t4`.`id` = `test`.`t3`.`person_id`) and (`test`.`t3`.`id` = `test`.`t2`.`profile_id`) and (`test`.`t1`.`id` = `test`.`t2`.`REV`) and (`test`.`t1`.`timestamp` < 1294664900039) and (`test`.`t1`.`timestamp` > 1294644616416)) +SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +# This should have join order of t2,t3,t4,t1 with the same plan as above +# because all RIGHT JOIN operations are converted into INNER JOIN +EXPLAIN EXTENDED SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`REVTYPE` = 2) and (`test`.`t1`.`id` = `test`.`t2`.`REV`) and (`test`.`t3`.`id` = `test`.`t2`.`profile_id`) and (`test`.`t4`.`id` = `test`.`t3`.`person_id`) and (`test`.`t1`.`timestamp` < 1294664900039) and (`test`.`t1`.`timestamp` > 1294644616416)) +SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +DROP TABLE t1,t2,t3,t4; +# end of 10.1 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 6d20c08..19cc5b8 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1976,7 +1976,7 @@ set @save_join_cache_level= @@join_cache_level; SET @@join_cache_level = 3; SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; --echo # --echo # MDEV-14779: using left join causes incorrect results with materialization and derived tables @@ -2042,4 +2042,129 @@ DROP TABLE t1,t2; --echo # end of 5.5 tests +--echo # +--echo # MDEV-19258: chained right joins all converted to inner joins +--echo # + + CREATE TABLE t1 ( + id int NOT NULL AUTO_INCREMENT, + timestamp bigint NOT NULL, + modifiedBy varchar(255) DEFAULT NULL, + PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + REV int NOT NULL, + REVTYPE tinyint DEFAULT NULL, + profile_id int DEFAULT NULL, + PRIMARY KEY (id,REV) +); + +CREATE TABLE t3 ( + id int NOT NULL, + REV int NOT NULL, + person_id int DEFAULT NULL, + PRIMARY KEY (id,REV) +); + +CREATE TABLE t4 ( + id int NOT NULL, + REV int NOT NULL, + PRIMARY KEY (id,REV) +); + +INSERT INTO t1 VALUES +(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), +(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), +(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), +(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), +(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), +(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), +(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), +(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), +(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), +(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), +(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), +(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), +(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), +(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), +(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), +(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), +(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), +(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), +(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), +(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), +(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), +(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), +(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), +(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), +(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), +(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), +(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), +(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), +(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), +(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); + + +INSERT INTO t2 VALUES +(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), +(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), +(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), +(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), +(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), +(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), +(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), +(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), +(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), +(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), +(12,605892,2,10219),(13,1,0,10220); + +INSERT INTO t3 VALUES +(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), +(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), +(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), +(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), +(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), +(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), +(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), +(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); + +INSERT INTO t4 VALUES +(300000,1),(300001,1),(300003,1),(300004,1), +(300005,1),(300005,688796),(300006,1),(300006,97697), +(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), +(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), +(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), +(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); + +let $q1= +SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id + INNER JOIN t3 ON t3.id=t2.profile_id + INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND + t2.REVTYPE=2; + +--echo # This should have join order of t2,t3,t4,t1 +eval EXPLAIN EXTENDED $q1; +eval $q1; + +let $q2= +SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id + RIGHT JOIN t3 ON t3.id=t2.profile_id + RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 + AND t2.REVTYPE=2; + +--echo # This should have join order of t2,t3,t4,t1 with the same plan as above +--echo # because all RIGHT JOIN operations are converted into INNER JOIN +eval EXPLAIN EXTENDED $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +--echo # end of 10.1 tests + SET optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cb8c042..242e787 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -14625,8 +14625,20 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, table->table->maybe_null= FALSE; table->outer_join= 0; if (!(straight_join || table->straight)) - table->dep_tables= table->embedding && !table->embedding->sj_subq_pred ? - table->embedding->dep_tables : 0; + { + table->dep_tables= 0; + TABLE_LIST *embedding= table->embedding; + while (embedding) + { + if (embedding->nested_join->join_list.head()->outer_join) + { + if (!embedding->sj_subq_pred) + table->dep_tables= embedding->dep_tables; + break; + } + embedding= embedding->embedding; + } + } if (table->on_expr) { /* Add ON expression to the WHERE or upper-level ON condition. */