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
