List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:January 14 2011 10:42am
Subject:bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4107) Bug#58134
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0/ based on revid:ole.john.aske@stripped

 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
=== 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();


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110114104253-ojmo06ia9rjdvqkp.bundle
Thread
bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4107) Bug#58134Ole John Aske14 Jan