List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:October 13 2010 11:52am
Subject:Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3262)
Bug#52329
View as plain text  
Jørgen,

Approved.  My only suggestion is to rather have 3 versions of the query 
without GROUP BY, and just the currently failing version with GROUP BY. 
  My reasoning is that queries with GROUP BY will be executed the same 
way in most tests, while queries without GROUP BY will use different 
semijoin strategies in different tests.

--
Øystein


On 10/13/10 01:38 PM, Jorgen Loland wrote:
> #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;
>       }
>
>
>
>
>


-- 
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3262) Bug#52329Jorgen Loland13 Oct
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3262)Bug#52329Øystein Grøvlen13 Oct
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3262)Bug#52329Roy Lyseng13 Oct