List:Commits« Previous MessageNext Message »
From:jonas oreland Date:June 29 2011 2:15pm
Subject:bzr push into mysql-5.5-cluster branch (jonas.oreland:3392 to 3393)
View as plain text  
 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 oreland29 Jun