1DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 2SET @save_optimizer_switch=@@optimizer_switch; 3SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off'); 4set join_cache_level=1; 5CREATE TABLE t0 (a int, b int, c int); 6CREATE TABLE t1 (a int, b int, c int); 7CREATE TABLE t2 (a int, b int, c int); 8CREATE TABLE t3 (a int, b int, c int); 9CREATE TABLE t4 (a int, b int, c int); 10CREATE TABLE t5 (a int, b int, c int); 11CREATE TABLE t6 (a int, b int, c int); 12CREATE TABLE t7 (a int, b int, c int); 13CREATE TABLE t8 (a int, b int, c int); 14CREATE TABLE t9 (a int, b int, c int); 15INSERT INTO t0 VALUES (1,1,0), (1,2,0), (2,2,0); 16INSERT INTO t1 VALUES (1,3,0), (2,2,0), (3,2,0); 17INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); 18INSERT INTO t3 VALUES (1,2,0), (2,2,0); 19INSERT INTO t4 VALUES (3,2,0), (4,2,0); 20INSERT INTO t5 VALUES (3,1,0), (2,2,0), (3,3,0); 21INSERT INTO t6 VALUES (3,2,0), (6,2,0), (6,1,0); 22INSERT INTO t7 VALUES (1,1,0), (2,2,0); 23INSERT INTO t8 VALUES (0,2,0), (1,2,0); 24INSERT INTO t9 VALUES (1,1,0), (1,2,0), (3,3,0); 25SELECT t2.a,t2.b 26FROM t2; 27a b 283 3 294 2 305 3 31SELECT t3.a,t3.b 32FROM t3; 33a b 341 2 352 2 36SELECT t4.a,t4.b 37FROM t4; 38a b 393 2 404 2 41SELECT t3.a,t3.b,t4.a,t4.b 42FROM t3,t4; 43a b a b 441 2 3 2 452 2 3 2 461 2 4 2 472 2 4 2 48SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 49FROM t2 50LEFT JOIN 51(t3, t4) 52ON t2.b=t4.b; 53a b a b a b 543 3 NULL NULL NULL NULL 554 2 1 2 3 2 564 2 1 2 4 2 574 2 2 2 3 2 584 2 2 2 4 2 595 3 NULL NULL NULL NULL 60SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 61FROM t2 62LEFT JOIN 63(t3, t4) 64ON t3.a=1 AND t2.b=t4.b; 65a b a b a b 663 3 NULL NULL NULL NULL 674 2 1 2 3 2 684 2 1 2 4 2 695 3 NULL NULL NULL NULL 70EXPLAIN EXTENDED 71SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 72FROM t2 73LEFT JOIN 74(t3, t4) 75ON t2.b=t4.b 76WHERE t3.a=1 OR t3.c IS NULL; 77id select_type table type possible_keys key key_len ref rows filtered Extra 781 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 791 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 801 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 81Warnings: 82Note 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`) where `test`.`t3`.`a` = 1 or `test`.`t3`.`c` is null 83SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 84FROM t2 85LEFT JOIN 86(t3, t4) 87ON t2.b=t4.b 88WHERE t3.a=1 OR t3.c IS NULL; 89a b a b a b 903 3 NULL NULL NULL NULL 914 2 1 2 3 2 924 2 1 2 4 2 935 3 NULL NULL NULL NULL 94SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 95FROM t2 96LEFT JOIN 97(t3, t4) 98ON t2.b=t4.b 99WHERE t3.a>1 OR t3.c IS NULL; 100a b a b a b 1013 3 NULL NULL NULL NULL 1024 2 2 2 3 2 1034 2 2 2 4 2 1045 3 NULL NULL NULL NULL 105SELECT t5.a,t5.b 106FROM t5; 107a b 1083 1 1092 2 1103 3 111SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 112FROM t3,t4,t5; 113a b a b a b 1141 2 3 2 3 1 1152 2 3 2 3 1 1161 2 4 2 3 1 1172 2 4 2 3 1 1181 2 3 2 2 2 1192 2 3 2 2 2 1201 2 4 2 2 2 1212 2 4 2 2 2 1221 2 3 2 3 3 1232 2 3 2 3 3 1241 2 4 2 3 3 1252 2 4 2 3 3 126SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 127FROM t2 128LEFT JOIN 129(t3, t4, t5) 130ON t2.b=t4.b; 131a b a b a b a b 1323 3 NULL NULL NULL NULL NULL NULL 1334 2 1 2 3 2 3 1 1344 2 1 2 3 2 2 2 1354 2 1 2 3 2 3 3 1364 2 1 2 4 2 3 1 1374 2 1 2 4 2 2 2 1384 2 1 2 4 2 3 3 1394 2 2 2 3 2 3 1 1404 2 2 2 3 2 2 2 1414 2 2 2 3 2 3 3 1424 2 2 2 4 2 3 1 1434 2 2 2 4 2 2 2 1444 2 2 2 4 2 3 3 1455 3 NULL NULL NULL NULL NULL NULL 146EXPLAIN EXTENDED 147SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 148FROM t2 149LEFT JOIN 150(t3, t4, t5) 151ON t2.b=t4.b 152WHERE t3.a>1 OR t3.c IS NULL; 153id select_type table type possible_keys key key_len ref rows filtered Extra 1541 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1551 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1561 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1571 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 158Warnings: 159Note 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`) where `test`.`t3`.`a` > 1 or `test`.`t3`.`c` is null 160SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 161FROM t2 162LEFT JOIN 163(t3, t4, t5) 164ON t2.b=t4.b 165WHERE t3.a>1 OR t3.c IS NULL; 166a b a b a b a b 1673 3 NULL NULL NULL NULL NULL NULL 1684 2 2 2 3 2 3 1 1694 2 2 2 3 2 2 2 1704 2 2 2 3 2 3 3 1714 2 2 2 4 2 3 1 1724 2 2 2 4 2 2 2 1734 2 2 2 4 2 3 3 1745 3 NULL NULL NULL NULL NULL NULL 175EXPLAIN EXTENDED 176SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 177FROM t2 178LEFT JOIN 179(t3, t4, t5) 180ON t2.b=t4.b 181WHERE (t3.a>1 OR t3.c IS NULL) AND 182(t5.a<3 OR t5.c IS NULL); 183id select_type table type possible_keys key key_len ref rows filtered Extra 1841 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1851 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1861 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1871 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where 188Warnings: 189Note 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`) where (`test`.`t3`.`a` > 1 or `test`.`t3`.`c` is null) and (`test`.`t5`.`a` < 3 or `test`.`t5`.`c` is null) 190SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b 191FROM t2 192LEFT JOIN 193(t3, t4, t5) 194ON t2.b=t4.b 195WHERE (t3.a>1 OR t3.c IS NULL) AND 196(t5.a<3 OR t5.c IS NULL); 197a b a b a b a b 1983 3 NULL NULL NULL NULL NULL NULL 1994 2 2 2 3 2 2 2 2004 2 2 2 4 2 2 2 2015 3 NULL NULL NULL NULL NULL NULL 202SELECT t6.a,t6.b 203FROM t6; 204a b 2053 2 2066 2 2076 1 208SELECT t7.a,t7.b 209FROM t7; 210a b 2111 1 2122 2 213SELECT t6.a,t6.b,t7.a,t7.b 214FROM t6,t7; 215a b a b 2163 2 1 1 2173 2 2 2 2186 2 1 1 2196 2 2 2 2206 1 1 1 2216 1 2 2 222SELECT t8.a,t8.b 223FROM t8; 224a b 2250 2 2261 2 227EXPLAIN EXTENDED 228SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 229FROM (t6, t7) 230LEFT JOIN 231t8 232ON t7.b=t8.b AND t6.b < 10; 233id select_type table type possible_keys key key_len ref rows filtered Extra 2341 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 2351 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 2361 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 237Warnings: 238Note 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) where 1 239SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 240FROM (t6, t7) 241LEFT JOIN 242t8 243ON t7.b=t8.b AND t6.b < 10; 244a b a b a b 2453 2 1 1 NULL NULL 2463 2 2 2 0 2 2473 2 2 2 1 2 2486 2 1 1 NULL NULL 2496 2 2 2 0 2 2506 2 2 2 1 2 2516 1 1 1 NULL NULL 2526 1 2 2 0 2 2536 1 2 2 1 2 254SELECT t5.a,t5.b 255FROM t5; 256a b 2573 1 2582 2 2593 3 260SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 261FROM t5 262LEFT JOIN 263( 264(t6, t7) 265LEFT JOIN 266t8 267ON t7.b=t8.b AND t6.b < 10 268) 269ON t6.b >= 2 AND t5.b=t7.b; 270a b a b a b a b 2713 1 3 2 1 1 NULL NULL 2723 1 6 2 1 1 NULL NULL 2732 2 3 2 2 2 0 2 2742 2 3 2 2 2 1 2 2752 2 6 2 2 2 0 2 2762 2 6 2 2 2 1 2 2773 3 NULL NULL NULL NULL NULL NULL 278SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 279FROM t5 280LEFT JOIN 281( 282(t6, t7) 283LEFT JOIN 284t8 285ON t7.b=t8.b AND t6.b < 10 286) 287ON t6.b >= 2 AND t5.b=t7.b AND 288(t8.a < 1 OR t8.c IS NULL); 289a b a b a b a b 2903 1 3 2 1 1 NULL NULL 2913 1 6 2 1 1 NULL NULL 2922 2 3 2 2 2 0 2 2932 2 6 2 2 2 0 2 2943 3 NULL NULL NULL NULL NULL NULL 295SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 296FROM t2 297LEFT JOIN 298(t3, t4) 299ON t3.a=1 AND t2.b=t4.b; 300a b a b a b 3013 3 NULL NULL NULL NULL 3024 2 1 2 3 2 3034 2 1 2 4 2 3045 3 NULL NULL NULL NULL 305SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 306t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 307FROM t2 308LEFT JOIN 309(t3, t4) 310ON t3.a=1 AND t2.b=t4.b, 311t5 312LEFT JOIN 313( 314(t6, t7) 315LEFT JOIN 316t8 317ON t7.b=t8.b AND t6.b < 10 318) 319ON t6.b >= 2 AND t5.b=t7.b; 320a b a b a b a b a b a b a b 3213 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 3223 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 3234 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 3244 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 3254 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 3264 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 3275 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 3285 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 3293 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3303 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3313 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 3323 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 3334 2 1 2 3 2 2 2 3 2 2 2 0 2 3344 2 1 2 3 2 2 2 3 2 2 2 1 2 3354 2 1 2 3 2 2 2 6 2 2 2 0 2 3364 2 1 2 3 2 2 2 6 2 2 2 1 2 3374 2 1 2 4 2 2 2 3 2 2 2 0 2 3384 2 1 2 4 2 2 2 3 2 2 2 1 2 3394 2 1 2 4 2 2 2 6 2 2 2 0 2 3404 2 1 2 4 2 2 2 6 2 2 2 1 2 3415 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3425 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3435 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 3445 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 3453 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 3464 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 3474 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 3485 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 349SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 350t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 351FROM t2 352LEFT JOIN 353(t3, t4) 354ON t3.a=1 AND t2.b=t4.b, 355t5 356LEFT JOIN 357( 358(t6, t7) 359LEFT JOIN 360t8 361ON t7.b=t8.b AND t6.b < 10 362) 363ON t6.b >= 2 AND t5.b=t7.b 364WHERE t2.a > 3 AND 365(t6.a < 6 OR t6.c IS NULL); 366a b a b a b a b a b a b a b 3674 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 3684 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 3695 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 3704 2 1 2 3 2 2 2 3 2 2 2 0 2 3714 2 1 2 3 2 2 2 3 2 2 2 1 2 3724 2 1 2 4 2 2 2 3 2 2 2 0 2 3734 2 1 2 4 2 2 2 3 2 2 2 1 2 3745 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 3755 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 3764 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 3774 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 3785 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 379SELECT t1.a,t1.b 380FROM t1; 381a b 3821 3 3832 2 3843 2 385SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 386t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 387FROM t1 388LEFT JOIN 389( 390t2 391LEFT JOIN 392(t3, t4) 393ON t3.a=1 AND t2.b=t4.b, 394t5 395LEFT JOIN 396( 397(t6, t7) 398LEFT JOIN 399t8 400ON t7.b=t8.b AND t6.b < 10 401) 402ON t6.b >= 2 AND t5.b=t7.b 403) 404ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 405(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 406(t1.a != 2); 407a b a b a b a b a b a b a b a b 4081 3 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4091 3 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4101 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4111 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4121 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4131 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4141 3 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4151 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4161 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 4171 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4181 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 4191 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 4201 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 4211 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4221 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4231 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4241 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4251 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4261 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4271 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4282 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 4293 2 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4303 2 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4313 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4323 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4333 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4343 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4353 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4363 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4373 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 4383 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 4393 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 4403 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 4413 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2 4423 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4433 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 4443 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 4453 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 4463 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 4473 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 4483 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2 4493 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 4503 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4513 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4523 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4533 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4543 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4553 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4563 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 457SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 458t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 459FROM t1 460LEFT JOIN 461( 462t2 463LEFT JOIN 464(t3, t4) 465ON t3.a=1 AND t2.b=t4.b, 466t5 467LEFT JOIN 468( 469(t6, t7) 470LEFT JOIN 471t8 472ON t7.b=t8.b AND t6.b < 10 473) 474ON t6.b >= 2 AND t5.b=t7.b 475) 476ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 477(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 478(t1.a != 2) 479WHERE (t2.a >= 4 OR t2.c IS NULL); 480a b a b a b a b a b a b a b a b 4811 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4821 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 4831 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 4841 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 4851 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 4861 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 4871 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 4881 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 4891 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 4901 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 4911 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 4921 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 4931 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 4942 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 4953 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 4963 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 4973 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 4983 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 4993 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 5003 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2 5013 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 5023 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 5033 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 5043 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2 5053 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2 5063 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 5073 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2 5083 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 5093 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 5103 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 5113 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 5123 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 5133 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 5143 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 5153 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 516SELECT t0.a,t0.b 517FROM t0; 518a b 5191 1 5201 2 5212 2 522EXPLAIN EXTENDED 523SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 524t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 525FROM t0,t1 526LEFT JOIN 527( 528t2 529LEFT JOIN 530(t3, t4) 531ON t3.a=1 AND t2.b=t4.b, 532t5 533LEFT JOIN 534( 535(t6, t7) 536LEFT JOIN 537t8 538ON t7.b=t8.b AND t6.b < 10 539) 540ON t6.b >= 2 AND t5.b=t7.b 541) 542ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 543(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 544(t1.a != 2) 545WHERE t0.a=1 AND 546t0.b=t1.b AND 547(t2.a >= 4 OR t2.c IS NULL); 548id select_type table type possible_keys key key_len ref rows filtered Extra 5491 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 5501 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5511 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 5521 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 5531 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 5541 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 5551 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 5561 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 5571 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 558Warnings: 559Note 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`) 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)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2)) 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) 560SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 561t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b 562FROM t0,t1 563LEFT JOIN 564( 565t2 566LEFT JOIN 567(t3, t4) 568ON t3.a=1 AND t2.b=t4.b, 569t5 570LEFT JOIN 571( 572(t6, t7) 573LEFT JOIN 574t8 575ON t7.b=t8.b AND t6.b < 10 576) 577ON t6.b >= 2 AND t5.b=t7.b 578) 579ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 580(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 581(t1.a != 2) 582WHERE t0.a=1 AND 583t0.b=t1.b AND 584(t2.a >= 4 OR t2.c IS NULL); 585a b a b a b a b a b a b a b a b a b 5861 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 5871 2 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL 5881 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 5891 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2 5901 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2 5911 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 5921 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2 5931 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 5941 2 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL 5951 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 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 4 2 2 2 3 2 2 2 1 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 4 2 2 2 6 2 2 2 1 2 6001 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 6011 2 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL 6021 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 6031 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2 6041 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2 6051 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 6061 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2 6071 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 608EXPLAIN EXTENDED 609SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 610t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 611FROM t0,t1 612LEFT JOIN 613( 614t2 615LEFT JOIN 616(t3, t4) 617ON t3.a=1 AND t2.b=t4.b, 618t5 619LEFT JOIN 620( 621(t6, t7) 622LEFT JOIN 623t8 624ON t7.b=t8.b AND t6.b < 10 625) 626ON t6.b >= 2 AND t5.b=t7.b 627) 628ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 629(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 630(t1.a != 2), 631t9 632WHERE t0.a=1 AND 633t0.b=t1.b AND 634(t2.a >= 4 OR t2.c IS NULL) AND 635(t3.a < 5 OR t3.c IS NULL) AND 636(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 637(t5.a >=2 OR t5.c IS NULL) AND 638(t6.a >=4 OR t6.c IS NULL) AND 639(t7.a <= 2 OR t7.c IS NULL) AND 640(t8.a < 1 OR t8.c IS NULL) AND 641(t8.b=t9.b OR t8.c IS NULL) AND 642(t9.a=1); 643id select_type table type possible_keys key key_len ref rows filtered Extra 6441 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 6451 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 6461 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 6471 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 6481 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 6491 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where 6501 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 6511 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 6521 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 6531 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 654Warnings: 655Note 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`) 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)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2)) 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) 656SELECT t9.a,t9.b 657FROM t9; 658a b 6591 1 6601 2 6613 3 662SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 663t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 664FROM t0,t1 665LEFT JOIN 666( 667t2 668LEFT JOIN 669(t3, t4) 670ON t3.a=1 AND t2.b=t4.b, 671t5 672LEFT JOIN 673( 674(t6, t7) 675LEFT JOIN 676t8 677ON t7.b=t8.b AND t6.b < 10 678) 679ON t6.b >= 2 AND t5.b=t7.b 680) 681ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 682(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 683(t1.a != 2), 684t9 685WHERE t0.a=1 AND 686t0.b=t1.b AND 687(t2.a >= 4 OR t2.c IS NULL) AND 688(t3.a < 5 OR t3.c IS NULL) AND 689(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 690(t5.a >=2 OR t5.c IS NULL) AND 691(t6.a >=4 OR t6.c IS NULL) AND 692(t7.a <= 2 OR t7.c IS NULL) AND 693(t8.a < 1 OR t8.c IS NULL) AND 694(t8.b=t9.b OR t8.c IS NULL) AND 695(t9.a=1); 696a b a b a b a b a b a b a b a b a b a b 6971 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 6981 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 6991 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 7001 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 7011 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 7021 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 7031 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 7041 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 7051 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 7061 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 7071 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 7081 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 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 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 7111 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 7121 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 7131 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 714SELECT t1.a,t1.b 715FROM t1; 716a b 7171 3 7182 2 7193 2 720SELECT t2.a,t2.b 721FROM t2; 722a b 7233 3 7244 2 7255 3 726SELECT t3.a,t3.b 727FROM t3; 728a b 7291 2 7302 2 731SELECT t2.a,t2.b,t3.a,t3.b 732FROM t2 733LEFT JOIN 734t3 735ON t2.b=t3.b; 736a b a b 7373 3 NULL NULL 7384 2 1 2 7394 2 2 2 7405 3 NULL NULL 741SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b 742FROM t1, t2 743LEFT JOIN 744t3 745ON t2.b=t3.b 746WHERE t1.a <= 2; 747a b a b a b 7481 3 3 3 NULL NULL 7492 2 3 3 NULL NULL 7501 3 4 2 1 2 7511 3 4 2 2 2 7522 2 4 2 1 2 7532 2 4 2 2 2 7541 3 5 3 NULL NULL 7552 2 5 3 NULL NULL 756SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b 757FROM t1, t3 758RIGHT JOIN 759t2 760ON t2.b=t3.b 761WHERE t1.a <= 2; 762a b a b a b 7631 3 3 3 NULL NULL 7642 2 3 3 NULL NULL 7651 3 4 2 1 2 7661 3 4 2 2 2 7672 2 4 2 1 2 7682 2 4 2 2 2 7691 3 5 3 NULL NULL 7702 2 5 3 NULL NULL 771SELECT t3.a,t3.b,t4.a,t4.b 772FROM t3,t4; 773a b a b 7741 2 3 2 7752 2 3 2 7761 2 4 2 7772 2 4 2 778SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 779FROM t2 780LEFT JOIN 781(t3, t4) 782ON t3.a=1 AND t2.b=t4.b; 783a b a b a b 7843 3 NULL NULL NULL NULL 7854 2 1 2 3 2 7864 2 1 2 4 2 7875 3 NULL NULL NULL NULL 788SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 789FROM t1, t2 790LEFT JOIN 791(t3, t4) 792ON t3.a=1 AND t2.b=t4.b 793WHERE t1.a <= 2; 794a b a b a b a b 7951 3 3 3 NULL NULL NULL NULL 7962 2 3 3 NULL NULL NULL NULL 7971 3 4 2 1 2 3 2 7981 3 4 2 1 2 4 2 7992 2 4 2 1 2 3 2 8002 2 4 2 1 2 4 2 8011 3 5 3 NULL NULL NULL NULL 8022 2 5 3 NULL NULL NULL NULL 803SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 804FROM t1, (t3, t4) 805RIGHT JOIN 806t2 807ON t3.a=1 AND t2.b=t4.b 808WHERE t1.a <= 2; 809a b a b a b a b 8101 3 3 3 NULL NULL NULL NULL 8112 2 3 3 NULL NULL NULL NULL 8121 3 4 2 1 2 3 2 8131 3 4 2 1 2 4 2 8142 2 4 2 1 2 3 2 8152 2 4 2 1 2 4 2 8161 3 5 3 NULL NULL NULL NULL 8172 2 5 3 NULL NULL NULL NULL 818SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 819FROM t1, (t3, t4) 820RIGHT JOIN 821t2 822ON t3.a=1 AND t2.b=t4.b 823WHERE t1.a <= 2; 824a b a b a b a b 8251 3 3 3 NULL NULL NULL NULL 8262 2 3 3 NULL NULL NULL NULL 8271 3 4 2 1 2 3 2 8281 3 4 2 1 2 4 2 8292 2 4 2 1 2 3 2 8302 2 4 2 1 2 4 2 8311 3 5 3 NULL NULL NULL NULL 8322 2 5 3 NULL NULL NULL NULL 833EXPLAIN EXTENDED 834SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 835FROM t1, (t3, t4) 836RIGHT JOIN 837t2 838ON t3.a=1 AND t2.b=t4.b 839WHERE t1.a <= 2; 840id select_type table type possible_keys key key_len ref rows filtered Extra 8411 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 8421 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 8431 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 8441 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 845Warnings: 846Note 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`) where `test`.`t1`.`a` <= 2 847INSERT INTO t2 VALUES (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0); 848CREATE INDEX idx_b ON t2(b); 849EXPLAIN EXTENDED 850SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 851FROM (t3,t4) 852LEFT JOIN 853(t1,t2) 854ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0; 855id select_type table type possible_keys key key_len ref rows filtered Extra 8561 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 8571 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 8581 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where 8591 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 860Warnings: 861Note 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 862SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 863FROM (t3,t4) 864LEFT JOIN 865(t1,t2) 866ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0; 867a b a b a b 8684 2 1 2 3 2 8694 2 1 2 3 2 8704 2 1 2 3 2 871NULL NULL 2 2 3 2 8724 2 1 2 4 2 8734 2 1 2 4 2 8744 2 1 2 4 2 875NULL NULL 2 2 4 2 876EXPLAIN EXTENDED 877SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 878t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 879FROM t0,t1 880LEFT JOIN 881( 882t2 883LEFT JOIN 884(t3, t4) 885ON t3.a=1 AND t2.b=t4.b AND t2.a>0, 886t5 887LEFT JOIN 888( 889(t6, t7) 890LEFT JOIN 891t8 892ON t7.b=t8.b AND t6.b < 10 893) 894ON t6.b >= 2 AND t5.b=t7.b 895) 896ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 897(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 898(t1.a != 2), 899t9 900WHERE t0.a=1 AND 901t0.b=t1.b AND 902(t2.a >= 4 OR t2.c IS NULL) AND 903(t3.a < 5 OR t3.c IS NULL) AND 904(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 905(t5.a >=2 OR t5.c IS NULL) AND 906(t6.a >=4 OR t6.c IS NULL) AND 907(t7.a <= 2 OR t7.c IS NULL) AND 908(t8.a < 1 OR t8.c IS NULL) AND 909(t8.b=t9.b OR t8.c IS NULL) AND 910(t9.a=1); 911id select_type table type possible_keys key key_len ref rows filtered Extra 9121 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 9131 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 9141 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where 9151 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 9161 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 9171 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 9181 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 9191 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 9201 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 9211 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 922Warnings: 923Note 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) 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)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2)) 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) 924INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0); 925INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0); 926CREATE INDEX idx_b ON t4(b); 927CREATE INDEX idx_b ON t5(b); 928EXPLAIN EXTENDED 929SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 930t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 931FROM t0,t1 932LEFT JOIN 933( 934t2 935LEFT JOIN 936(t3, t4) 937ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0, 938t5 939LEFT JOIN 940( 941(t6, t7) 942LEFT JOIN 943t8 944ON t7.b=t8.b AND t6.b < 10 945) 946ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 947) 948ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 949(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 950(t1.a != 2), 951t9 952WHERE t0.a=1 AND 953t0.b=t1.b AND 954(t2.a >= 4 OR t2.c IS NULL) AND 955(t3.a < 5 OR t3.c IS NULL) AND 956(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 957(t5.a >=2 OR t5.c IS NULL) AND 958(t6.a >=4 OR t6.c IS NULL) AND 959(t7.a <= 2 OR t7.c IS NULL) AND 960(t8.a < 1 OR t8.c IS NULL) AND 961(t8.b=t9.b OR t8.c IS NULL) AND 962(t9.a=1); 963id select_type table type possible_keys key key_len ref rows filtered Extra 9641 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 9651 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 9661 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 9671 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 9681 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 9691 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 9701 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 9711 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 9721 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 9731 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 974Warnings: 975Note 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)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`a` > 0)) 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) 976INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0); 977CREATE INDEX idx_b ON t8(b); 978EXPLAIN EXTENDED 979SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 980t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 981FROM t0,t1 982LEFT JOIN 983( 984t2 985LEFT JOIN 986(t3, t4) 987ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0, 988t5 989LEFT JOIN 990( 991(t6, t7) 992LEFT JOIN 993t8 994ON t7.b=t8.b AND t6.b < 10 AND t8.a>=0 995) 996ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 997) 998ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 999(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 1000(t1.a != 2), 1001t9 1002WHERE t0.a=1 AND 1003t0.b=t1.b AND 1004(t2.a >= 4 OR t2.c IS NULL) AND 1005(t3.a < 5 OR t3.c IS NULL) AND 1006(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 1007(t5.a >=2 OR t5.c IS NULL) AND 1008(t6.a >=4 OR t6.c IS NULL) AND 1009(t7.a <= 2 OR t7.c IS NULL) AND 1010(t8.a < 1 OR t8.c IS NULL) AND 1011(t8.b=t9.b OR t8.c IS NULL) AND 1012(t9.a=1); 1013id select_type table type possible_keys key key_len ref rows filtered Extra 10141 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 10151 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 10161 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 10171 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 10181 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 10191 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 10201 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 10211 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 10221 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 10231 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where 1024Warnings: 1025Note 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)) 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) 1026INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); 1027CREATE INDEX idx_b ON t1(b); 1028CREATE INDEX idx_a ON t0(a); 1029EXPLAIN EXTENDED 1030SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 1031t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 1032FROM t0,t1 1033LEFT JOIN 1034( 1035t2 1036LEFT JOIN 1037(t3, t4) 1038ON t3.a=1 AND t2.b=t4.b, 1039t5 1040LEFT JOIN 1041( 1042(t6, t7) 1043LEFT JOIN 1044t8 1045ON t7.b=t8.b AND t6.b < 10 1046) 1047ON t6.b >= 2 AND t5.b=t7.b 1048) 1049ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 1050(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 1051(t1.a != 2) AND t1.a>0, 1052t9 1053WHERE t0.a=1 AND 1054t0.b=t1.b AND 1055(t2.a >= 4 OR t2.c IS NULL) AND 1056(t3.a < 5 OR t3.c IS NULL) AND 1057(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 1058(t5.a >=2 OR t5.c IS NULL) AND 1059(t6.a >=4 OR t6.c IS NULL) AND 1060(t7.a <= 2 OR t7.c IS NULL) AND 1061(t8.a < 1 OR t8.c IS NULL) AND 1062(t8.b=t9.b OR t8.c IS NULL) AND 1063(t9.a=1); 1064id select_type table type possible_keys key key_len ref rows filtered Extra 10651 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 10661 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 10671 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) 10681 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 10691 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 10701 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where 10711 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 10721 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 10731 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where 10741 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where 1075Warnings: 1076Note 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)) 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) 1077SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, 1078t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b 1079FROM t0,t1 1080LEFT JOIN 1081( 1082t2 1083LEFT JOIN 1084(t3, t4) 1085ON t3.a=1 AND t2.b=t4.b, 1086t5 1087LEFT JOIN 1088( 1089(t6, t7) 1090LEFT JOIN 1091t8 1092ON t7.b=t8.b AND t6.b < 10 1093) 1094ON t6.b >= 2 AND t5.b=t7.b 1095) 1096ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND 1097(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND 1098(t1.a != 2) AND t1.a>0, 1099t9 1100WHERE t0.a=1 AND 1101t0.b=t1.b AND 1102(t2.a >= 4 OR t2.c IS NULL) AND 1103(t3.a < 5 OR t3.c IS NULL) AND 1104(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND 1105(t5.a >=2 OR t5.c IS NULL) AND 1106(t6.a >=4 OR t6.c IS NULL) AND 1107(t7.a <= 2 OR t7.c IS NULL) AND 1108(t8.a < 1 OR t8.c IS NULL) AND 1109(t8.b=t9.b OR t8.c IS NULL) AND 1110(t9.a=1); 1111a b a b a b a b a b a b a b a b a b a b 11121 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 11131 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2 11141 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1 11151 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 11161 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1 11171 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1 11181 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1 11191 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1 11201 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2 11211 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2 11221 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 11231 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2 11241 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2 11251 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2 11261 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2 11271 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2 11281 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2 1129SELECT t2.a,t2.b 1130FROM t2; 1131a b 11323 3 11334 2 11345 3 1135-1 9 1136-3 10 1137-2 8 1138-4 11 1139-5 15 1140SELECT t3.a,t3.b 1141FROM t3; 1142a b 11431 2 11442 2 1145SELECT t2.a,t2.b,t3.a,t3.b 1146FROM t2 LEFT JOIN t3 ON t2.b=t3.b 1147WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL); 1148a b a b 11494 2 1 2 11504 2 2 2 11515 3 NULL NULL 1152SELECT t2.a,t2.b,t3.a,t3.b 1153FROM t2 LEFT JOIN t3 ON t2.b=t3.b 1154WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL); 1155a b a b 11564 2 1 2 11574 2 2 2 11585 3 NULL NULL 1159ALTER TABLE t3 1160CHANGE COLUMN a a1 int, 1161CHANGE COLUMN c c1 int; 1162SELECT t2.a,t2.b,t3.a1,t3.b 1163FROM t2 LEFT JOIN t3 ON t2.b=t3.b 1164WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL); 1165a b a1 b 11664 2 1 2 11674 2 2 2 11685 3 NULL NULL 1169SELECT t2.a,t2.b,t3.a1,t3.b 1170FROM t2 NATURAL LEFT JOIN t3 1171WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL); 1172a b a1 b 11734 2 1 2 11744 2 2 2 11755 3 NULL NULL 1176DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 1177CREATE TABLE t1 (a int); 1178CREATE TABLE t2 (a int); 1179CREATE TABLE t3 (a int); 1180INSERT INTO t1 VALUES (1); 1181INSERT INTO t2 VALUES (2); 1182INSERT INTO t3 VALUES (2); 1183INSERT INTO t1 VALUES (2); 1184SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a; 1185a a a 11861 NULL NULL 11872 2 2 1188SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; 1189a a a 11901 NULL NULL 11912 2 2 1192DELETE FROM t1 WHERE a=2; 1193SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; 1194a a a 11951 NULL NULL 1196DELETE FROM t2; 1197SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; 1198a a a 11991 NULL NULL 1200DROP TABLE t1,t2,t3; 1201CREATE TABLE t1(a int, key (a)); 1202CREATE TABLE t2(b int, key (b)); 1203CREATE TABLE t3(c int, key (c)); 1204INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), 1205(10), (11), (12), (13), (14), (15), (16), (17), (18), (19); 1206INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), 1207(10), (11), (12), (13), (14), (15), (16), (17), (18), (19); 1208INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5); 1209EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c; 1210id select_type table type possible_keys key key_len ref rows Extra 12111 SIMPLE t1 index NULL a 5 NULL 21 Using index 12121 SIMPLE t2 range b b 5 NULL 3 Using where; Using index 12131 SIMPLE t3 ref c c 5 test.t2.b 2 Using index 1214EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1215id select_type table type possible_keys key key_len ref rows Extra 12161 SIMPLE t1 index NULL a 5 NULL 21 Using index 12171 SIMPLE t2 range b b 5 NULL 3 Using where; Using index 12181 SIMPLE t3 ref c c 5 test.t2.b 2 Using index 1219SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1220a b c 1221NULL 0 0 1222NULL 1 1 1223NULL 2 2 12240 0 0 12250 1 1 12260 2 2 12271 0 0 12281 1 1 12291 2 2 12302 0 0 12312 1 1 12322 2 2 12333 0 0 12343 1 1 12353 2 2 12364 0 0 12374 1 1 12384 2 2 12395 0 0 12405 1 1 12415 2 2 12426 0 0 12436 1 1 12446 2 2 12457 0 0 12467 1 1 12477 2 2 12488 0 0 12498 1 1 12508 2 2 12519 0 0 12529 1 1 12539 2 2 125410 0 0 125510 1 1 125610 2 2 125711 0 0 125811 1 1 125911 2 2 126012 0 0 126112 1 1 126212 2 2 126313 0 0 126413 1 1 126513 2 2 126614 0 0 126714 1 1 126814 2 2 126915 0 0 127015 1 1 127115 2 2 127216 0 0 127316 1 1 127416 2 2 127517 0 0 127617 1 1 127717 2 2 127818 0 0 127918 1 1 128018 2 2 128119 0 0 128219 1 1 128319 2 2 1284DELETE FROM t3; 1285EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1286id select_type table type possible_keys key key_len ref rows Extra 12871 SIMPLE t1 index NULL a 5 NULL 21 Using index 12881 SIMPLE t3 index c c 5 NULL 0 Using where; Using index 12891 SIMPLE t2 ref b b 5 test.t3.c 2 Using index 1290SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; 1291a b c 1292NULL NULL NULL 12930 NULL NULL 12941 NULL NULL 12952 NULL NULL 12963 NULL NULL 12974 NULL NULL 12985 NULL NULL 12996 NULL NULL 13007 NULL NULL 13018 NULL NULL 13029 NULL NULL 130310 NULL NULL 130411 NULL NULL 130512 NULL NULL 130613 NULL NULL 130714 NULL NULL 130815 NULL NULL 130916 NULL NULL 131017 NULL NULL 131118 NULL NULL 131219 NULL NULL 1313DROP TABLE t1,t2,t3; 1314CREATE TABLE t1 (c11 int); 1315CREATE TABLE t2 (c21 int); 1316CREATE TABLE t3 (c31 int); 1317INSERT INTO t1 VALUES (4), (5); 1318SELECT * FROM t1 LEFT JOIN t2 ON c11=c21; 1319c11 c21 13204 NULL 13215 NULL 1322EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21; 1323id select_type table type possible_keys key key_len ref rows Extra 13241 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 13251 SIMPLE t1 ALL NULL NULL NULL NULL 2 1326SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; 1327c11 c21 c31 13284 NULL NULL 13295 NULL NULL 1330EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; 1331id select_type table type possible_keys key key_len ref rows Extra 13321 SIMPLE t1 ALL NULL NULL NULL NULL 2 13331 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where 13341 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where 1335DROP TABLE t1,t2,t3; 1336CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); 1337INSERT INTO t1 VALUES (23, 2340), (26, 9900); 1338CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2)); 1339INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr'); 1340create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL); 1341INSERT INTO t3 VALUES (3,23), (6,26); 1342CREATE TABLE t4 (groupid int(12)); 1343INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6); 1344SELECT * FROM 1345(SELECT DISTINCT gl.groupid, gp.price 1346FROM t4 gl 1347LEFT JOIN 1348(t3 g INNER JOIN t2 p ON g.goodsid = p.goods 1349INNER JOIN t1 gp ON p.goods = gp.goods) 1350ON gl.groupid = g.groupid and p.shop = 'fr') t; 1351groupid price 13521 NULL 13532 NULL 13543 2340 13554 NULL 13565 NULL 13576 9900 1358CREATE VIEW v1 AS 1359SELECT g.groupid groupid, p.goods goods, 1360p.name name, p.shop shop, 1361gp.price price 1362FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods 1363INNER JOIN t1 gp on p.goods = gp.goods; 1364CREATE VIEW v2 AS 1365SELECT DISTINCT g.groupid, fr.price 1366FROM t4 g 1367LEFT JOIN 1368v1 fr on g.groupid = fr.groupid and fr.shop = 'fr'; 1369SELECT * FROM v2; 1370groupid price 13711 NULL 13722 NULL 13733 2340 13744 NULL 13755 NULL 13766 9900 1377SELECT * FROM 1378(SELECT DISTINCT g.groupid, fr.price 1379FROM t4 g 1380LEFT JOIN 1381v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t; 1382groupid price 13831 NULL 13842 NULL 13853 2340 13864 NULL 13875 NULL 13886 9900 1389DROP VIEW v1,v2; 1390DROP TABLE t1,t2,t3,t4; 1391CREATE TABLE t1(a int); 1392CREATE TABLE t2(b int); 1393CREATE TABLE t3(c int, d int); 1394CREATE TABLE t4(d int); 1395CREATE TABLE t5(e int, f int); 1396CREATE TABLE t6(f int); 1397CREATE VIEW v1 AS 1398SELECT e FROM t5 JOIN t6 ON t5.e=t6.f; 1399CREATE VIEW v2 AS 1400SELECT e FROM t5 NATURAL JOIN t6; 1401SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d); 1402a 1403SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d); 1404ERROR 42S22: Unknown column 't1.x' in 'field list' 1405SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4; 1406a 1407SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4; 1408ERROR 42S22: Unknown column 't1.x' in 'field list' 1409SELECT v1.e FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1410e 1411SELECT v1.x FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1412ERROR 42S22: Unknown column 'v1.x' in 'field list' 1413SELECT v2.e FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1414e 1415SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); 1416ERROR 42S22: Unknown column 'v2.x' in 'field list' 1417DROP VIEW v1, v2; 1418DROP TABLE t1, t2, t3, t4, t5, t6; 1419create table t1 (id1 int(11) not null); 1420insert into t1 values (1),(2); 1421create table t2 (id2 int(11) not null); 1422insert into t2 values (1),(2),(3),(4); 1423create table t3 (id3 char(16) not null); 1424insert into t3 values ('100'); 1425create table t4 (id2 int(11) not null, id3 char(16)); 1426create table t5 (id1 int(11) not null, key (id1)); 1427insert into t5 values (1),(2),(1); 1428create view v1 as 1429select t4.id3 from t4 join t2 on t4.id2 = t2.id2; 1430select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3); 1431id1 14321 14332 1434drop view v1; 1435drop table t1, t2, t3, t4, t5; 1436create table t0 (a int); 1437insert into t0 values (0),(1),(2),(3); 1438create table t1(a int); 1439insert into t1 select A.a + 10*(B.a) from t0 A, t0 B; 1440create table t2 (a int, b int); 1441insert into t2 values (1,1), (2,2), (3,3); 1442create table t3(a int, b int, filler char(200), key(a)); 1443insert into t3 select a,a,'filler' from t1; 1444insert into t3 select a,a,'filler' from t1; 1445create table t4 like t3; 1446insert into t4 select * from t3; 1447insert into t4 select * from t3; 1448create table t5 like t4; 1449insert into t5 select * from t4; 1450insert into t5 select * from t4; 1451create table t6 like t5; 1452insert into t6 select * from t5; 1453insert into t6 select * from t5; 1454create table t7 like t6; 1455insert into t7 select * from t6; 1456insert into t7 select * from t6; 1457explain select * from t4 join 1458t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; 1459id select_type table type possible_keys key key_len ref rows Extra 14601 SIMPLE t2 ALL NULL NULL NULL NULL X 14611 SIMPLE t3 ref a a 5 test.t2.b X Using where 14621 SIMPLE t5 ref a a 5 test.t3.b X 14631 SIMPLE t4 ref a a 5 test.t3.b X Using index condition 1464explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b 1465join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; 1466id select_type table type possible_keys key key_len ref rows Extra 14671 SIMPLE t2 ALL NULL NULL NULL NULL X 14681 SIMPLE t3 ref a a 5 test.t2.b X Using index condition 14691 SIMPLE t4 ref a a 5 test.t3.b X Using where 14701 SIMPLE t6 ref a a 5 test.t4.b X 14711 SIMPLE t5 ref a a 5 test.t2.b X Using where 14721 SIMPLE t7 ref a a 5 test.t5.b X 1473explain select * from t2 left join 1474(t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b 1475join t5 on t5.a=t3.b) on t3.a=t2.b; 1476id select_type table type possible_keys key key_len ref rows Extra 14771 SIMPLE t2 ALL NULL NULL NULL NULL X 14781 SIMPLE t3 ref a a 5 test.t2.b X Using where 14791 SIMPLE t5 ref a a 5 test.t3.b X 14801 SIMPLE t4 ref a a 5 test.t5.a X Using where 14811 SIMPLE t6 ref a a 5 test.t4.b X 1482drop table t0, t1, t2, t3, t4, t5, t6, t7; 1483create table t1 (a int); 1484insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1485create table t2 (a int, filler char(100), key(a)); 1486insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B; 1487create table t3 like t2; 1488insert into t3 select * from t2; 1489explain select * from t1 left join 1490(t2 left join t3 on (t2.a = t3.a)) 1491on (t1.a = t2.a); 1492id select_type table type possible_keys key key_len ref rows Extra 14931 SIMPLE t1 ALL NULL NULL NULL NULL 10 14941 SIMPLE t2 ref a a 5 test.t1.a 1 Using where 14951 SIMPLE t3 ref a a 5 test.t1.a 1 Using where 1496drop table t1, t2, t3; 1497CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); 1498CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); 1499CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY, 1500id int NOT NULL, 1501pid int NOT NULL); 1502INSERT INTO t1 VALUES (1, 'A'), (3, 'C'); 1503INSERT INTO t2 VALUES (1, 'A'), (3, 'C'); 1504INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3); 1505SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1) 1506ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id) 1507LEFT JOIN t2 ON (t3.pid=t2.pid) 1508WHERE p.id=1; 1509id type cid id pid id type pid type 15101 A NULL NULL NULL NULL NULL NULL NULL 1511CREATE VIEW v1 AS 1512SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B'; 1513SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id 1514LEFT JOIN t2 ON v1.pid=t2.pid 1515WHERE p.id=1; 1516id type cid id pid pid type 15171 A NULL NULL NULL NULL NULL 1518DROP VIEW v1; 1519DROP TABLE t1,t2,t3; 1520CREATE TABLE t1 (id1 int PRIMARY KEY, id2 int); 1521CREATE TABLE t2 (id1 int PRIMARY KEY, id2 int); 1522CREATE TABLE t3 (id1 int PRIMARY KEY, id2 int); 1523CREATE TABLE t4 (id1 int PRIMARY KEY, id2 int); 1524CREATE TABLE t5 (id1 int PRIMARY KEY, id2 int); 1525SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa 1526FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1 1527LEFT OUTER JOIN 1528(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1) 1529ON t3.id2 IS NOT NULL 1530WHERE t1.id1=2; 1531id ngroupbynsa 1532PREPARE stmt FROM 1533"SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa 1534 FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1 1535 LEFT OUTER JOIN 1536 (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1) 1537 ON t3.id2 IS NOT NULL 1538 WHERE t1.id1=2"; 1539EXECUTE stmt; 1540id ngroupbynsa 1541EXECUTE stmt; 1542id ngroupbynsa 1543EXECUTE stmt; 1544id ngroupbynsa 1545EXECUTE stmt; 1546id ngroupbynsa 1547INSERT INTO t1 VALUES (1,1), (2,1), (3,2); 1548INSERT INTO t2 VALUES (2,1), (3,2), (4,3); 1549INSERT INTO t3 VALUES (1,1), (3,2), (2,NULL); 1550INSERT INTO t4 VALUES (1,1), (2,1), (3,3); 1551INSERT INTO t5 VALUES (1,1), (2,2), (3,3), (4,3); 1552EXECUTE stmt; 1553id ngroupbynsa 15542 1 15552 1 1556EXECUTE stmt; 1557id ngroupbynsa 15582 1 15592 1 1560EXECUTE stmt; 1561id ngroupbynsa 15622 1 15632 1 1564EXECUTE stmt; 1565id ngroupbynsa 15662 1 15672 1 1568SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa 1569FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1 1570LEFT OUTER JOIN 1571(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1) 1572ON t3.id2 IS NOT NULL 1573WHERE t1.id1=2; 1574id ngroupbynsa 15752 1 15762 1 1577DROP TABLE t1,t2,t3,t4,t5; 1578CREATE TABLE t1 ( 1579id int NOT NULL PRIMARY KEY, 1580ct int DEFAULT NULL, 1581pc int DEFAULT NULL, 1582INDEX idx_ct (ct), 1583INDEX idx_pc (pc) 1584); 1585INSERT INTO t1 VALUES 1586(1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL); 1587CREATE TABLE t2 ( 1588id int NOT NULL PRIMARY KEY, 1589sr int NOT NULL, 1590nm varchar(255) NOT NULL, 1591INDEX idx_sr (sr) 1592); 1593INSERT INTO t2 VALUES 1594(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand'); 1595CREATE TABLE t3 ( 1596id int NOT NULL PRIMARY KEY, 1597ct int NOT NULL, 1598ln int NOT NULL, 1599INDEX idx_ct (ct), 1600INDEX idx_ln (ln) 1601); 1602CREATE TABLE t4 ( 1603id int NOT NULL PRIMARY KEY, 1604nm varchar(255) NOT NULL 1605); 1606INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique'); 1607SELECT t1.* 1608FROM t1 LEFT JOIN 1609(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id 1610WHERE t1.id='5'; 1611id ct pc 16125 NULL NULL 1613SELECT t1.*, t4.nm 1614FROM t1 LEFT JOIN 1615(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id 1616LEFT JOIN t4 ON t2.sr=t4.id 1617WHERE t1.id='5'; 1618id ct pc nm 16195 NULL NULL NULL 1620DROP TABLE t1,t2,t3,t4; 1621CREATE TABLE t1 (a INT, b INT); 1622CREATE TABLE t2 (a INT); 1623CREATE TABLE t3 (a INT, c INT); 1624CREATE TABLE t4 (a INT, c INT); 1625CREATE TABLE t5 (a INT, c INT); 1626SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) 1627LEFT JOIN t5 USING (a)) USING (a); 1628b 1629SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) 1630LEFT JOIN t5 USING (a)) USING (a); 1631ERROR 23000: Column 'c' in field list is ambiguous 1632SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) 1633JOIN t5 USING (a)) USING (a); 1634b 1635SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a) 1636JOIN t5 USING (a)) USING (a); 1637ERROR 23000: Column 'c' in field list is ambiguous 1638DROP TABLE t1,t2,t3,t4,t5; 1639CREATE TABLE t1 (a INT, b INT); 1640CREATE TABLE t2 (a INT, b INT); 1641CREATE TABLE t3 (a INT, b INT); 1642INSERT INTO t1 VALUES (1,1); 1643INSERT INTO t2 VALUES (1,1); 1644INSERT INTO t3 VALUES (1,1); 1645SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a); 1646ERROR 23000: Column 'a' in from clause is ambiguous 1647DROP TABLE t1,t2,t3; 1648CREATE TABLE t1 ( 1649carrier char(2) default NULL, 1650id int NOT NULL auto_increment PRIMARY KEY 1651); 1652INSERT INTO t1 VALUES 1653('CO',235371754),('CO',235376554),('CO',235376884),('CO',235377874), 1654('CO',231060394),('CO',231059224),('CO',231059314),('CO',231060484), 1655('CO',231060274),('CO',231060124),('CO',231060244),('CO',231058594), 1656('CO',231058924),('CO',231058504),('CO',231059344),('CO',231060424), 1657('CO',231059554),('CO',231060304),('CO',231059644),('CO',231059464), 1658('CO',231059764),('CO',231058294),('CO',231058624),('CO',231058864), 1659('CO',231059374),('CO',231059584),('CO',231059734),('CO',231059014), 1660('CO',231059854),('CO',231059494),('CO',231059794),('CO',231058534), 1661('CO',231058324),('CO',231058684),('CO',231059524),('CO',231059974); 1662CREATE TABLE t2 ( 1663scan_date date default NULL, 1664package_id int default NULL, 1665INDEX scan_date(scan_date), 1666INDEX package_id(package_id) 1667); 1668INSERT INTO t2 VALUES 1669('2008-12-29',231062944),('2008-12-29',231065764),('2008-12-29',231066124), 1670('2008-12-29',231060094),('2008-12-29',231061054),('2008-12-29',231065644), 1671('2008-12-29',231064384),('2008-12-29',231064444),('2008-12-29',231073774), 1672('2008-12-29',231058594),('2008-12-29',231059374),('2008-12-29',231066004), 1673('2008-12-29',231068494),('2008-12-29',231070174),('2008-12-29',231071884), 1674('2008-12-29',231063274),('2008-12-29',231063754),('2008-12-29',231064144), 1675('2008-12-29',231069424),('2008-12-29',231073714),('2008-12-29',231058414), 1676('2008-12-29',231060994),('2008-12-29',231069154),('2008-12-29',231068614), 1677('2008-12-29',231071464),('2008-12-29',231074014),('2008-12-29',231059614), 1678('2008-12-29',231059074),('2008-12-29',231059464),('2008-12-29',231069094), 1679('2008-12-29',231067294),('2008-12-29',231070144),('2008-12-29',231073804), 1680('2008-12-29',231072634),('2008-12-29',231058294),('2008-12-29',231065344), 1681('2008-12-29',231066094),('2008-12-29',231069034),('2008-12-29',231058594), 1682('2008-12-29',231059854),('2008-12-29',231059884),('2008-12-29',231059914), 1683('2008-12-29',231063664),('2008-12-29',231063814),('2008-12-29',231063904); 1684CREATE TABLE t3 ( 1685package_id int default NULL, 1686INDEX package_id(package_id) 1687); 1688INSERT INTO t3 VALUES 1689(231058294),(231058324),(231058354),(231058384),(231058414),(231058444), 1690(231058474),(231058504),(231058534),(231058564),(231058594),(231058624), 1691(231058684),(231058744),(231058804),(231058864),(231058924),(231058954), 1692(231059014),(231059074),(231059104),(231059134),(231059164),(231059194), 1693(231059224),(231059254),(231059284),(231059314),(231059344),(231059374), 1694(231059404),(231059434),(231059464),(231059494),(231059524),(231059554), 1695(231059584),(231059614),(231059644),(231059674),(231059704),(231059734), 1696(231059764),(231059794),(231059824),(231059854),(231059884),(231059914), 1697(231059944),(231059974),(231060004),(231060034),(231060064),(231060094), 1698(231060124),(231060154),(231060184),(231060214),(231060244),(231060274), 1699(231060304),(231060334),(231060364),(231060394),(231060424),(231060454), 1700(231060484),(231060514),(231060544),(231060574),(231060604),(231060634), 1701(231060664),(231060694),(231060724),(231060754),(231060784),(231060814), 1702(231060844),(231060874),(231060904),(231060934),(231060964),(231060994), 1703(231061024),(231061054),(231061084),(231061144),(231061174),(231061204), 1704(231061234),(231061294),(231061354),(231061384),(231061414),(231061474), 1705(231061564),(231061594),(231061624),(231061684),(231061714),(231061774), 1706(231061804),(231061894),(231061984),(231062074),(231062134),(231062224), 1707(231062254),(231062314),(231062374),(231062434),(231062494),(231062554), 1708(231062584),(231062614),(231062644),(231062704),(231062734),(231062794), 1709(231062854),(231062884),(231062944),(231063004),(231063034),(231063064), 1710(231063124),(231063154),(231063184),(231063214),(231063274),(231063334), 1711(231063394),(231063424),(231063454),(231063514),(231063574),(231063664); 1712CREATE TABLE t4 ( 1713carrier char(2) NOT NULL default '' PRIMARY KEY, 1714id int(11) default NULL, 1715INDEX id(id) 1716); 1717INSERT INTO t4 VALUES 1718('99',6),('SK',456),('UA',486),('AI',1081),('OS',1111),('VS',1510); 1719CREATE TABLE t5 ( 1720carrier_id int default NULL, 1721INDEX carrier_id(carrier_id) 1722); 1723INSERT INTO t5 VALUES 1724(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1725(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1726(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1727(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1728(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6), 1729(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(6),(456),(456),(456), 1730(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456),(456), 1731(456),(486),(1081),(1111),(1111),(1111),(1111),(1510); 1732SELECT COUNT(*) 1733FROM((t2 JOIN t1 ON t2.package_id = t1.id) 1734JOIN t3 ON t3.package_id = t1.id); 1735COUNT(*) 17366 1737EXPLAIN 1738SELECT COUNT(*) 1739FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 1740JOIN t3 ON t3.package_id = t1.id) 1741LEFT JOIN 1742(t5 JOIN t4 ON t5.carrier_id = t4.id) 1743ON t4.carrier = t1.carrier; 1744id select_type table type possible_keys key key_len ref rows Extra 17451 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index 17461 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 17471 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 17481 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index 17491 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index 1750SELECT COUNT(*) 1751FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 1752JOIN t3 ON t3.package_id = t1.id) 1753LEFT JOIN 1754(t5 JOIN t4 ON t5.carrier_id = t4.id) 1755ON t4.carrier = t1.carrier; 1756COUNT(*) 17576 1758DROP TABLE t1,t2,t3,t4,t5; 1759CREATE TABLE t1 ( 1760pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1761a int DEFAULT NULL, 1762KEY idx(a) 1763); 1764CREATE TABLE t2 ( 1765pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1766a int DEFAULT NULL, 1767KEY idx(a) 1768); 1769CREATE TABLE t3 ( 1770pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1771a int DEFAULT NULL, 1772KEY idx(a) 1773); 1774INSERT INTO t1 VALUES 1775(1,2), (2,7), (3,5), (4,7), (5,5), (6,NULL), (7,NULL), (8,9); 1776INSERT INTO t2 VALUES 1777(1,NULL), (4,2), (5,2), (3,4), (2,8); 1778INSERT INTO t3 VALUES 1779(1,9), (2,2), (3,5), (4,2), (5,7), (6,0), (7,5); 1780SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a 1781FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a; 1782pk a pk a pk a 17831 2 4 2 2 2 17841 2 4 2 4 2 17851 2 5 2 2 2 17861 2 5 2 4 2 17872 7 NULL NULL NULL NULL 17883 5 NULL NULL NULL NULL 17894 7 NULL NULL NULL NULL 17905 5 NULL NULL NULL NULL 17916 NULL NULL NULL NULL NULL 17927 NULL NULL NULL NULL NULL 17938 9 NULL NULL NULL NULL 1794SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a 1795FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a 1796WHERE t2.pk IS NULL; 1797pk a pk a pk a 17982 7 NULL NULL NULL NULL 17993 5 NULL NULL NULL NULL 18004 7 NULL NULL NULL NULL 18015 5 NULL NULL NULL NULL 18026 NULL NULL NULL NULL NULL 18037 NULL NULL NULL NULL NULL 18048 9 NULL NULL NULL NULL 1805SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a 1806FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a 1807WHERE t3.pk IS NULL; 1808pk a pk a pk a 18092 7 NULL NULL NULL NULL 18103 5 NULL NULL NULL NULL 18114 7 NULL NULL NULL NULL 18125 5 NULL NULL NULL NULL 18136 NULL NULL NULL NULL NULL 18147 NULL NULL NULL NULL NULL 18158 9 NULL NULL NULL NULL 1816DROP TABLE t1, t2, t3; 1817CREATE TABLE t1 (a int NOT NULL ); 1818INSERT INTO t1 VALUES (9), (9); 1819CREATE TABLE t2 (a int NOT NULL ); 1820INSERT INTO t2 VALUES (9); 1821CREATE TABLE t3 (a int NOT NULL, b int); 1822INSERT INTO t3 VALUES (19,9); 1823CREATE TABLE t4 (b int) ; 1824SELECT * FROM t1 LEFT JOIN 1825((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) 1826ON t1.a=t2.a; 1827a a a b b 18289 9 19 9 NULL 18299 9 19 9 NULL 1830SELECT * FROM t1 LEFT JOIN 1831((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) 1832ON t1.a=t2.a 1833WHERE t3.a IS NULL; 1834a a a b b 1835EXPLAIN EXTENDED 1836SELECT * FROM t1 LEFT JOIN 1837((t2 LEFT JOIN t3 ON t2.a=t3.b) LEFT JOIN t4 ON t3.a=t4.b) 1838ON t1.a=t2.a 1839WHERE t3.a IS NULL; 1840id select_type table type possible_keys key key_len ref rows filtered Extra 18411 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 18421 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using where 18431 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00 Using where; Not exists 18441 SIMPLE t4 ALL NULL NULL NULL NULL 0 0.00 Using where 1845Warnings: 1846Note 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`)) on(`test`.`t2`.`a` = `test`.`t1`.`a`) where `test`.`t3`.`a` is null 1847DROP TABLE t1,t2,t3,t4; 1848SET optimizer_switch=@save_optimizer_switch; 1849End of 5.0 tests 1850# 1851# MDEV-621: LP:693329 - Assertion `!is_interleave_error' failed on low optimizer_search_depth 1852# 1853set @tmp_mdev621= @@optimizer_search_depth; 1854SET SESSION optimizer_search_depth = 4; 1855CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int) ; 1856INSERT IGNORE INTO t1 VALUES (0,0,2,0),(NULL,0,2,0); 1857CREATE TABLE t2 (f1 int) ; 1858CREATE TABLE t3 (f3 int,PRIMARY KEY (f3)) ; 1859CREATE TABLE t4 (f5 int) ; 1860CREATE TABLE t5 (f2 int) ; 1861SELECT alias2.f4 FROM t1 AS alias1 1862LEFT JOIN t1 AS alias2 1863LEFT JOIN t2 AS alias3 1864LEFT JOIN t3 AS alias4 ON alias3.f1 = alias4.f3 1865ON alias2.f1 1866LEFT JOIN t4 AS alias5 1867JOIN t5 ON alias5.f5 1868ON alias2.f3 ON alias1.f2; 1869f4 1870NULL 1871NULL 1872DROP TABLE t1,t2,t3,t4,t5; 1873# 1874# MDEV-7992: Nested left joins + 'not exists' optimization 1875# 1876CREATE TABLE t1( 1877K1 INT PRIMARY KEY, 1878Name VARCHAR(15) 1879); 1880INSERT INTO t1 VALUES 1881(1,'T1Row1'), (2,'T1Row2'); 1882CREATE TABLE t2( 1883K2 INT PRIMARY KEY, 1884K1r INT, 1885rowTimestamp DATETIME, 1886Event VARCHAR(15) 1887); 1888INSERT INTO t2 VALUES 1889(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), 1890(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), 1891(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); 1892SELECT t1a.*, t2a.*, 1893t2i.K2 AS K2B, t2i.K1r AS K1rB, 1894t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB 1895FROM 1896t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 1897LEFT JOIN 1898( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) 1899ON (t1i.K1 = 1) AND 1900(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR 1901(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) 1902OR (t2i.K2 IS NULL)) 1903WHERE 1904t2a.K1r = 1 AND t2i.K2 IS NULL; 1905K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB 19061 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL 1907EXPLAIN EXTENDED SELECT t1a.*, t2a.*, 1908t2i.K2 AS K2B, t2i.K1r AS K1rB, 1909t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB 1910FROM 1911t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 1912LEFT JOIN 1913( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) 1914ON (t1i.K1 = 1) AND 1915(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR 1916(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) 1917OR (t2i.K2 IS NULL)) 1918WHERE 1919t2a.K1r = 1 AND t2i.K2 IS NULL; 1920id select_type table type possible_keys key key_len ref rows filtered Extra 19211 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 19221 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where 19231 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index 19241 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists 1925Warnings: 1926Note 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 1927CREATE VIEW v1 AS 1928SELECT t2i.* 1929FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1 1930WHERE t1i.K1 = 1 ; 1931SELECT 1932t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, 1933t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB 1934FROM 1935t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 1936LEFT JOIN 1937v1 as t2b 1938ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR 1939(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) 1940OR (t2b.K2 IS NULL) 1941WHERE 1942t1a.K1 = 1 AND 1943t2b.K2 IS NULL; 1944K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB 19451 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL 1946EXPLAIN EXTENDED SELECT 1947t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, 1948t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB 1949FROM 1950t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 1951LEFT JOIN 1952v1 as t2b 1953ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR 1954(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) 1955OR (t2b.K2 IS NULL) 1956WHERE 1957t1a.K1 = 1 AND 1958t2b.K2 IS NULL; 1959id select_type table type possible_keys key key_len ref rows filtered Extra 19601 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 19611 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where 19621 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index 19631 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists 1964Warnings: 1965Note 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 1966DROP VIEW v1; 1967DROP TABLE t1,t2; 1968set optimizer_search_depth= @tmp_mdev621; 1969# 1970# MDEV-19588: Nested left joins using optimized join cache 1971# 1972set optimizer_switch='optimize_join_buffer_size=on'; 1973set @save_join_cache_level= @@join_cache_level; 1974set join_cache_level=2; 1975CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; 1976CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; 1977INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); 1978CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; 1979INSERT INTO t3 VALUES 1980(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), 1981(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), 1982(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); 1983SELECT t3.* 1984FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 1985WHERE t2.pk < 13 OR t3.i1 IS NULL; 1986pk c1 i1 19877 a NULL 198817 a NULL 198926 a NULL 1990explain extended SELECT t3.* 1991FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 1992WHERE t2.pk < 13 OR t3.i1 IS NULL; 1993id select_type table type possible_keys key key_len ref rows filtered Extra 19941 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00 19951 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join) 19961 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1997Warnings: 1998Note 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 1999DROP TABLE t1,t2,t3; 2000set join_cache_level= @save_join_cache_level; 2001set optimizer_switch=@save_optimizer_switch; 2002