diff options
| -rw-r--r-- | mysql-test/r/func_test.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/index_merge.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/join_nested.result | 28 | ||||
| -rw-r--r-- | mysql-test/r/range.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 14 | ||||
| -rw-r--r-- | sql/item.cc | 101 | ||||
| -rw-r--r-- | sql/item.h | 4 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 11 | ||||
| -rw-r--r-- | sql/opt_range.cc | 57 | ||||
| -rw-r--r-- | sql/sql_select.cc | 344 | ||||
| -rw-r--r-- | sql/sql_select.h | 3 | ||||
| -rw-r--r-- | sql/table.h | 2 |
12 files changed, 416 insertions, 160 deletions
diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index a3a4f892975..4d3a570d1c4 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -79,7 +79,7 @@ explain extended select * from t1 where 1 xor 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (1 xor 1) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` select - a from t1; - a -1 diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index 4055256f5ea..6b41992a733 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -82,7 +82,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where explain select * from t0 where key2=10 or key3=3 or key4 is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using where +1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or (key3=10) or (key4 <=> null); id select_type table type possible_keys key key_len ref rows Extra @@ -257,8 +257,8 @@ explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2=4) and t1.key1<200; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where -1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 Using where +1 SIMPLE t0 range i1,i2 i1 4 NULL 179 Using where +1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 09c3e67ae70..6eff8676d9d 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -76,7 +76,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t2`.`b` = `test`.`t4`.`b`)) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`)) +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 @@ -153,7 +153,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t2`.`b` = `test`.`t4`.`b`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) +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 @@ -183,7 +183,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t2`.`b` = `test`.`t4`.`b`)) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`))) +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 @@ -233,7 +233,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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 +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`.`t8`.`b` = `test`.`t7`.`b`) and (`test`.`t6`.`b` < 10))) where 1 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t6, t7 @@ -562,7 +562,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` = `test`.`t7`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t1`.`b` = `test`.`t5`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t0`.`a` = 1) and (`test`.`t0`.`b` = `test`.`t1`.`b`) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`))) +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 @@ -660,7 +660,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` = `test`.`t7`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t1`.`b` = `test`.`t5`.`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`.`t0`.`a` = 1) and (`test`.`t0`.`b` = `test`.`t1`.`b`) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`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`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)) and (`test`.`t9`.`a` = 1)) +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 @@ -858,7 +858,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 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`.`t3` join `test`.`t2` left join `test`.`t4` on(((`test`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where (`test`.`t1`.`a` <= 2) +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`.`t3` join `test`.`t2` left 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 @@ -872,7 +872,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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 +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`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = 1))) where 1 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM t3,t4 LEFT JOIN @@ -935,7 +935,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` = `test`.`t7`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t1`.`b` = `test`.`t5`.`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`.`t0`.`a` = 1) and (`test`.`t0`.`b` = `test`.`t1`.`b`) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`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`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)) and (`test`.`t9`.`a` = 1)) +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 @@ -986,7 +986,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` = `test`.`t7`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t1`.`b` = `test`.`t5`.`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`.`t0`.`a` = 1) and (`test`.`t0`.`b` = `test`.`t1`.`b`) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`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`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)) and (`test`.`t9`.`a` = 1)) +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, @@ -1033,10 +1033,10 @@ id select_type table type possible_keys key key_len ref rows Extra 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.t7.b 2 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`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` = `test`.`t7`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t1`.`b` = `test`.`t5`.`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`.`t0`.`a` = 1) and (`test`.`t0`.`b` = `test`.`t1`.`b`) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`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`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)) and (`test`.`t9`.`a` = 1)) +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 @@ -1084,10 +1084,10 @@ id select_type table type possible_keys key key_len ref rows Extra 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.t7.b 2 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`.`t3`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`b` = `test`.`t7`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t1`.`b` = `test`.`t5`.`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`.`t0`.`a` = 1) and (`test`.`t0`.`b` = `test`.`t1`.`b`) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`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`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)) and (`test`.`t9`.`a` = 1)) +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 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index add6d8f320d..7dd62296f4a 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -254,12 +254,12 @@ INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2); explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using index -1 SIMPLE t1 ALL i1 NULL NULL NULL 4 Range checked for each record (index map: 0x1) +1 SIMPLE t1 index i1 i1 4 NULL 4 Using where; Using index explain select * from t1 force index(i1), t2 force index(j1) where (t1.key1 <t2.keya + 1) and t2.keya=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref j1 j1 4 const 1 Using index -1 SIMPLE t1 ALL i1 NULL NULL NULL 4 Range checked for each record (index map: 0x1) +1 SIMPLE t1 index i1 i1 4 NULL 4 Using where; Using index DROP TABLE t1,t2; CREATE TABLE t1 ( a int(11) default NULL, diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4dab9082d2e..56df7576fb5 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -331,7 +331,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index Warnings: Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) +Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t6`.`clinic_uq` = `test`.`t7`.`uq`)) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous drop table t1,t2,t3; @@ -545,7 +545,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = _latin1'1') and (`test`.`t1`.`numreponse` = 3)) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = _latin1'1'))) and (`test`.`t1`.`numeropost` = _latin1'1')) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1313,7 +1313,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1675,10 +1675,10 @@ Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `tes explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 7 Using where; Using index +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index Warnings: Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and ((`test`.`t1`.`id` = `test`.`tt`.`id`) or isnull(`test`.`t1`.`id`))) having (`test`.`t1`.`id` is not null))) +Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`tt`.`id` = `test`.`t1`.`id`)) having (`test`.`t1`.`id` is not null))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); @@ -1704,7 +1704,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 Using where Warnings: -Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`a`.`id` = `test`.`b`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`) +Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS `id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from `test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where (if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`) drop table t1,t2; create table t1 (a int); insert into t1 values (1); @@ -1832,7 +1832,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where Warnings: Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) +Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`up`.`a` = `test`.`t1`.`a`)) drop table t1; CREATE TABLE t1 (t1_a int); INSERT INTO t1 VALUES (1); diff --git a/sql/item.cc b/sql/item.cc index a17336ea634..c99c0a9ba6d 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -408,7 +408,7 @@ bool DTCollation::aggregate(DTCollation &dt, bool superset_conversion) Item_field::Item_field(Field *f) :Item_ident(NullS, f->table_name, f->field_name), - item_equal(0), + item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0) { set_field(f); @@ -419,7 +419,7 @@ Item_field::Item_field(Field *f) Item_field::Item_field(THD *thd, Field *f) :Item_ident(NullS, thd->strdup(f->table_name), thd->strdup(f->field_name)), - item_equal(0), + item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0) { set_field(f); @@ -432,11 +432,12 @@ Item_field::Item_field(THD *thd, Item_field *item) :Item_ident(thd, item), field(item->field), result_field(item->result_field), + item_equal(item->item_equal), + no_const_subst(item->no_const_subst), have_privileges(item->have_privileges), any_privileges(item->any_privileges) { collation.set(DERIVATION_IMPLICIT); - item_equal= item->item_equal; } void Item_field::set_field(Field *field_par) @@ -1607,7 +1608,101 @@ void Item_field::cleanup() First Item_equal containing the field, if success 0, otherwise */ +Item_equal *Item_field::find_item_equal(COND_EQUAL *cond_equal) +{ + Item_equal *item= 0; + while (cond_equal) + { + List_iterator_fast<Item_equal> li(cond_equal->current_level); + while ((item= li++)) + { + if (item->contains(field)) + return item; + } + /* + The field is not found in any of the multiple equalities + of the current level. Look for it in upper levels + */ + cond_equal= cond_equal->upper_levels; + } + return 0; +} + +/* + Set a pointer to the multiple equality the field reference belongs to (if any) + + SYNOPSIS + equal_fields_propagator() + arg - reference to list of multiple equalities where + the field (this object) is to be looked for + + DESCRIPTION + The function looks for a multiple equality containing the field item + among those referenced by arg. + In the case such equality exists the function does the following. + If the found multiple equality contains a constant, then the field + reference is substituted for this constant, otherwise it sets a pointer + to the multiple equality in the field item. + + NOTES + This function is supposed to be called as a callback parameter in calls + of the transform method. + + RETURN VALUES + pointer to the replacing constant item, if the field item was substituted + pointer to the field item, otherwise. +*/ + +Item *Item_field::equal_fields_propagator(byte *arg) +{ + if (no_const_subst) + return this; + item_equal= find_item_equal((COND_EQUAL *) arg); + Item *item= 0; + if (item_equal) + item= item_equal->get_const(); + if (!item) + item= this; + return item; +} + + +/* + Set a pointer to the multiple equality the field reference belongs to (if any) + + SYNOPSIS + replace_equal_field_processor() + arg - a dummy parameter, is not used here + + DESCRIPTION + The function replaces a pointer to a field in the Item_field object + by a pointer to another field. + The replacement field is taken from the very beginning of + the item_equal list which the Item_field object refers to (belongs to) + If the Item_field object does not refer any Item_equal object, + nothing is done. + + NOTES + This function is supposed to be called as a callback parameter in calls + of the walk method. + + RETURN VALUES + 0 +*/ + +bool Item_field::replace_equal_field_processor(byte *arg) +{ + if (item_equal) + { + Item_field *subst= item_equal->get_first(); + if (!field->eq(subst->field)) + { + field= subst->field; + return 0; + } + } + return 0; } void Item::init_make_field(Send_field *tmp_field, diff --git a/sql/item.h b/sql/item.h index 9826bcb4a5a..c8e274b7205 100644 --- a/sql/item.h +++ b/sql/item.h @@ -457,6 +457,8 @@ protected: void set_field(Field *field); public: Field *field,*result_field; + Item_equal *item_equal; + bool no_const_subst; /* if any_privileges set to TRUE then here real effective privileges will be stored @@ -468,7 +470,7 @@ public: Item_field(const char *db_par,const char *table_name_par, const char *field_name_par) :Item_ident(db_par,table_name_par,field_name_par), - field(0), result_field(0), item_equal(0),} + field(0), result_field(0), item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0) { collation.set(DERIVATION_IMPLICIT); } // Constructor need to process subselect with temporary tables (see Item) diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 1f2e744f714..66af96f671f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -231,6 +231,8 @@ void Item_bool_func2::fix_length_and_dec() conv->collation.set(args[weak]->collation.derivation); conv->fix_fields(thd, 0, &conv); } + if (args[weak]->type() == FIELD_ITEM) + ((Item_field *)args[weak])->no_const_subst= 1; args[weak]= conv ? conv : args[weak]; } } @@ -1956,7 +1958,7 @@ Item_cond::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) char buff[sizeof(char*)]; // Max local vars in function #endif not_null_tables_cache= used_tables_cache= 0; - const_item_cache= 0; + const_item_cache= 1; /* and_table_cache is the value that Item_cond_or() returns for not_null_tables() @@ -1987,7 +1989,7 @@ Item_cond::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref) tmp_table_map= item->not_null_tables(); not_null_tables_cache|= tmp_table_map; and_tables_cache&= tmp_table_map; - const_item_cache&= item->const_item(); + const_item_cache&= item->const_item(); with_sum_func= with_sum_func || item->with_sum_func; if (item->maybe_null) maybe_null=1; @@ -2051,7 +2053,7 @@ void Item_cond::split_sum_func(Item **ref_pointer_array, List<Item> &fields) List_iterator<Item> li(list); Item *item; used_tables_cache=0; - const_item_cache=0; + const_item_cache=1; while ((item=li++)) { if (item->with_sum_func && item->type() != SUM_FUNC_ITEM) @@ -2088,7 +2090,7 @@ void Item_cond::update_used_tables() { item->update_used_tables(); used_tables_cache|= item->used_tables(); - const_item_cache&= item->const_item(); + const_item_cache&= item->const_item(); } } @@ -2934,6 +2936,7 @@ void Item_equal::add(Item *c) } Item_func_eq *func= new Item_func_eq(c, const_item); func->set_cmp_func(); + func->quick_fix_field(); cond_false = !(func->val_int()); } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 502a8d51b07..fe34ac7235e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3339,6 +3339,63 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond) } DBUG_RETURN(ftree); + } + default: + if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM) + { + field_item= (Item_field*) (cond_func->arguments()[0]); + value= cond_func->arg_count > 1 ? cond_func->arguments()[1] : 0; + } + else if (cond_func->have_rev_func() && + cond_func->arguments()[1]->type() == Item::FIELD_ITEM) + { + field_item= (Item_field*) (cond_func->arguments()[1]); + value= cond_func->arguments()[0]; + } + else + DBUG_RETURN(0); + } + + /* + If the where condition contains a predicate (ti.field op const), + then not only SELL_TREE for this predicate is built, but + the trees for the results of substitution of ti.field for + each tj.field belonging to the same multiple equality as ti.field + are built as well. + E.g. for WHERE t1.a=t2.a AND t2.a > 10 + a SEL_TREE for t2.a > 10 will be built for quick select from t2 + and + a SEL_TREE for t1.a > 10 will be built for quick select from t1. + */ + + for (uint i= 0; i < cond_func->arg_count; i++) + { + Item *arg= cond_func->arguments()[i]; + if (arg != field_item) + ref_tables|= arg->used_tables(); + } + Field *field= field_item->field; + Item_result cmp_type= field->cmp_type(); + if (!((ref_tables | field->table->map) & param_comp)) + ftree= get_func_mm_tree(param, cond_func, field, value, cmp_type); + Item_equal *item_equal= field_item->item_equal; + if (item_equal) + { + Item_equal_iterator it(*item_equal); + Item_field *item; + while ((item= it++)) + { + Field *f= item->field; + if (field->eq(f)) + continue; + if (!((ref_tables | f->table->map) & param_comp)) + { + tree= get_func_mm_tree(param, cond_func, f, value, cmp_type); + ftree= !ftree ? tree : tree_and(param, ftree, tree); + } + } + } + DBUG_RETURN(ftree); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b3355f6c57c..05df457da1b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -91,8 +91,10 @@ static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables, uint select_options, const char *info, Item *having, Procedure *proc, SELECT_LEX_UNIT *unit); -static COND *build_all_equal_items(COND *cond, - COND_EQUAL *inherited); +static COND *build_equal_items(COND *cond, + COND_EQUAL *inherited, + List<TABLE_LIST> *join_list, + COND_EQUAL **cond_equal_ref); static COND* substitute_for_best_equal_field(COND *cond, COND_EQUAL *cond_equal, void *table_join_idx); @@ -530,17 +532,29 @@ JOIN::optimize() } } #endif - - /* Eliminate NOT operators */ - conds= eliminate_not_funcs(conds); - DBUG_EXECUTE("where", print_where(conds, "after negation elimination");); + SELECT_LEX *sel= thd->lex->current_select; + if (sel->first_cond_optimization) { - TABLE_LIST *tables; - for (tables= tables_list; tables; tables= tables->next) - { - if (tables->on_expr) - tables->on_expr= eliminate_not_funcs(tables->on_expr); - } + /* + The following code will allocate the new items in a permanent + MEMROOT for prepared statements and stored procedures. + */ + + Item_arena *arena= thd->current_arena, backup; + if (arena->is_conventional()) + arena= 0; // For easier test + else + thd->set_n_backup_item_arena(arena, &backup); + + sel->first_cond_optimization= 0; + + /* Convert all outer joins to inner joins if possible */ + conds= simplify_joins(this, join_list, conds, TRUE); + + sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; + + if (arena) + thd->restore_backup_item_arena(arena, &backup); } /* @@ -550,32 +564,8 @@ JOIN::optimize() that occurs in a function set a pointer to the multiple equality predicate. Substitute a constant instead of this field if the multiple equality contains a constant. - */ - if (conds) - { - conds= build_all_equal_items(conds, NULL); - conds->update_used_tables(); - if (conds->type() == Item::COND_ITEM && - ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC) - cond_equal= &((Item_cond_and*) conds)->cond_equal; - else if (conds->type() == Item::FUNC_ITEM && - ((Item_cond*) conds)->functype() == Item_func::MULT_EQUAL_FUNC) - { - cond_equal= new COND_EQUAL; - cond_equal->current_level.push_back((Item_equal *) conds); - } - } - { - TABLE_LIST *tables; - for (tables= tables_list; tables; tables= tables->next) - { - if (tables->on_expr) - { - tables->on_expr= build_all_equal_items(tables->on_expr, cond_equal); - tables->on_expr->update_used_tables(); - } - } - } + */ + conds= build_equal_items(conds, NULL, join_list, &cond_equal); conds= optimize_cond(this, conds,&cond_value); if (thd->net.report_error) @@ -669,31 +659,6 @@ JOIN::optimize() if (const_tables && !thd->locked_tables && !(select_options & SELECT_NO_UNLOCK)) mysql_unlock_some_tables(thd, table, const_tables); - /* - Among the equal fields belonging to the same multiple equality - choose the one that is to be retrieved first and substitute - all references to these in where condition for a reference for - the selected field. - */ - if (conds) - { - conds= substitute_for_best_equal_field(conds, cond_equal, map2table); - conds->update_used_tables(); - } - { - TABLE_LIST *tables; - for (tables= tables_list; tables; tables= tables->next) - { - if (tables->on_expr) - { - tables->on_expr= substitute_for_best_equal_field(tables->on_expr, - cond_equal, - map2table); - tables->on_expr->update_used_tables(); - map2table[tables->table->tablenr]->on_expr= tables->on_expr; - } - } - } if (!conds && outer_join) { /* Handle the case where we have an OUTER JOIN without a WHERE */ @@ -710,6 +675,32 @@ JOIN::optimize() make_outerjoin_info(this); + /* + Among the equal fields belonging to the same multiple equality + choose the one that is to be retrieved first and substitute + all references to these in where condition for a reference for + the selected field. + */ + if (conds) + { + conds= substitute_for_best_equal_field(conds, cond_equal, map2table); + conds->update_used_tables(); + } + /* + Permorm the the optimization on fields evaluation mentioned above + for all on expressions. + */ + for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++) + { + if (*tab->on_expr_ref) + { + *tab->on_expr_ref= substitute_for_best_equal_field(*tab->on_expr_ref, + tab->cond_equal, + map2table); + (*tab->on_expr_ref)->update_used_tables(); + } + } + if (make_join_select(this, select, conds)) { zero_result_cause= @@ -1545,8 +1536,6 @@ JOIN::exec() /* table->keyuse is set in the case there was an original WHERE clause on the table that was optimized away. - table->on_expr tells us that it was a LEFT JOIN and there will be - at least one row generated from the table. */ if (curr_table->select_cond || (curr_table->keyuse && !curr_table->first_inner)) @@ -1646,6 +1635,7 @@ JOIN::cleanup() tmp_table_param.copy_field=0; DBUG_RETURN(tmp_join->cleanup()); } + cond_equal= 0; lock=0; // It's faster to unlock later join_free(1); @@ -1794,7 +1784,7 @@ Cursor::fetch(ulong num_rows) { THD *thd= join->thd; JOIN_TAB *join_tab= join->join_tab + join->const_tables;; - COND *on_expr= join_tab->on_expr; + COND *on_expr= *join_tab->on_expr_ref; COND *select_cond= join_tab->select_cond; READ_RECORD *info= &join_tab->read_record; @@ -2156,7 +2146,8 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, s->dependent= tables->dep_tables; s->key_dependent= 0; - if ((s->on_expr=tables->on_expr)) + s->on_expr_ref= &tables->on_expr; + if (*s->on_expr_ref) { /* s is the only inner table of an outer join */ if (!table->file->records) @@ -2378,7 +2369,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, SQL_SELECT *select; select= make_select(s->table, found_const_table_map, found_const_table_map, - s->on_expr ? s->on_expr : conds, + *s->on_expr_ref ? *s->on_expr_ref : conds, &error); records= get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit); @@ -2396,7 +2387,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, join->const_table_map|= s->table->map; set_position(join,const_count++,s,(KEYUSE*) 0); s->type= JT_CONST; - if (s->on_expr) + if (*s->on_expr_ref) { /* Generate empty row */ s->info= "Impossible ON condition"; @@ -2774,16 +2765,26 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level, case Item_func::OPTIMIZE_NONE: break; case Item_func::OPTIMIZE_KEY: - // BETWEEN, IN, NOT + { + // BETWEEN, IN, NE if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM && !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) - add_key_field(key_fields,*and_level,cond_func, - ((Item_field*)(cond_func->key_item()->real_item()))->field, - cond_func->argument_count() == 2 && - cond_func->functype() == Item_func::IN_FUNC, - cond_func->arguments()+1, cond_func->argument_count()-1, - usable_tables); + { + Item **values= cond_func->arguments()+1; + if (cond_func->functype() == Item_func::NE_FUNC && + cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM && + !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT)) + values--; + add_key_equal_fields(key_fields, *and_level, cond_func, + (Item_field*) (cond_func->key_item()->real_item()), + cond_func->argument_count() == 2 && + cond_func->functype() == Item_func::IN_FUNC, + values, + cond_func->argument_count()-1, + usable_tables); + } break; + } case Item_func::OPTIMIZE_OP: { bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC || @@ -3048,9 +3049,9 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, for inner tables in outer joins these keys will be taken into account as well. */ - if (join_tab[i].on_expr) + if (*join_tab[i].on_expr_ref) { - add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr, + add_key_fields(join_tab,&end,&and_level,*join_tab[i].on_expr_ref, join_tab[i].table->map); } else @@ -4654,7 +4655,7 @@ get_best_combination(JOIN *join) form=join->table[tablenr]=j->table; used_tables|= form->map; form->reginfo.join_tab=j; - if (!j->on_expr) + if (!*j->on_expr_ref) form->reginfo.not_exists_optimize=0; // Only with LEFT JOIN if (j->type == JT_CONST) continue; // Handled in make_join_stat.. @@ -4909,7 +4910,7 @@ make_simple_join(JOIN *join,TABLE *tmp_table) join_tab->type= JT_ALL; /* Map through all records */ join_tab->keys.init(~0); /* test everything in quick */ join_tab->info=0; - join_tab->on_expr=0; + join_tab->on_expr_ref=0; join_tab->last_inner= 0; join_tab->first_unmatched= 0; join_tab->ref.key = -1; @@ -4978,7 +4979,7 @@ add_found_match_trig_cond(JOIN_TAB *tab, COND *cond, JOIN_TAB *root_tab) first inner table of the embedding outer join operation, if there is any, through the field t0->first_upper. The on expression for the outer join operation is attached to the - corresponding first inner table through the field t0->on_expr. + corresponding first inner table through the field t0->on_expr_ref. Here ti are structures of the JOIN_TAB type. EXAMPLE @@ -4992,8 +4993,8 @@ add_found_match_trig_cond(JOIN_TAB *tab, COND *cond, JOIN_TAB *root_tab) is selected, the following references will be set; t4->last_inner=[t4], t4->first_inner=[t4], t4->first_upper=[t2] t2->last_inner=[t4], t2->first_inner=t3->first_inner=[t2], - on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to t2->on_expr, - while t3.a=t4.a will be attached to t4->on_expr. + on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to + *t2->on_expr_ref, while t3.a=t4.a will be attached to *t4->on_expr_ref. NOTES The function assumes that the simplification procedure has been @@ -5020,7 +5021,8 @@ make_outerjoin_info(JOIN *join) is in the query above.) */ tab->last_inner= tab->first_inner= tab; - tab->on_expr= tbl->on_expr; + tab->on_expr_ref= &tbl->on_expr; + tab->cond_equal= tbl->cond_equal; if (embedding) tab->first_upper= embedding->nested_join->first_nested; } @@ -5034,7 +5036,8 @@ make_outerjoin_info(JOIN *join) Save reference to it in the nested join structure. */ nested_join->first_nested= tab; - tab->on_expr= embedding->on_expr; + tab->on_expr_ref= &embedding->on_expr; + tab->cond_equal= tbl->cond_equal; if (embedding->embedding) tab->first_upper= embedding->embedding->nested_join->first_nested; } @@ -5069,10 +5072,10 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) for (JOIN_TAB *tab= join->join_tab+join->const_tables; tab < join->join_tab+join->tables ; tab++) { - if (tab->on_expr) + if (*tab->on_expr_ref) { JOIN_TAB *cond_tab= tab->first_inner; - COND *tmp= make_cond_for_table(tab->on_expr, + COND *tmp= make_cond_for_table(*tab->on_expr_ref, join->const_table_map, (table_map) 0); if (!tmp) @@ -5210,7 +5213,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { /* Join with outer join condition */ COND *orig_cond=sel->cond; - sel->cond= and_conds(sel->cond, tab->on_expr); + sel->cond= and_conds(sel->cond, *tab->on_expr_ref); if (sel->cond && !sel->cond->fixed) sel->cond->fix_fields(join->thd, 0, &sel->cond); if (sel->test_quick_select(join->thd, tab->keys, @@ -5225,7 +5228,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) we have to check isn't it only "impossible ON" instead */ sel->cond=orig_cond; - if (!tab->on_expr || + if (!*tab->on_expr_ref || sel->test_quick_select(join->thd, tab->keys, used_tables & ~ current_map, (join->select_options & @@ -5290,7 +5293,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) Table tab is the last inner table of an outer join. An on expression is always attached to it. */ - COND *on_expr= first_inner_tab->on_expr; + COND *on_expr= *first_inner_tab->on_expr_ref; table_map used_tables= join->const_table_map | OUTER_REF_TABLE_BIT | RAND_TABLE_BIT; @@ -5804,7 +5807,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, bool *simple_order) table_map not_const_tables= ~join->const_table_map; table_map ref; prev_ptr= &first_order; - *simple_order= join->join_tab[join->const_tables].on_expr ? 0 : 1; + *simple_order= *join->join_tab[join->const_tables].on_expr_ref ? 0 : 1; /* NOTE: A variable of not_const_tables ^ first_table; breaks gcc 2.7 */ @@ -6024,7 +6027,7 @@ finish: general case, its own constant for each fields from the multiple equality. But at the same time it would allow us to get rid of constant propagation completely: it would be done by the call - to build_all_equal_items. + to build_equal_items_for_cond. IMPLEMENTATION The implementation does not follow exactly the above rules to @@ -6156,7 +6159,7 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) bool copyfl; if (field_item->result_type() == STRING_RESULT && - ((Field_str *) field_item)->charset() != + ((Field_str *) field_item->field)->charset() != ((Item_cond *) item)->compare_collation()) return FALSE; @@ -6192,7 +6195,7 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) Replace all equality predicates in a condition by multiple equality items SYNOPSIS - build_all_equal_items() + build_equal_items_for_cond() cond condition(expression) where to make replacement inherited path to all inherited multiple equality items @@ -6253,7 +6256,7 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) pointer to the transformed condition */ -static COND *build_all_equal_items(COND *cond, +static COND *build_equal_items_for_cond(COND *cond, COND_EQUAL *inherited) { Item_equal *item_equal; @@ -6315,7 +6318,7 @@ static COND *build_all_equal_items(COND *cond, while((item= li++)) { Item *new_item; - if ((new_item = build_all_equal_items(item, inherited))!= item) + if ((new_item = build_equal_items_for_cond(item, inherited))!= item) { /* This replacement happens only for standalone equalities */ li.replace(new_item); @@ -6352,6 +6355,113 @@ static COND *build_all_equal_items(COND *cond, cond->update_used_tables(); } return cond; +} + +/* + Build multiple equalities for a condition and all on expressions that + inherit these multiple equalities + + SYNOPSIS + build_equal_items() + cond condition to build the multiple equalities for + inherited path to all inherited multiple equality items + join_list list of join tables to which the condition refers to + cond_equal_ref :out pointer to the structure to place built equalities in + + DESCRIPTION + The function first applies the build_equal_items_for_cond function + to build all multiple equalities for condition cond utilizing equalities + referred through the parameter inherited. The extended set of + equalities is returned in the structure referred by the cond_equal_ref + parameter. After this the function calls itself recursively for + all on expressions whose direct references can be found in join_list + and who inherit directly the multiple equalities just having built. + + NOTES + The on expression used in an outer join operation inherits all equalities + from the on expression of the embedding join, if there is any, or + otherwise - from the where condition. + This fact is not obvious, but presumably can be proved. + Consider the following query: + SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a=t3.a AND t2.a=t4.a + WHERE t1.a=t2.a; + If the on expression in the query inherits =(t1.a,t2.a), then we + can build the multiple equality =(t1.a,t2.a,t3.a,t4.a) that infers + the equality t3.a=t4.a. Although the on expression + t1.a=t3.a AND t2.a=t4.a AND t3.a=t4.a is not equivalent to the one + in the query the latter can be replaced by the former: the new query + will return the same result set as the original one. + + Interesting that multiple equality =(t1.a,t2.a,t3.a,t4.a) allows us + to use t1.a=t3.a AND t3.a=t4.a under the on condition: + SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a=t3.a AND t3.a=t4.a + WHERE t1.a=t2.a + This query equivalent to: + SELECT * FROM (t1 LEFT JOIN (t3,t4) ON t1.a=t3.a AND t3.a=t4.a),t2 + WHERE t1.a=t2.a + Similarly the original query can be rewritten to the query: + SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t2.a=t4.a AND t3.a=t4.a + WHERE t1.a=t2.a + that is equivalent to: + SELECT * FROM (t2 LEFT JOIN (t3,t4)ON t2.a=t4.a AND t3.a=t4.a), t1 + WHERE t1.a=t2.a + Thus, applying equalities from the where condition we basically + can get more freedom in performing join operations. + Althogh we don't use this property now, it probably makes sense to use + it in the future. + + RETURN + pointer to the transformed condition containing multiple equalities +*/ + +static COND *build_equal_items(COND *cond, + COND_EQUAL *inherited, + List<TABLE_LIST> *join_list, + COND_EQUAL **cond_equal_ref) +{ + COND_EQUAL *cond_equal= 0; + + if (cond) + { + cond= build_equal_items_for_cond(cond, inherited); + cond->update_used_tables(); + if (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + cond_equal= &((Item_cond_and*) cond)->cond_equal; + else if (cond->type() == Item::FUNC_ITEM && + ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + cond_equal= new COND_EQUAL; + cond_equal->current_level.push_back((Item_equal *) cond); + } + } + if (cond_equal) + { + cond_equal->upper_levels= inherited; + inherited= cond_equal; + } + *cond_equal_ref= cond_equal; + + if (join_list) + { + TABLE_LIST *table; + List_iterator<TABLE_LIST> li(*join_list); + + while ((table= li++)) + { + if (table->on_expr) + { + List<TABLE_LIST> *join_list= table->nested_join ? + &table->nested_join->join_list : NULL; + table->on_expr= build_equal_items(table->on_expr, + inherited, + join_list, + &table->cond_equal); + } + } + } + + return cond; } /* @@ -6447,6 +6557,11 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, { List<Item> eq_list; Item_func_eq *eq_item= 0; + if (((Item *) item_equal)->const_item() && !item_equal->val_int()) + { + cond= new Item_int((char*) "FALSE",0,1); + return cond; + } Item *item_const= item_equal->get_const(); Item_equal_iterator it(*item_equal); Item *head; @@ -6484,6 +6599,7 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, if (!eq_item) return 0; eq_item->set_cmp_func(); + eq_item->quick_fix_field(); } } @@ -6495,6 +6611,9 @@ static Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, cond= new Item_cond_and(eq_list); else ((Item_cond *) cond)->add_at_head(&eq_list); + + cond->quick_fix_field(); + cond->update_used_tables(); return cond; } @@ -6589,7 +6708,6 @@ static COND* substitute_for_best_equal_field(COND *cond, return cond; } - /* change field = field to field = const for each found field = const in the and_level @@ -6998,38 +7116,15 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top) } } DBUG_RETURN(conds); +} static COND * optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value) { THD *thd= join->thd; - SELECT_LEX *select= thd->lex->current_select;} + SELECT_LEX *select= thd->lex->current_select; DBUG_ENTER("optimize_cond"); - if (select->first_cond_optimization) - { - /* - The following code will allocate the new items in a permanent - MEMROOT for prepared statements and stored procedures. - */ - - Item_arena *arena= thd->current_arena, backup; - if (arena->is_conventional()) - arena= 0; // For easier test - else - thd->set_n_backup_item_arena(arena, &backup); - - select->first_cond_optimization= 0; - - /* Convert all outer joins to inner joins if possible */ - conds= simplify_joins(join, join->join_list, conds, TRUE); - - select->prep_where= conds ? conds->copy_andor_structure(thd) : 0; - - if (arena) - thd->restore_backup_item_arena(arena, &backup); - } - if (!conds) { *cond_value= Item::COND_TRUE; @@ -7047,6 +7142,7 @@ optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value) DBUG_EXECUTE("where",print_where(conds,"after const change");); conds= remove_eq_conds(thd, conds, cond_value) ; DBUG_EXECUTE("info",print_where(conds,"after remove");); + } DBUG_RETURN(conds); } @@ -8871,9 +8967,9 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos) table->file->extra(HA_EXTRA_NO_KEYREAD); } } - if (tab->on_expr && !table->null_row) + if (*tab->on_expr_ref && !table->null_row) { - if ((table->null_row= test(tab->on_expr->val_int() == 0))) + if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0))) mark_as_null_row(table); } if (!table->null_row) diff --git a/sql/sql_select.h b/sql/sql_select.h index c9cfeb70225..18664624a77 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -91,7 +91,8 @@ typedef struct st_join_table { SQL_SELECT *select; COND *select_cond; QUICK_SELECT_I *quick; - Item *on_expr; /* associated on expression */ + Item **on_expr_ref; /* pointer to the associated on expression */ + COND_EQUAL *cond_equal; /* multiple equalities for the on expression */ st_join_table *first_inner; /* first inner table for including outerjoin */ bool found; /* true after all matches or null complement */ bool not_null_compl;/* true before null complement is added */ diff --git a/sql/table.h b/sql/table.h index 35f6c6803fb..605cd516d9c 100644 --- a/sql/table.h +++ b/sql/table.h @@ -21,6 +21,7 @@ class Item; /* Needed by ORDER */ class GRANT_TABLE; class st_select_lex_unit; class st_select_lex; +class COND_EQUAL; /* Order clause list element */ @@ -209,6 +210,7 @@ typedef struct st_table_list char *db, *alias, *real_name; char *option; /* Used by cache index */ Item *on_expr; /* Used with outer join */ + COND_EQUAL *cond_equal; /* Used with outer join */ struct st_table_list *natural_join; /* natural join on this table*/ /* ... join ... USE INDEX ... IGNORE INDEX */ List<String> *use_index, *ignore_index; |