From: Jorgen Loland
Date: October 13 2010 1:27pm
Subject: bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3261 to 3262)
 Bug#52329
List-Archive: http://lists.mysql.com/commits/120687
X-Bug: 52329
Message-Id: <20101013132757.D4D739B0@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============6789083480760602814=="

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

 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-byte should be the first byte for this
      field's part of the KEY ("foo<nullbyte_a2>bar").
      
      Before, the null-byte pointer for each key field wrongly pointed
      to the first byte of the KEY instead of the first byte of the key
      part. Thus, when setting the null byte for a2 above, the first byte
      of a1 was wrongly updated: "0oo bar" ('f' in foo replaced
      with null-byte of a2, and the null-byte 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
        byte of that field's part of KEY, not the first byte of KEY.
     @ sql/sql_select.cc
        setup_sj_materialization():
        Make the null-pointer for each key part point to the first
        byte of that field's part of KEY, not the first byte 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
 3261 Tor Didriksen	2010-10-11 [merge]
      Automerge from next-mr-opt-team

    added:
      mysql-test/suite/rpl/r/rpl_filter_database.result
      mysql-test/suite/rpl/t/rpl_filter_database-slave.opt
      mysql-test/suite/rpl/t/rpl_filter_database.test
    modified:
      client/mysqltest.cc
      mysql-test/extra/rpl_tests/rpl_mixing_engines.inc
      mysql-test/extra/rpl_tests/rpl_mixing_engines.test
      mysql-test/mysql-test-run.pl
      mysql-test/r/explain.result
      mysql-test/r/not_partition.result
      mysql-test/r/partition_exchange.result
      mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
      mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
      mysql-test/suite/binlog/t/binlog_killed.test
      mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
      mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result
      mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_non_direct_mixed_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_non_direct_row_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_non_direct_stm_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
      mysql-test/suite/rpl/r/rpl_row_mixing_engines.result
      mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
      mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
      mysql-test/suite/sys_vars/r/max_binlog_cache_size_basic.result
      mysql-test/t/partition_exchange.test
      sql/binlog.cc
      sql/log_event.cc
      sql/share/errmsg-utf8.txt
      sql/sql_insert.cc
      sql/sql_parse.cc
      sql/sql_partition_admin.cc
      sql/sql_partition_admin.h
      sql/sql_string.h
      storage/perfschema/table_setup_actors.cc
      storage/perfschema/table_threads.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/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 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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-13 13:27: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 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 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-09-28 15:17:29 +0000
+++ b/sql/item_subselect.cc	2010-10-13 13:27: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 13:27: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;
     }


