From: Ole John Aske Date: November 16 2011 8:19am Subject: bzr push into mysql-5.5-cluster branch (ole.john.aske:3650 to 3651) List-Archive: http://lists.mysql.com/commits/141961 Message-Id: <20111116081907.1AECC234@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3651 Ole John Aske 2011-11-16 [merge] Merge 7.0-spj-ss -> 5.5-cluster added: mysql-test/suite/ndb/t/ndb_join_pushdown_default.test renamed: mysql-test/suite/ndb/r/ndb_join_pushdown.result => mysql-test/suite/ndb/r/ndb_join_pushdown_default.result mysql-test/suite/ndb/t/ndb_join_pushdown.test => mysql-test/suite/ndb/t/ndb_join_pushdown.inc modified: mysql-test/suite/ndb/r/ndb_condition_pushdown.result mysql-test/suite/ndb/t/ndb_condition_pushdown.test sql/ha_ndbcluster.cc mysql-test/suite/ndb/r/ndb_join_pushdown_default.result mysql-test/suite/ndb/t/ndb_join_pushdown.inc 3650 jonas oreland 2011-11-16 [merge] ndb - merge 71 to 72 modified: storage/ndb/include/kernel/ndb_limits.h storage/ndb/include/mgmapi/mgmapi_config_parameters.h storage/ndb/include/ndb_version.h.in storage/ndb/src/kernel/blocks/dbacc/Dbacc.hpp storage/ndb/src/kernel/blocks/dbacc/DbaccMain.cpp storage/ndb/src/kernel/blocks/dbdih/Dbdih.hpp storage/ndb/src/kernel/blocks/dbdih/DbdihMain.cpp storage/ndb/src/kernel/blocks/dblqh/Dblqh.hpp storage/ndb/src/kernel/blocks/dblqh/DblqhCommon.cpp storage/ndb/src/kernel/blocks/dblqh/DblqhCommon.hpp storage/ndb/src/kernel/blocks/dblqh/DblqhInit.cpp storage/ndb/src/kernel/blocks/dblqh/DblqhMain.cpp storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp storage/ndb/src/kernel/blocks/dbtup/Dbtup.hpp storage/ndb/src/kernel/blocks/dbtup/DbtupDiskAlloc.cpp storage/ndb/src/kernel/blocks/dbtup/DbtupExecQuery.cpp storage/ndb/src/kernel/blocks/dbtup/DbtupGen.cpp storage/ndb/src/kernel/blocks/dbtup/DbtupIndex.cpp storage/ndb/src/kernel/blocks/dbtup/DbtupMeta.cpp storage/ndb/src/kernel/blocks/dbtux/Dbtux.hpp storage/ndb/src/kernel/vm/GlobalData.hpp storage/ndb/src/kernel/vm/pc.hpp === modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-11-10 20:35:28 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-11-16 08:17:17 +0000 @@ -2210,38 +2210,6 @@ select * from t where x not like 'ye%' o x no drop table t; -set @@optimizer_switch = '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 100.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 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`.`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; create table tx ( a int not null, b int not null, @@ -2303,20 +2271,9 @@ Note 9999 Table 't2' is not pushable: GR Note 9999 Table 'tx' is not pushable: GROUP BY cannot be done using index on grouped columns. 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; -create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb; -insert into t values (1,0), (2,0), (3,0), (4,0); -set @@optimizer_switch='engine_condition_pushdown=on'; -select table1.pk1, table2.pk1, table1.pk2, table2.pk2 -from t as table1, t as table2 -where table2.pk1 in (0,3) and -(table1.pk1 = 7 or table2.pk1 = 3); -pk1 pk1 pk2 pk2 -1 3 0 0 -2 3 0 0 -3 3 0 0 -4 3 0 0 -drop table t; -set @@optimizer_switch = 'engine_condition_pushdown=on'; +set engine_condition_pushdown = on; +Warnings: +Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead 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; @@ -2347,15 +2304,20 @@ select * from t where exists (select * from subq where subq.i=3 and t.i=3); pk i 1 3 -explain extended -select * from t -where i = (select max(i) from t); -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 SUBQUERY t ALL NULL NULL NULL NULL 4 100.00 -Warnings: -Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` where (`test`.`t`.`i` = (select max(`test`.`t`.`i`) from `test`.`t`)) drop table t,subq; +create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb; +insert into t values (1,0), (2,0), (3,0), (4,0); +set @@optimizer_switch='engine_condition_pushdown=on'; +select table1.pk1, table2.pk1, table1.pk2, table2.pk2 +from t as table1, t as table2 +where table2.pk1 in (0,3) and +(table1.pk1 = 7 or table2.pk1 = 3); +pk1 pk1 pk2 pk2 +1 3 0 0 +2 3 0 0 +3 3 0 0 +4 3 0 0 +drop table t; create table mytable(i int, s varchar(255) ) engine = ndb; insert into mytable values(0,"Text Hej"),(1, "xText aaja"); set @@optimizer_switch = 'engine_condition_pushdown=on'; === renamed file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' => 'mysql-test/suite/ndb/r/ndb_join_pushdown_default.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-10-24 08:50:10 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2011-11-16 08:17:17 +0000 @@ -5543,7 +5543,7 @@ sorted_scan_count pushed_queries_defined 405 pushed_queries_dropped -11 +8 pushed_queries_executed 550 set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/t/ndb_condition_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2011-11-10 20:35:28 +0000 +++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2011-11-16 08:17:17 +0000 @@ -2290,31 +2290,6 @@ 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 @@optimizer_switch = '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; - # Bug#58553 Queries with pushed conditions causes 'explain extended' to crash mysqld create table tx ( a int not null, @@ -2351,29 +2326,8 @@ group by t2.c; drop table tx; -# Bug#58791 Incorrect result as Cluster may fail to reject an unpushable condition - -create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb; -insert into t values (1,0), (2,0), (3,0), (4,0); - -set @@optimizer_switch='engine_condition_pushdown=on'; - -# Multiple instances of same table (t as table, ) confused -# ha_ndbcluster::cond_push() which accepted -# '(table1.pk1 = 7 or table2.pk1 = 3)' as a pushable cond. -# for 'table2' -# - ---sorted_result -select table1.pk1, table2.pk1, table1.pk2, table2.pk2 - from t as table1, t as table2 - where table2.pk1 in (0,3) and - (table1.pk1 = 7 or table2.pk1 = 3); - -drop table t; - # Bug#58134: Incorrectly condition pushdown inside subquery to NDB engine -set @@optimizer_switch = 'engine_condition_pushdown=on'; +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); @@ -2395,13 +2349,31 @@ select * from t where exists select * from t where exists (select * from subq where subq.i=3 and t.i=3); -# extra test of subquery -explain extended -select * from t -where i = (select max(i) from t); - drop table t,subq; + + +# Bug#58791 Incorrect result as Cluster may fail to reject an unpushable condition + +create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb; +insert into t values (1,0), (2,0), (3,0), (4,0); + +set @@optimizer_switch='engine_condition_pushdown=on'; + +# Multiple instances of same table (t as table, ) confused +# ha_ndbcluster::cond_push() which accepted +# '(table1.pk1 = 7 or table2.pk1 = 3)' as a pushable cond. +# for 'table2' +# + +--sorted_result +select table1.pk1, table2.pk1, table1.pk2, table2.pk2 + from t as table1, t as table2 + where table2.pk1 in (0,3) and + (table1.pk1 = 7 or table2.pk1 = 3); + +drop table t; + # Bug#11765142 58073: CONCAT AND OR GIVE INCORRECT QUERY RESULTS create table mytable(i int, s varchar(255) ) engine = ndb; insert into mytable values(0,"Text Hej"),(1, "xText aaja"); === renamed file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test' => 'mysql-test/suite/ndb/t/ndb_join_pushdown.inc' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-10-24 08:50:10 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2011-11-16 08:17:17 +0000 @@ -1,5 +1,3 @@ --- source include/have_ndb.inc - ######################################## # Define two connections as we want DDL to use its own connection # in order to keep DDL statistics counting out of the way === added file 'mysql-test/suite/ndb/t/ndb_join_pushdown_default.test' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown_default.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown_default.test 2011-11-15 13:57:10 +0000 @@ -0,0 +1,7 @@ +# +# Test of pushed joins, aka SPJ, or AQL. +# (Runs with default set of switches) +# + +--source include/have_ndb.inc +--source ndb_join_pushdown.inc === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-11-15 14:45:16 +0000 +++ b/sql/ha_ndbcluster.cc 2011-11-16 08:17:17 +0000 @@ -14564,10 +14564,6 @@ ha_ndbcluster::read_multi_range_first(KE } else // if ((...PUSHED_ROOT && m_pushed_join->get_query_def().isScanQuery()) ||... { - if (m_pushed_join_operation == PUSHED_ROOT) - { - m_thd_ndb->m_pushed_queries_dropped++; - } if (!trans) { DBUG_ASSERT(active_index != MAX_KEY); No bundle (reason: useless for push emails).