| List: | Commits | « Previous MessageNext Message » | |
| From: | igor | Date: | March 30 2008 7:59am |
| Subject: | bk commit into 6.0 tree (igor:1.2606) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 6.0 repository of igor. When igor does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2008-03-29 22:59:02-07:00, igor@stripped +10 -0 There could be observed the following problems: 1. EXPLAIN did not mention pushdown conditions from on expressions in the 'extra' column. As a result if a query had no where conditions pushed down to a table, but had on conditions pushed to this table the 'extra' column in the EXPLAIN for the table missed 'using where'. 2. Conditions for ref access were not eliminated from on expressions though such conditions were eliminated from the where condition. The patch resolves these two problems. The test results were adjusted accordingly. mysql-test/r/compress.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +6 -6 Adjusted test results. mysql-test/r/join_nested.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +17 -17 Adjusted test results. mysql-test/r/join_outer.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +3 -3 Adjusted test results. mysql-test/r/order_by.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +1 -1 Adjusted test results. mysql-test/r/pool_of_threads.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +6 -6 Adjusted test results. mysql-test/r/select.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +12 -12 Adjusted test results. mysql-test/r/ssl.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +6 -6 Adjusted test results. mysql-test/r/ssl_compress.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +6 -6 Adjusted test results. mysql-test/r/view.result@stripped, 2008-03-29 22:58:53-07:00, igor@stripped +4 -4 Adjusted test results. sql/sql_select.cc@stripped, 2008-03-29 22:58:54-07:00, igor@stripped +41 -15 There could be observed the following problems: 1. EXPLAIN did not mention pushdown conditions from on expressions in the 'extra' column. As a result if a query had no where conditions pushed down to a table, but had on conditions pushed to this table the 'extra' column in the EXPLAIN for the table missed 'using where'. 2. Conditions for ref access were not eliminated from on expressions though such conditions were eliminated from the where condition. The patch resolves these two problems. diff -Nrup a/mysql-test/r/compress.result b/mysql-test/r/compress.result --- a/mysql-test/r/compress.result 2008-02-12 11:12:14 -08:00 +++ b/mysql-test/r/compress.result 2008-03-29 22:58:53 -07:00 @@ -1395,15 +1395,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1419,15 +1419,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff -Nrup a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result --- a/mysql-test/r/join_nested.result 2007-07-20 21:21:20 -07:00 +++ b/mysql-test/r/join_nested.result 2008-03-29 22:58:53 -07:00 @@ -74,7 +74,7 @@ WHERE t3.a=1 OR t3.c IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 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` 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 @@ -150,7 +150,7 @@ WHERE t3.a>1 OR t3.c IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 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`)) @@ -180,7 +180,7 @@ WHERE (t3.a>1 OR t3.c IS NULL) AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 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`))) @@ -230,7 +230,7 @@ ON t7.b=t8.b AND t6.b < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 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 @@ -546,12 +546,12 @@ id select_type table type possible_keys 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 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` 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, @@ -837,8 +837,8 @@ WHERE t1.a <= 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 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); @@ -851,7 +851,7 @@ 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 filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer -1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 +1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 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 @@ -1196,13 +1196,13 @@ INSERT INTO t3 VALUES (0), (1), (2), (3) 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 t3 index c c 5 NULL 6 Using where; 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 +1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c NULL 0 0 @@ -1273,7 +1273,7 @@ EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN 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 +1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c NULL NULL NULL @@ -1317,8 +1317,8 @@ c11 c21 c31 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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where 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); diff -Nrup a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result --- a/mysql-test/r/join_outer.result 2007-05-27 12:19:31 -07:00 +++ b/mysql-test/r/join_outer.result 2008-03-29 22:58:53 -07:00 @@ -630,7 +630,7 @@ insert into t2 values (10,1),(20,2),(30, explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index -1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using index +1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL @@ -688,8 +688,8 @@ a1 a2 b1 b2 c1 c2 explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where drop table t1, t2, t3; create table t1 ( a int(11), diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result --- a/mysql-test/r/order_by.result 2008-02-14 14:37:04 -08:00 +++ b/mysql-test/r/order_by.result 2008-03-29 22:58:53 -07:00 @@ -1004,7 +1004,7 @@ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where SELECT t2.b as c FROM t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) ORDER BY c; diff -Nrup a/mysql-test/r/pool_of_threads.result b/mysql-test/r/pool_of_threads.result --- a/mysql-test/r/pool_of_threads.result 2008-02-14 07:45:49 -08:00 +++ b/mysql-test/r/pool_of_threads.result 2008-03-29 22:58:53 -07:00 @@ -1389,15 +1389,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1413,15 +1413,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff -Nrup a/mysql-test/r/select.result b/mysql-test/r/select.result --- a/mysql-test/r/select.result 2008-03-12 03:54:16 -07:00 +++ b/mysql-test/r/select.result 2008-03-29 22:58:53 -07:00 @@ -1391,15 +1391,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1415,15 +1415,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -2362,7 +2362,7 @@ insert into t1 values (1,2), (2,2), (3,2 insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using index condition +1 SIMPLE t2 ref c,d d 5 const 2 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2370,7 +2370,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using index condition +1 SIMPLE t2 ref c,d d 5 const 2 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2397,11 +2397,11 @@ INSERT INTO t2 VALUES ('one'),('two'),(' EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 +1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref a a 23 test.t1.a 2 +1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where DROP TABLE t1, t2; CREATE TABLE t1 ( city char(30) ); INSERT INTO t1 VALUES ('London'); @@ -3855,7 +3855,7 @@ cc 3 7 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref name name 6 test.t1.name 2 +1 SIMPLE t2 ref name name 6 test.t1.name 2 Using where SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; name name n ccc NULL NULL @@ -3929,7 +3929,7 @@ cc 3 7 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 ref name name 6 test.t1.name 2 +1 SIMPLE t2 ref name name 6 test.t1.name 2 Using where SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; name name n ccc NULL NULL diff -Nrup a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result --- a/mysql-test/r/ssl.result 2008-02-12 11:12:16 -08:00 +++ b/mysql-test/r/ssl.result 2008-03-29 22:58:53 -07:00 @@ -1392,15 +1392,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1416,15 +1416,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff -Nrup a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result --- a/mysql-test/r/ssl_compress.result 2008-02-12 11:12:16 -08:00 +++ b/mysql-test/r/ssl_compress.result 2008-03-29 22:58:53 -07:00 @@ -1395,15 +1395,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 @@ -1419,15 +1419,15 @@ id select_type table type possible_keys explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 +1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 diff -Nrup a/mysql-test/r/view.result b/mysql-test/r/view.result --- a/mysql-test/r/view.result 2008-03-12 03:54:16 -07:00 +++ b/mysql-test/r/view.result 2008-03-29 22:58:53 -07:00 @@ -1418,8 +1418,8 @@ a a b explain extended select * from t3 left join v3 on (t3.a = v3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 create view v1 (a) as select a from t1; @@ -1433,8 +1433,8 @@ a a b explain extended select * from t3 left join v4 on (t3.a = v4.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc --- a/sql/sql_select.cc 2008-03-20 00:59:16 -07:00 +++ b/sql/sql_select.cc 2008-03-29 22:58:54 -07:00 @@ -173,6 +173,10 @@ int join_read_next_same_or_null(READ_REC static COND *make_cond_for_table(COND *cond,table_map table, table_map used_table, bool exclude_expensive_cond); +static COND *make_cond_for_table_from_pred(COND *root_cond, COND *cond, + table_map tables, + table_map used_table, + bool exclude_expensive_cond); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order, @@ -232,7 +236,8 @@ void select_describe(JOIN *join, bool ne bool distinct, const char *message=NullS); static Item *remove_additional_cond(Item* conds); static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab); -static bool test_if_ref(Item_field *left_item,Item *right_item); +static bool test_if_ref(COND *root_cond, + Item_field *left_item,Item *right_item); static bool replace_where_subcondition(JOIN *join, Item *old_cond, Item *new_cond, bool fix_fields); @@ -849,7 +854,8 @@ void JOIN::remove_subq_pushed_predicates ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC && ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM && ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM && - test_if_ref ((Item_field *)((Item_func *)conds)->arguments()[1], + test_if_ref (this->conds, + (Item_field *)((Item_func *)conds)->arguments()[1], ((Item_func *)conds)->arguments()[0])) { *where= 0; @@ -7526,7 +7532,7 @@ make_join_select(JOIN *join,SQL_SELECT * } if (tmp || !cond || tab->type == JT_REF || tab->type == JT_REF_OR_NULL || - tab->type == JT_EQ_REF) + tab->type == JT_EQ_REF || first_inner_tab) { DBUG_EXECUTE("where",print_where(tmp,tab->table->alias, QT_ORDINARY);); SQL_SELECT *sel= tab->select= ((SQL_SELECT*) @@ -7552,7 +7558,7 @@ make_join_select(JOIN *join,SQL_SELECT * /* Push condition to storage engine if this is enabled and the condition is not guarded */ tab->table->file->pushed_cond= NULL; - if (thd->variables.engine_condition_pushdown) + if (thd->variables.engine_condition_pushdown && !first_inner_tab) { COND *push_cond= make_cond_for_table(tmp, current_map, current_map, 0); @@ -7776,6 +7782,8 @@ make_join_select(JOIN *join,SQL_SELECT * if (!cond_tab->select_cond) DBUG_RETURN(1); cond_tab->select_cond->quick_fix_field(); + if (cond_tab->select) + cond_tab->select->cond= cond_tab->select_cond; } } first_inner_tab= first_inner_tab->first_upper; @@ -14873,11 +14881,15 @@ end_write_group(JOIN *join, JOIN_TAB *jo 1 if right_item is used removable reference key on left_item */ -static bool test_if_ref(Item_field *left_item,Item *right_item) +static bool test_if_ref(COND *root_cond, + Item_field *left_item,Item *right_item) { Field *field=left_item->field; - // No need to change const test. We also have to keep tests on LEFT JOIN - if (!field->table->const_table && !field->table->maybe_null) + JOIN_TAB *join_tab= field->table->reginfo.join_tab; + // No need to change const test + if (!field->table->const_table && join_tab && + (!join_tab->first_inner || + *join_tab->first_inner->on_expr_ref == root_cond)) { Item *ref_item=part_of_refkey(field->table,field); if (ref_item && ref_item->eq(right_item,1)) @@ -14968,6 +14980,7 @@ static bool replace_where_subcondition(J tables Tables for which "current field values" are available used_table Table that we're extracting the condition for (may also include PSEUDO_TABLE_BITS + exclude_expensive_cond Do not push expensive conditions DESCRIPTION Extract the condition that can be checked after reading the table @@ -14995,6 +15008,15 @@ static COND * make_cond_for_table(COND *cond, table_map tables, table_map used_table, bool exclude_expensive_cond) { + return make_cond_for_table_from_pred(cond, cond, tables, used_table, + exclude_expensive_cond); +} + +static COND * +make_cond_for_table_from_pred(COND *root_cond, COND *cond, + table_map tables, table_map used_table, + bool exclude_expensive_cond) +{ if (used_table && !(cond->used_tables() & used_table) && /* Exclude constant conditions not checked at optimization time if @@ -15016,8 +15038,9 @@ make_cond_for_table(COND *cond, table_ma Item *item; while ((item=li++)) { - Item *fix=make_cond_for_table(item,tables,used_table, - exclude_expensive_cond); + Item *fix=make_cond_for_table_from_pred(root_cond, item, + tables, used_table, + exclude_expensive_cond); if (fix) new_cond->argument_list()->push_back(fix); } @@ -15047,7 +15070,9 @@ make_cond_for_table(COND *cond, table_ma Item *item; while ((item=li++)) { - Item *fix=make_cond_for_table(item,tables,0L, exclude_expensive_cond); + Item *fix=make_cond_for_table_from_pred(root_cond, item, + tables, 0L, + exclude_expensive_cond); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -15083,18 +15108,19 @@ make_cond_for_table(COND *cond, table_ma Remove equalities that are guaranteed to be true by use of 'ref' access method */ - if (((Item_func*) cond)->functype() == Item_func::EQ_FUNC) + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*) cond)->functype() == Item_func::EQ_FUNC) { - Item *left_item= ((Item_func*) cond)->arguments()[0]; - Item *right_item= ((Item_func*) cond)->arguments()[1]; + Item *left_item= ((Item_func*) cond)->arguments()[0]->real_item(); + Item *right_item= ((Item_func*) cond)->arguments()[1]->real_item(); if (left_item->type() == Item::FIELD_ITEM && - test_if_ref((Item_field*) left_item,right_item)) + test_if_ref(root_cond, (Item_field*) left_item,right_item)) { cond->marker=3; // Checked when read return (COND*) 0; } if (right_item->type() == Item::FIELD_ITEM && - test_if_ref((Item_field*) right_item,left_item)) + test_if_ref(root_cond, (Item_field*) right_item,left_item)) { cond->marker=3; // Checked when read return (COND*) 0;
| Thread | ||
|---|---|---|
| • bk commit into 6.0 tree (igor:1.2606) | igor | 30 Mar 2008 |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.611 sec. using MySQL 5.1.14-beta-log