From: Ole John Aske Date: January 14 2011 10:43am Subject: bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4106 to 4107) Bug#58134 List-Archive: http://lists.mysql.com/commits/128717 X-Bug: 58134 Message-Id: <20110114104333.8027F1818C@loki43.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4107 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for bug#58134: 'Incorrectly condition pushdown inside subquery to NDB engine' An incorrect 'table_map' containing both the table itself, and possible any outer-refs if this was the last table in the subquery, was presented to make_cond_for_table(). As a pushed condition is only able to refer column from the table the condition is pushed to, nothing else than columns from the table itself (tab->table->map) may be refered in the pushed condition constructed by 'push_cond= make_cond_for_table()'. Also fix a minor 'copy and paste' bug in a comment in make_cond_for_table(). modified: mysql-test/suite/ndb/r/ndb_condition_pushdown.result mysql-test/suite/ndb/t/ndb_condition_pushdown.test sql/sql_select.cc 4106 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for Bug#57034 incorrect OUTER JOIN result when joined on unique key Item_equal::val_int() checked for NULL-values by checking Item::null_value *before* the respective ::store_value() and ::cmp(Item*) metods where called. As Item::null_value is set by these metods, the value of 'null_value' is not valid until *after* ::store_value() or ::cmp() has been called for the Item object. Fix is to swap order of ::store_value()/::cmp() and checking of Item::null_value. This pattern is widely used other places inside item_cmpfunc.cc . modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/item_cmpfunc.cc === modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2010-11-29 15:43:51 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-01-14 10:42:53 +0000 @@ -2247,5 +2247,37 @@ id select_type table type possible_keys Warnings: Note 1003 select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`b` = `test`.`t2`.`d`) and (`test`.`tx`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c` drop table tx; +set engine_condition_pushdown = on; +create table t (pk int, i int) engine = ndb; +insert into t values (1,3), (3,6), (6,9), (9,1); +create table subq (pk int, i int) engine = ndb; +insert into subq values (1,3), (3,6), (6,9), (9,1); +explain extended +select * from t where exists +(select * from t as subq where subq.i=3 and t.i=3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 50.00 Using where +2 DEPENDENT SUBQUERY subq ALL NULL NULL NULL NULL 4 100.00 Using where with pushed condition: (`test`.`subq`.`i` = 3) +Warnings: +Note 1276 Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` where exists(select 1 from `test`.`t` `subq` where ((`test`.`subq`.`i` = 3) and (`test`.`t`.`i` = 3))) +explain extended +select * from t where exists +(select * from subq where subq.i=3 and t.i=3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 Using where +2 DEPENDENT SUBQUERY subq ALL NULL NULL NULL NULL 4 50.00 Using where with pushed condition: (`test`.`subq`.`i` = 3) +Warnings: +Note 1276 Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` where exists(select 1 from `test`.`subq` where ((`test`.`subq`.`i` = 3) and (`test`.`t`.`i` = 3))) +select * from t where exists +(select * from t as subq where subq.i=3 and t.i=3); +pk i +1 3 +select * from t where exists +(select * from subq where subq.i=3 and t.i=3); +pk i +1 3 +drop table t,subq; set engine_condition_pushdown = @old_ecpd; DROP TABLE t1,t2,t3,t4,t5; === modified file 'mysql-test/suite/ndb/t/ndb_condition_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2010-11-29 15:43:51 +0000 +++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2011-01-14 10:42:53 +0000 @@ -2320,6 +2320,32 @@ group by t2.c; drop table tx; +# Bug#58134: Incorrectly condition pushdown inside subquery to NDB engine +set engine_condition_pushdown = on; + +create table t (pk int, i int) engine = ndb; +insert into t values (1,3), (3,6), (6,9), (9,1); +create table subq (pk int, i int) engine = ndb; +insert into subq values (1,3), (3,6), (6,9), (9,1); + +# 'Explain extended' to verify that only 'subq.i=3' is pushed +explain extended +select * from t where exists + (select * from t as subq where subq.i=3 and t.i=3); +explain extended + select * from t where exists + (select * from subq where subq.i=3 and t.i=3); + +--sorted_result +select * from t where exists + (select * from t as subq where subq.i=3 and t.i=3); +--sorted_result +select * from t where exists + (select * from subq where subq.i=3 and t.i=3); + +drop table t,subq; + + set engine_condition_pushdown = @old_ecpd; DROP TABLE t1,t2,t3,t4,t5; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-12 11:54:35 +0000 +++ b/sql/sql_select.cc 2011-01-14 10:42:53 +0000 @@ -6361,7 +6361,11 @@ make_join_select(JOIN *join,SQL_SELECT * if (thd->variables.engine_condition_pushdown) { COND *push_cond= +#ifndef MCP_BUG58134 + make_cond_for_table(tmp, tab->table->map, tab->table->map); +#else make_cond_for_table(tmp, current_map, current_map); +#endif if (push_cond) { /* Push condition to handler */ @@ -12860,7 +12864,7 @@ make_cond_for_table(COND *cond, table_ma new_cond->argument_list()->push_back(fix); } /* - Item_cond_and do not need fix_fields for execution, its parameters + Item_cond_or do not need fix_fields for execution, its parameters are fixed or do not need fix_fields, too */ new_cond->quick_fix_field(); No bundle (reason: useless for push emails).