From 455352b0b892252fdb060a1da71bd008da36f4ac Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 17 May 2007 19:38:34 +0300 Subject: Bug#22855: Conversion errors when constructing the condition for an IN predicates were treated as if the affected column contains NULL. If such a IN predicate is inside NOT we get wrong results. Corrected the handling of conversion errors in an IN predicate that is resolved by unique_subquery (through subselect_uniquesubquery_engine). mysql-test/r/subselect3.result: Bug#22855: test case mysql-test/t/subselect3.test: Bug#22855: test case sql/item_subselect.cc: Bug#22855: corrected the handling of conversion errors and NULL key values in IN predicate that is resolved by index lookup. --- mysql-test/t/subselect3.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/t/subselect3.test') diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index e8eae3e2452..178d95dfff9 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -531,3 +531,21 @@ SELECT a, MAX(b), DROP TABLE t1, t2; + +# +# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated +# subquery +# +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(-1), (65),(66); + +CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); +INSERT INTO t2 VALUES (65),(66); + +SELECT a FROM t1 WHERE a NOT IN (65,66); +SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); +EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); + +DROP TABLE t1; + +--echo End of 5.0 tests -- cgit v1.2.1