#At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-52329/ based on revid:tor.didriksen@stripped
3262 Jorgen Loland 2010-10-13
Bug#52329: Wrong result: subquery materialization, IN, non-null
field followed by nullable
Consider a query
SELECT * FROM t1
WHERE (a1, a2) IN (
SELECT b1, b2 FROM t2 ...)
When solved using materialized subselect, we check if a record
from t1 is part of the result set by constructing a key from the
a1 and a2 values and perform a lookup based on that key. If a1
and a2 are CHAR(3) with values foo and bar, the KEY is "foobar".
If a2 is NULLable, the null-bit should be the first bit for this
field's part of the KEY ("foo<nullbit_a2>bar").
Before, the null-bit pointer for each key field wrongly pointed
to the first bit of the KEY instead of the first bit of the key
part. Thus, when setting the null bit for a2 above, the first bit
of a1 was wrongly updated: "0oo bar" ('f' in foo replaced
with null-bit of a2, and the null-bit of a2 not set)
This patch sets the null-pointer for each key part to the first
bit of that field's part of KEY, not the first bit of KEY.
The bug was masked if the first field was NOT NULL. The bug also
applied to semi-join materialization since the code has been
copied (also fixed by this patch).
@ mysql-test/include/subquery_sj.inc
Add test for BUG52329
@ mysql-test/r/subquery_sj_all.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_all_jcl6.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_all_jcl7.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_dupsweed.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_dupsweed_jcl6.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_dupsweed_jcl7.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_firstmatch.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_firstmatch_jcl6.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_firstmatch_jcl7.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_loosescan.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_loosescan_jcl6.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_loosescan_jcl7.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_mat.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_mat_jcl6.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_mat_jcl7.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_mat_nosj.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_none.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_none_jcl6.result
Add test for BUG52329
@ mysql-test/r/subquery_sj_none_jcl7.result
Add test for BUG52329
@ sql/item_subselect.cc
subselect_hash_sj_engine::init_permanent():
Make the null-pointer for each key part point to the first
bit of that field's part of KEY, not the first bit of KEY.
@ sql/sql_select.cc
setup_sj_materialization():
Make the null-pointer for each key part point to the first
bit of that field's part of KEY, not the first bit of KEY.
modified:
mysql-test/include/subquery_sj.inc
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
sql/item_subselect.cc
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 11:38:36 +0000
@@ -3326,3 +3326,46 @@ 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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+ SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+
+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 t2c WHERE b1 > '0');
+
+
+DROP TABLE t1,t2a,t2b,t2c;
+
+--echo # End BUG#52329
=== 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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-13 11:38:36 +0000
@@ -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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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 11:38: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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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 11:38: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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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 11:38: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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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 11:38: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 t2a WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+SELECT b1, b2 FROM t2b WHERE b1 > '0' GROUP BY b1, b2);
+a1 a2
+1 - 12 2 - 22
+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 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 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2010-09-28 15:17:29 +0000
+++ b/sql/item_subselect.cc 2010-10-13 11:38:36 +0000
@@ -3214,7 +3214,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/sql_select.cc'
--- a/sql/sql_select.cc 2010-09-30 14:53:11 +0000
+++ b/sql/sql_select.cc 2010-10-13 11:38:36 +0000
@@ -10722,7 +10722,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;
}
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101013113836-crgb6dner06b1qz3.bundle
