From: Ole John Aske
Date: November 19 2010 10:52am
Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
 (ole.john.aske:3373 to 3374) Bug#58134
List-Archive: http://lists.mysql.com/commits/124414
X-Bug: 58134
Message-Id: <20101119105208.A9AD4222@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3374 Ole John Aske	2010-11-19
      SPJ-scan-scan: Cherry picked proposed fix for bug#58134: Incorrectly condition pushdown inside subquery to NDB engine into SPJ branch.
      
      Testblocker, prevented extensive RQG testing with subqueries.

    modified:
      mysql-test/suite/ndb/r/ndb_condition_pushdown.result
      mysql-test/suite/ndb/t/ndb_condition_pushdown.test
      sql/sql_select.cc
 3373 Ole John Aske	2010-11-17
      SPJ-scan-scan: Rework / refactoring of http://lists.mysql.com/commits/124035
      
      Changed the way AQP determines a table to has been 'optimized away, or const'ified by optimizer'

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      sql/abstract_query_plan.cc
      sql/ha_ndbcluster.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2010-11-15 10:26:48 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2010-11-19 10:51:30 +0000
@@ -2192,5 +2192,37 @@ select * from t where x not like 'ye%' o
 x
 no
 drop table t;
+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-11 08:21:34 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test	2010-11-19 10:51:30 +0000
@@ -2284,6 +2284,32 @@ explain select * from t where x not like
 select * from t where x not like 'ye%' order by x;
 drop table t;
 
+# 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-11-16 13:26:19 +0000
+++ b/sql/sql_select.cc	2010-11-19 10:51:30 +0000
@@ -6455,7 +6455,7 @@ make_join_select(JOIN *join,SQL_SELECT *
 	  if (thd->variables.engine_condition_pushdown)
           {
             COND *push_cond= 
-              make_cond_for_table(tmp, current_map, current_map);
+              make_cond_for_table(tmp, tab->table->map, tab->table->map);
             if (push_cond)
             {
               /* Push condition to handler */
@@ -13072,7 +13072,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).