#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:ole.john.aske@stripped
3474 Ole John Aske 2010-11-19
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
inside 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 2008-01-31 13:47:50 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2010-11-19 10:09:13 +0000
@@ -1916,5 +1916,37 @@ a b d
50 5 21845
Warnings:
Warning 4294 Scan filter is too large, discarded
+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 2008-01-31 13:47:50 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2010-11-19 10:09:13 +0000
@@ -2050,5 +2050,31 @@ select a,b,d from t1
order by b;
--enable_query_log
+# 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-20 13:22:47 +0000
+++ b/sql/sql_select.cc 2010-11-19 10:09:13 +0000
@@ -6371,7 +6371,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 */
@@ -12857,7 +12857,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-20101119100913-r7tzb15bdquf3q7f.bundle
