From 9514ac9f29c54e6006413ce5e5b968f593b73287 Mon Sep 17 00:00:00 2001
From: "gkodinov/kgeorge@magare.gmz" <>
Date: Tue, 10 Apr 2007 19:08:08 +0300
Subject: Bug #19372: Added a test case. The problem was fixed by the fix for
 bug #17379. The problem was that because of some conditions the optimizer
 always preferred range or full index scan access methods to lookup access
 methods even when the latter were much cheaper.

---
 mysql-test/r/select.result |  9 +++++++++
 mysql-test/t/select.test   | 13 +++++++++++++
 2 files changed, 22 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index b501d547e0a..bfe0b9d19df 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3986,4 +3986,13 @@ t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
 faq_id
 265
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
+INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
+EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
+ON ( f1.b=f2.b AND f1.a<f2.a ) 
+WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	f1	index	inx	inx	10	NULL	7	Using where; Using index
+1	SIMPLE	f2	ref	inx	inx	5	test.f1.b	1	Using where; Using index
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index c5c7d07ee25..56b2f1b02b8 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3357,4 +3357,17 @@ SELECT t2.faq_id
 
 DROP TABLE t1,t2;
 
+
+#
+# Bug #19372: Optimizer does not use index anymore when WHERE index NOT IN
+# () is added
+#
+CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
+
+INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
+EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
+    ON ( f1.b=f2.b AND f1.a<f2.a ) 
+    WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
+DROP TABLE t1;    
+
 --echo End of 5.0 tests
-- 
cgit v1.2.1