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).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4106 to 4107)Bug#58134 | Ole John Aske | 14 Jan |
