From: Jorgen Loland
Date: October 13 2010 11:38am
Subject: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3262) Bug#52329
List-Archive: http://lists.mysql.com/commits/120676
X-Bug: 52329
Message-Id: <20101013113842.A69719B0@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============2029363788335986776=="

--===============2029363788335986776==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#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;
     }


--===============2029363788335986776==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   crgb6dner06b1qz3
# target_branch: file:///export/home/jl208045/mysql/mysql-next-mr-opt-\
#   backporting-52329/
# testament_sha1: 98fd711f3df5d79b105e34b4dec1efb3644f08fb
# timestamp: 2010-10-13 13:38:42 +0200
# base_revision_id: tor.didriksen@stripped\
#   gwydzbgti73pxxcg
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXTnSv4AJnl/gFCwCALY9///
///foL////BgGX73zX052kJPt3DV6F2OBVKEgUSqhEAoRWw0B2YSokiJCoSAAKSAVR1JlQDjI0yY
mgyZMJpkDIaA0Bpk0MAJoDFU9qag8KA0BoDQaDagBoMgAAAAZTJk1UAD0mmh6gAAAAAAAANAk0qR
T9Jk1NNGgBp5QAAAAAAAABFIgAmiYBMQYiGmpiptR6j1ND1A9TIB6n6iBUogAIamRGIJoARqbTJk
aCYaRppptQ9T1P0tTYChsBQkCeG6Oe3ROzGS106jlwtvMISBgcs4qOQ0sEYGB8zNtYmq/DDO6muv
X7sJ6swajr0LnObB4SF0hwFk75DAkLvC++vIkMmVOqdqSK8LN62U60VcVVUsyrZ33WtJCMlFgwUI
koRChEKht6k0moUIL3RIWMoIpaSHSzp+EuglaQn4Y63kf1tKuBZhkqgVQKCaBIKqHWixd24uheEk
r62gCK/diDKPAiid/dfHiEYiSfugj+v8hHy9SPsgjp/Yj3Ef2Iu++EkoQT1EBOaEHYJDEkOSSCLF
RRERUEERURURZvzhfQmO7zuVow3c+GLQ3naAJBQTwm2WUAgMp0QVgluadhEicSFbIJHl5DFbVRK6
KqZ1YUrSuDP+7tgEbqkewj/sJ+oK9qgVoBPxQEuRIASAV/EFfZATyRO8AT2tcw76gBO1SAkcfAEi
Q9pHMihJM3tI6EUvvrIVkKyQ9ZHs/x/nv5ynh3EWK1giva/X4+l93MI4gj4SSWwhL8/wpj5fPRPS
pakaBGpFr837XQxIxtpLoGmURn5XRGJT6cWNcHq5t97/p8obpY44Imv5Cmq+G4uhKkS5HNBGewUv
SlgopJxgaLMESxUnSXYyg3ol38EBNwJ/ER5VIsoRYRUklSKkVIqRUioi0jMkeUxwq432la6yy2b5
Xr3S7ji/KVqrEA0U4lyHMwwZJa7KVGUqCdgUZCiKSNVXVQh6MYwMD84IwkHrsUIH5wcIWUDnvFLL
Iwuw3lPdjZdryqwo7rc+vgnmO2ySbKE7uFgSD3JAUkBZARkPvEevYkmnR1USFEhZTOkhSQrIWCLC
SWEYhGYklBJKcFZCPupIaSFVEhUitZCvu8f78xFBFBFCKEWyCLP0d+sEUCNaCPnCIAT08Mij/frI
sZqEwSASFCaCzQW+Ek7loR9hFwknOIwI6wRjSBKgScAkKEgVq93R8Xu9agTiCcwTICSAbJFg339m
yr0beZnrNpujPSa0Z3daZxrOgspKcEOkfqOp/Z+7ranshAyHub32QQjAxIiNADgamYBbMLABqf6k
1smbn01y+DwfnRoBL+1iJym1vV6AmgfpaYIVgmekYbVC3r5+t3bP8DrnrNdImsE0wiOIYcyhAcAS
t72tsZk9kkWxCiYFSXEMWdkhEdEhEdKQk5m9BIdqXMAkySBuBlw0zCMYxjFVVVETa4Gz1l0EUiHX
AQQKyJyAnORvypql8ABKTkxRQ1vBLShQZIQKRK8JAq3tAi3NRKuABpBIRKqnzrKRkQSBJtL3lLWz
ZQZFZZgfGsl3n9XuVrZFsxXPFpY5O5pOCMqhL0t5KMHUJfpglZesyYlbQulJXK60iyjm4ZrekzcZ
SL6EZzRs51MWTh0arGTNpcub63NmT2zFu/KddCQsC6HYnogi3cjadOlxF/gI+UkKBHfYwYcgjMig
jEitSK14t1hx1EWiLoIwiQFYJmGi7P/DBVXGHZAA5kTuZNVFbY42zaSHi8ffyuEdtJYmmcz42smA
TX067aiDUDa4F90EBeZq6dOjRdhBvMOF+PCdOzC/LTbPumTozhF4TyE67XSlAlLSOBLSKySiiZKU
5L7329rUt1cWEVd/f1y75zwa0Mar16mxSCU6CRWb0PHE5glnSorUZra695wOBvaTeQnMSi1ylc/F
cR4YylbcUjs6syrJ4eFzSjvYPAT0pGbJu7lXCjdtt5cmLMncs5z2ytQnWdSQ9Mq5kh2JDjm9Yj+Z
IUCPZ2arlGsEZJFCSUnO3R21JJuRakBKwVi0EqdxCG6AVbwTaN5nL2x4bkrN5MBJplCVuQDiCcqg
ECbSUL1XOWul5FJrsBao5yq9s2aSFzZgtYTPKYMF928ulzsJnlbqdHKbdLZhoypauEbWVI2UeDyy
vvhOvYI6OkbOi0RYvVCNC10lgRyZLGK0jRmsaPdIUY6burbVe3UPXnLNer5ml2vc6Xr0jnSwjAj6
MpZLpvC9IxM7LZe2uyCW6uzm0uhOrwngwpFWCwwDEbK6Som4aSCdhXU77iiooh96TPqCfVXFb6Jg
9Dhw5+Ddu4kO6cXhOFO5ksYS9yUdlz2zkRgvd7J+UynITRoycmDpK8nVR2l53mjJ6N2TNynEcmUp
NpvKSHOWc7ts+RiQTgJGUW2XmDWA0ukEshTjCO4biQJS4tpooanUTIzUPLzxJtdZuodBvqGZrPN2
lBsJzVlHRWfBRg6S1ZgzYt1vZfKLFFrA76DEqrJOl0FzQYTM5OZFUaupiCVqm0QgvLnDNbktvZ3O
TRswUXsWTNiyWLZDaULi83FJB7tyh/Mm9Ho+WTr4CX2motpy0mUEBuOizFRiul+bsokcKJfRHx0l
7q5NF5ve/gI/URzrybqOV7FQzpKLZV5keDd0YC7hDmoo7pmsyLyqxi3p0XNfRa3mtjhM19zBq0Up
ctbPnkOhiWOaqyq92YKtVSi0pKqC84GRLi4FRbiofT7PV2P9G2/Zp2ZURGuirZsbmQA6WuasNdjn
IBKtG0l4zKBS4hRxtgkNmwqKi0rCrc3t5kbjr1wN53GY544mWhzEdDXeybrcnRy5atKNM9TtTlSS
MnHFpyWqMcgnd1c2y98k7L3C0JmtjWYCeU8ZymDWbd/JXm7m6qtbbsNvW6ylJJvfjKYmaYNHVwo3
YYRHosYbdubF2tazqzuwdmLd8jysbNZ3SGnio2bN3K5Vi67qcPZN1jwozYtMqNLXlXF8s8HekdM+
t2+bWm3bnYlnbrf3TYS/q7+Ci9guY9VywijVRRzXtKKVbMlzJpPQR4TrMZ7COk9Gchqs1y520sW2
387elfmBGU6zOZSy5ns6qKgmwwg23V6il+QDm0mjfiIMje0ay482NuS58W83vy1Y1aztRm7pVznP
VxLndhx8dzBa6tnRctcNHV6b16pRe7mbOcuUtpSQ7qEdeF9K8vKdpfpLaKUWTqzstNdjNd8t5oRy
Xqr3DNnMLtE1qZLlLhLlVxFi5L1WjJaqzZsC23nFryBIp52ussn55W5jSS33Z5SKTCpvsLyDi7iw
3nybTEmXFp4gOD9Db3l/JE7m+NcLhiCSEnKRVDHAUxzRrrlTSqSVNNEVsF1JbUUUtbFbRL0itnIi
t9skVvwrfhZCXBZRnlSsMu6A4QyUIBFkqEAofRU0crjujuhZAYjCPYJDckJYMYxjGKqqqoo8f90j
EI6fVV8hGJhBPX+8hPxEfhEk/gEeojdI4iUpClKU/wQmokl1l1kSSoRYiEpEfdBGQRQI/YQlAjGq
IT+iITOBFAj8Qn3QuFUZArAK/+Ir/JAEkKDeBFIIptWGUEfOQm6ITFCOQi2770QmL8UhLIIoCKIR
0COiSRP5QRQkmRJMCMokmcCNwj8UQn4kfvJJaJJtBGoin+aSFKSFKSFKSGoRjCSahGkEVgi8hNAi
4IrepyEcySWoRgSS0RdBFAilIjO4klB73+qfpfUrISuKCRkICQgJEpAyKmW6zZLymrTZC5VHmzpO
U6KRGpQZIP7j86rEoBT9CBhEnCshhQPyhXsnoCucPjIE91IDI0goU/2hFa1gi/QT7FEj7H9CQvkR
Py/W+19Py8j63OPtW+bHdjbg+5mott83JeumX2qWbMcbWrpMWLFu4ZqKKiM89zHdu9UvvzftwVnN
iq5tH3L3qiPh7+t2F3I9WPthCzN1V8G8rYpVxxzWvFUtQfz/iVfvQeI6uf6HYU7cxv2edgSQHr4t
EPdufMEGK+AC/5UQUS7NpIIFaSBnADVIp7pI90hmJ8m3sJJTapGR624R2CsIoFPY8IqIAGhBB6Og
wKTzKGx/Rm+BstZ26KWLDNk9rR+4JRwUYL3T4XDCa4vgas3m0Uc1k2U3Yv49f+v41YuTBg6u5c2V
dXNl0brWljB0d5ha2bL2zq9MrME4euVVeH87BTUzZc6EUdD6SUOxaezCpxxBMUBgFe3I+Kh365jl
rJhxKZPM5G8/1cAuJntq4NT62Zi39imzla2cnqVfHMl7owI4hV4mDhsuS5u+8XKNWbtovfz6efhf
Tk5tnHh4WvCZT4JoHyMIH58kQdybn5tc6ZeAiQghYV7hEM0udnidQB8/q0AOis4APq+751OYB9JC
kIkGeJxuBJSKCcBJCwUg4mfpV1PM6l5IxeryObxQqpuyZveaPklz3vLHN7nYSxkU6kdqdRKLtlW6
l8SLYJU998U/PYdzmyPQ8Fvg7K9l69Vuo+sjh8jk1Vdnzetu8Fzd6fTt08XUFppMx0DaqYKFDiCZ
wB+XR3vZyTMy1dld71a/FE7dYTrExSFPGEOXj8j1srga5Ih8EUSKy95tHksUdlijR5vieLJcRgyk
PFe8lkxYvRVqo9/BqxwXatjNyb0Y0R0omDXJfIe5stPa9D3e7n3OjR5zNm0otdnL5kj6Ei1I7fNW
vpJF6O6EyWvmMPV1d7hwzkiwg6JXyzAN3Dp4ont5UeW2rwzKWiFURoOJO09i1vLOHVg83n8fe951
kmW+flTyydm61Y+jxnqhOl+mJuqb70blHgA7kiefkQgMwH0uzqnewA/NjxuCkAZc5/L2UJAN3Wgo
AFsbQSlXLsqWKrFIgFCejJB2ONSKcFrwKxt/ulEASg8d3L9eQNB5B0KmdUFBzPEkVHgTOu61i9jN
aq8plRT801e+3aN1rJoovXLmr0TkwMtm7JV8TTk2XYWtnOLWb86rRe6TNcxUGReSVNnIzGRaZjPi
Zy02KlRzP/fZB+v3FPwkKQKTQD0Vje7VCIPImqfQgR7qZFfF726jq08W7dCpBuFlAkrGYJcAN70t
21QKRSoSQZSEGrwYNp4ODnOrVYF8RKQSFPV7NHk8WpMlU0MgQdW64PTCQfoQiba/f/H4NNUfV4An
u2Jl87mT4uxQyb9LY+ALifCCbyc02T3gPMRlsENkIDl2Cfb7eSjd3lSp5Pq1YBrbEFLCw7/tjGb9
eiD9dvddZB5X3r/T8qLPRubFS1ByEfO9gnoQdYBu+Wd7vM51t+5UuFIIR8hS+Qqmxm7RE6s9D7vZ
vcmhjIzqkCm3dxdb7NnQi5l8mdIJ29nVuO9ersQeb091DHm5AOAdIeYpSPIkEQwCQQeoZIMNAhRA
h6KEx/agakhEytEGpn5vzsUe/oiYikRgghAgFADdsEa0WD0kSCBTBEj4IDt2q5kT4tTWVqroCLv1
brl/SQ9nnPlbkWXPGiIcRZzp64dIrRWQzlApO8U2Kmsxs5yLvq+JZYqWnbW10IkwTa34RBFMQqYM
Cm2aA3E72BSYUsN0jCVop9xFIrCUglsbEWVR5kU90usCbXF0RRlEVeqXy67P7iOpFTcXYF21slnJ
GIDCQxADfbAJC/f7yRPXo1vzUO++jmkiUckijW7ydjBofCPs6nMPSBK3h1W39/ZAcyJ5ujEzKIe9
+EKn1RIAYVnSqf4VdbvrEiDAAqKeNAjtD+SCEINE/B/nrQGYayp9XYroQjjccckT3c4Jm5bhA822
jf8YFIAYA5QSFJ1qk/TNyIsbdNQbnyn8L628wmoZQrfW+bsdhqbnER/LFmob2h1iDnZt50b3dphF
Gb9QBzgmuEgMZQAj28JCw/TIXBNhKTKdlyCV8ZXKn0JtxLylVJ0DECkIkKG/vmqVup3Y8QBhS1CU
hM+485ZbSkvfQR+x2ns8X64j6mvnwJl4PxEdiGfOYaHFoohEggBSoqiUoj8wm1z76W5gEo1qmKhM
AtZtlnflEMBEI7eIfuBO61WgJeThbMHlClwpVQ8JaMUHpUKaIixYATHbAnI2JwzP3yQ1um222270
24JInNooBLHWqdhYAAukrYgym2oluZVUctttrVUbbba1VG222tVRrRrRrRrRrRttauGmGTOTJlwt
lUqvwli6VcNrp0Zia2xNgEoJZMgIghjR3ShnWUKEV7rGhsQuq/KysSlKwTSog/sEUIShFAihGM3z
iL9sdEjK2SW1kNCRcStosrdfIW3xYYWCZkfBOpCsEmCkSrKRShV9iWf8XPpMsJ1Klw9nJk0PiyLI
TmU65WCfXPf6XOOcxls9bsWAG75uFh7KF4qMjoH9/x83h3Ij64gATOb6cd9PFQ7leR6U7jBmPJ2u
pqObljrte9+wpCD3tzvdADS73S+2Z/B4sskSlWXc+UqWDW5OhQ0qHN8XufHg8mwVMm3Qw8mjNplK
IRJIkmiUTnUCS0AmbMzAU5Ps6rQTxQT8HvuiCOYA+ID4O3ltVNrS+zMB14O3Z4qHg2PZ//F3JFOF
CQdOdK/g


--===============2029363788335986776==--