3393 jonas oreland 2011-06-29
ndb - reenabled condition pushdown (patch written by magnus)
modified:
mysql-test/suite/ndb/r/ndb_condition_pushdown.result
mysql-test/suite/ndb/r/ndb_join_pushdown.result
mysql-test/suite/ndb/t/disabled.def
mysql-test/suite/ndb/t/ndb_condition_pushdown.test
sql/ha_ndbcluster.cc
sql/ha_ndbcluster_cond.cc
3392 Martin Skold 2011-06-29
Enabled ndb_index_ordered since it apparently doesn't fail anymore
modified:
mysql-test/suite/ndb/t/disabled.def
=== modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-06-28 17:02:13 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-06-29 14:14:47 +0000
@@ -51,8 +51,8 @@ CREATE TABLE t3 (pk1 int unsigned NOT N
insert into t3 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");
CREATE TABLE t4 (pk1 int unsigned NOT NULL PRIMARY KEY, attr1 int unsigned NOT NULL, attr2 bigint unsigned, attr3 tinyint unsigned, attr4 VARCHAR(10) , KEY (attr1)) ENGINE=ndbcluster;
insert into t4 values (0,0,0,0,"a"),(1,1,9223372036854775803,1,"b"),(2,2,9223372036854775804,2,"c"),(3,3,9223372036854775805,3,"d"),(4,4,9223372036854775806,4,"e"),(5,5,9223372036854775807,5,"f");
-set @old_ecpd = @@session.engine_condition_pushdown;
-set engine_condition_pushdown = off;
+set @old_ecpd = @@session.optimizer_switch;
+set @@optimizer_switch = 'engine_condition_pushdown=off';
select auto from t1 where
string = "aaaa" and
vstring = "aaaa" and
@@ -484,7 +484,7 @@ pk1 attr1 attr2 attr3 attr4 pk1 attr1 at
2 2 9223372036854775804 2 c 2 2 9223372036854775804 2 c
3 3 9223372036854775805 3 d 3 3 9223372036854775805 3 d
4 4 9223372036854775806 4 e 4 4 9223372036854775806 4 e
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
explain
select auto from t1 where
string = "aaaa" and
@@ -1738,7 +1738,7 @@ pk1 attr1 attr2 attr3 pk1 attr1 attr2 at
explain
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 index attr1 PRIMARY 4 NULL # Using where with pushed condition
+1 SIMPLE t4 range attr1 attr1 4 NULL # Using where with pushed condition; Using filesort
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4
2 2 9223372036854775804 2 c
@@ -1769,12 +1769,12 @@ id select_type table type possible_keys
create table t5 (a int primary key auto_increment, b tinytext not null)
engine = ndb;
insert into t5 (b) values ('jonas'), ('jensing'), ('johan');
-set engine_condition_pushdown = off;
+set @@optimizer_switch='engine_condition_pushdown=off';
select * from t5 where b like '%jo%' order by a;
a b
1 jonas
3 johan
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
explain select * from t5 where b like '%jo%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t5 ALL NULL NULL NULL NULL # Using where
@@ -1782,7 +1782,7 @@ select * from t5 where b like '%jo%' ord
a b
1 jonas
3 johan
-set engine_condition_pushdown = off;
+set @@optimizer_switch='engine_condition_pushdown=off';
select auto from t1 where date_time like '1902-02-02 %' order by auto;
auto
2
@@ -1790,7 +1790,7 @@ select auto from t1 where date_time not
auto
3
4
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
explain select auto from t1 where date_time like '1902-02-02 %';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where
@@ -1808,7 +1808,7 @@ drop table t1;
create table t1 (a int, b varchar(3), primary key using hash(a))
engine=ndb;
insert into t1 values (1,'a'), (2,'ab'), (3,'abc');
-set engine_condition_pushdown = off;
+set @@optimizer_switch='engine_condition_pushdown=off';
select * from t1 where b like 'ab';
a b
2 ab
@@ -1821,7 +1821,7 @@ a b
select * from t1 where b like 'abc' or b like 'abc';
a b
3 abc
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
select * from t1 where b like 'ab';
a b
2 ab
@@ -1838,7 +1838,7 @@ drop table t1;
create table t1 (a int, b char(3), primary key using hash(a))
engine=ndb;
insert into t1 values (1,'a'), (2,'ab'), (3,'abc');
-set engine_condition_pushdown = off;
+set @@optimizer_switch='engine_condition_pushdown=off';
select * from t1 where b like 'ab';
a b
2 ab
@@ -1851,7 +1851,7 @@ a b
select * from t1 where b like 'abc' or b like 'abc';
a b
3 abc
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
select * from t1 where b like 'ab';
a b
2 ab
@@ -1868,11 +1868,11 @@ drop table t1;
create table t1 ( fname varchar(255), lname varchar(255) )
engine=ndbcluster;
insert into t1 values ("Young","Foo");
-set engine_condition_pushdown = 0;
+set @@optimizer_switch = 'engine_condition_pushdown=off';
SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
fname lname
Young Foo
-set engine_condition_pushdown = 1;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
fname lname
Young Foo
@@ -1880,11 +1880,11 @@ insert into t1 values ("aaa", "aaa");
insert into t1 values ("bbb", "bbb");
insert into t1 values ("ccc", "ccc");
insert into t1 values ("ddd", "ddd");
-set engine_condition_pushdown = 0;
+set @@optimizer_switch = 'engine_condition_pushdown=off';
SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
fname lname
Young Foo
-set engine_condition_pushdown = 1;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
SELECT fname, lname FROM t1 WHERE (fname like 'Y%') or (lname like 'F%');
fname lname
Young Foo
@@ -1928,7 +1928,7 @@ insert into t1 values (20,2,200,0+0x2222
insert into t1 values (30,3,300,0+0x3333);
insert into t1 values (40,4,400,0+0x4444);
insert into t1 values (50,5,500,0+0x5555);
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
select a,b,d from t1
where b in (0,1,2,5)
order by b;
@@ -1973,7 +1973,7 @@ insert into balerno values (2, repeat('B
insert into balerno values (3, repeat('BC', 1000), repeat('CC', 1000));
insert into balerno values (4, repeat('BD', 1000), repeat('CD', 1000));
insert into balerno values (5, repeat('BE', 1000), repeat('CE', 1000));
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
select a from balerno where b in (
repeat('10', 1000),
repeat('11', 1000),
@@ -2044,7 +2044,7 @@ a
1
show warnings;
Level Code Message
-set engine_condition_pushdown = off;
+set @@optimizer_switch='engine_condition_pushdown=off';
select a from balerno where b in (
repeat('10', 1000),
repeat('11', 1000),
@@ -2083,7 +2083,7 @@ Level Code Message
drop table balerno;
create table t (pk int primary key, x varchar(1)) engine = ndb;
insert into t values (0,"a");
-set engine_condition_pushdown = off;
+set @@optimizer_switch='engine_condition_pushdown=off';
select * from t where x <> "aa";
pk x
0 a
@@ -2102,7 +2102,7 @@ pk x
0 a
select * from t where x like "aa?";
pk x
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
explain select * from t where x <> "aa";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ALL NULL NULL NULL NULL 2 Using where
@@ -2151,7 +2151,7 @@ pk x
drop table t;
create table t (pk int primary key, x int) engine = ndb;
insert into t values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5);
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
explain select * from t where 3 between 1+1 and x order by pk;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t index NULL PRIMARY 4 NULL 6 Using where
@@ -2169,7 +2169,7 @@ pk x
4 4
5 5
drop table t;
-set engine_condition_pushdown = on;
+set @@optimizer_switch = 'engine_condition_pushdown=on';
create table t (x enum ('yes','yep','no')) engine = ndb;
insert into t values ('yes'),('yep'),('no');
explain select * from t where x like 'yes' order by x;
@@ -2192,7 +2192,7 @@ select * from t where x not like 'ye%' o
x
no
drop table t;
-set engine_condition_pushdown = on;
+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;
@@ -2201,7 +2201,7 @@ 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
+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
@@ -2211,7 +2211,7 @@ 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)
+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)))
@@ -2236,7 +2236,7 @@ id select_type table type possible_keys
1 SIMPLE tx ALL NULL NULL NULL NULL 2 100.00 Using where with pushed condition: (`test`.`tx`.`c` = 1)
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where with pushed condition: (`test`.`t2`.`c` = 1); Using join buffer
Warnings:
-Note 1644 Can't push table 't2' as child, 'type' must be a 'ref' access
+Note 1709 Can't push table 't2' as child, 'type' must be a 'ref' access
Note 1003 select `test`.`tx`.`a` AS `a`,`test`.`tx`.`b` AS `b`,`test`.`tx`.`c` AS `c`,`test`.`tx`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`c` = 1) and (`test`.`t2`.`c` = 1))
explain extended
select straight_join *
@@ -2261,15 +2261,15 @@ id select_type table type possible_keys
1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 2 100.00 Using where; Using filesort
1 SIMPLE tx eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00
Warnings:
-Note 1644 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
-Note 1644 Table 'tx' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 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`
explain extended select * from tx join tx as t2 on tx.c=1 where t2.c=1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tx ALL NULL NULL NULL NULL 2 100.00 Using where with pushed condition: (`test`.`tx`.`c` = 1)
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where with pushed condition: (`test`.`t2`.`c` = 1); Using join buffer
Warnings:
-Note 1644 Can't push table 't2' as child, 'type' must be a 'ref' access
+Note 1709 Can't push table 't2' as child, 'type' must be a 'ref' access
Note 1003 select `test`.`tx`.`a` AS `a`,`test`.`tx`.`b` AS `b`,`test`.`tx`.`c` AS `c`,`test`.`tx`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`c` = 1) and (`test`.`t2`.`c` = 1))
explain extended
select t2.c, count(distinct t2.a)
@@ -2281,46 +2281,13 @@ id select_type table type possible_keys
1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 2 100.00 Using where; Using filesort
1 SIMPLE tx eq_ref PRIMARY PRIMARY 8 test.t2.c,test.t2.d 1 100.00
Warnings:
-Note 1644 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
-Note 1644 Table 'tx' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 Table 't2' is not pushable: GROUP BY cannot be done using index on grouped columns.
+Note 1709 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;
-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 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 t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
insert into t values (1,0), (2,0), (3,0), (4,0);
-set engine_condition_pushdown=1;
+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
@@ -2331,7 +2298,7 @@ pk1 pk1 pk2 pk2
3 3 0 0
4 3 0 0
drop table t;
-set engine_condition_pushdown = on;
+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;
@@ -2340,7 +2307,7 @@ 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
+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
@@ -2350,7 +2317,7 @@ 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)
+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)))
@@ -2362,6 +2329,14 @@ 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;
-set engine_condition_pushdown = @old_ecpd;
+set @@session.optimizer_switch = @old_ecpd;
DROP TABLE t1,t2,t3,t4,t5;
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-06-28 17:02:13 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-06-29 14:14:47 +0000
@@ -3126,7 +3126,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 5 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
@@ -3142,7 +3142,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
@@ -3158,7 +3158,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 6 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
@@ -3174,7 +3174,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
@@ -3190,7 +3190,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 7 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
@@ -3206,7 +3206,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
@@ -3222,7 +3222,7 @@ explain extended
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE x ref PRIMARY PRIMARY 4 const 3 100.00 Parent of 2 pushed join@1
-1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where
+1 SIMPLE y eq_ref PRIMARY PRIMARY 8 test.x.d3,const 1 100.00 Child of 'x' in pushed join@1; Using where with pushed condition: (`test`.`y`.`b3` = <cache>((60 + 3)))
Warnings:
Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = <cache>((60 + 3))) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f))
select * from t3 x, t3 y where x.a3=0x2f and y.a3=x.d3 and y.b3=(60+3);
=== modified file 'mysql-test/suite/ndb/t/disabled.def'
--- a/mysql-test/suite/ndb/t/disabled.def 2011-06-29 13:09:47 +0000
+++ b/mysql-test/suite/ndb/t/disabled.def 2011-06-29 14:14:47 +0000
@@ -16,8 +16,6 @@ ndb_partition_error2 : Bug#40989 ndb_par
ndb_cache_trans : Bug#42197 Query cache and autocommit
ndb_disconnect_ddl : Bug#31853 flaky testcase...
-ndb_condition_pushdown : SEAGULL
-
ndb_multi : SEAGULL schema distribution
ndb_sql_allow_batching : SEAGULL WL3733 xxx_allow_batching
=== modified file 'mysql-test/suite/ndb/t/ndb_condition_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2011-06-27 07:58:59 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2011-06-29 14:14:47 +0000
@@ -2389,6 +2389,11 @@ 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;
set @@session.optimizer_switch = @old_ecpd;
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2011-06-28 17:02:13 +0000
+++ b/sql/ha_ndbcluster.cc 2011-06-29 14:14:47 +0000
@@ -4187,6 +4187,7 @@ int ha_ndbcluster::full_table_scan(const
DBUG_PRINT("info", ("Starting unique index scan"));
if (!m_cond)
m_cond= new ha_ndbcluster_cond;
+
if (!m_cond)
{
my_errno= HA_ERR_OUT_OF_MEM;
@@ -14622,8 +14623,9 @@ const
Item*
ha_ndbcluster::cond_push(const Item *cond)
{
- DBUG_ENTER("cond_push");
+ DBUG_ENTER("ha_ndbcluster::cond_push");
+#if 1
if (cond->used_tables() & ~table->map)
{
/**
@@ -14635,6 +14637,14 @@ ha_ndbcluster::cond_push(const Item *con
DBUG_EXECUTE("where",print_where((Item *)cond, "Rejected cond_push", QT_ORDINARY););
DBUG_RETURN(cond);
}
+#else
+ /*
+ Make sure that 'cond' does not refer field(s) from other tables
+ or other instances of this table.
+ (This was a legacy bug in optimizer)
+ */
+ DBUG_ASSERT(!(cond->used_tables() & ~table->map));
+#endif
if (!m_cond)
m_cond= new ha_ndbcluster_cond;
if (!m_cond)
=== modified file 'sql/ha_ndbcluster_cond.cc'
--- a/sql/ha_ndbcluster_cond.cc 2011-06-23 06:59:40 +0000
+++ b/sql/ha_ndbcluster_cond.cc 2011-06-29 14:14:47 +0000
@@ -40,7 +40,8 @@ typedef NdbDictionary::Table NDBTAB;
position of fields that is not directly available in the Item tree.
Also checks if condition is supported.
*/
-void ndb_serialize_cond(const Item *item, void *arg)
+static void
+ndb_serialize_cond(const Item *item, void *arg)
{
Ndb_cond_traverse_context *context= (Ndb_cond_traverse_context *) arg;
DBUG_ENTER("ndb_serialize_cond");
@@ -230,6 +231,7 @@ void ndb_serialize_cond(const Item *item
context->expect(Item::STRING_ITEM);
context->expect(Item::VARBIN_ITEM);
context->expect(Item::FUNC_ITEM);
+ context->expect(Item::CACHE_ITEM);
ndb_serialize_cond(rewrite_context->left_hand_item, arg);
context->skip= 0; // Any FUNC_ITEM expression has already been parsed
context->rewrite_stack= rewrite_context; // Enable rewrite mode
@@ -258,7 +260,7 @@ void ndb_serialize_cond(const Item *item
{
Item_field *field_item= (Item_field *) item;
Field *field= field_item->field;
- enum_field_types type= field->real_type();
+ const enum_field_types type= field->real_type();
/*
Check that the field is part of the table of the handler
instance and that we expect a field with of this result type.
@@ -270,13 +272,13 @@ void ndb_serialize_cond(const Item *item
DBUG_PRINT("info", ("table %s", tab->getName()));
DBUG_PRINT("info", ("column %s", field->field_name));
DBUG_PRINT("info", ("column length %u", field->field_length));
- DBUG_PRINT("info", ("type %d", field->real_type()));
+ DBUG_PRINT("info", ("type %d", type));
DBUG_PRINT("info", ("result type %d", field->result_type()));
// Check that we are expecting a field and with the correct
// result type and of length that can store the item value
if (context->expecting(Item::FIELD_ITEM) &&
- context->expecting_field_type(field->real_type()) &&
+ context->expecting_field_type(type) &&
context->expecting_max_length(field->field_length) &&
(context->expecting_field_result(field->result_type()) ||
// Date and year can be written as string or int
@@ -579,6 +581,7 @@ void ndb_serialize_cond(const Item *item
context->expect(Item::STRING_ITEM);
context->expect(Item::VARBIN_ITEM);
context->expect(Item::FUNC_ITEM);
+ context->expect(Item::CACHE_ITEM);
break;
}
case Item_func::IN_FUNC:
@@ -606,6 +609,7 @@ void ndb_serialize_cond(const Item *item
context->expect(Item::STRING_ITEM);
context->expect(Item::VARBIN_ITEM);
context->expect(Item::FUNC_ITEM);
+ context->expect(Item::CACHE_ITEM);
break;
}
case Item_func::NEG_FUNC:
@@ -759,8 +763,8 @@ void ndb_serialize_cond(const Item *item
{
#ifndef DBUG_OFF
char buff[256];
- String str(buff,(uint32) sizeof(buff), system_charset_info);
- str.length(0);
+ String str(buff,0, system_charset_info);
+ //str.length(0);// Magnus
Item_string *string_item= (Item_string *) item;
DBUG_PRINT("info", ("value \"%s\"",
string_item->val_str(&str)->ptr()));
@@ -924,9 +928,151 @@ void ndb_serialize_cond(const Item *item
}
break;
}
+ case Item::CACHE_ITEM:
+ {
+ DBUG_PRINT("info", ("CACHE_ITEM"));
+ Item_cache* cache_item = (Item_cache*)item;
+ DBUG_PRINT("info", ("result type %d", cache_item->result_type()));
+
+ // Item_cache has cached "something", use its value
+ // based on the result_type of the item
+ switch(cache_item->result_type())
+ {
+ case INT_RESULT:
+ DBUG_PRINT("info", ("INT_RESULT"));
+ if (context->expecting(Item::INT_ITEM))
+ {
+ DBUG_PRINT("info", ("value %ld",
+ (long) ((Item_int*) item)->value));
+ NDB_ITEM_QUALIFICATION q;
+ q.value_type= Item::INT_ITEM;
+ curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item);
+ if (! context->expecting_no_field_result())
+ {
+ // We have not seen the field argument yet
+ context->expect_only(Item::FIELD_ITEM);
+ context->expect_only_field_result(INT_RESULT);
+ context->expect_field_result(REAL_RESULT);
+ context->expect_field_result(DECIMAL_RESULT);
+ }
+ else
+ {
+ // Expect another logical expression
+ context->expect_only(Item::FUNC_ITEM);
+ context->expect(Item::COND_ITEM);
+ }
+ }
+ else
+ context->supported= FALSE;
+ break;
+
+ case REAL_RESULT:
+ DBUG_PRINT("info", ("REAL_RESULT"));
+ if (context->expecting(Item::REAL_ITEM))
+ {
+ DBUG_PRINT("info", ("value %f", ((Item_float*) item)->value));
+ NDB_ITEM_QUALIFICATION q;
+ q.value_type= Item::REAL_ITEM;
+ curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item);
+ if (! context->expecting_no_field_result())
+ {
+ // We have not seen the field argument yet
+ context->expect_only(Item::FIELD_ITEM);
+ context->expect_only_field_result(REAL_RESULT);
+ }
+ else
+ {
+ // Expect another logical expression
+ context->expect_only(Item::FUNC_ITEM);
+ context->expect(Item::COND_ITEM);
+ }
+ }
+ else
+ context->supported= FALSE;
+ break;
+
+ case DECIMAL_RESULT:
+ DBUG_PRINT("info", ("DECIMAL_RESULT"));
+ if (context->expecting(Item::DECIMAL_ITEM))
+ {
+ DBUG_PRINT("info", ("value %f",
+ ((Item_decimal*) item)->val_real()));
+ NDB_ITEM_QUALIFICATION q;
+ q.value_type= Item::DECIMAL_ITEM;
+ curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item);
+ if (! context->expecting_no_field_result())
+ {
+ // We have not seen the field argument yet
+ context->expect_only(Item::FIELD_ITEM);
+ context->expect_only_field_result(REAL_RESULT);
+ context->expect_field_result(DECIMAL_RESULT);
+ }
+ else
+ {
+ // Expect another logical expression
+ context->expect_only(Item::FUNC_ITEM);
+ context->expect(Item::COND_ITEM);
+ }
+ }
+ else
+ context->supported= FALSE;
+ break;
+
+ case STRING_RESULT:
+ DBUG_PRINT("info", ("STRING_RESULT"));
+ // Check that we do support pushing the item value length
+ if (context->expecting(Item::STRING_ITEM) &&
+ context->expecting_length(item->max_length))
+ {
+ #ifndef DBUG_OFF
+ char buff[256];
+ String str(buff,0, system_charset_info);
+ //str.length(0);// Magnus
+ Item_string *string_item= (Item_string *) item;
+ DBUG_PRINT("info", ("value \"%s\"",
+ string_item->val_str(&str)->ptr()));
+ #endif
+ NDB_ITEM_QUALIFICATION q;
+ q.value_type= Item::STRING_ITEM;
+ curr_cond->ndb_item= new Ndb_item(NDB_VALUE, q, item);
+ if (! context->expecting_no_field_result())
+ {
+ // We have not seen the field argument yet
+ context->expect_only(Item::FIELD_ITEM);
+ context->expect_only_field_result(STRING_RESULT);
+ context->expect_collation(item->collation.collation);
+ context->expect_length(item->max_length);
+ }
+ else
+ {
+ // Expect another logical expression
+ context->expect_only(Item::FUNC_ITEM);
+ context->expect(Item::COND_ITEM);
+ context->expect_no_length();
+ // Check that we are comparing with a field with same collation
+ if (!context->expecting_collation(item->collation.collation))
+ {
+ DBUG_PRINT("info", ("Found non-matching collation %s",
+ item->collation.collation->name));
+ context->supported= FALSE;
+ }
+ }
+ }
+ else
+ context->supported= FALSE;
+ break;
+
+ default:
+ context->supported= FALSE;
+ break;
+ }
+ break;
+ }
+
default:
{
- DBUG_PRINT("info", ("Found item of type %d", item->type()));
+ DBUG_PRINT("info", ("Found unsupported item of type %d",
+ item->type()));
context->supported= FALSE;
}
}
@@ -967,7 +1113,7 @@ Item*
ha_ndbcluster_cond::cond_push(const Item *cond,
TABLE *table, const NDBTAB *ndb_table)
{
- DBUG_ENTER("cond_push");
+ DBUG_ENTER("ha_ndbcluster_cond::cond_push");
Ndb_cond_stack *ndb_cond = new Ndb_cond_stack();
if (ndb_cond == NULL)
{
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.5-cluster branch (jonas.oreland:3392 to 3393) | jonas oreland | 29 Jun |
