List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:October 22 2010 1:33pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (tor.didriksen:3331)
View as plain text  
#At file:///export/home/didrik/repo/next-mr-bf-merge-from-opt-team/ based on revid:anitha.gopi@stripped

 3331 Tor Didriksen	2010-10-22 [merge]
      Automerge from next-mr-opt-team.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/optimizer_switch.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      mysql-test/t/optimizer_switch.test
      sql/item.cc
      sql/item.h
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
      sql/item_func.cc
      sql/item_func.h
      sql/item_row.cc
      sql/item_row.h
      sql/item_subselect.cc
      sql/item_subselect.h
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2010-09-20 14:06:02 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-10-13 13:27:36 +0000
@@ -3326,3 +3326,48 @@ eval explain $query;
 eval $query;
 
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # BUG#52329 - Wrong result: subquery materialization, IN, 
+--echo #             non-null field followed by nullable
+--echo #
+
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+                       ('1 - 11', '2 - 21'),
+                       ('1 - 12', '2 - 22'),
+                       ('1 - 12', '2 - 22'),
+                       ('1 - 13', '2 - 23');
+
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2a WHERE b1 > '0');
+
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2b WHERE b1 > '0');
+
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2c WHERE b1 > '0');
+
+
+DROP TABLE t1,t2a,t2b,t2c;
+
+--echo # End BUG#52329

=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result	2010-08-19 07:10:58 +0000
+++ b/mysql-test/r/optimizer_switch.result	2010-10-15 10:32:50 +0000
@@ -204,3 +204,345 @@ SET optimizer_switch="default";
 call run_n_times(1);
 DROP PROCEDURE run_n_times;
 DROP TABLE it, ot;
+# 
+# BUG#31480: Incorrect result for nested subquery when executed via semijoin
+# 
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL);
+CREATE TABLE t3 (e INT NOT NULL);
+CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL);
+INSERT INTO t1 VALUES (1,10);
+INSERT INTO t1 VALUES (2,10);
+INSERT INTO t1 VALUES (1,20);
+INSERT INTO t1 VALUES (2,20);
+INSERT INTO t1 VALUES (3,20);
+INSERT INTO t1 VALUES (2,30);
+INSERT INTO t1 VALUES (4,40);
+INSERT INTO t2 VALUES (2,10);
+INSERT INTO t2 VALUES (2,20);
+INSERT INTO t2 VALUES (4,10);
+INSERT INTO t2 VALUES (5,10);
+INSERT INTO t2 VALUES (3,20);
+INSERT INTO t2 VALUES (2,40);
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (30);
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (20);
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (3,10);
+INSERT INTO t4 VALUES (4,10);
+INSERT INTO t4 VALUES (4,20);
+INSERT INTO t4 VALUES (4,20);
+# Reference to the parent query block (used tables was wrong)
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+# Subquery with GROUP BY and HAVING
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+a	b
+2	10
+2	20
+3	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+# The query result with semijoin is WRONG
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+a	b
+# Subquery with ORDER BY and LIMIT
+set @@optimizer_switch='materialization=off,semijoin=off';
+# NOTE: The ordered subquery should have a LIMIT clause to make sense
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+a	b
+2	10
+2	20
+2	30
+4	40
+set @@optimizer_switch='materialization=off,semijoin=on';
+# The query result with semijoin is WRONG
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+a	b
+2	10
+2	20
+2	30
+4	40
+# Reference to the transformed-away query block (dependency was wrong)
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+4	40
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+4	40
+# Reference above the parent query block (should not be affected)
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+3	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+a	b
+1	10
+1	20
+4	40
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+2	DEPENDENT SUBQUERY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+a	b
+1	10
+1	20
+4	40
+# EXISTS with reference to the parent query block
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+# Scalar subquery with reference to the parent query block
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+# Combine scalar subquery with quantified comparison subquery
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	20
+2	30
+DROP TABLE t1, t2, t3, t4;
+set @@optimizer_switch='default';
+# End of BUG#31480

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-10-15 10:32:50 +0000
@@ -3784,8 +3784,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5148,4 +5148,42 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5152,6 +5152,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5152,6 +5152,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-10-15 10:32:50 +0000
@@ -3783,8 +3783,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5146,4 +5146,42 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3787,8 +3787,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5150,6 +5150,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3787,8 +3787,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5150,6 +5150,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-10-15 10:32:50 +0000
@@ -3784,8 +3784,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5148,6 +5148,44 @@ a
 1
 DROP TABLE t1,t2,t3;
 #
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5152,6 +5152,44 @@ a
 1
 DROP TABLE t1,t2,t3;
 #
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5152,6 +5152,44 @@ a
 1
 DROP TABLE t1,t2,t3;
 #
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5151,4 +5151,42 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3792,8 +3792,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5155,6 +5155,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3792,8 +3792,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5155,6 +5155,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-10-15 10:32:50 +0000
@@ -3794,8 +3794,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5158,4 +5158,42 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3798,8 +3798,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5162,6 +5162,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3798,8 +3798,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 
@@ -5162,6 +5162,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-10-13 13:27:36 +0000
@@ -5369,4 +5369,42 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-10-13 13:27:36 +0000
@@ -5295,4 +5295,42 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-10-13 13:27:36 +0000
@@ -5299,6 +5299,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-10-13 13:27:36 +0000
@@ -5299,6 +5299,44 @@ a
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#52329 - Wrong result: subquery materialization, IN, 
+#             non-null field followed by nullable
+#
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+('1 - 11', '2 - 21'),
+('1 - 12', '2 - 22'),
+('1 - 12', '2 - 22'),
+('1 - 13', '2 - 23');
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2a WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2c WHERE b1 > '0');
+a1	a2
+1 - 12	2 - 22
+DROP TABLE t1,t2a,t2b,t2c;
+# End BUG#52329
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/t/optimizer_switch.test'
--- a/mysql-test/t/optimizer_switch.test	2010-08-19 07:10:58 +0000
+++ b/mysql-test/t/optimizer_switch.test	2010-10-15 10:32:50 +0000
@@ -209,3 +209,199 @@ DROP PROCEDURE run_n_times;
 DROP TABLE it, ot;
 
 # End of Bug#50489
