1set @save_optimizer_switch_jcl6=@@optimizer_switch; 2set @@optimizer_switch='optimize_join_buffer_size=on'; 3set @@optimizer_switch='semijoin_with_cache=on'; 4set @@optimizer_switch='outer_join_with_cache=on'; 5set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 6set join_cache_level=6; 7set optimizer_search_depth=62; 8set @optimizer_switch_for_join_nested_test=@@optimizer_switch; 9set @join_cache_level_for_join_nested_test=@@join_cache_level; 10DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 11SET @save_optimizer_switch=@@optimizer_switch; 12SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off'); 13set join_cache_level=@join_cache_level_for_join_nested_test; 14CREATE TABLE t0 (a int, b int, c int); 15CREATE TABLE t1 (a int, b int, c int); 16CREATE TABLE t2 (a int, b int, c int); 17CREATE TABLE t3 (a int, b int, c int); 18CREATE TABLE t4 (a int, b int, c int); 19CREATE TABLE t5 (a int, b int, c int); 20CREATE TABLE t6 (a int, b int, c int); 21CREATE TABLE t7 (a int, b int, c int); 22CREATE TABLE t8 (a int, b int, c int); 23CREATE TABLE t9 (a int, b int, c int); 24INSERT INTO t0 VALUES (1,1,0), (1,2,0), (2,2,0); 25INSERT INTO t1 VALUES (1,3,0), (2,2,0), (3,2,0); 26INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); 27INSERT INTO t3 VALUES (1,2,0), (2,2,0); 28INSERT INTO t4 VALUES (3,2,0), (4,2,0); 29INSERT INTO t5 VALUES (3,1,0), (2,2,0), (3,3,0); 30INSERT INTO t6 VALUES (3,2,0), (6,2,0), (6,1,0); 31INSERT INTO t7 VALUES (1,1,0), (2,2,0); 32INSERT INTO t8 VALUES (0,2,0), (1,2,0); 33INSERT INTO t9 VALUES (1,1,0), (1,2,0), (3,3,0); 34SELECT t2.a,t2.b 35FROM t2; 36a b 373 3 384 2 395 3 40SELECT t3.a,t3.b 41FROM t3; 42a b 431 2 442 2 45SELECT t4.a,t4.b 46FROM t4; 47a b 483 2 494 2 50SELECT t3.a,t3.b,t4.a,t4.b 51FROM t3,t4; 52a b a b 531 2 3 2 542 2 3 2 551 2 4 2 562 2 4 2 57SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 58FROM t2 59LEFT JOIN 60(t3, t4) 61ON t2.b=t4.b; 62a b a b a b 634 2 1 2 3 2 644 2 2 2 3 2 654 2 1 2 4 2 664 2 2 2 4 2 673 3 NULL NULL NULL NULL 685 3 NULL NULL NULL NULL 69SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 70FROM t2 71LEFT JOIN 72(t3, t4) 73ON t3.a=1 AND t2.b=t4.b; 74a b a b a b 754 2 1 2 3 2 764 2 1 2 4 2 773 3 NULL NULL NULL NULL 785 3 NULL NULL NULL NULL 79EXPLAIN EXTENDED 80SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 81FROM t2 82LEFT JOIN 83(t3, t4) 84ON t2.b=t4.b 85WHERE t3.a=1 OR t3.c IS NULL; 86id select_type table type possible_keys key key_len ref rows filtered Extra 871 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 881 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 891 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 90Warnings: 91Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t3`.`a` = 1 or `test`.`t3`.`c` is null 92SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 93FROM t2 94LEFT JOIN 95(t3, t4) 96ON t2.b=t4.b 97WHERE t3.a=1 OR t3.c IS NULL; 98a b a b a b 994 2 1 2 3 2 1004 2 1 2 4 2 1013 3 NULL NULL NULL NULL 1025 3 NULL NULL NULL NULL 103SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 104FROM t2 105LEFT JOIN 106(t3, t4) 107ON t2.b=t4.b 108WHERE t3.a>1 OR t3.c IS NULL; 109a b a b a b 1104 2 2 2 3 2 1114 2 2 2 4 2 1123 3 NULL NULL NULL NULL 1135 3 NULL NULL NULL NULL 114SELECT t5.a,t5.b 115FROM t5; 116a b 1173 1 1182 2 1193 3 120SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 121FROM t3,t4,t5; 122a b a b a b 1231 2 3 2 3 1 1242 2 3 2 3 1 1251 2 4 2 3 1 1262 2 4 2 3 1 1271 2 3 2 2 2 1282 2 3 2 2 2 1291 2 4 2 2 2 1302 2 4 2 2 2 1311 2 3 2 3 3 1322 2 3 2 3 3 1331 2 4 2 3 3 1342 2 4 2 3 3 135SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 136FROM t2 137LEFT JOIN 138(t3, t4, t5) 139ON t2.b=t4.b; 140a b a b a b a b 1414 2 1 2 3 2 3 1 1424 2 2 2 3 2 3 1 1434 2 1 2 4 2 3 1 1444 2 2 2 4 2 3 1 1454 2 1 2 3 2 2 2 1464 2 2 2 3 2 2 2 1474 2 1 2 4 2 2 2 1484 2 2 2 4 2 2 2 1494 2 1 2 3 2 3 3 1504 2 2 2 3 2 3 3 1514 2 1 2 4 2 3 3 1524 2 2 2 4 2 3 3 1533 3 NULL NULL NULL NULL NULL NULL 1545 3 NULL NULL NULL NULL NULL NULL 155EXPLAIN EXTENDED 156SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 157FROM t2 158LEFT JOIN 159(t3, t4, t5) 160ON t2.b=t4.b 161WHERE t3.a>1 OR t3.c IS NULL; 162id select_type table type possible_keys key key_len ref rows filtered Extra 1631 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1641 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 1651 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1661 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) 167Warnings: 168Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(`test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t3`.`a` > 1 or `test`.`t3`.`c` is null 169SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 170FROM t2 171LEFT JOIN 172(t3, t4, t5) 173ON t2.b=t4.b 174WHERE t3.a>1 OR t3.c IS NULL; 175a b a b a b a b 1764 2 2 2 3 2 3 1 1774 2 2 2 4 2 3 1 1784 2 2 2 3 2 2 2 1794 2 2 2 4 2 2 2 1804 2 2 2 3 2 3 3 1814 2 2 2 4 2 3 3 1823 3 NULL NULL NULL NULL NULL NULL 1835 3 NULL NULL NULL NULL NULL NULL 184EXPLAIN EXTENDED 185SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 186FROM t2 187LEFT JOIN 188(t3, t4, t5) 189ON t2.b=t4.b 190WHERE (t3.a>1 OR t3.c IS NULL) AND 191(t5.a<3 OR t5.c IS NULL); 192id select_type table type possible_keys key key_len ref rows filtered Extra 1931 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1941 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 1951 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1961 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 197Warnings: 198Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(`test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where (`test`.`t3`.`a` > 1 or `test`.`t3`.`c` is null) and (`test`.`t5`.`a` < 3 or `test`.`t5`.`c` is null) 199SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 200FROM t2 201LEFT JOIN 202(t3, t4, t5) 203ON t2.b=t4.b 204WHERE (t3.a>1 OR t3.c IS NULL) AND 205(t5.a<3 OR t5.c IS NULL); 206a b a b a b a b 2074 2 2 2 3 2 2 2 2084 2 2 2 4 2 2 2 2093 3 NULL NULL NULL NULL NULL NULL 2105 3 NULL NULL NULL NULL NULL NULL 211SELECT t6.a,t6.b 212FROM t6; 213a b 2143 2 2156 2 2166 1 217SELECT t7.a,t7.b 218FROM t7; 219a b 2201 1 2212 2 222SELECT t6.a,t6.b,t7.a,t7.b 223FROM t6,t7; 224a b a b 2253 2 1 1 2263 2 2 2 2276 2 1 1 2286 2 2 2 2296 1 1 1 2306 1 2 2 231SELECT t8.a,t8.b 232FROM t8; 233a b 2340 2 2351 2 236EXPLAIN EXTENDED 237SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 238FROM (t6, t7) 239LEFT JOIN 240t8 241ON t7.b=t8.b AND t6.b < 10; 242id select_type table type possible_keys key key_len ref rows filtered Extra 2431 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 2441 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 2451 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 246Warnings: 247Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t7`.`b` and `test`.`t6`.`b` < 10 and `test`.`t7`.`b` is not null) where 1 248SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 249FROM (t6, t7) 250LEFT JOIN 251t8 252ON t7.b=t8.b AND t6.b < 10; 253a b a b a b 2543 2 2 2 0 2 2556 2 2 2 0 2 2566 1 2 2 0 2 2573 2 2 2 1 2 2586 2 2 2 1 2 2596 1 2 2 1 2 2603 2 1 1 NULL NULL 2616 2 1 1 NULL NULL 2626 1 1 1 NULL NULL 263SELECT t5.a,t5.b 264FROM t5; 265a b 2663 1 2672 2 2683 3 269SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 270FROM t5 271LEFT JOIN 272( 273(t6, t7) 274LEFT JOIN 275t8 276ON t7.b=t8.b AND t6.b < 10 277) 278ON t6.b >= 2 AND t5.b=t7.b; 279a b a b a b a b 2802 2 3 2 2 2 0 2 2812 2 6 2 2 2 0 2 2822 2 3 2 2 2 1 2 2832 2 6 2 2 2 1 2 2843 1 3 2 1 1 NULL NULL 2853 1 6 2 1 1 NULL NULL 2863 3 NULL NULL NULL NULL NULL NULL 287SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 288FROM t5 289LEFT JOIN 290( 291(t6, t7) 292LEFT JOIN 293t8 294ON t7.b=t8.b AND t6.b < 10 295) 296ON t6.b >= 2 AND t5.b=t7.b AND 297(t8.a < 1 OR t8.c IS NULL); 298a b a b a b a b 2992 2 3 2 2 2 0 2 3002 2 6 2 2 2 0 2 3013 1 3 2 1 1 NULL NULL 3023 1 6 2 1 1 NULL NULL 3033 3 NULL NULL NULL NULL NULL NULL 304SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 305FROM t2 306LEFT JOIN 307(t3, t4) 308ON t3.a=1 AND t2.b=t4.b; 309a b a b a b 3104 2 1 2 3 2 3114 2 1 2 4 2 3123 3 NULL NULL NULL NULL 3135 3 NULL NULL NULL NULL 314SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 315t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 316FROM t2 317LEFT JOIN 318(t3, t4) 319ON t3.a=1 AND t2.b=t4.b, 320t5 321LEFT JOIN 322( 323(t6, t7) 324LEFT JOIN 325t8 326ON t7.b=t8.b AND t6.b < 10 327) 328ON t6.b >= 2 AND t5.b=t7.b; 329a b a b a b a b a b a b a b 3304 2 1 2 3 2 2 2 3 2 2 2 0 2 3314 2 1 2 4 2 2 2 3 2 2 2 0 2 3324 2 1 2 3 2 2 2 6 2 2 2 0 2 3334 2 1 2 4 2 2 2 6 2 2 2 0 2 3344 2 1 2 3 2 2 2 3 2 2 2 1 2 3354 2 1 2 4 2 2 2 3 2 2 2 1 2 3364 2 1 2 3 2 2 2 6 2 2 2 1 2 3374 2 1 2 4 2 2 2 6 2 2 2 1 2 3384 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 3394 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 3404 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 3414 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 3424 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 3434 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 3443 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3455 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3463 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 3475 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 3483 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3495 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3503 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 3515 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 3523 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 3535 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 3543 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 3555 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 3563 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 3575 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 358SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 359t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 360FROM t2 361LEFT JOIN 362(t3, t4) 363ON t3.a=1 AND t2.b=t4.b, 364t5 365LEFT JOIN 366( 367(t6, t7) 368LEFT JOIN 369t8 370ON t7.b=t8.b AND t6.b < 10 371) 372ON t6.b >= 2 AND t5.b=t7.b 373WHERE t2.a > 3 AND 374(t6.a < 6 OR t6.c IS NULL); 375a b a b a b a b a b a b a b 3764 2 1 2 3 2 2 2 3 2 2 2 0 2 3774 2 1 2 4 2 2 2 3 2 2 2 0 2 3784 2 1 2 3 2 2 2 3 2 2 2 1 2 3794 2 1 2 4 2 2 2 3 2 2 2 1 2 3804 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 3814 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 3824 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 3834 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 3845 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3855 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3865 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 3875 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 388SELECT t1.a,t1.b 389FROM t1; 390a b 3911 3 3922 2 3933 2 394SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 395t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 396FROM t1 397LEFT JOIN 398( 399t2 400LEFT JOIN 401(t3, t4) 402ON t3.a=1 AND t2.b=t4.b, 403t5 404LEFT JOIN 405( 406(t6, t7) 407LEFT JOIN 408t8 409ON t7.b=t8.b AND t6.b < 10 410) 411ON t6.b >= 2 AND t5.b=t7.b 412) 413ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 414(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 415(t1.a != 2); 416a b a b a b a b a b a b a b a b 4173 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 4183 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 4193 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 4203 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 4213 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 4223 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 4233 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2 4243 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2 4251 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4263 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4271 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 4283 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 4291 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 4303 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 4311 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 4323 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 4331 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4343 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4351 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 4363 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 4371 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4383 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4391 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4403 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4411 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4423 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4431 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4443 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4451 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4463 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4471 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4483 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4491 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4503 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4511 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4523 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4531 3 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4543 2 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4551 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4563 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4571 3 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4583 2 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4591 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4603 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4611 3 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4623 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4631 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4643 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4652 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 466SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 467t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 468FROM t1 469LEFT JOIN 470( 471t2 472LEFT JOIN 473(t3, t4) 474ON t3.a=1 AND t2.b=t4.b, 475t5 476LEFT JOIN 477( 478(t6, t7) 479LEFT JOIN 480t8 481ON t7.b=t8.b AND t6.b < 10 482) 483ON t6.b >= 2 AND t5.b=t7.b 484) 485ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 486(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 487(t1.a != 2) 488WHERE (t2.a >= 4 OR t2.c IS NULL); 489a b a b a b a b a b a b a b a b 4903 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 4913 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 4923 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 4933 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 4943 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 4953 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 4963 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2 4973 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2 4981 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4993 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 5001 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 5013 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 5021 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 5033 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 5041 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 5053 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 5061 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 5073 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 5081 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 5093 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 5101 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 5113 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 5121 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 5133 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 5141 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 5153 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 5161 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 5173 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 5181 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 5193 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 5201 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 5213 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 5221 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 5233 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 5242 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 525SELECT t0.a,t0.b 526FROM t0; 527a b 5281 1 5291 2 5302 2 531EXPLAIN EXTENDED 532SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 533t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 534FROM t0,t1 535LEFT JOIN 536( 537t2 538LEFT JOIN 539(t3, t4) 540ON t3.a=1 AND t2.b=t4.b, 541t5 542LEFT JOIN 543( 544(t6, t7) 545LEFT JOIN 546t8 547ON t7.b=t8.b AND t6.b < 10 548) 549ON t6.b >= 2 AND t5.b=t7.b 550) 551ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 552(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 553(t1.a != 2) 554WHERE t0.a=1 AND 555t0.b=t1.b AND 556(t2.a >= 4 OR t2.c IS NULL); 557id select_type table type possible_keys key key_len ref rows filtered Extra 5581 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 5591 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 5601 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 5611 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 5621 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 5631 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) 5641 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 5651 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 5661 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 567Warnings: 568Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) 569SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 570t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 571FROM t0,t1 572LEFT JOIN 573( 574t2 575LEFT JOIN 576(t3, t4) 577ON t3.a=1 AND t2.b=t4.b, 578t5 579LEFT JOIN 580( 581(t6, t7) 582LEFT JOIN 583t8 584ON t7.b=t8.b AND t6.b < 10 585) 586ON t6.b >= 2 AND t5.b=t7.b 587) 588ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 589(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 590(t1.a != 2) 591WHERE t0.a=1 AND 592t0.b=t1.b AND 593(t2.a >= 4 OR t2.c IS NULL); 594a b a b a b a b a b a b a b a b a b 5951 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 5961 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 5971 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 5981 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 5991 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 6001 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 6011 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2 6021 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2 6031 2 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 6041 2 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 6051 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 6061 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 6071 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 6081 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 6091 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 6101 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 6111 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 6121 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 6131 2 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 6141 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 6151 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 6161 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 617EXPLAIN EXTENDED 618SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 619t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 620FROM t0,t1 621LEFT JOIN 622( 623t2 624LEFT JOIN 625(t3, t4) 626ON t3.a=1 AND t2.b=t4.b, 627t5 628LEFT JOIN 629( 630(t6, t7) 631LEFT JOIN 632t8 633ON t7.b=t8.b AND t6.b < 10 634) 635ON t6.b >= 2 AND t5.b=t7.b 636) 637ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 638(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 639(t1.a != 2), 640t9 641WHERE t0.a=1 AND 642t0.b=t1.b AND 643(t2.a >= 4 OR t2.c IS NULL) AND 644(t3.a < 5 OR t3.c IS NULL) AND 645(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 646(t5.a >=2 OR t5.c IS NULL) AND 647(t6.a >=4 OR t6.c IS NULL) AND 648(t7.a <= 2 OR t7.c IS NULL) AND 649(t8.a < 1 OR t8.c IS NULL) AND 650(t8.b=t9.b OR t8.c IS NULL) AND 651(t9.a=1); 652id select_type table type possible_keys key key_len ref rows filtered Extra 6531 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 6541 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 6551 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 6561 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 6571 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 6581 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 6591 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 6601 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 6611 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 6621 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 663Warnings: 664Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t9`.`b` = `test`.`t8`.`b` or `test`.`t8`.`c` is null) 665SELECT t9.a,t9.b 666FROM t9; 667a b 6681 1 6691 2 6703 3 671SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 672t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 673FROM t0,t1 674LEFT JOIN 675( 676t2 677LEFT JOIN 678(t3, t4) 679ON t3.a=1 AND t2.b=t4.b, 680t5 681LEFT JOIN 682( 683(t6, t7) 684LEFT JOIN 685t8 686ON t7.b=t8.b AND t6.b < 10 687) 688ON t6.b >= 2 AND t5.b=t7.b 689) 690ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 691(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 692(t1.a != 2), 693t9 694WHERE t0.a=1 AND 695t0.b=t1.b AND 696(t2.a >= 4 OR t2.c IS NULL) AND 697(t3.a < 5 OR t3.c IS NULL) AND 698(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 699(t5.a >=2 OR t5.c IS NULL) AND 700(t6.a >=4 OR t6.c IS NULL) AND 701(t7.a <= 2 OR t7.c IS NULL) AND 702(t8.a < 1 OR t8.c IS NULL) AND 703(t8.b=t9.b OR t8.c IS NULL) AND 704(t9.a=1); 705a b a b a b a b a b a b a b a b a b a b 7061 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 7071 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 7081 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 7091 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 7101 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 7111 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 7121 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 7131 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 7141 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 7151 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 7161 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 7171 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 7181 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 7191 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 7201 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 7211 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 7221 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 723SELECT t1.a,t1.b 724FROM t1; 725a b 7261 3 7272 2 7283 2 729SELECT t2.a,t2.b 730FROM t2; 731a b 7323 3 7334 2 7345 3 735SELECT t3.a,t3.b 736FROM t3; 737a b 7381 2 7392 2 740SELECT t2.a,t2.b,t3.a,t3.b 741FROM t2 742LEFT JOIN 743t3 744ON t2.b=t3.b; 745a b a b 7464 2 1 2 7474 2 2 2 7483 3 NULL NULL 7495 3 NULL NULL 750SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b 751FROM t1, t2 752LEFT JOIN 753t3 754ON t2.b=t3.b 755WHERE t1.a <= 2; 756a b a b a b 7571 3 4 2 1 2 7582 2 4 2 1 2 7591 3 4 2 2 2 7602 2 4 2 2 2 7611 3 3 3 NULL NULL 7622 2 3 3 NULL NULL 7631 3 5 3 NULL NULL 7642 2 5 3 NULL NULL 765SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b 766FROM t1, t3 767RIGHT JOIN 768t2 769ON t2.b=t3.b 770WHERE t1.a <= 2; 771a b a b a b 7721 3 4 2 1 2 7732 2 4 2 1 2 7741 3 4 2 2 2 7752 2 4 2 2 2 7761 3 3 3 NULL NULL 7772 2 3 3 NULL NULL 7781 3 5 3 NULL NULL 7792 2 5 3 NULL NULL 780SELECT t3.a,t3.b,t4.a,t4.b 781FROM t3,t4; 782a b a b 7831 2 3 2 7842 2 3 2 7851 2 4 2 7862 2 4 2 787SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 788FROM t2 789LEFT JOIN 790(t3, t4) 791ON t3.a=1 AND t2.b=t4.b; 792a b a b a b 7934 2 1 2 3 2 7944 2 1 2 4 2 7953 3 NULL NULL NULL NULL 7965 3 NULL NULL NULL NULL 797SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 798FROM t1, t2 799LEFT JOIN 800(t3, t4) 801ON t3.a=1 AND t2.b=t4.b 802WHERE t1.a <= 2; 803a b a b a b a b 8041 3 4 2 1 2 3 2 8052 2 4 2 1 2 3 2 8061 3 4 2 1 2 4 2 8072 2 4 2 1 2 4 2 8081 3 3 3 NULL NULL NULL NULL 8092 2 3 3 NULL NULL NULL NULL 8101 3 5 3 NULL NULL NULL NULL 8112 2 5 3 NULL NULL NULL NULL 812SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 813FROM t1, (t3, t4) 814RIGHT JOIN 815t2 816ON t3.a=1 AND t2.b=t4.b 817WHERE t1.a <= 2; 818a b a b a b a b 8191 3 4 2 1 2 3 2 8202 2 4 2 1 2 3 2 8211 3 4 2 1 2 4 2 8222 2 4 2 1 2 4 2 8231 3 3 3 NULL NULL NULL NULL 8242 2 3 3 NULL NULL NULL NULL 8251 3 5 3 NULL NULL NULL NULL 8262 2 5 3 NULL NULL NULL NULL 827SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 828FROM t1, (t3, t4) 829RIGHT JOIN 830t2 831ON t3.a=1 AND t2.b=t4.b 832WHERE t1.a <= 2; 833a b a b a b a b 8341 3 4 2 1 2 3 2 8352 2 4 2 1 2 3 2 8361 3 4 2 1 2 4 2 8372 2 4 2 1 2 4 2 8381 3 3 3 NULL NULL NULL NULL 8392 2 3 3 NULL NULL NULL NULL 8401 3 5 3 NULL NULL NULL NULL 8412 2 5 3 NULL NULL NULL NULL 842EXPLAIN EXTENDED 843SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 844FROM t1, (t3, t4) 845RIGHT JOIN 846t2 847ON t3.a=1 AND t2.b=t4.b 848WHERE t1.a <= 2; 849id select_type table type possible_keys key key_len ref rows filtered Extra 8501 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 8511 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 8521 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 8531 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 854Warnings: 855Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t1`.`a` <= 2 856INSERT INTO t2 VALUES (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0); 857CREATE INDEX idx_b ON t2(b); 858EXPLAIN EXTENDED 859SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 860FROM (t3,t4) 861LEFT JOIN 862(t1,t2) 863ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0; 864id select_type table type possible_keys key key_len ref rows filtered Extra 8651 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 8661 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 8671 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 8681 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) 869Warnings: 870Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t3`.`b` and `test`.`t2`.`b` = `test`.`t3`.`b` and `test`.`t2`.`a` > 0 and `test`.`t3`.`b` is not null) where 1 871SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 872FROM (t3,t4) 873LEFT JOIN 874(t1,t2) 875ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0; 876a b a b a b 8774 2 1 2 3 2 8784 2 1 2 4 2 8794 2 1 2 3 2 8804 2 1 2 4 2 8814 2 1 2 3 2 8824 2 1 2 4 2 883NULL NULL 2 2 3 2 884NULL NULL 2 2 4 2 885EXPLAIN EXTENDED 886SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 887t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 888FROM t0,t1 889LEFT JOIN 890( 891t2 892LEFT JOIN 893(t3, t4) 894ON t3.a=1 AND t2.b=t4.b AND t2.a>0, 895t5 896LEFT JOIN 897( 898(t6, t7) 899LEFT JOIN 900t8 901ON t7.b=t8.b AND t6.b < 10 902) 903ON t6.b >= 2 AND t5.b=t7.b 904) 905ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 906(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 907(t1.a != 2), 908t9 909WHERE t0.a=1 AND 910t0.b=t1.b AND 911(t2.a >= 4 OR t2.c IS NULL) AND 912(t3.a < 5 OR t3.c IS NULL) AND 913(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 914(t5.a >=2 OR t5.c IS NULL) AND 915(t6.a >=4 OR t6.c IS NULL) AND 916(t7.a <= 2 OR t7.c IS NULL) AND 917(t8.a < 1 OR t8.c IS NULL) AND 918(t8.b=t9.b OR t8.c IS NULL) AND 919(t9.a=1); 920id select_type table type possible_keys key key_len ref rows filtered Extra 9211 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 9221 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 9231 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 9241 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 9251 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 9261 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 9271 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 9281 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 9291 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 9301 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 931Warnings: 932Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t3`.`b` = `test`.`t4`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t9`.`b` = `test`.`t8`.`b` or `test`.`t8`.`c` is null) 933INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0); 934INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0); 935CREATE INDEX idx_b ON t4(b); 936CREATE INDEX idx_b ON t5(b); 937EXPLAIN EXTENDED 938SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 939t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 940FROM t0,t1 941LEFT JOIN 942( 943t2 944LEFT JOIN 945(t3, t4) 946ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0, 947t5 948LEFT JOIN 949( 950(t6, t7) 951LEFT JOIN 952t8 953ON t7.b=t8.b AND t6.b < 10 954) 955ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 956) 957ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 958(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 959(t1.a != 2), 960t9 961WHERE t0.a=1 AND 962t0.b=t1.b AND 963(t2.a >= 4 OR t2.c IS NULL) AND 964(t3.a < 5 OR t3.c IS NULL) AND 965(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 966(t5.a >=2 OR t5.c IS NULL) AND 967(t6.a >=4 OR t6.c IS NULL) AND 968(t7.a <= 2 OR t7.c IS NULL) AND 969(t8.a < 1 OR t8.c IS NULL) AND 970(t8.b=t9.b OR t8.c IS NULL) AND 971(t9.a=1); 972id select_type table type possible_keys key key_len ref rows filtered Extra 9731 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 9741 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 9751 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 9761 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 9771 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 9781 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 9791 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 9801 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 9811 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 9821 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 983Warnings: 984Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t4`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) 985INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0); 986CREATE INDEX idx_b ON t8(b); 987EXPLAIN EXTENDED 988SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 989t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 990FROM t0,t1 991LEFT JOIN 992( 993t2 994LEFT JOIN 995(t3, t4) 996ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0, 997t5 998LEFT JOIN 999( 1000(t6, t7) 1001LEFT JOIN 1002t8 1003ON t7.b=t8.b AND t6.b < 10 AND t8.a>=0 1004) 1005ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 1006) 1007ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 1008(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 1009(t1.a != 2), 1010t9 1011WHERE t0.a=1 AND 1012t0.b=t1.b AND 1013(t2.a >= 4 OR t2.c IS NULL) AND 1014(t3.a < 5 OR t3.c IS NULL) AND 1015(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 1016(t5.a >=2 OR t5.c IS NULL) AND 1017(t6.a >=4 OR t6.c IS NULL) AND 1018(t7.a <= 2 OR t7.c IS NULL) AND 1019(t8.a < 1 OR t8.c IS NULL) AND 1020(t8.b=t9.b OR t8.c IS NULL) AND 1021(t9.a=1); 1022id select_type table type possible_keys key key_len ref rows filtered Extra 10231 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 10241 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 10251 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 10261 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 10271 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 10281 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 10291 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 10301 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 10311 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 10321 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1033Warnings: 1034Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t4`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t8`.`a` >= 0 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) 1035INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); 1036CREATE INDEX idx_b ON t1(b); 1037CREATE INDEX idx_a ON t0(a); 1038EXPLAIN EXTENDED 1039SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 1040t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 1041FROM t0,t1 1042LEFT JOIN 1043( 1044t2 1045LEFT JOIN 1046(t3, t4) 1047ON t3.a=1 AND t2.b=t4.b, 1048t5 1049LEFT JOIN 1050( 1051(t6, t7) 1052LEFT JOIN 1053t8 1054ON t7.b=t8.b AND t6.b < 10 1055) 1056ON t6.b >= 2 AND t5.b=t7.b 1057) 1058ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 1059(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 1060(t1.a != 2) AND t1.a>0, 1061t9 1062WHERE t0.a=1 AND 1063t0.b=t1.b AND 1064(t2.a >= 4 OR t2.c IS NULL) AND 1065(t3.a < 5 OR t3.c IS NULL) AND 1066(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 1067(t5.a >=2 OR t5.c IS NULL) AND 1068(t6.a >=4 OR t6.c IS NULL) AND 1069(t7.a <= 2 OR t7.c IS NULL) AND 1070(t8.a < 1 OR t8.c IS NULL) AND 1071(t8.b=t9.b OR t8.c IS NULL) AND 1072(t9.a=1); 1073id select_type table type possible_keys key key_len ref rows filtered Extra 10741 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 10751 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 10761 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 10771 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 10781 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 10791 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 10801 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 10811 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 10821 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 10831 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1084Warnings: 1085Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2 and `test`.`t1`.`a` > 0) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t8`.`b` = `test`.`t9`.`b` or `test`.`t8`.`c` is null) 1086SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 1087t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 1088FROM t0,t1 1089LEFT JOIN 1090( 1091t2 1092LEFT JOIN 1093(t3, t4) 1094ON t3.a=1 AND t2.b=t4.b, 1095t5 1096LEFT JOIN 1097( 1098(t6, t7) 1099LEFT JOIN 1100t8 1101ON t7.b=t8.b AND t6.b < 10 1102) 1103ON t6.b >= 2 AND t5.b=t7.b 1104) 1105ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 1106(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 1107(t1.a != 2) AND t1.a>0, 1108t9 1109WHERE t0.a=1 AND 1110t0.b=t1.b AND 1111(t2.a >= 4 OR t2.c IS NULL) AND 1112(t3.a < 5 OR t3.c IS NULL) AND 1113(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 1114(t5.a >=2 OR t5.c IS NULL) AND 1115(t6.a >=4 OR t6.c IS NULL) AND 1116(t7.a <= 2 OR t7.c IS NULL) AND 1117(t8.a < 1 OR t8.c IS NULL) AND 1118(t8.b=t9.b OR t8.c IS NULL) AND 1119(t9.a=1); 1120a b a b a b a b a b a b a b a b a b a b 11211 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 11221 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 11231 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 11241 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 11251 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 11261 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 11271 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 11281 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 11291 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 11301 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 11311 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 11321 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 11331 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 11341 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 11351 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 11361 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 11371 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 1138SELECT t2.a,t2.b 1139FROM t2; 1140a b 11413 3 11424 2 11435 3 1144-1 9 1145-3 10 1146-2 8 1147-4 11 1148-5 15 1149SELECT t3.a,t3.b 1150FROM t3; 1151a b 11521 2 11532 2 1154SELECT t2.a,t2.b,t3.a,t3.b 1155FROM t2 LEFT JOIN t3 ON t2.b=t3.b 1156WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL); 1157a b a b 11584 2 1 2 11594 2 2 2 11605 3 NULL NULL 1161SELECT t2.a,t2.b,t3.a,t3.b 1162FROM t2 LEFT JOIN t3 ON t2.b=t3.b 1163WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL); 1164a b a b 11654 2 1 2 11664 2 2 2 11675 3 NULL NULL 1168ALTER TABLE t3 1169CHANGE COLUMN a a1 int, 1170CHANGE COLUMN c c1 int; 1171SELECT t2.a,t2.b,t3.a1,t3.b 1172FROM t2 LEFT JOIN t3 ON t2.b=t3.b 1173WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL); 1174a b a1 b 11754 2 1 2 11764 2 2 2 11775 3 NULL NULL 1178SELECT t2.a,t2.b,t3.a1,t3.b 1179FROM t2 NATURAL LEFT JOIN t3 1180WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL); 1181a b a1 b 11824 2 1 2 11834 2 2 2 11845 3 NULL NULL 1185DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 1186CREATE TABLE t1 (a int); 1187CREATE TABLE t2 (a int); 1188CREATE TABLE t3 (a int); 1189INSERT INTO t1 VALUES (1); 1190INSERT INTO t2 VALUES (2); 1191INSERT INTO t3 VALUES (2); 1192INSERT INTO t1 VALUES (2); 1193SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a; 1194a a a 11952 2 2 11961 NULL NULL 1197SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; 1198a a a 11992 2 2 12001 NULL NULL 1201DELETE FROM t1 WHERE a=2; 1202SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; 1203a a a 12041 NULL NULL 1205DELETE FROM t2; 1206SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; 1207a a a 12081 NULL NULL 1209DROP TABLE t1,t2,t3; 1210CREATE TABLE t1(a int, key (a)); 1211CREATE TABLE t2(b int, key (b)); 1212CREATE TABLE t3(c int, key (c)); 1213INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), 1214(10), (11), (12), (13), (14), (15), (16), (17), (18), (19); 1215INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), 1216(10), (11), (12), (13), (14), (15), (16), (17), (18), (19); 1217INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5); 1218EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c; 1219id select_type table type possible_keys key key_len ref rows Extra 12201 SIMPLE t1 index NULL a 5 NULL 21 Using index 12211 SIMPLE t2 range b b 5 NULL 3 Using where; Using index 12221 SIMPLE t3 ref c c 5 test.t2.b 2 Using index 1223EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1224id select_type table type possible_keys key key_len ref rows Extra 12251 SIMPLE t1 index NULL a 5 NULL 21 Using index 12261 SIMPLE t2 range b b 5 NULL 3 Using where; Using index 12271 SIMPLE t3 ref c c 5 test.t2.b 2 Using index 1228SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1229a b c 1230NULL 0 0 1231NULL 1 1 1232NULL 2 2 12330 0 0 12340 1 1 12350 2 2 12361 0 0 12371 1 1 12381 2 2 12392 0 0 12402 1 1 12412 2 2 12423 0 0 12433 1 1 12443 2 2 12454 0 0 12464 1 1 12474 2 2 12485 0 0 12495 1 1 12505 2 2 12516 0 0 12526 1 1 12536 2 2 12547 0 0 12557 1 1 12567 2 2 12578 0 0 12588 1 1 12598 2 2 12609 0 0 12619 1 1 12629 2 2 126310 0 0 126410 1 1 126510 2 2 126611 0 0 126711 1 1 126811 2 2 126912 0 0 127012 1 1 127112 2 2 127213 0 0 127313 1 1 127413 2 2 127514 0 0 127614 1 1 127714 2 2 127815 0 0 127915 1 1 128015 2 2 128116 0 0 128216 1 1 128316 2 2 128417 0 0 128517 1 1 128617 2 2 128718 0 0 128818 1 1 128918 2 2 129019 0 0 129119 1 1 129219 2 2 1293DELETE FROM t3; 1294EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1295id select_type table type possible_keys key key_len ref rows Extra 12961 SIMPLE t1 index NULL a 5 NULL 21 Using index 12971 SIMPLE t3 index c c 5 NULL 0 Using where; Using index 12981 SIMPLE t2 ref b b 5 test.t3.c 2 Using index 1299SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1300a b c 1301NULL NULL NULL 13020 NULL NULL 13031 NULL NULL 13042 NULL NULL 13053 NULL NULL 13064 NULL NULL 13075 NULL NULL 13086 NULL NULL 13097 NULL NULL 13108 NULL NULL 13119 NULL NULL 131210 NULL NULL 131311 NULL NULL 131412 NULL NULL 131513 NULL NULL 131614 NULL NULL 131715 NULL NULL 131816 NULL NULL 131917 NULL NULL 132018 NULL NULL 132119 NULL NULL 1322DROP TABLE t1,t2,t3; 1323CREATE TABLE t1 (c11 int); 1324CREATE TABLE t2 (c21 int); 1325CREATE TABLE t3 (c31 int); 1326INSERT INTO t1 VALUES (4), (5); 1327SELECT * FROM t1 LEFT JOIN t2 ON c11=c21; 1328c11 c21 13294 NULL 13305 NULL 1331EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21; 1332id select_type table type possible_keys key key_len ref rows Extra 13331 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 13341 SIMPLE t1 ALL NULL NULL NULL NULL 2 1335SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; 1336c11 c21 c31 13374 NULL NULL 13385 NULL NULL 1339EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; 1340id select_type table type possible_keys key key_len ref rows Extra 13411 SIMPLE t1 ALL NULL NULL NULL NULL 2 13421 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (flat, BNLH join) 13431 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (incremental, BNLH join) 1344DROP TABLE t1,t2,t3; 1345CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); 1346INSERT INTO t1 VALUES (23, 2340), (26, 9900); 1347CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2)); 1348INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr'); 1349create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL); 1350INSERT INTO t3 VALUES (3,23), (6,26); 1351CREATE TABLE t4 (groupid int(12)); 1352INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6); 1353SELECT * FROM 1354(SELECT DISTINCT gl.groupid, gp.price 1355FROM t4 gl 1356LEFT JOIN 1357(t3 g INNER JOIN t2 p ON g.goodsid = p.goods 1358INNER JOIN t1 gp ON p.goods = gp.goods) 1359ON gl.groupid = g.groupid and p.shop = 'fr') t; 1360groupid price 13613 2340 13626 9900 13631 NULL 13642 NULL 13654 NULL 13665 NULL 1367CREATE VIEW v1 AS 1368SELECT g.groupid groupid, p.goods goods, 1369p.name name, p.shop shop, 1370gp.price price 1371FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods 1372INNER JOIN t1 gp on p.goods = gp.goods; 1373CREATE VIEW v2 AS 1374SELECT DISTINCT g.groupid, fr.price 1375FROM t4 g 1376LEFT JOIN 1377v1 fr on g.groupid = fr.groupid and fr.shop = 'fr'; 1378SELECT * FROM v2; 1379groupid price 13803 2340 13816 9900 13821 NULL 13832 NULL 13844 NULL 13855 NULL 1386SELECT * FROM 1387(SELECT DISTINCT g.groupid, fr.price 1388FROM t4 g 1389LEFT JOIN 1390v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t; 1391groupid price 13923 2340 13936 9900 13941 NULL 13952 NULL 13964 NULL 13975 NULL 1398DROP VIEW v1,v2; 1399DROP TABLE t1,t2,t3,t4; 1400CREATE TABLE t1(a int); 1401CREATE TABLE t2(b int); 1402CREATE TABLE t3(c int, d int); 1403CREATE TABLE t4(d int); 1404CREATE TABLE t5(e int, f int); 1405CREATE TABLE t6(f int); 1406CREATE VIEW v1 AS 1407SELECT e FROM t5 JOIN t6 ON t5.e=t6.f; 1408CREATE VIEW v2 AS 1409SELECT e FROM t5 NATURAL JOIN t6; 1410SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d); 1411a 1412SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d); 1413ERROR 42S22: Unknown column 't1.x' in 'field list' 1414SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4; 1415a 1416SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4; 1417ERROR 42S22: Unknown column 't1.x' in 'field list' 1418SELECT v1.e FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1419e 1420SELECT v1.x FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1421ERROR 42S22: Unknown column 'v1.x' in 'field list' 1422SELECT v2.e FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1423e 1424SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1425ERROR 42S22: Unknown column 'v2.x' in 'field list' 1426DROP VIEW v1, v2; 1427DROP TABLE t1, t2, t3, t4, t5, t6; 1428create table t1 (id1 int(11) not null); 1429insert into t1 values (1),(2); 1430create table t2 (id2 int(11) not null); 1431insert into t2 values (1),(2),(3),(4); 1432create table t3 (id3 char(16) not null); 1433insert into t3 values ('100'); 1434create table t4 (id2 int(11) not null, id3 char(16)); 1435create table t5 (id1 int(11) not null, key (id1)); 1436insert into t5 values (1),(2),(1); 1437create view v1 as 1438select t4.id3 from t4 join t2 on t4.id2 = t2.id2; 1439select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3); 1440id1 14411 14422 1443drop view v1; 1444drop table t1, t2, t3, t4, t5; 1445create table t0 (a int); 1446insert into t0 values (0),(1),(2),(3); 1447create table t1(a int); 1448insert into t1 select A.a + 10*(B.a) from t0 A, t0 B; 1449create table t2 (a int, b int); 1450insert into t2 values (1,1), (2,2), (3,3); 1451create table t3(a int, b int, filler char(200), key(a)); 1452insert into t3 select a,a,'filler' from t1; 1453insert into t3 select a,a,'filler' from t1; 1454create table t4 like t3; 1455insert into t4 select * from t3; 1456insert into t4 select * from t3; 1457create table t5 like t4; 1458insert into t5 select * from t4; 1459insert into t5 select * from t4; 1460create table t6 like t5; 1461insert into t6 select * from t5; 1462insert into t6 select * from t5; 1463create table t7 like t6; 1464insert into t7 select * from t6; 1465insert into t7 select * from t6; 1466explain select * from t4 join 1467t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; 1468id select_type table type possible_keys key key_len ref rows Extra 14691 SIMPLE t2 ALL NULL NULL NULL NULL X 14701 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 14711 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 14721 SIMPLE t4 ref a a 5 test.t3.b X Using index condition(BKA); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1473explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b 1474join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; 1475id select_type table type possible_keys key key_len ref rows Extra 14761 SIMPLE t2 ALL NULL NULL NULL NULL X 14771 SIMPLE t3 ref a a 5 test.t2.b X Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 14781 SIMPLE t4 ref a a 5 test.t3.b X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 14791 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 14801 SIMPLE t5 ref a a 5 test.t2.b X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 14811 SIMPLE t7 ref a a 5 test.t5.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1482explain select * from t2 left join 1483(t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b 1484join t5 on t5.a=t3.b) on t3.a=t2.b; 1485id select_type table type possible_keys key key_len ref rows Extra 14861 SIMPLE t2 ALL NULL NULL NULL NULL X 14871 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 14881 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 14891 SIMPLE t4 ref a a 5 test.t5.a X Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 14901 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1491drop table t0, t1, t2, t3, t4, t5, t6, t7; 1492create table t1 (a int); 1493insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1494create table t2 (a int, filler char(100), key(a)); 1495insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B; 1496create table t3 like t2; 1497insert into t3 select * from t2; 1498explain select * from t1 left join 1499(t2 left join t3 on (t2.a = t3.a)) 1500on (t1.a = t2.a); 1501id select_type table type possible_keys key key_len ref rows Extra 15021 SIMPLE t1 ALL NULL NULL NULL NULL 10 15031 SIMPLE t2 ref a a 5 test.t1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 15041 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1505drop table t1, t2, t3; 1506CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); 1507CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); 1508CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY, 1509id int NOT NULL, 1510pid int NOT NULL); 1511INSERT INTO t1 VALUES (1, 'A'), (3, 'C'); 1512INSERT INTO t2 VALUES (1, 'A'), (3, 'C'); 1513INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3); 1514SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1) 1515ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id) 1516LEFT JOIN t2 ON (t3.pid=t2.pid) 1517WHERE p.id=1; 1518id type cid id pid id type pid type 15191 A NULL NULL NULL NULL NULL NULL NULL 1520CREATE VIEW v1 AS 1521SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B'; 1522SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id 1523LEFT JOIN t2 ON v1.pid=t2.pid 1524WHERE p.id=1; 1525id type cid id pid pid type 15261 A NULL NULL NULL NULL NULL 1527DROP VIEW v1; 1528DROP TABLE t1,t2,t3; 1529CREATE TABLE t1 (id1 int PRIMARY KEY, id2 int); 1530CREATE TABLE t2 (id1 int PRIMARY KEY, id2 int); 1531CREATE TABLE t3 (id1 int PRIMARY KEY, id2 int); 1532CREATE TABLE t4 (id1 int PRIMARY KEY, id2 int); 1533CREATE TABLE t5 (id1 int PRIMARY KEY, id2 int); 1534SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa 1535FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1 1536LEFT OUTER JOIN 1537(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1) 1538ON t3.id2 IS NOT NULL 1539WHERE t1.id1=2; 1540id ngroupbynsa 1541PREPARE stmt FROM 1542"SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa 1543 FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1 1544 LEFT OUTER JOIN 1545 (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1) 1546 ON t3.id2 IS NOT NULL 1547 WHERE t1.id1=2"; 1548EXECUTE stmt; 1549id ngroupbynsa 1550EXECUTE stmt; 1551id ngroupbynsa 1552EXECUTE stmt; 1553id ngroupbynsa 1554EXECUTE stmt; 1555id ngroupbynsa 1556INSERT INTO t1 VALUES (1,1), (2,1), (3,2); 1557INSERT INTO t2 VALUES (2,1), (3,2), (4,3); 1558INSERT INTO t3 VALUES (1,1), (3,2), (2,NULL); 1559INSERT INTO t4 VALUES (1,1), (2,1), (3,3); 1560INSERT INTO t5 VALUES (1,1), (2,2), (3,3), (4,3); 1561EXECUTE stmt; 1562id ngroupbynsa 15632 1 15642 1 1565EXECUTE stmt; 1566id ngroupbynsa 15672 1 15682 1 1569EXECUTE stmt; 1570id ngroupbynsa 15712 1 15722 1 1573EXECUTE stmt; 1574id ngroupbynsa 15752 1 15762 1 1577SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa 1578FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1 1579LEFT OUTER JOIN 1580(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1) 1581ON t3.id2 IS NOT NULL 1582WHERE t1.id1=2; 1583id ngroupbynsa 15842 1 15852 1 1586DROP TABLE t1,t2,t3,t4,t5; 1587CREATE TABLE t1 ( 1588id int NOT NULL PRIMARY KEY, 1589ct int DEFAULT NULL, 1590pc int DEFAULT NULL, 1591INDEX idx_ct (ct), 1592INDEX idx_pc (pc) 1593); 1594INSERT INTO t1 VALUES 1595(1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL); 1596CREATE TABLE t2 ( 1597id int NOT NULL PRIMARY KEY, 1598sr int NOT NULL, 1599nm varchar(255) NOT NULL, 1600INDEX idx_sr (sr) 1601); 1602INSERT INTO t2 VALUES 1603(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand'); 1604CREATE TABLE t3 ( 1605id int NOT NULL PRIMARY KEY, 1606ct int NOT NULL, 1607ln int NOT NULL, 1608INDEX idx_ct (ct), 1609INDEX idx_ln (ln) 1610); 1611CREATE TABLE t4 ( 1612id int NOT NULL PRIMARY KEY, 1613nm varchar(255) NOT NULL 1614); 1615INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique'); 1616SELECT t1.* 1617FROM t1 LEFT JOIN 1618(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id 1619WHERE t1.id='5'; 1620id ct pc 16215 NULL NULL 1622SELECT t1.*, t4.nm 1623FROM t1 LEFT JOIN 1624(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id 1625LEFT JOIN t4 ON t2.sr=t4.id 1626WHERE t1.id='5'; 1627id ct pc nm 16285 NULL NULL NULL 1629DROP TABLE t1,t2,t3,t4; 1630CREATE TABLE t1 (a INT, b INT); 1631CREATE TABLE t2 (a INT); 1632CREATE TABLE t3 (a INT, c INT); 1633CREATE TABLE t4 (a INT, c INT); 1634CREATE TABLE t5 (a INT, c INT); 1635SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) 1636LEFT JOIN t5 USING (a)) USING (a); 1637b 1638SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) 1639LEFT JOIN t5 USING (a)) USING (a); 1640ERROR 23000: Column 'c' in field list is ambiguous 1641SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) 1642JOIN t5 USING (a)) USING (a); 1643b 1644SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) 1645JOIN t5 USING (a)) USING (a); 1646ERROR 23000: Column 'c' in field list is ambiguous 1647DROP TABLE t1,t2,t3,t4,t5; 1648CREATE TABLE t1 (a INT, b INT); 1649CREATE TABLE t2 (a INT, b INT); 1650CREATE TABLE t3 (a INT, b INT); 1651INSERT INTO t1 VALUES (1,1); 1652INSERT INTO t2 VALUES (1,1); 1653INSERT INTO t3 VALUES (1,1); 1654SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a); 1655ERROR 23000: Column 'a' in from clause is ambiguous 1656DROP TABLE t1,t2,t3; 1657CREATE TABLE t1 ( 1658carrier char(2) default NULL, 1659id int NOT NULL auto_increment PRIMARY KEY 1660); 1661INSERT INTO t1 VALUES 1662('CO',235371754),('CO',235376554),('CO',235376884),('CO',235377874), 1663('CO',231060394),('CO',231059224),('CO',231059314),('CO',231060484), 1664('CO',231060274),('CO',231060124),('CO',231060244),('CO',231058594), 1665('CO',231058924),('CO',231058504),('CO',231059344),('CO',231060424), 1666('CO',231059554),('CO',231060304),('CO',231059644),('CO',231059464), 1667('CO',231059764),('CO',231058294),('CO',231058624),('CO',231058864), 1668('CO',231059374),('CO',231059584),('CO',231059734),('CO',231059014), 1669('CO',231059854),('CO',231059494),('CO',231059794),('CO',231058534), 1670('CO',231058324),('CO',231058684),('CO',231059524),('CO',231059974); 1671CREATE TABLE t2 ( 1672scan_date date default NULL, 1673package_id int default NULL, 1674INDEX scan_date(scan_date), 1675INDEX package_id(package_id) 1676); 1677INSERT INTO t2 VALUES 1678('2008-12-29',231062944),('2008-12-29',231065764),('2008-12-29',231066124), 1679('2008-12-29',231060094),('2008-12-29',231061054),('2008-12-29',231065644), 1680('2008-12-29',231064384),('2008-12-29',231064444),('2008-12-29',231073774), 1681('2008-12-29',231058594),('2008-12-29',231059374),('2008-12-29',231066004), 1682('2008-12-29',231068494),('2008-12-29',231070174),('2008-12-29',231071884), 1683('2008-12-29',231063274),('2008-12-29',231063754),('2008-12-29',231064144), 1684('2008-12-29',231069424),('2008-12-29',231073714),('2008-12-29',231058414), 1685('2008-12-29',231060994),('2008-12-29',231069154),('2008-12-29',231068614), 1686('2008-12-29',231071464),('2008-12-29',231074014),('2008-12-29',231059614), 1687('2008-12-29',231059074),('2008-12-29',231059464),('2008-12-29',231069094), 1688('2008-12-29',231067294),('2008-12-29',231070144),('2008-12-29',231073804), 1689('2008-12-29',231072634),('2008-12-29',231058294),('2008-12-29',231065344), 1690('2008-12-29',231066094),('2008-12-29',231069034),('2008-12-29',231058594), 1691('2008-12-29',231059854),('2008-12-29',231059884),('2008-12-29',231059914), 1692('2008-12-29',231063664),('2008-12-29',231063814),('2008-12-29',231063904); 1693CREATE TABLE t3 ( 1694package_id int default NULL, 1695INDEX package_id(package_id) 1696); 1697INSERT INTO t3 VALUES 1698(231058294),(231058324),(231058354),(231058384),(231058414),(231058444), 1699(231058474),(231058504),(231058534),(231058564),(231058594),(231058624), 1700(231058684),(231058744),(231058804),(231058864),(231058924),(231058954), 1701(231059014),(231059074),(231059104),(231059134),(231059164),(231059194), 1702(231059224),(231059254),(231059284),(231059314),(231059344),(231059374), 1703(231059404),(231059434),(231059464),(231059494),(231059524),(231059554), 1704(231059584),(231059614),(231059644),(231059674),(231059704),(231059734), 1705(231059764),(231059794),(231059824),(231059854),(231059884),(231059914), 1706(231059944),(231059974),(231060004),(231060034),(231060064),(231060094), 1707(231060124),(231060154),(231060184),(231060214),(231060244),(231060274), 1708(231060304),(231060334),(231060364),(231060394),(231060424),(231060454), 1709(231060484),(231060514),(231060544),(231060574),(231060604),(231060634), 1710(231060664),(231060694),(231060724),(231060754),(231060784),(231060814), 1711(231060844),(231060874),(231060904),(231060934),(231060964),(231060994), 1712(231061024),(231061054),(231061084),(231061144),(231061174),(231061204), 1713(231061234),(231061294),(231061354),(231061384),(231061414),(231061474), 1714(231061564),(231061594),(231061624),(231061684),(231061714),(231061774), 1715(231061804),(231061894),(231061984),(231062074),(231062134),(231062224), 1716(231062254),(231062314),(231062374),(231062434),(231062494),(231062554), 1717(231062584),(231062614),(231062644),(231062704),(231062734),(231062794), 1718(231062854),(231062884),(231062944),(231063004),(231063034),(231063064), 1719(231063124),(231063154),(231063184),(231063214),(231063274),(231063334), 1720(231063394),(231063424),(231063454),(231063514),(231063574),(231063664); 1721CREATE TABLE t4 ( 1722carrier char(2) NOT NULL default '' PRIMARY KEY, 1723id int(11) default NULL, 1724INDEX id(id) 1725); 1726INSERT INTO t4 VALUES 1727('99',6),('SK',456),('UA',486),('AI',1081),('OS',1111),('VS',1510); 1728CREATE TABLE t5 ( 1729carrier_id int default NULL, 1730INDEX carrier_id(carrier_id) 1731); 1732INSERT INTO t5 VALUES 1733(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1734(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1735(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1736(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1737(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1738(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(456),(456),(456), 1739(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456), 1740(456),(486),(1081),(1111),(1111),(1111),(1111),(1510); 1741SELECT COUNT(*) 1742FROM((t2 JOIN t1 ON t2.package_id = t1.id) 1743JOIN t3 ON t3.package_id = t1.id); 1744COUNT(*) 17456 1746EXPLAIN 1747SELECT COUNT(*) 1748FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 1749JOIN t3 ON t3.package_id = t1.id) 1750LEFT JOIN 1751(t5 JOIN t4 ON t5.carrier_id = t4.id) 1752ON t4.carrier = t1.carrier; 1753id select_type table type possible_keys key key_len ref rows Extra 17541 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index 17551 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 17561 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 17571 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index 17581 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index 1759SELECT COUNT(*) 1760FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 1761JOIN t3 ON t3.package_id = t1.id) 1762LEFT JOIN 1763(t5 JOIN t4 ON t5.carrier_id = t4.id) 1764ON t4.carrier = t1.carrier; 1765COUNT(*) 17666 1767DROP TABLE t1,t2,t3,t4,t5; 1768CREATE TABLE t1 ( 1769pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1770a int DEFAULT NULL, 1771KEY idx(a) 1772); 1773CREATE TABLE t2 ( 1774pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1775a int DEFAULT NULL, 1776KEY idx(a) 1777); 1778CREATE TABLE t3 ( 1779pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1780a int DEFAULT NULL, 1781KEY idx(a) 1782); 1783INSERT INTO t1 VALUES 1784(1,2), (2,7), (3,5), (4,7), (5,5), (6,NULL), (7,NULL), (8,9); 1785INSERT INTO t2 VALUES 1786(1,NULL), (4,2), (5,2), (3,4), (2,8); 1787INSERT INTO t3 VALUES 1788(1,9), (2,2), (3,5), (4,2), (5,7), (6,0), (7,5); 1789SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a 1790FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a; 1791pk a pk a pk a 17921 2 4 2 2 2 17931 2 5 2 2 2 17941 2 4 2 4 2 17951 2 5 2 4 2 17962 7 NULL NULL NULL NULL 17973 5 NULL NULL NULL NULL 17984 7 NULL NULL NULL NULL 17995 5 NULL NULL NULL NULL 18006 NULL NULL NULL NULL NULL 18017 NULL NULL NULL NULL NULL 18028 9 NULL NULL NULL NULL 1803SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a 1804FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a 1805WHERE t2.pk IS NULL; 1806pk a pk a pk a 18072 7 NULL NULL NULL NULL 18083 5 NULL NULL NULL NULL 18094 7 NULL NULL NULL NULL 18105 5 NULL NULL NULL NULL 18116 NULL NULL NULL NULL NULL 18127 NULL NULL NULL NULL NULL 18138 9 NULL NULL NULL NULL 1814SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a 1815FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a 1816WHERE t3.pk IS NULL; 1817pk a pk a pk a 18182 7 NULL NULL NULL NULL 18193 5 NULL NULL NULL NULL 18204 7 NULL NULL NULL NULL 18215 5 NULL NULL NULL NULL 18226 NULL NULL NULL NULL NULL 18237 NULL NULL NULL NULL NULL 18248 9 NULL NULL NULL NULL 1825DROP TABLE t1, t2, t3; 1826CREATE TABLE t1 (a int NOT NULL ); 1827INSERT INTO t1 VALUES (9), (9); 1828CREATE TABLE t2 (a int NOT NULL ); 1829INSERT INTO t2 VALUES (9); 1830CREATE TABLE t3 (a int NOT NULL, b int); 1831INSERT INTO t3 VALUES (19,9); 1832CREATE TABLE t4 (b int) ; 1833SELECT * FROM t1 LEFT JOIN 1834((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) 1835ON t1.a=t2.a; 1836a a a b b 18379 9 19 9 NULL 18389 9 19 9 NULL 1839SELECT * FROM t1 LEFT JOIN 1840((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) 1841ON t1.a=t2.a 1842WHERE t3.a IS NULL; 1843a a a b b 1844EXPLAIN EXTENDED 1845SELECT * FROM t1 LEFT JOIN 1846((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) 1847ON t1.a=t2.a 1848WHERE t3.a IS NULL; 1849id select_type table type possible_keys key key_len ref rows filtered Extra 18501 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 18511 SIMPLE t2 hash_ALL NULL #hash#$hj 4 test.t1.a 1 100.00 Using where; Using join buffer (flat, BNLH join) 18521 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.a 1 100.00 Using where; Not exists; Using join buffer (incremental, BNLH join) 18531 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t3.a 0 0.00 Using where; Using join buffer (incremental, BNLH join) 1854Warnings: 1855Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`b` AS `b` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(`test`.`t3`.`b` = `test`.`t1`.`a`) left join `test`.`t4` on(`test`.`t4`.`b` = `test`.`t3`.`a` and `test`.`t3`.`a` is not null)) on(`test`.`t2`.`a` = `test`.`t1`.`a`) where `test`.`t3`.`a` is null 1856DROP TABLE t1,t2,t3,t4; 1857SET optimizer_switch=@save_optimizer_switch; 1858End of 5.0 tests 1859# 1860# MDEV-621: LP:693329 - Assertion `!is_interleave_error' failed on low optimizer_search_depth 1861# 1862set @tmp_mdev621= @@optimizer_search_depth; 1863SET SESSION optimizer_search_depth = 4; 1864CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int) ; 1865INSERT IGNORE INTO t1 VALUES (0,0,2,0),(NULL,0,2,0); 1866CREATE TABLE t2 (f1 int) ; 1867CREATE TABLE t3 (f3 int,PRIMARY KEY (f3)) ; 1868CREATE TABLE t4 (f5 int) ; 1869CREATE TABLE t5 (f2 int) ; 1870SELECT alias2.f4 FROM t1 AS alias1 1871LEFT JOIN t1 AS alias2 1872LEFT JOIN t2 AS alias3 1873LEFT JOIN t3 AS alias4 ON alias3.f1 = alias4.f3 1874ON alias2.f1 1875LEFT JOIN t4 AS alias5 1876JOIN t5 ON alias5.f5 1877ON alias2.f3 ON alias1.f2; 1878f4 1879NULL 1880NULL 1881DROP TABLE t1,t2,t3,t4,t5; 1882# 1883# MDEV-7992: Nested left joins + 'not exists' optimization 1884# 1885CREATE TABLE t1( 1886K1 INT PRIMARY KEY, 1887Name VARCHAR(15) 1888); 1889INSERT INTO t1 VALUES 1890(1,'T1Row1'), (2,'T1Row2'); 1891CREATE TABLE t2( 1892K2 INT PRIMARY KEY, 1893K1r INT, 1894rowTimestamp DATETIME, 1895Event VARCHAR(15) 1896); 1897INSERT INTO t2 VALUES 1898(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), 1899(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), 1900(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); 1901SELECT t1a.*, t2a.*, 1902t2i.K2 AS K2B, t2i.K1r AS K1rB, 1903t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB 1904FROM 1905t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 1906LEFT JOIN 1907( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) 1908ON (t1i.K1 = 1) AND 1909(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR 1910(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) 1911OR (t2i.K2 IS NULL)) 1912WHERE 1913t2a.K1r = 1 AND t2i.K2 IS NULL; 1914K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB 19151 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL 1916EXPLAIN EXTENDED SELECT t1a.*, t2a.*, 1917t2i.K2 AS K2B, t2i.K1r AS K1rB, 1918t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB 1919FROM 1920t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 1921LEFT JOIN 1922( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) 1923ON (t1i.K1 = 1) AND 1924(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR 1925(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) 1926OR (t2i.K2 IS NULL)) 1927WHERE 1928t2a.K1r = 1 AND t2i.K2 IS NULL; 1929id select_type table type possible_keys key key_len ref rows filtered Extra 19301 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 19311 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where 19321 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index 19331 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists 1934Warnings: 1935Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(`test`.`t2i`.`K1r` = 1)) on(`test`.`t1i`.`K1` = 1 and (`test`.`t2i`.`K1r` = 1 and `test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp` or `test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp` and `test`.`t2i`.`K2` > `test`.`t2a`.`K2` or `test`.`t2i`.`K2` is null)) where `test`.`t2a`.`K1r` = 1 and `test`.`t2i`.`K2` is null 1936CREATE VIEW v1 AS 1937SELECT t2i.* 1938FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1 1939WHERE t1i.K1 = 1 ; 1940SELECT 1941t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, 1942t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB 1943FROM 1944t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 1945LEFT JOIN 1946v1 as t2b 1947ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR 1948(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) 1949OR (t2b.K2 IS NULL) 1950WHERE 1951t1a.K1 = 1 AND 1952t2b.K2 IS NULL; 1953K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB 19541 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL 1955EXPLAIN EXTENDED SELECT 1956t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, 1957t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB 1958FROM 1959t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 1960LEFT JOIN 1961v1 as t2b 1962ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR 1963(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) 1964OR (t2b.K2 IS NULL) 1965WHERE 1966t1a.K1 = 1 AND 1967t2b.K2 IS NULL; 1968id select_type table type possible_keys key key_len ref rows filtered Extra 19691 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 19701 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where 19711 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index 19721 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists 1973Warnings: 1974Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(`test`.`t2i`.`K1r` = 1)) on(`test`.`t1i`.`K1` = 1 and (`test`.`t2i`.`K1r` = 1 and `test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp` or `test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp` and `test`.`t2i`.`K2` > `t2a`.`K2` or `test`.`t2i`.`K2` is null)) where `t2a`.`K1r` = 1 and `test`.`t2i`.`K2` is null 1975DROP VIEW v1; 1976DROP TABLE t1,t2; 1977set optimizer_search_depth= @tmp_mdev621; 1978# 1979# MDEV-19588: Nested left joins using optimized join cache 1980# 1981set optimizer_switch='optimize_join_buffer_size=on'; 1982set @save_join_cache_level= @@join_cache_level; 1983set join_cache_level=2; 1984CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; 1985CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; 1986INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); 1987CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; 1988INSERT INTO t3 VALUES 1989(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), 1990(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), 1991(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); 1992SELECT t3.* 1993FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 1994WHERE t2.pk < 13 OR t3.i1 IS NULL; 1995pk c1 i1 19967 a NULL 199717 a NULL 199826 a NULL 1999explain extended SELECT t3.* 2000FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 2001WHERE t2.pk < 13 OR t3.i1 IS NULL; 2002id select_type table type possible_keys key key_len ref rows filtered Extra 20031 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00 20041 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join) 20051 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 2006Warnings: 2007Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on(`test`.`t2`.`i1` = `test`.`t3`.`i1`)) on(`test`.`t1`.`i1` = `test`.`t3`.`i1`) where `test`.`t2`.`pk` < 13 or `test`.`t3`.`i1` is null 2008DROP TABLE t1,t2,t3; 2009set join_cache_level= @save_join_cache_level; 2010set optimizer_switch=@save_optimizer_switch; 2011CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); 2012CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); 2013CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); 2014CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); 2015INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0); 2016INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0); 2017INSERT INTO t7 VALUES (1,1,0), (2,2,0); 2018INSERT INTO t8 VALUES (0,2,0), (1,2,0); 2019INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0); 2020INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0); 2021INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0); 2022EXPLAIN 2023SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2024FROM t5 2025LEFT JOIN 2026( 2027(t6, t7) 2028LEFT JOIN 2029t8 2030ON t7.b=t8.b AND t6.b < 10 2031) 2032ON t6.b >= 2 AND t5.b=t7.b AND 2033(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; 2034id select_type table type possible_keys key key_len ref rows Extra 20351 SIMPLE t5 ALL NULL NULL NULL NULL 3 20361 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 20371 SIMPLE t6 range PRIMARY,b_i PRIMARY 4 NULL 3 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join) 20381 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 2039SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2040FROM t5 2041LEFT JOIN 2042( 2043(t6, t7) 2044LEFT JOIN 2045t8 2046ON t7.b=t8.b AND t6.b < 10 2047) 2048ON t6.b >= 2 AND t5.b=t7.b AND 2049(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; 2050a b a b a b a b 20512 2 1 2 2 2 1 2 20522 2 3 2 2 2 1 2 20531 1 1 2 1 1 NULL NULL 20541 1 3 2 1 1 NULL NULL 20553 3 NULL NULL NULL NULL NULL NULL 2056DELETE FROM t5; 2057DELETE FROM t6; 2058DELETE FROM t7; 2059DELETE FROM t8; 2060INSERT INTO t5 VALUES (1,3,0), (3,2,0); 2061INSERT INTO t6 VALUES (3,3,0); 2062INSERT INTO t7 VALUES (1,2,0); 2063INSERT INTO t8 VALUES (1,1,0); 2064EXPLAIN 2065SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2066FROM t5 LEFT JOIN 2067(t6 LEFT JOIN t7 ON t7.a=1, t8) 2068ON (t5.b=t8.b); 2069id select_type table type possible_keys key key_len ref rows Extra 20701 SIMPLE t5 ALL NULL NULL NULL NULL 2 20711 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 20721 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 20731 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 2074SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2075FROM t5 LEFT JOIN 2076(t6 LEFT JOIN t7 ON t7.a=1, t8) 2077ON (t5.b=t8.b); 2078a b a b a b a b 20791 3 NULL NULL NULL NULL NULL NULL 20803 2 NULL NULL NULL NULL NULL NULL 2081EXPLAIN 2082SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2083FROM t5 LEFT JOIN 2084(t6 LEFT JOIN t7 ON t7.b=2, t8) 2085ON (t5.b=t8.b); 2086id select_type table type possible_keys key key_len ref rows Extra 20871 SIMPLE t5 ALL NULL NULL NULL NULL 2 20881 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 20891 SIMPLE t7 ref b_i b_i 5 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 20901 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 2091SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2092FROM t5 LEFT JOIN 2093(t6 LEFT JOIN t7 ON t7.b=2, t8) 2094ON (t5.b=t8.b); 2095a b a b a b a b 20961 3 NULL NULL NULL NULL NULL NULL 20973 2 NULL NULL NULL NULL NULL NULL 2098EXPLAIN 2099SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2100FROM t5 LEFT JOIN 2101(t8, t6 LEFT JOIN t7 ON t7.a=1) 2102ON (t5.b=t8.b); 2103id select_type table type possible_keys key key_len ref rows Extra 21041 SIMPLE t5 ALL NULL NULL NULL NULL 2 21051 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) 21061 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) 21071 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 2108SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 2109FROM t5 LEFT JOIN 2110(t8, t6 LEFT JOIN t7 ON t7.a=1) 2111ON (t5.b=t8.b); 2112a b a b a b a b 21131 3 NULL NULL NULL NULL NULL NULL 21143 2 NULL NULL NULL NULL NULL NULL 2115DROP TABLE t5,t6,t7,t8; 2116set join_cache_level=default; 2117set @@optimizer_switch=@save_optimizer_switch_jcl6; 2118set @optimizer_switch_for_join_nested_test=NULL; 2119set @join_cache_level_for_join_nested_test=NULL; 2120