From a9b348c71c065c75fe45b4e40c7032484771a0cc Mon Sep 17 00:00:00 2001
From: "gkodinov/kgeorge@macbook.gmz" <>
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/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