summaryrefslogtreecommitdiff
path: root/mysql-test/r/join_nested.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/join_nested.result')
-rw-r--r--mysql-test/r/join_nested.result1405
1 files changed, 1405 insertions, 0 deletions
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
new file mode 100644
index 00000000000..9d514be76e8
--- /dev/null
+++ b/mysql-test/r/join_nested.result
@@ -0,0 +1,1405 @@
+DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
+CREATE TABLE t0 (a int, b int, c int);
+CREATE TABLE t1 (a int, b int, c int);
+CREATE TABLE t2 (a int, b int, c int);
+CREATE TABLE t3 (a int, b int, c int);
+CREATE TABLE t4 (a int, b int, c int);
+CREATE TABLE t5 (a int, b int, c int);
+CREATE TABLE t6 (a int, b int, c int);
+CREATE TABLE t7 (a int, b int, c int);
+CREATE TABLE t8 (a int, b int, c int);
+CREATE TABLE t9 (a int, b int, c int);
+INSERT INTO t0 VALUES (1,1,0), (1,2,0), (2,2,0);
+INSERT INTO t1 VALUES (1,3,0), (2,2,0), (3,2,0);
+INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
+INSERT INTO t3 VALUES (1,2,0), (2,2,0);
+INSERT INTO t4 VALUES (3,2,0), (4,2,0);
+INSERT INTO t5 VALUES (3,1,0), (2,2,0), (3,3,0);
+INSERT INTO t6 VALUES (3,2,0), (6,2,0), (6,1,0);
+INSERT INTO t7 VALUES (1,1,0), (2,2,0);
+INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+INSERT INTO t9 VALUES (1,1,0), (1,2,0), (3,3,0);
+SELECT t2.a,t2.b
+FROM t2;
+a b
+3 3
+4 2
+5 3
+SELECT t3.a,t3.b
+FROM t3;
+a b
+1 2
+2 2
+SELECT t4.a,t4.b
+FROM t4;
+a b
+3 2
+4 2
+SELECT t3.a,t3.b,t4.a,t4.b
+FROM t3,t4;
+a b a b
+1 2 3 2
+2 2 3 2
+1 2 4 2
+2 2 4 2
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t2.b=t4.b;
+a b a b a b
+3 3 NULL NULL NULL NULL
+4 2 1 2 3 2
+4 2 1 2 4 2
+4 2 2 2 3 2
+4 2 2 2 4 2
+5 3 NULL NULL NULL NULL
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b;
+a b a b a b
+3 3 NULL NULL NULL NULL
+4 2 1 2 3 2
+4 2 1 2 4 2
+5 3 NULL NULL NULL NULL
+EXPLAIN EXTENDED
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t2.b=t4.b
+WHERE t3.a=1 OR t3.c IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 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 isnull(`test`.`t3`.`c`))
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t2.b=t4.b
+WHERE t3.a=1 OR t3.c IS NULL;
+a b a b a b
+3 3 NULL NULL NULL NULL
+4 2 1 2 3 2
+4 2 1 2 4 2
+5 3 NULL NULL NULL NULL
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t2.b=t4.b
+WHERE t3.a>1 OR t3.c IS NULL;
+a b a b a b
+3 3 NULL NULL NULL NULL
+4 2 2 2 3 2
+4 2 2 2 4 2
+5 3 NULL NULL NULL NULL
+SELECT t5.a,t5.b
+FROM t5;
+a b
+3 1
+2 2
+3 3
+SELECT t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
+FROM t3,t4,t5;
+a b a b a b
+1 2 3 2 3 1
+2 2 3 2 3 1
+1 2 4 2 3 1
+2 2 4 2 3 1
+1 2 3 2 2 2
+2 2 3 2 2 2
+1 2 4 2 2 2
+2 2 4 2 2 2
+1 2 3 2 3 3
+2 2 3 2 3 3
+1 2 4 2 3 3
+2 2 4 2 3 3
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
+FROM t2
+LEFT JOIN
+(t3, t4, t5)
+ON t2.b=t4.b;
+a b a b a b a b
+3 3 NULL NULL NULL NULL NULL NULL
+4 2 1 2 3 2 3 1
+4 2 1 2 3 2 2 2
+4 2 1 2 3 2 3 3
+4 2 1 2 4 2 3 1
+4 2 1 2 4 2 2 2
+4 2 1 2 4 2 3 3
+4 2 2 2 3 2 3 1
+4 2 2 2 3 2 2 2
+4 2 2 2 3 2 3 3
+4 2 2 2 4 2 3 1
+4 2 2 2 4 2 2 2
+4 2 2 2 4 2 3 3
+5 3 NULL NULL NULL NULL NULL NULL
+EXPLAIN EXTENDED
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
+FROM t2
+LEFT JOIN
+(t3, t4, t5)
+ON t2.b=t4.b
+WHERE t3.a>1 OR t3.c IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3
+Warnings:
+Note 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 isnull(`test`.`t3`.`c`))
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
+FROM t2
+LEFT JOIN
+(t3, t4, t5)
+ON t2.b=t4.b
+WHERE t3.a>1 OR t3.c IS NULL;
+a b a b a b a b
+3 3 NULL NULL NULL NULL NULL NULL
+4 2 2 2 3 2 3 1
+4 2 2 2 3 2 2 2
+4 2 2 2 3 2 3 3
+4 2 2 2 4 2 3 1
+4 2 2 2 4 2 2 2
+4 2 2 2 4 2 3 3
+5 3 NULL NULL NULL NULL NULL NULL
+EXPLAIN EXTENDED
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
+FROM t2
+LEFT JOIN
+(t3, t4, t5)
+ON t2.b=t4.b
+WHERE (t3.a>1 OR t3.c IS NULL) AND
+(t5.a<3 OR t5.c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`)))
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
+FROM t2
+LEFT JOIN
+(t3, t4, t5)
+ON t2.b=t4.b
+WHERE (t3.a>1 OR t3.c IS NULL) AND
+(t5.a<3 OR t5.c IS NULL);
+a b a b a b a b
+3 3 NULL NULL NULL NULL NULL NULL
+4 2 2 2 3 2 2 2
+4 2 2 2 4 2 2 2
+5 3 NULL NULL NULL NULL NULL NULL
+SELECT t6.a,t6.b
+FROM t6;
+a b
+3 2
+6 2
+6 1
+SELECT t7.a,t7.b
+FROM t7;
+a b
+1 1
+2 2
+SELECT t6.a,t6.b,t7.a,t7.b
+FROM t6,t7;
+a b a b
+3 2 1 1
+3 2 2 2
+6 2 1 1
+6 2 2 2
+6 1 1 1
+6 1 2 2
+SELECT t8.a,t8.b
+FROM t8;
+a b
+0 2
+1 2
+EXPLAIN EXTENDED
+SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM (t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3
+1 SIMPLE t8 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 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`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1
+SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM (t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10;
+a b a b a b
+3 2 1 1 NULL NULL
+3 2 2 2 0 2
+3 2 2 2 1 2
+6 2 1 1 NULL NULL
+6 2 2 2 0 2
+6 2 2 2 1 2
+6 1 1 1 NULL NULL
+6 1 2 2 0 2
+6 1 2 2 1 2
+SELECT t5.a,t5.b
+FROM t5;
+a b
+3 1
+2 2
+3 3
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b;
+a b a b a b a b
+3 1 3 2 1 1 NULL NULL
+3 1 6 2 1 1 NULL NULL
+2 2 3 2 2 2 0 2
+2 2 3 2 2 2 1 2
+2 2 6 2 2 2 0 2
+2 2 6 2 2 2 1 2
+3 3 NULL NULL NULL NULL NULL NULL
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b AND
+(t8.a < 1 OR t8.c IS NULL);
+a b a b a b a b
+3 1 3 2 1 1 NULL NULL
+3 1 6 2 1 1 NULL NULL
+2 2 3 2 2 2 0 2
+2 2 6 2 2 2 0 2
+3 3 NULL NULL NULL NULL NULL NULL
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b;
+a b a b a b
+3 3 NULL NULL NULL NULL
+4 2 1 2 3 2
+4 2 1 2 4 2
+5 3 NULL NULL NULL NULL
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b;
+a b a b a b a b a b a b a b
+3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
+4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
+5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+4 2 1 2 3 2 2 2 3 2 2 2 0 2
+4 2 1 2 3 2 2 2 3 2 2 2 1 2
+4 2 1 2 3 2 2 2 6 2 2 2 0 2
+4 2 1 2 3 2 2 2 6 2 2 2 1 2
+4 2 1 2 4 2 2 2 3 2 2 2 0 2
+4 2 1 2 4 2 2 2 3 2 2 2 1 2
+4 2 1 2 4 2 2 2 6 2 2 2 0 2
+4 2 1 2 4 2 2 2 6 2 2 2 1 2
+5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+WHERE t2.a > 3 AND
+(t6.a < 6 OR t6.c IS NULL);
+a b a b a b a b a b a b a b
+4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+4 2 1 2 3 2 2 2 3 2 2 2 0 2
+4 2 1 2 3 2 2 2 3 2 2 2 1 2
+4 2 1 2 4 2 2 2 3 2 2 2 0 2
+4 2 1 2 4 2 2 2 3 2 2 2 1 2
+5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+SELECT t1.a,t1.b
+FROM t1;
+a b
+1 3
+2 2
+3 2
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2);
+a b a b a b a b a b a b a b a b
+1 3 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+1 3 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+1 3 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+1 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+1 3 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+1 3 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+1 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+1 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
+1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+1 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+1 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
+1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+1 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+1 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+3 2 3 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+3 2 3 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+3 2 3 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+3 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+3 2 3 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+3 2 3 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
+3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2
+3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2
+3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2
+3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2
+3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
+3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2
+3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2
+3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2
+3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2
+3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2)
+WHERE (t2.a >= 4 OR t2.c IS NULL);
+a b a b a b a b a b a b a b a b
+1 3 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+1 3 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
+1 3 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+1 3 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+1 3 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
+1 3 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+1 3 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+1 3 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+1 3 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+1 3 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+1 3 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
+3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2
+3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2
+3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2
+3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2
+3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
+3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2
+3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2
+3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2
+3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2
+3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+SELECT t0.a,t0.b
+FROM t0;
+a b
+1 1
+1 2
+2 2
+EXPLAIN EXTENDED
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2)
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3
+1 SIMPLE t8 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)))
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2)
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL);
+a b a b a b a b a b a b a b a b a b
+1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+1 2 3 2 4 2 1 2 3 2 3 1 3 2 1 1 NULL NULL
+1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL
+1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 0 2
+1 2 3 2 4 2 1 2 3 2 2 2 3 2 2 2 1 2
+1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2
+1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 1 2
+1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL
+1 2 3 2 4 2 1 2 4 2 3 1 3 2 1 1 NULL NULL
+1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL
+1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 0 2
+1 2 3 2 4 2 1 2 4 2 2 2 3 2 2 2 1 2
+1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2
+1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 1 2
+1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL
+1 2 3 2 5 3 NULL NULL NULL NULL 3 1 3 2 1 1 NULL NULL
+1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 0 2
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 3 2 2 2 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL
+EXPLAIN EXTENDED
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t8 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+SELECT t9.a,t9.b
+FROM t9;
+a b
+1 1
+1 2
+3 3
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+a b a b a b a b a b a b a b a b a b a b
+1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1
+1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
+1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
+1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1
+1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2
+1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2
+1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
+1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2
+1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2
+SELECT t1.a,t1.b
+FROM t1;
+a b
+1 3
+2 2
+3 2
+SELECT t2.a,t2.b
+FROM t2;
+a b
+3 3
+4 2
+5 3
+SELECT t3.a,t3.b
+FROM t3;
+a b
+1 2
+2 2
+SELECT t2.a,t2.b,t3.a,t3.b
+FROM t2
+LEFT JOIN
+t3
+ON t2.b=t3.b;
+a b a b
+3 3 NULL NULL
+4 2 1 2
+4 2 2 2
+5 3 NULL NULL
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
+FROM t1, t2
+LEFT JOIN
+t3
+ON t2.b=t3.b
+WHERE t1.a <= 2;
+a b a b a b
+1 3 3 3 NULL NULL
+2 2 3 3 NULL NULL
+1 3 4 2 1 2
+1 3 4 2 2 2
+2 2 4 2 1 2
+2 2 4 2 2 2
+1 3 5 3 NULL NULL
+2 2 5 3 NULL NULL
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
+FROM t1, t3
+RIGHT JOIN
+t2
+ON t2.b=t3.b
+WHERE t1.a <= 2;
+a b a b a b
+1 3 3 3 NULL NULL
+2 2 3 3 NULL NULL
+1 3 4 2 1 2
+1 3 4 2 2 2
+2 2 4 2 1 2
+2 2 4 2 2 2
+1 3 5 3 NULL NULL
+2 2 5 3 NULL NULL
+SELECT t3.a,t3.b,t4.a,t4.b
+FROM t3,t4;
+a b a b
+1 2 3 2
+2 2 3 2
+1 2 4 2
+2 2 4 2
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b;
+a b a b a b
+3 3 NULL NULL NULL NULL
+4 2 1 2 3 2
+4 2 1 2 4 2
+5 3 NULL NULL NULL NULL
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t1, t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b
+WHERE t1.a <= 2;
+a b a b a b a b
+1 3 3 3 NULL NULL NULL NULL
+2 2 3 3 NULL NULL NULL NULL
+1 3 4 2 1 2 3 2
+1 3 4 2 1 2 4 2
+2 2 4 2 1 2 3 2
+2 2 4 2 1 2 4 2
+1 3 5 3 NULL NULL NULL NULL
+2 2 5 3 NULL NULL NULL NULL
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t1, (t3, t4)
+RIGHT JOIN
+t2
+ON t3.a=1 AND t2.b=t4.b
+WHERE t1.a <= 2;
+a b a b a b a b
+1 3 3 3 NULL NULL NULL NULL
+2 2 3 3 NULL NULL NULL NULL
+1 3 4 2 1 2 3 2
+1 3 4 2 1 2 4 2
+2 2 4 2 1 2 3 2
+2 2 4 2 1 2 4 2
+1 3 5 3 NULL NULL NULL NULL
+2 2 5 3 NULL NULL NULL NULL
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t1, (t3, t4)
+RIGHT JOIN
+t2
+ON t3.a=1 AND t2.b=t4.b
+WHERE t1.a <= 2;
+a b a b a b a b
+1 3 3 3 NULL NULL NULL NULL
+2 2 3 3 NULL NULL NULL NULL
+1 3 4 2 1 2 3 2
+1 3 4 2 1 2 4 2
+2 2 4 2 1 2 3 2
+2 2 4 2 1 2 4 2
+1 3 5 3 NULL NULL NULL NULL
+2 2 5 3 NULL NULL NULL NULL
+EXPLAIN EXTENDED
+SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t1, (t3, t4)
+RIGHT JOIN
+t2
+ON t3.a=1 AND t2.b=t4.b
+WHERE t1.a <= 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2)
+CREATE INDEX idx_b ON t2(b);
+EXPLAIN EXTENDED
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM (t3,t4)
+LEFT JOIN
+(t1,t2)
+ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+Warnings:
+Note 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`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM (t3,t4)
+LEFT JOIN
+(t1,t2)
+ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
+a b a b a b
+4 2 1 2 3 2
+4 2 1 2 3 2
+4 2 1 2 3 2
+NULL NULL 2 2 3 2
+4 2 1 2 4 2
+4 2 1 2 4 2
+4 2 1 2 4 2
+NULL NULL 2 2 4 2
+EXPLAIN EXTENDED
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t8 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+CREATE INDEX idx_b ON t4(b);
+CREATE INDEX idx_b ON t5(b);
+EXPLAIN EXTENDED
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 Using where
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t8 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+CREATE INDEX idx_b ON t8(b);
+EXPLAIN EXTENDED
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 Using where
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 Using where
+1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+CREATE INDEX idx_b ON t1(b);
+CREATE INDEX idx_a ON t0(a);
+EXPLAIN EXTENDED
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ref idx_a idx_a 5 const 1 Using where
+1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 Using where
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t6 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 Using where
+1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) 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 isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
+t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
+FROM t0,t1
+LEFT JOIN
+(
+t2
+LEFT JOIN
+(t3, t4)
+ON t3.a=1 AND t2.b=t4.b,
+t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b
+)
+ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
+(t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
+(t1.a != 2),
+t9
+WHERE t0.a=1 AND
+t0.b=t1.b AND
+(t2.a >= 4 OR t2.c IS NULL) AND
+(t3.a < 5 OR t3.c IS NULL) AND
+(t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
+(t5.a >=2 OR t5.c IS NULL) AND
+(t6.a >=4 OR t6.c IS NULL) AND
+(t7.a <= 2 OR t7.c IS NULL) AND
+(t8.a < 1 OR t8.c IS NULL) AND
+(t8.b=t9.b OR t8.c IS NULL) AND
+(t9.a=1);
+a b a b a b a b a b a b a b a b a b a b
+1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1
+1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
+1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
+1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1
+1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2
+1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2
+1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
+1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2
+1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2
+SELECT t2.a,t2.b
+FROM t2;
+a b
+3 3
+4 2
+5 3
+SELECT t3.a,t3.b
+FROM t3;
+a b
+1 2
+2 2
+SELECT t2.a,t2.b,t3.a,t3.b
+FROM t2 LEFT JOIN t3 ON t2.b=t3.b
+WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);
+a b a b
+4 2 1 2
+4 2 2 2
+5 3 NULL NULL
+SELECT t2.a,t2.b,t3.a,t3.b
+FROM t2 LEFT JOIN (t3) ON t2.b=t3.b
+WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);
+a b a b
+4 2 1 2
+4 2 2 2
+5 3 NULL NULL
+ALTER TABLE t3
+CHANGE COLUMN a a1 int,
+CHANGE COLUMN c c1 int;
+SELECT t2.a,t2.b,t3.a1,t3.b
+FROM t2 LEFT JOIN t3 ON t2.b=t3.b
+WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
+a b a1 b
+4 2 1 2
+4 2 2 2
+5 3 NULL NULL
+SELECT t2.a,t2.b,t3.a1,t3.b
+FROM t2 NATURAL LEFT JOIN t3
+WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
+a b a1 b
+4 2 1 2
+4 2 2 2
+5 3 NULL NULL
+DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE TABLE t3 (a int);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (2);
+INSERT INTO t1 VALUES (2);
+SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;
+a a a
+1 NULL NULL
+2 2 2
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
+a a a
+1 NULL NULL
+2 2 2
+DELETE FROM t1 WHERE a=2;
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
+a a a
+1 NULL NULL
+DELETE FROM t2;
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
+a a a
+1 NULL NULL
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1(a int, key (a));
+CREATE TABLE t2(b int, key (b));
+CREATE TABLE t3(c int, key (c));
+INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
+(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
+INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
+(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
+INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
+EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 5 NULL 21 Using index
+1 SIMPLE t3 index c c 5 NULL 6 Using index
+1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index
+EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 5 NULL 21 Using index
+1 SIMPLE t3 index c c 5 NULL 6 Using index
+1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+a b c
+NULL 0 0
+NULL 1 1
+NULL 2 2
+0 0 0
+0 1 1
+0 2 2
+1 0 0
+1 1 1
+1 2 2
+2 0 0
+2 1 1
+2 2 2
+3 0 0
+3 1 1
+3 2 2
+4 0 0
+4 1 1
+4 2 2
+5 0 0
+5 1 1
+5 2 2
+6 0 0
+6 1 1
+6 2 2
+7 0 0
+7 1 1
+7 2 2
+8 0 0
+8 1 1
+8 2 2
+9 0 0
+9 1 1
+9 2 2
+10 0 0
+10 1 1
+10 2 2
+11 0 0
+11 1 1
+11 2 2
+12 0 0
+12 1 1
+12 2 2
+13 0 0
+13 1 1
+13 2 2
+14 0 0
+14 1 1
+14 2 2
+15 0 0
+15 1 1
+15 2 2
+16 0 0
+16 1 1
+16 2 2
+17 0 0
+17 1 1
+17 2 2
+18 0 0
+18 1 1
+18 2 2
+19 0 0
+19 1 1
+19 2 2
+DELETE FROM t3;
+EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 5 NULL 21 Using index
+1 SIMPLE t3 index c c 5 NULL 0 Using index
+1 SIMPLE t2 ref b b 5 test.t3.c 2 Using index
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+a b c
+NULL NULL NULL
+0 NULL NULL
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 NULL NULL
+7 NULL NULL
+8 NULL NULL
+9 NULL NULL
+10 NULL NULL
+11 NULL NULL
+12 NULL NULL
+13 NULL NULL
+14 NULL NULL
+15 NULL NULL
+16 NULL NULL
+17 NULL NULL
+18 NULL NULL
+19 NULL NULL
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (c11 int);
+CREATE TABLE t2 (c21 int);
+CREATE TABLE t3 (c31 int);
+INSERT INTO t1 VALUES (4), (5);
+SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
+c11 c21
+4 NULL
+5 NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
+c11 c21 c31
+4 NULL NULL
+5 NULL NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 0
+1 SIMPLE t3 ALL NULL NULL NULL NULL 0
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
+INSERT INTO t1 VALUES (23, 2340), (26, 9900);
+CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
+INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
+create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
+INSERT INTO t3 VALUES (3,23), (6,26);
+CREATE TABLE t4 (groupid int(12));
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
+SELECT * FROM
+(SELECT DISTINCT gl.groupid, gp.price
+FROM t4 gl
+LEFT JOIN
+(t3 g INNER JOIN t2 p ON g.goodsid = p.goods
+INNER JOIN t1 gp ON p.goods = gp.goods)
+ON gl.groupid = g.groupid and p.shop = 'fr') t;
+groupid price
+1 NULL
+2 NULL
+3 2340
+4 NULL
+5 NULL
+6 9900
+CREATE VIEW v1 AS
+SELECT g.groupid groupid, p.goods goods,
+p.name name, p.shop shop,
+gp.price price
+FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
+INNER JOIN t1 gp on p.goods = gp.goods;
+CREATE VIEW v2 AS
+SELECT DISTINCT g.groupid, fr.price
+FROM t4 g
+LEFT JOIN
+v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
+SELECT * FROM v2;
+groupid price
+1 NULL
+2 NULL
+3 2340
+4 NULL
+5 NULL
+6 9900
+SELECT * FROM
+(SELECT DISTINCT g.groupid, fr.price
+FROM t4 g
+LEFT JOIN
+v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
+groupid price
+1 NULL
+2 NULL
+3 2340
+4 NULL
+5 NULL
+6 9900
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1(a int);
+CREATE TABLE t2(b int);
+CREATE TABLE t3(c int, d int);
+CREATE TABLE t4(d int);
+CREATE TABLE t5(e int, f int);
+CREATE TABLE t6(f int);
+CREATE VIEW v1 AS
+SELECT e FROM t5 JOIN t6 ON t5.e=t6.f;
+CREATE VIEW v2 AS
+SELECT e FROM t5 NATURAL JOIN t6;
+SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);
+a
+SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);
+ERROR 42S22: Unknown column 't1.x' in 'field list'
+SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;
+a
+SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;
+ERROR 42S22: Unknown column 't1.x' in 'field list'
+SELECT v1.e FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
+e
+SELECT v1.x FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
+ERROR 42S22: Unknown column 'v1.x' in 'field list'
+SELECT v2.e FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
+e
+SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
+ERROR 42S22: Unknown column 'v2.x' in 'field list'
+DROP VIEW v1, v2;
+DROP TABLE t1, t2, t3, t4, t5, t6;
View Lorry Controller Status