From: Roy Lyseng
Date: December 14 2010 4:12pm
Subject: Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58626
List-Archive: http://lists.mysql.com/commits/126802
Message-Id: <4D079762.9030404@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Ole John,

fix is approved!

On 01.12.10 11.12, Ole John Aske wrote:
> #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped
>
>   3477 Ole John Aske	2010-12-01
>        Fix for bug#58626, Incorrect result for WHERE<column>  IN (<subquery>) IS UNKNOWN.
>
>        NOTE: This fix is backported from 5.6.99 which already seems to have fixed this problem.
>
>        The fix ensures that if 'Full scan on NULL key' access method may be used
>        for a table, we can't assume that any part of the predicate is covered by
>        the REF-key. (The join_tab is known to have 'Full scan on NULL key' if
>        any cond_guards[] has been defined for 'join_tab->ref')
>
>        part_of_refkey() will therefore return '0' if a potential'Full scan on NULL key'
>        is detected - Which will force make_cond_for_table() to include all part of a
>        predicate covering the specified 'tables' and 'used_table' mask.
>
>      modified:
>        mysql-test/r/join_outer.result
>        mysql-test/t/join_outer.test
>        sql/sql_select.cc
> === modified file 'mysql-test/r/join_outer.result'
> --- a/mysql-test/r/join_outer.result	2010-10-29 08:23:06 +0000
> +++ b/mysql-test/r/join_outer.result	2010-12-01 10:12:05 +0000
> @@ -1427,4 +1427,44 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN
>   GROUP BY t2.f1, t2.f2;
>   f1	f1	f2
>   DROP TABLE t1,t2;
> +#
> +# Bug#58626 Incorrect result for WHERE<column>  IN (<subquery>) IS UNKNOWN
> +#
> +CREATE TABLE t1 (I INT NOT NULL);
> +INSERT INTO t1 VALUES (1),(2);
> +CREATE TABLE t2 (I INT NOT NULL);
> +INSERT INTO t2 VALUES (3);
> +CREATE TABLE t3 (PK1 INT, PK2 INT, PRIMARY KEY(PK1,PK2));
> +INSERT INTO t3 VALUES (1,1),(2,2),(3,3);
> +SELECT * FROM
> +t1 LEFT JOIN t2 ON t2.i = t1.i
> +WHERE t2.i IN
> +(
> +SELECT STRAIGHT_JOIN t3.pk1 FROM t3 JOIN t3 as t4
> +ON t4.pk1=t3.pk1
> +WHERE t3.pk2 = t2.i
> +)
> +IS UNKNOWN;
> +I	I
> +SELECT * FROM
> +t1 LEFT JOIN t2 ON t2.i = t1.i
> +WHERE t2.i IN
> +(
> +SELECT t3.pk1 FROM t3 LEFT JOIN t3 as t4
> +ON t4.pk1=t3.pk1
> +WHERE t3.pk2 = t2.i
> +)
> +IS UNKNOWN;
> +I	I
> +SELECT * FROM
> +t1 LEFT JOIN t2 ON t2.i = t1.i
> +WHERE t2.i IN
> +(
> +SELECT t3.pk1 FROM t3 JOIN t3 as t4
> +ON t4.pk1=t3.pk1
> +WHERE t3.pk2 = t2.i
> +)
> +IS UNKNOWN;
> +I	I
> +DROP TABLE t1,t2,t3;
>   End of 5.1 tests
>
> === modified file 'mysql-test/t/join_outer.test'
> --- a/mysql-test/t/join_outer.test	2010-10-29 08:23:06 +0000
> +++ b/mysql-test/t/join_outer.test	2010-12-01 10:12:05 +0000
> @@ -1010,4 +1010,48 @@ GROUP BY t2.f1, t2.f2;
>
>   DROP TABLE t1,t2;
>
> +--echo #
> +--echo # Bug#58626 Incorrect result for WHERE<column>  IN (<subquery>) IS UNKNOWN
> +--echo #
> +
> +CREATE TABLE t1 (I INT NOT NULL);
> +INSERT INTO t1 VALUES (1),(2);
> +CREATE TABLE t2 (I INT NOT NULL);
> +INSERT INTO t2 VALUES (3);
> +CREATE TABLE t3 (PK1 INT, PK2 INT, PRIMARY KEY(PK1,PK2));
> +INSERT INTO t3 VALUES (1,1),(2,2),(3,3);
> +
> +##'IS UNKNOWN' should not return any rows as subquery returns an empty set
> +SELECT * FROM
> + t1 LEFT JOIN t2 ON t2.i = t1.i
> + WHERE t2.i IN
> + (
> +   SELECT STRAIGHT_JOIN t3.pk1 FROM t3 JOIN t3 as t4
> +    ON t4.pk1=t3.pk1
> +    WHERE t3.pk2 = t2.i
> + )
> + IS UNKNOWN;
> +
> +SELECT * FROM
> + t1 LEFT JOIN t2 ON t2.i = t1.i
> + WHERE t2.i IN
> + (
> +   SELECT t3.pk1 FROM t3 LEFT JOIN t3 as t4
> +    ON t4.pk1=t3.pk1
> +    WHERE t3.pk2 = t2.i
> + )
> + IS UNKNOWN;
> +
> +SELECT * FROM
> + t1 LEFT JOIN t2 ON t2.i = t1.i
> + WHERE t2.i IN
> + (
> +   SELECT t3.pk1 FROM t3 JOIN t3 as t4
> +    ON t4.pk1=t3.pk1
> +    WHERE t3.pk2 = t2.i
> + )
> + IS UNKNOWN;
> +
> +DROP TABLE t1,t2,t3;
> +
>   --echo End of 5.1 tests
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2010-10-29 08:23:06 +0000
> +++ b/sql/sql_select.cc	2010-12-01 10:12:05 +0000
> @@ -12917,10 +12917,23 @@ part_of_refkey(TABLE *table,Field *field
>       KEY_PART_INFO *key_part=
>         table->key_info[table->reginfo.join_tab->ref.key].key_part;
>
> -    for (uint part=0 ; part<  ref_parts ; part++,key_part++)
> +    uint part;
> +
> +    /* If execution plan may use 'Full scan on NULL key', There might
> +     * not by any 'REF' access and entire predicate should be preserved.
> +     */

Please remember coding style for comments.
> +    for (part=0 ; part<  ref_parts ; part++)
> +    {
> +      if (table->reginfo.join_tab->ref.cond_guards[part])
> +        return (Item*) 0;
> +    }
> +
> +    for (part=0 ; part<  ref_parts ; part++,key_part++)
> +    {
>         if (field->eq(key_part->field)&&
>   	!(key_part->key_part_flag&  (HA_PART_KEY_SEG | HA_NULL_PART)))
>   	return table->reginfo.join_tab->ref.items[part];
> +    }
>     }
>     return (Item*) 0;
>   }

Thanks,
Roy