+
+--echo # 
+--echo # BUG#31480: Incorrect result for nested subquery when executed via semijoin
+--echo # 
+
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL);
+CREATE TABLE t3 (e INT NOT NULL);
+CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL);
+
+INSERT INTO t1 VALUES (1,10);
+INSERT INTO t1 VALUES (2,10);
+INSERT INTO t1 VALUES (1,20);
+INSERT INTO t1 VALUES (2,20);
+INSERT INTO t1 VALUES (3,20);
+INSERT INTO t1 VALUES (2,30);
+INSERT INTO t1 VALUES (4,40);
+
+INSERT INTO t2 VALUES (2,10);
+INSERT INTO t2 VALUES (2,20);
+INSERT INTO t2 VALUES (4,10);
+INSERT INTO t2 VALUES (5,10);
+INSERT INTO t2 VALUES (3,20);
+INSERT INTO t2 VALUES (2,40);
+
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (30);
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (20);
+
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (3,10);
+INSERT INTO t4 VALUES (4,10);
+INSERT INTO t4 VALUES (4,20);
+INSERT INTO t4 VALUES (4,20);
+
+--echo # Reference to the parent query block (used tables was wrong)
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+                                  WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Subquery with GROUP BY and HAVING
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+                                  GROUP BY f
+                                  HAVING ta.a=tc.f));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+--echo # The query result with semijoin is WRONG
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Subquery with ORDER BY and LIMIT
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+--echo # NOTE: The ordered subquery should have a LIMIT clause to make sense
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d IN (SELECT g FROM t4 as tc
+                                  WHERE ta.a=tc.f
+                                  ORDER BY tc.f));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+--echo # The query result with semijoin is WRONG
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Reference to the transformed-away query block (dependency was wrong)
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+                                  WHERE tb.d=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Reference above the parent query block (should not be affected)
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+                  WHERE ta.a IN (SELECT c FROM t2 AS tb
+                                 WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+                                                    WHERE t.b=tc.e)));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # EXISTS with reference to the parent query block
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE EXISTS (SELECT * FROM t3 as tc
+                             WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Scalar subquery with reference to the parent query block
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+                             WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Combine scalar subquery with quantified comparison subquery
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE (SELECT MIN(e) FROM t3 as tc
+                      WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+                                              WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2, t3, t4;
+
+set @@optimizer_switch='default';
+
+--echo # End of BUG#31480

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-09-28 15:17:29 +0000
+++ b/sql/item.cc	2010-10-15 10:32:50 +0000
@@ -2314,21 +2314,75 @@ table_map Item_field::used_tables() cons
 }
 
 
-void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+table_map Item_field::resolved_used_tables() const
 {
-  if (new_parent == depended_from)
-    depended_from= NULL;
-  Name_resolution_context *ctx= new Name_resolution_context();
-  ctx->outer_context= NULL; // We don't build a complete name resolver
-  ctx->table_list= NULL;    // We rely on first_name_resolution_table instead
-  ctx->select_lex= new_parent;
-  ctx->first_name_resolution_table= context->first_name_resolution_table;
-  ctx->last_name_resolution_table=  context->last_name_resolution_table;
-  ctx->error_processor=             context->error_processor;
-  ctx->error_processor_data=        context->error_processor_data;
-  ctx->resolve_in_select_list=      context->resolve_in_select_list;
-  ctx->security_ctx=                context->security_ctx;
-  this->context=ctx;
+  if (field->table->const_table)
+    return 0;					// const item
+  return field->table->map;
+}
+
+void Item_field::fix_after_pullout(st_select_lex *parent_select,
+                                   st_select_lex *removed_select,
+                                   Item **ref)
+{
+  if (context->select_lex == removed_select ||
+      context->select_lex == parent_select)
+  {
+    if (parent_select == depended_from)
+      depended_from= NULL;
+    Name_resolution_context *ctx= new Name_resolution_context();
+    ctx->outer_context= NULL; // We don't build a complete name resolver
+    ctx->table_list= NULL;    // We rely on first_name_resolution_table instead
+    ctx->select_lex= parent_select;
+    ctx->first_name_resolution_table= context->first_name_resolution_table;
+    ctx->last_name_resolution_table=  context->last_name_resolution_table;
+    ctx->error_processor=             context->error_processor;
+    ctx->error_processor_data=        context->error_processor_data;
+    ctx->resolve_in_select_list=      context->resolve_in_select_list;
+    ctx->security_ctx=                context->security_ctx;
+    this->context=ctx;
+  }
+  else
+  {
+    /*
+      The definition scope of this field item reference is inner to the removed
+      select_lex object.
+      No new resolution is needed, but we may need to update the dependency.
+    */
+    if (removed_select == depended_from)
+      depended_from= parent_select;
+  }
+
+  if (depended_from)
+  {
+    /*
+      Refresh used_tables information for subqueries between the definition
+      scope and resolution scope of the field item reference.
+    */
+    st_select_lex *child_select= context->select_lex;
+
+    if (child_select->outer_select() != depended_from)
+    {
+      /*
+        The subquery on this level is outer-correlated with respect to the field
+      */
+      Item_subselect *subq_predicate= child_select->master_unit()->item;
+      subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
+    }
+
+    while (child_select->outer_select() != depended_from)
+      child_select= child_select->outer_select();
+
+    /*
+      child_select is select_lex immediately inner to the depended_from level.
+      Now, locate the subquery predicate that contains this select_lex and
+      update used tables information.
+    */
+    Item_subselect *subq_predicate= child_select->master_unit()->item;
+
+    subq_predicate->used_tables_cache|= this->resolved_used_tables();
+    subq_predicate->const_item_cache&= this->const_item();
+  }
 }
 
 
@@ -6804,26 +6858,31 @@ bool Item_outer_ref::fix_fields(THD *thd
   return err;
 }
 
-void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_outer_ref::fix_after_pullout(st_select_lex *parent_select,
+                                       st_select_lex *removed_select,
+                                       Item **ref_arg)
 {
-  if (depended_from == new_parent)
+  if (depended_from == parent_select)
   {
-    *ref= outer_ref;
-    outer_ref->fix_after_pullout(new_parent, ref);
+    *ref_arg= outer_ref;
+    outer_ref->fix_after_pullout(parent_select, removed_select, ref_arg);
   }
   // @todo: Find an actual test case for this funcion.
   DBUG_ASSERT(false);
 }
 
-void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+void Item_ref::fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref_arg)
 {
   // @todo: Find an actual test case where depended_from == new_parent.
-  DBUG_ASSERT(depended_from != new_parent);
-  if (depended_from == new_parent)
+  DBUG_ASSERT(depended_from != parent_select);
+  if (depended_from == parent_select)
     depended_from= NULL;
 }
 
