#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 Didriksen | 22 Oct |