--===============6789083480760602814==
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\
#   91b1neg0ms0k67hd
# target_branch: file:///export/home/jl208045/mysql/mysql-next-mr-opt-\
#   backporting-52329/
# testament_sha1: da366e98ba50d7bc61dd9b52474c72904a7740b3
# timestamp: 2010-10-13 15:27:57 +0200
# base_revision_id: tor.didriksen@stripped\
#   gwydzbgti73pxxcg
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWcv5tB4AJjv/gFCwCALY9///
///foL////BgGZ732X04Tok+zOhlNVgIigAABRRJVCiGwAb1ipESIigJAAUJFFFOGYAHGRpkxNBk
yYTTIGQ0BoDTJoYATQGIptTTUwjQ9QBkAAZBoAGgAAAAyqfkmp6gEwmRgTRiNGCNGmmAmIMRpiNB
JpSU2UynlQ8p5Joaem1TJoyaGgA0wIaMCaYgikQBNNNJgmAg0JkJpPU1Mg9CBoANPSYKlEABAIJo
aCaEwpkzU2k9T0h6NQ9ExqHpqfwtTYChsBQkCdHfHPblOzCQREV06jlvtYhEYG86SNNjBGBg8huP
4efmLxJ47x78je+54P4srPaduZg5TcPCQukOgWTwkMCQu3VVSyBQtLY1jshSXcTxlOMYJGmRIids
p7aaC9QjFRYMyhElCIUIhUN3YmlAoWyFJDpEhYygilpIcrOX4y6CVpA/jobQCJfMqx5jnklOEpwf
hiBHFVDVfq6J/OhCISTZ/34ker+txBlHeRRO7pfHoCMRJPtgj/j7hH6OpH7YI5fgR7iPwIu++Eko
QCdMIBOqEHhJDMkOmSCLFRRERUEERURURZxHL5SZ/J1ubZjn14zaGrtgEgoQ5sTTTIIBLjJMwhw7
saDTDSSaJTfvDQyokuwo66Ztbma/IcmYBDcUR5kf+Ef8kfC8klpH1QRnEUEUJJ9RHzwR7YjyCPny
m6eN4R8L4Ijj0BIUOoJoBIBW46gmQJSiSqkpWQrIesjz/p/bu4ynf0IsVrBFe1+vycr7uIRzgj4E
WwIvz/GmPl4KWJFPnpGgRqRc+j/RyMCMLaS6BplEZ+VxF2JT9WKUqzloM+er8d8N8sMMyJr9xTVf
DcXQlSJcEgiCIzxKWiwUvSpgopJ1ZjKWZEsVJ1F2FUHfEZ/hBHgR/IjyqRZQiwipJKkVIqRUipFR
FpGhI8pjhVzz2m1lggiVBdQwGCCkc5lEBGEcVYawTirK06hG0yErNtLogkLRCpllGrt10/PC6IiV
0r75Zr5B67FCA+cIb4CUBo1Ckrc1+DPw4YYl3C62Vt1Vuw/H8wEzwn65yQUj7qKUSFKSFFJD7xHn
wI27m6iQokLJTekhSQrCSwRYSSwjEIzEkQJI7AsgQ/VkDZIFEkChC2QL7vD+fERQRQRQihFsgiz9
XdrBFAjWgj5wigj4/XwWz+fwqxskLCKEUkLISWSFEhpCSdFoR+0i0STjEYEdYIxpBF5FlCKSFSL/
pcubp9agTiCdgTEFQdqa3//R0l/P16efynOmqmVNVdE66aqqp19nky9iJuke4fsOp/k/o62p7IQM
ic5pPnhCmrZSlOQmrrLAwCZWl8/bHZk0ujurl7O9+dGQJf2sROU2t83yBMh/C0zIVgmikYbVC3z8
fW7to9jz0VmukTWCd0IjgGbmZQCdAIZz4pnNJqxxZ3UctryWud0xpMcGLqtzsq5SqlXZKihzilVN
d1gpQoXBTPMzYYYYYVVVVETd2px7auwkgs99BEklMUCc5EUbYr9gBKTkxRk1tbNQYQgUiRZuFhJJ
pLkSaTZXWgTBeRWIwwmi0YkWTxtXxk0lZ5VUWKPdVlLoL2VRZK11MGcvSKBMk0aMJksuSyWexerm
ulzZbssZ3EW0ZK6rVk0VgyokwmZ8mbBqtYLmai9euZbtNdKN3nM1XB+c+IJ7gnt5khjDsTxgizsR
rOXO4i/vEfKSFAjexgw4hGZFBGJFakVryw1h3CJgJQgJUiDUCYDRdZn/1zKqzh/KABwRNTrJtlah
Ba59hRQ4lJxLO1tQCa72woG2/G9whwjNhYZgUJwCsXgPFquiCGjMUGNeNufps9Ntmfp689LbbK2U
3uY0z31I1CXPUJ0y0CYTRWx3t9maRwEyIrJKKSWNsmL6+nezXGFDnRJs7LmWDne461ObJixbysRd
c7LTvQ8eoJ2oLFGotMiBuZmJtxMTSbDTubTf6Kx2Eo6ynBddcRhhKVxSNrFO5sqExxzLHBm2bieC
RkWrGe7F5XYEpiubnumHpCc5yJCi4kOZIbcXrEfwJCgR597Rco0gjJIoSSk55cdWuPTewklCSYwR
kRTQjjiTShJJqR4JozmEr4+EZKiKxtftfbHCWCXEenAkONCSWyt5Wq9xlxFktyySSYeNu9N2/Hgq
2XcJC5Wbqb7bMZXJcoJlBKrLHFyeqX3cp3uOOV4jetSNVLlPLG++E07BGLlOvXB5LxHNQI4m7irL
Ajq7KkYzbJ8Uhm4uGWD17Tj3OfbFY1y4kXkf4ayybTuhka9F3JfNmrjjXbYJjVV27VE2Uz8Ji8Fs
EuySScZxSjC6a3MlixfXCcHBR46PBdnyaNJ9OLBcE/Zi4N19jxmRw73csZmLnIcNIxzdu1XW/Je8
HsldSOdXefnNpyE8F7vYs2M8HjYueE4tnFb2asncwX6r10ujdfKTnMZSQ5Szpdz00c1FlCrW/DKz
TWYiYSjVm3I1a2rIPVSTxShGM3mu21t0vmq9cuyZsEw2YyybLFZk9sqqJi6LEvdXv0dWLeejoysZ
cdXFsvcmk496i9xdqsL6Ok6qK7qrGEuDlfapdSvvWkbSR0QvZOKxxXXua9VayascVNGbBybNZDGa
qziyWsmi9m+mdJD81s90909mB3eCM8nNk6X00hnIgiuHEh1FMtxeK1wjQUkwlM9JfuAT/IjSvZe4
sZUrScZRRwekjud7AX4ObihmePJa6rXXC2UzWPf789zYz488WbZ1c1/djOdo53c2rm0Uzsc30SG+
7FRnkzWt3qc1i80B0bTEpKTOWmCh8fN83Y/q22bb9t9ERtypr27NTmdITErJMpnJHR0Was88sFjv
VSJ7m65exuUKzwlOOng2OLVc0M2Zc9fDkyart2c55sHyhOD0+nFm56NMmGFvaltJIwY8rTJ0XtHR
2wPZgqO641mY1FJaZjYYUtjQA8He2thi7NsYkaTQaCRKJ1VczWxCtTW6SsazI1EEjAqRO/o6sOnb
OYZ5M3H5XlwZhPBk4tZykLFzs1rywWqN2e615zu3eXl2WvDtY1Wt3Fjk4PTOLvaNFxkW56xTuy2X
6cTVGWrCYz28atzgAxVSUGOOgoLVVVzq5tFi1RkR1WsDmubt1wTul4m5vbHmCZ3vvUNJPNbuoidN
NWfH5AAlLsbWuZTowcXBWWygmd6qnB27YuDu1bu5+sS508sGzDFY8plc5uS54PbOeFzgxTFSzZ0m
HoZuDfs7O3bwsbOrJq7pmuLG7jN7e3NY+PVq73LqyYtnPns7165e4Oa1kosaTnhO11QrIdqEYYvJ
2tFbhBE7iZqIrwqoIM5ibQBqJnysDAVyYxXVNrGajZxqmImEvI3VYrVV9IwW6Kb4+dMZaynZeo0e
ZF/d6Hg6NmD9G3Bsq6U8q0ozar431XOLosbtN3qmD5pory9Or2iZz9LZyLOiJry3oF2AJIJxKKoY
3CmnXGNcs9KpJU10RYwX0l1RRSymSoAahSU6wSVFCpKmuVVM0GkAqgtuiwMXZCcJKEgRZKhAKH4K
mXTTpxYUIAiEiBj8BTUoySFjGMYqqqoicvjJDQAhxfdT6AhoMIJ7P3kJ+Qj8Ykn+oR6iN0jnEpSF
KUp/QhNQi6y6yJJUIsRCUiPsgjIIoEf5kJQIxqiE/iiEzgRQI/IR+UM0kiVJJQj8Ykn94EVSE0CK
QRTasMoI+khN0QmKEcBFt33ohMX5JCWQRQEUQjkEckkifdBFCMiMCMokmcCNwj8kQn5EfvItEk2g
jURT+1JClJClJClJDUIxhJNQjSCKwReQmgRcEVvU4COJJLUIwItEXQRQIpSIzuIgDZ+8f1H6ySr5
0CnOQpIUiBhm/eminpVFKqx5C0qjWPNRUVjwILSQChAN0YWuIYASMwgYRJwrIYUD9sK9k9AV0B8/
eYJ9ELFHUkLvtIrWsEc2D6lMNhKKJH637H8SQykRL35/u2bvraPQsmz631t2LBYquVfucFq5o+x6
nRRWXMLG7Y3dJi5LURlmpkzmJWcn9n4fnkznpJnNE9N2JrM5Tgaw55eCCE5GqvJ3O9VdmveL2yRl
CV/k6fbCepOnn9z4JHw2Dfs8rAkgPnqaIeGp8QQYr7wF/wogolubSQQK0kDOAGQpHRU8VC4B9sOg
KxAjy9D3vg4hHgSSkQnsfAJWBHumrk9eSrhqd02fdKsWV5ra4LnoULFzlwfbMWKx71GzxatJg+7F
x6f1/3qyfIuZtTq6ODBznBbaXUm4tJwXl5nN7DT0/x2imTtZdKEUbnhJQ7Fp8MAu4vJEi7hgCUoD
AK9tZ8lDTsmPf0OBqKauR8auh/m0lxMvN7S0hxg6Ei8zm85kVPPq1UfJL1zdeuI6w9jio6rWKq5O
DRn94vbqtWrPv738MG3s4302LXdLp8UsPmYUT6PbEB5Ji/NrnTLyESEELCvuEQ5y62eZ2+zMB0VG
wB8H6NZ1rcAH0kKQiQYxOOYJKRQVKkBJC0UgmKbzxOpBR1NPh4HjWcCZM0lWuKnBRRznw8ODykPY
0nJqt2e/sJxXXOKm6roR2p1Ese5lRszdd2OkSMIJUtaLnFPhPp8Xeuq5KrGDJR8bst+oirudHJwa
Lfm8ykpoIOWrRnwOTgbzd4hrVJqGt2gny93c9nFMzLX2V5PZr6omv17STtFApClBtkBJCI19/ud3
tdbA2yEQ80gUlWciRicDyN18MjoeBY9i4jBI8fU5rFWDgtok5UmFO6sho10Nl5aciZvdJIpwOp3a
TH3FPqKXimXfKUtoKVCdyDnNJpPcK+GBToMQ8Ep4YANmzw5onx0o57auWxT3uEK4iJhwMGXTj2xV
os9TZwjg6SovPryAHcwid9PlE3p5P0o3KO8B2pE9HIhAZgPpdiqcGAH4YxuCOUvl7qEgG7KgoAFr
bASlXH0VLFVikQChPZkg63PUim1a8xWNv5yiAJQdOX8ZA6ZnM+VE/b3OxzIKyw8DmEoyfRP0/Ls+
di0dFHB8a5wbL170Tk+DgyXOTZm5N1zVzM2arOYsbGS5c0fo1XyjeSOHh5rA3KnU/9+iD9v0FO+Q
pApNAPVWO9xUIg8SapxIEe+mRXqeLdR5tPc3aYVIOAsoEvAGdrQCTfKzfTApGChJBlIQaujBuOjm
dB5tVgXxEpBIU9Xs0cng1JiqmTIEHVuuD0zeIAzD+BCJus+n9/s01R9ngCfRsTR87mT4O1Q0N+ps
eoLgekE3m55sniA9BGW0Q2wgOPyCfp+Pio3cSpU8X3aswa2xBSwsOH44Rn/j5oP226brIPC+9fy+
KLPVubFS1BxEfG9gnkg6zd8aHT4nOtv3KlwpBCPgKXyFU2M3aInmzyfo9m9xaGMTQqQKbd3F1vzb
PIi5lmAH4aKgTt83VuOC+bsQeb5fgoZ+boAcweUPMUpHkSCIYBIIPUMUGGgQogQ9FCY/yoGpIRMb
RBqfFo+lijx8kTAUimsIUQLRM+5EwiSj46qlEjWIp7IDt2q4InyamsrVXIEp/LOUlWSh14vyM4JO
k3wIhpSeiOaGSShWQzlApO8U2Kmsws5yLvq9SyxUtPTW+IA2UolAJuc2EQRTEKmDApumgN5PMwKT
CJhiKVsoU+wikVhKQS2NyLKo9wT1kV98v3vLYijOIq85dLrtPsI7iKnoabM/GY15KmADCQxADfbA
JC/p+kkT27Nb81DlfR1SRKOiRRrd5OxgyfCPxdTnHtAlbv9Ft/u9UBzonm5YGdRD635oVPsiQAwr
OlU/fV2u9Yko1C9d6bUTvP7whSEts85+faCWHZfPZ2K5IRxuOOKJ9XQCZ+W4QPFto7/eBSAGAOUE
hSdapP0z8iLG3uqDc+E/a+tvM01DGFb63xdjsNTc4CP7MGah3tDrEHQzbzyb3d0pEiWTgR1pFDWt
BE690hYfrkNRKmx1FIgMu9lbH1HNk3lKqToGIFIRIUN2+apW9ztwBJiDCDbkG9nRENJ9QT+s1PPc
ftRPuZt+QJ15vsI7ENGgzZODRRCJBAkXr6VrSn2Fkws8rpxCaSpF3aSN5CwBczbbeGiIYCIR28Q+
4J3Wq0BLq5rZqHKAuFKqHfLTgg+NQpewRDEIA15APG13Gv8SQ53bbbbbdx70IUxiZZBDSckkOqSI
AGuyTSBLicMIcOiqo8FttrVUbbba1VG222tVRrRrRrRrRrRttauNsMtZMuDFt1SSTr3bOOSdDoz3
dpdGSyUIpvOihy5lcFkhTDwxlsxhnf9eOCLowI6SIT/0ihCUIoEUIxm+cRfXcKWTVwkoXgpSDKgD
WjRE6q1CmtJhZMBvBPN1glYJMFIlWUilCr7ksvu5ajRmnUqXD2cVbw9LQmg6AjXbMB+755UmnQ2N
DzNgTAAO/4c1h7qF4qMjyD93bi79KI+uAAEzm+nHvp4qGlXkelO4zMx5O11NRzccNdrwfsKQg8G5
73IBpe97n4zv0eLLFEpVlpfCVLBrcXJQ7lDm9XS9d7ybBUxbcmHk0Z+6UohEkiSaJROdQJLIEz52
YCnJ+HVaCdUEvF/qbuBBHUB5uzjsVNjS/DMBuvdmvooc2x7P9BdyRThQkMv5tB4=


--===============6789083480760602814==--