-void Item_direct_view_ref::fix_after_pullout(st_select_lex *new_parent,
+void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select,
+                                             st_select_lex *removed_select,
                                              Item **refptr)
 {
   DBUG_EXECUTE("where",
@@ -6831,11 +6890,11 @@ void Item_direct_view_ref::fix_after_pul
                            "Item_direct_view_ref::fix_after_pullout",
                            QT_ORDINARY););
 
-  (*ref)->fix_after_pullout(new_parent, ref);
+  (*ref)->fix_after_pullout(parent_select, removed_select, ref);
 
-  // @todo: Find an actual test case where depended_from == new_parent.
-  DBUG_ASSERT(depended_from != new_parent);
-  if (depended_from == new_parent)
+  // @todo: Find an actual test case where depended_from == parent_select.
+  DBUG_ASSERT(depended_from != parent_select);
+  if (depended_from == parent_select)
     depended_from= NULL;
 }
 

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-09-07 19:07:18 +0000
+++ b/sql/item.h	2010-10-15 10:32:50 +0000
@@ -592,12 +592,20 @@ public:
   virtual void make_field(Send_field *field);
   Field *make_string_field(TABLE *table);
   virtual bool fix_fields(THD *, Item **);
-  /*
-    Fix after some tables has been pulled out. Basically re-calculate all
-    attributes that are dependent on the tables.
-  */
-  virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {};
-
+  /**
+    Fix after tables have been moved from one select_lex level to the parent
+    level, e.g by semijoin conversion.
+    Basically re-calculate all attributes dependent on the tables.
+
+    @param parent_select  select_lex that tables are moved to.
+    @param removed_select select_lex that tables are moved away from,
+                          child of parent_select.
+    @param ref            updated with new ref whenever the function substitutes
+                          this item with another.
+  */
+  virtual void fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref) {};
   /*
     should be used in case where we are sure that we do not need
     complete fix_fields() procedure.
@@ -1753,11 +1761,16 @@ public:
   bool send(Protocol *protocol, String *str_arg);
   void reset_field(Field *f);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   void make_field(Send_field *tmp_field);
   int save_in_field(Field *field,bool no_conversions);
   void save_org_in_field(Field *field);
   table_map used_tables() const;
+  /*
+    Return used table information for the level on which this table is resolved.
+  */
+  table_map resolved_used_tables() const;
   enum Item_result result_type () const
   {
     return field->result_type();
@@ -2573,7 +2586,8 @@ public:
   bool send(Protocol *prot, String *tmp);
   void make_field(Send_field *field);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   int save_in_field(Field *field, bool no_conversions);
   void save_org_in_field(Field *field);
   enum Item_result result_type () const { return (*ref)->result_type(); }
@@ -2712,7 +2726,8 @@ public:
   {}
 
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   bool eq(const Item *item, bool binary_cmp) const;
   Item *get_tmp_table_item(THD *thd)
   {
@@ -2769,7 +2784,8 @@ public:
     outer_ref->save_org_in_field(result_field);
   }
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   table_map used_tables() const
   {
     return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-09-28 15:17:29 +0000
+++ b/sql/item_cmpfunc.cc	2010-10-15 10:32:50 +0000
@@ -1770,6 +1770,27 @@ bool Item_in_optimizer::fix_fields(THD *
 }
 
 
+void Item_in_optimizer::fix_after_pullout(st_select_lex *parent_select,
+                                          st_select_lex *removed_select,
+                                          Item **ref)
+{
+  used_tables_cache=0;
+  not_null_tables_cache= 0;
+  const_item_cache= 1;
+
+  /*
+    No need to call fix_after_pullout() on args[0] (ie left expression),
+    as Item_in_subselect::fix_after_pullout() will do this.
+    So, just forward the call to the Item_in_subselect object.
+  */
+
+  args[1]->fix_after_pullout(parent_select, removed_select, &args[1]);
+
+  used_tables_cache|= args[1]->used_tables();
+  not_null_tables_cache|= args[1]->not_null_tables();
+  const_item_cache&= args[1]->const_item();
+}
+
 /**
    The implementation of optimized \<outer expression\> [NOT] IN \<subquery\>
    predicates. The implementation works as follows.
@@ -1840,6 +1861,7 @@ bool Item_in_optimizer::fix_fields(THD *
      @see Item_in_subselect::val_bool()
      @see Item_is_not_null_test::val_int()
  */
+
 longlong Item_in_optimizer::val_int()
 {
   bool tmp;
@@ -4337,7 +4359,9 @@ Item_cond::fix_fields(THD *thd, Item **r
 }
 
 
-void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_cond::fix_after_pullout(st_select_lex *parent_select,
+                                  st_select_lex *removed_select,
+                                  Item **ref)
 {
   List_iterator<Item> li(list);
   Item *item;
@@ -4351,7 +4375,7 @@ void Item_cond::fix_after_pullout(st_sel
   while ((item=li++))
   {
     table_map tmp_table_map;
-    item->fix_after_pullout(new_parent, li.ref());
+    item->fix_after_pullout(parent_select, removed_select, li.ref());
     item= *li.ref();
     used_tables_cache|= item->used_tables();
     const_item_cache&= item->const_item();

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2010-08-12 00:26:10 +0000
+++ b/sql/item_cmpfunc.h	2010-10-15 10:32:50 +0000
@@ -274,6 +274,8 @@ public:
   { with_subselect= TRUE; }
   bool fix_fields(THD *, Item **);
   bool fix_left(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   bool is_null();
   longlong val_int();
   void cleanup();
@@ -1513,7 +1515,8 @@ public:
     list.prepand(nlist);
   }
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
 
   enum Type type() const { return COND_ITEM; }
   List<Item>* argument_list() { return &list; }

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2010-09-06 11:10:01 +0000
+++ b/sql/item_func.cc	2010-10-22 13:33:24 +0000
@@ -226,7 +226,9 @@ Item_func::fix_fields(THD *thd, Item **r
 }
 
 
-void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func::fix_after_pullout(st_select_lex *parent_select,
+                                  st_select_lex *removed_select,
+                                  Item **ref)
 {
   Item **arg,**arg_end;
 
@@ -237,7 +239,7 @@ void Item_func::fix_after_pullout(st_sel
   {
     for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
     {
-      (*arg)->fix_after_pullout(new_parent, arg);
+      (*arg)->fix_after_pullout(parent_select, removed_select, arg);
       Item *item= *arg;
 
       used_tables_cache|=     item->used_tables();

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2010-07-13 17:29:44 +0000
+++ b/sql/item_func.h	2010-10-15 10:32:50 +0000
@@ -120,7 +120,8 @@ public:
   // Constructor used for Item_cond_and/or (see Item comment)
   Item_func(THD *thd, Item_func *item);
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   table_map used_tables() const;
   table_map not_null_tables() const;
   void update_used_tables();

=== modified file 'sql/item_row.cc'
--- a/sql/item_row.cc	2010-07-13 17:29:44 +0000
+++ b/sql/item_row.cc	2010-10-15 10:32:50 +0000
@@ -138,13 +138,15 @@ void Item_row::update_used_tables()
   }
 }
 
-void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_row::fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref)
 {
   used_tables_cache= 0;
   const_item_cache= 1;
   for (uint i= 0; i < arg_count; i++)
   {
-    items[i]->fix_after_pullout(new_parent, &items[i]);
+    items[i]->fix_after_pullout(parent_select, removed_select, &items[i]);
     used_tables_cache|= items[i]->used_tables();
     const_item_cache&= items[i]->const_item();
   }

=== modified file 'sql/item_row.h'
--- a/sql/item_row.h	2010-07-13 17:29:44 +0000
+++ b/sql/item_row.h	2010-10-15 10:32:50 +0000
@@ -63,7 +63,8 @@ public:
     return 0;
   };
   bool fix_fields(THD *thd, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   void cleanup();
   void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields);
   table_map used_tables() const { return used_tables_cache; };

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-09-28 15:17:29 +0000
+++ b/sql/item_subselect.cc	2010-10-15 10:32:50 +0000
@@ -319,6 +319,69 @@ bool Item_subselect::exec()
 }
 
 
+/**
+  Fix used tables information for a subquery after query transformations.
+  Common actions for all predicates involving subqueries.
+  Most actions here involve re-resolving information for conditions
+  and items belonging to the subquery.
+  Notice that the usage information from underlying expressions is not
+  propagated to the subquery predicate, as it belongs to inner layers
+  of the query operator structure.
+  However, when underlying expressions contain outer references into
+  a select_lex on this level, the relevant information must be updated
+  when these expressions are resolved.
+*/
+
+void Item_subselect::fix_after_pullout(st_select_lex *parent_select,
+                                       st_select_lex *removed_select,
+                                       Item **ref)
+
+{
+  /* Clear usage information for this subquery predicate object */
+  used_tables_cache= 0;
+  const_item_cache= 1;
+
+  /*
+    Go through all query specification objects of the subquery and re-resolve
+    all relevant expressions belonging to them.
+  */
+  for (SELECT_LEX *sel= unit->first_select(); sel; sel= sel->next_select())
+  {
+    if (sel->where)
+      sel->where->fix_after_pullout(parent_select, removed_select,
+                                    &sel->where);
+
+    if (sel->having)
+      sel->having->fix_after_pullout(parent_select, removed_select,
+                                     &sel->having);
+
+    List_iterator<Item> li(sel->item_list);
+    Item *item;
+    while ((item=li++))
+      item->fix_after_pullout(parent_select, removed_select, li.ref());
+
+    /*
+      No need to call fix_after_pullout() for outer-join conditions, as these
+      cannot have outer references.
+    */
+
+    /* Re-resolve ORDER BY and GROUP BY fields */
+
+    for (ORDER *order= (ORDER*) sel->order_list.first;
+         order;
+         order= order->next)
+      (*order->item)->fix_after_pullout(parent_select, removed_select,
+                                        order->item);
+
+    for (ORDER *group= (ORDER*) sel->group_list.first;
+         group;
+         group= group->next)
+      (*group->item)->fix_after_pullout(parent_select, removed_select,
+                                        group->item);
+  }
+}
+
+
 /*
   Compute the IN predicate if the left operand's cache changed.
 */
@@ -1820,6 +1883,19 @@ bool Item_in_subselect::fix_fields(THD *
 }
 
 
+void Item_in_subselect::fix_after_pullout(st_select_lex *parent_select,
+                                          st_select_lex *removed_select,
+                                          Item **ref)
+{
+  Item_subselect::fix_after_pullout(parent_select, removed_select, ref);
+
+  left_expr->fix_after_pullout(parent_select, removed_select, &left_expr);
+
+  used_tables_cache|= left_expr->used_tables();
+  const_item_cache&= left_expr->const_item();
+}
+
+
 /**
   Try to create an engine to compute the subselect via materialization,
   and if this fails, revert to execution via the IN=>EXISTS transformation.
@@ -3214,7 +3290,7 @@ bool subselect_hash_sj_engine::init_perm
                                     use that information instead.
                                  */
                                  cur_ref_buff + null_count,
-                                 null_count ? tab->ref.key_buff : 0,
+                                 null_count ? cur_ref_buff : 0,
                                  cur_key_part->length, tab->ref.items[i]);
     cur_ref_buff+= cur_key_part->store_length;
   }

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2010-07-26 11:34:07 +0000
+++ b/sql/item_subselect.h	2010-10-15 10:32:50 +0000
@@ -124,6 +124,8 @@ public:
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   virtual bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;
@@ -170,6 +172,9 @@ public:
   friend bool Item_field::fix_fields(THD *, Item **);
   friend int  Item_field::fix_outer_field(THD *, Field **, Item **);
   friend bool Item_ref::fix_fields(THD *, Item **);
+  friend void Item_field::fix_after_pullout(st_select_lex *parent_select,
+                                            st_select_lex *removed_select,
+                                            Item **ref);
   friend void mark_select_range_as_dependent(THD*,
                                              st_select_lex*, st_select_lex*,
                                              Field*, Item*, Item_ident*);
@@ -403,6 +408,8 @@ public:
   bool test_limit(st_select_lex_unit *unit);
   virtual void print(String *str, enum_query_type query_type);
   bool fix_fields(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   bool setup_engine();
   bool init_left_expr_cache();
   bool is_expensive_processor(uchar *arg);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-30 14:53:11 +0000
+++ b/sql/sql_select.cc	2010-10-21 15:44:35 +0000
@@ -3548,16 +3548,20 @@ static TABLE_LIST *alloc_join_nest(THD *
 }
 
 
-void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
+void fix_list_after_tbl_changes(st_select_lex *parent_select,
+                                st_select_lex *removed_select,
+                                List<TABLE_LIST> *tlist)
 {
   List_iterator<TABLE_LIST> it(*tlist);
   TABLE_LIST *table;
   while ((table= it++))
   {
     if (table->on_expr)
-      table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
+      table->on_expr->fix_after_pullout(parent_select, removed_select,
+                                        &table->on_expr);
     if (table->nested_join)
-      fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
+      fix_list_after_tbl_changes(parent_select, removed_select,
+                                 &table->nested_join->join_list);
   }
 }
 
@@ -3870,15 +3874,16 @@ bool convert_subquery_to_semijoin(JOIN *
     sj_nest->sj_on_expr->fix_fields(thd, &sj_nest->sj_on_expr);
   }
 
+  /* Unlink the child select_lex: */
+  subq_lex->master_unit()->exclude_level();
   /*
     Walk through sj nest's WHERE and ON expressions and call
     item->fix_table_changes() for all items.
   */
-  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
-  fix_list_after_tbl_changes(parent_lex, &nested_join->join_list);
-
-  /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
-  subq_lex->master_unit()->exclude_level();
+  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, subq_lex,
+                                         &sj_nest->sj_on_expr);
+  fix_list_after_tbl_changes(parent_lex, subq_lex,
+                             &sj_nest->nested_join->join_list);
 
   //TODO fix QT_
   DBUG_EXECUTE("where",
@@ -7627,6 +7632,12 @@ optimize_straight_join(JOIN *join, table
  
   for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
   {
+    /*
+      Dependency computation (make_join_statistics()) and proper ordering
+      based on them (join_tab_cmp*) guarantee that this order is compatible
+      with execution, check it:
+    */
+    DBUG_ASSERT(!check_interleaving_with_nj(s));
     /* Find the best access method from 's' to the current partial plan */
     best_access_path(join, s, join_tables, idx, FALSE, record_count,
                      join->positions + idx, &loose_scan_pos);
@@ -10722,7 +10733,7 @@ bool setup_sj_materialization(JOIN_TAB *
                                       use that information instead.
                                    */
                                    cur_ref_buff + null_count,
-                                   null_count ? tab_ref->key_buff : 0,
+                                   null_count ? cur_ref_buff : 0,
                                    cur_key_part->length, tab_ref->items[i]);
       cur_ref_buff+= cur_key_part->store_length;
     }

No bundle (reason: revision is a merge).
Thread
bzr commit into mysql-next-mr-bugfixing branch (tor.didriksen:3331) Tor Didriksen22 Oct