Merge test cases for bugs that were fixed by MWL#89.

2 files changed, 367 insertions, 0 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index cb2190ef5ac..d0f546200dd 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1815,4 +1815,200 @@ SELECT DISTINCT f4
 FROM t1));
 f2	f1
 drop table t1, t2, t3, t4;
+#
+# LP BUG#611690 Crash in select_describe() with nested subqueries
+#
+CREATE TABLE t1 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (8,'v','v');
+INSERT INTO t1 VALUES (9,'r','r');
+CREATE TABLE t2 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (2,'w','w');
+INSERT INTO t2 VALUES (9,'m','m');
+set @old_optimizer_switch = @@optimizer_switch;
+set @@optimizer_switch='subquery_cache=off,materialization=on,in_to_exists=off,semijoin=off';
+EXPLAIN
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+ON SUBQUERY2_t2.col_varchar_key
+WHERE SUBQUERY2_t2.col_varchar_nokey IN
+(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	col_int_key	5	NULL	2	Using index
+2	SUBQUERY	SUBQUERY2_t1	index	NULL	col_int_key	5	NULL	2	Using index
+2	SUBQUERY	SUBQUERY2_t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+ON SUBQUERY2_t2.col_varchar_key
+WHERE SUBQUERY2_t2.col_varchar_nokey IN
+(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+col_int_key
+set @@optimizer_switch='subquery_cache=off,materialization=off,in_to_exists=on,semijoin=off';
+EXPLAIN
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+ON SUBQUERY2_t2.col_varchar_key
+WHERE SUBQUERY2_t2.col_varchar_nokey IN
+(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	col_int_key	5	NULL	2	Using index
+2	SUBQUERY	SUBQUERY2_t1	index	NULL	col_int_key	5	NULL	2	Using index
+2	SUBQUERY	SUBQUERY2_t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+ON SUBQUERY2_t2.col_varchar_key
+WHERE SUBQUERY2_t2.col_varchar_nokey IN
+(SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+col_int_key
+drop table t1, t2;
+set @@optimizer_switch = @old_optimizer_switch;
+#
+# LP BUG#612543 Crash in Item_field::used_tables() with view + subquery + prepared statements
+#
+CREATE TABLE t1 ( f1 int(11), f2 varchar(1));
+CREATE TABLE t2 ( f3 varchar(1));
+insert into t1 values (2,'x'), (5,'y');
+insert into t2 values ('x'), ('z');
+CREATE VIEW v2 AS SELECT * FROM t2;
+set @old_optimizer_switch = @@optimizer_switch;
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
+EXECUTE st1;
+f1	f2	f3
+2	x	x
+5	y	x
+EXECUTE st1;
+f1	f2	f3
+2	x	x
+5	y	x
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+PREPARE st2 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
+EXECUTE st2;
+f1	f2	f3
+2	x	x
+5	y	x
+EXECUTE st2;
+f1	f2	f3
+2	x	x
+5	y	x
+set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
+EXECUTE st3;
+f1	f2	f3
+2	x	x
+5	y	x
+EXECUTE st3;
+f1	f2	f3
+2	x	x
+5	y	x
+set @@optimizer_switch = @old_optimizer_switch;
+drop table t1, t2;
+drop view v2;
+#
+# LP BUG#611396 RQG: crash in Item_field::register_field_in_read_map with semijoin=off
+# and prepared statements and materialization
+CREATE TABLE t1 ( f1 int(11), f2 int(11)) ;
+CREATE TABLE t2 ( f1 int(11), f4 varchar(1), PRIMARY KEY (f1)) ;
+INSERT INTO t2 VALUES ('23','j'),('24','e');
+CREATE TABLE t3 ( f1 int(11), f4 varchar(1)) ;
+INSERT INTO t3 VALUES ('8','j');
+set @old_optimizer_switch = @@optimizer_switch;
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
+EXPLAIN
+SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
+FROM t2 JOIN t3 ON t3.f4 = t2.f4
+WHERE t3.f1 = 8
+GROUP BY 1, 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+PREPARE st1 FROM "
+SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
+FROM t2 JOIN t3 ON t3.f4 = t2.f4
+WHERE t3.f1 = 8
+GROUP BY 1, 2";
+EXECUTE st1;
+f1	(SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
+23	NULL
+EXECUTE st1;
+f1	(SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
+23	NULL
+set @@optimizer_switch = @old_optimizer_switch;
+drop table t1, t2, t3;
+#
+# LP BUG#611382 RQG: Query returns extra rows when executed with materialization=on
+#
+CREATE TABLE t1 ( f4 varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (NULL);
+CREATE TABLE t2 ( f2 date, f3 varchar(1), f4 varchar(1)) ;
+INSERT INTO t2 VALUES ('2005-05-03','c','c'),('1900-01-01','d','d');
+CREATE TABLE t3 ( f3 varchar(1)) ;
+INSERT INTO t3 VALUES ('c');
+set @old_optimizer_switch = @@optimizer_switch;
+set @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
+EXPLAIN SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+f4
+set @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
+EXPLAIN SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+f4
+set @@optimizer_switch = @old_optimizer_switch;
+drop table t1, t2, t3;
 set optimizer_switch=@subselect4_tmp;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 1cc3a7314a3..3af8528a105 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1494,4 +1494,175 @@ WHERE t3.f1 = (
 
 drop table t1, t2, t3, t4;
 
+--echo #
+--echo # LP BUG#611690 Crash in select_describe() with nested subqueries
+--echo #
+
+CREATE TABLE t1 (
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_varchar_nokey varchar(1) DEFAULT NULL,
+  KEY col_int_key (col_int_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (8,'v','v');
+INSERT INTO t1 VALUES (9,'r','r');
+
+CREATE TABLE t2 (
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_varchar_nokey varchar(1) DEFAULT NULL,
+  KEY col_int_key (col_int_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (2,'w','w');
+INSERT INTO t2 VALUES (9,'m','m');
+
+set @old_optimizer_switch = @@optimizer_switch;
+
+set @@optimizer_switch='subquery_cache=off,materialization=on,in_to_exists=off,semijoin=off';
+EXPLAIN
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+       FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+               ON SUBQUERY2_t2.col_varchar_key
+       WHERE SUBQUERY2_t2.col_varchar_nokey IN
+             (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+       FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+               ON SUBQUERY2_t2.col_varchar_key
+       WHERE SUBQUERY2_t2.col_varchar_nokey IN
+             (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+
+set @@optimizer_switch='subquery_cache=off,materialization=off,in_to_exists=on,semijoin=off';
+EXPLAIN
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+       FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+               ON SUBQUERY2_t2.col_varchar_key
+       WHERE SUBQUERY2_t2.col_varchar_nokey IN
+             (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+SELECT col_int_key
+FROM t2
+WHERE (SELECT SUBQUERY2_t1.col_int_key
+       FROM t1 SUBQUERY2_t1 STRAIGHT_JOIN t1 SUBQUERY2_t2
+               ON SUBQUERY2_t2.col_varchar_key
+       WHERE SUBQUERY2_t2.col_varchar_nokey IN
+             (SELECT col_varchar_nokey FROM t1 GROUP BY col_varchar_nokey));
+
+drop table t1, t2;
+
+set @@optimizer_switch = @old_optimizer_switch;
+
+
+--echo #
+--echo # LP BUG#612543 Crash in Item_field::used_tables() with view + subquery + prepared statements
+--echo #
+
+CREATE TABLE t1 ( f1 int(11), f2 varchar(1));
+CREATE TABLE t2 ( f3 varchar(1));
+insert into t1 values (2,'x'), (5,'y');
+insert into t2 values ('x'), ('z');
+CREATE VIEW v2 AS SELECT * FROM t2;
+
+set @old_optimizer_switch = @@optimizer_switch;
+
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 );
+PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
+EXECUTE st1;
+EXECUTE st1;
+
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 );
+PREPARE st2 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
+EXECUTE st2;
+EXECUTE st2;
+
+set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off,subquery_cache=off';
+EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 );
+PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
+EXECUTE st3;
+EXECUTE st3;
+
+set @@optimizer_switch = @old_optimizer_switch;
+
+drop table t1, t2;
+drop view v2;
+
+
+--echo #
+--echo # LP BUG#611396 RQG: crash in Item_field::register_field_in_read_map with semijoin=off
+--echo # and prepared statements and materialization
+
+CREATE TABLE t1 ( f1 int(11), f2 int(11)) ;
+CREATE TABLE t2 ( f1 int(11), f4 varchar(1), PRIMARY KEY (f1)) ;
+INSERT INTO t2 VALUES ('23','j'),('24','e');
+CREATE TABLE t3 ( f1 int(11), f4 varchar(1)) ;
+INSERT INTO t3 VALUES ('8','j');
+
+set @old_optimizer_switch = @@optimizer_switch;
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
+
+EXPLAIN
+SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
+FROM t2 JOIN t3 ON t3.f4 = t2.f4
+WHERE t3.f1 = 8
+GROUP BY 1, 2;
+
+PREPARE st1 FROM "
+SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
+FROM t2 JOIN t3 ON t3.f4 = t2.f4
+WHERE t3.f1 = 8
+GROUP BY 1, 2";
+
+EXECUTE st1;
+EXECUTE st1;
+
+set @@optimizer_switch = @old_optimizer_switch;
+
+drop table t1, t2, t3;
+
+
+--echo #
+--echo # LP BUG#611382 RQG: Query returns extra rows when executed with materialization=on
+--echo #
+
+CREATE TABLE t1 ( f4 varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (NULL);
+CREATE TABLE t2 ( f2 date, f3 varchar(1), f4 varchar(1)) ;
+INSERT INTO t2 VALUES ('2005-05-03','c','c'),('1900-01-01','d','d');
+CREATE TABLE t3 ( f3 varchar(1)) ;
+INSERT INTO t3 VALUES ('c');
+
+set @old_optimizer_switch = @@optimizer_switch;
+
+set @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
+
+EXPLAIN SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+
+SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+
+set @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
+
+EXPLAIN SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+
+SELECT t1.f4
+FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
+WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;
+
+set @@optimizer_switch = @old_optimizer_switch;
+
+drop table t1, t2, t3;
+
 set optimizer_switch=@subselect4_tmp;