Hi Ole John,
patch is approved.
To the best of my knowledge, this is how engine condition pushdown is supposed
to work, at least for NDB.
Thanks,
Roy
On 19.11.10 11.09, Ole John Aske wrote:
> #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();
>
>
>
>
