opt_range.cc:

  Fixed bug #11853.
  Corrected the code of the range optimization for
  NOT IN and NOT BETWEEN.
range.test, range.result:
  Fixed bug #11853.

2 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 6f9b4e41b22..e8e30b3653a 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -744,3 +744,27 @@ a	b
 1	3
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name));
+INSERT INTO t1 VALUES ('Betty'), ('Anna');
+SELECT * FROM t1;
+name
+Anna
+Betty
+DELETE FROM t1 WHERE name NOT LIKE 'A%a';
+SELECT * FROM t1;
+name
+Anna
+DROP TABLE t1;
+CREATE TABLE t1 (a int, KEY idx(a));
+INSERT INTO t1 VALUES (NULL), (1), (2), (3);
+SELECT * FROM t1;
+a
+NULL
+1
+2
+3
+DELETE FROM t1 WHERE NOT(a <=> 2);
+SELECT * FROM t1;
+a
+2
+DROP TABLE t1;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 3d2285b1633..065cef659b8 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -553,3 +553,26 @@ SELECT a,b FROM v1 WHERE a < 2 and b=3;
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+#
+# Bug #11853: DELETE statement with a NOT (LIKE/<=>) where condition
+#             for an indexed attribute              
+#             
+
+CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name));
+INSERT INTO t1 VALUES ('Betty'), ('Anna');
+
+SELECT * FROM t1;
+DELETE FROM t1 WHERE name NOT LIKE 'A%a';
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (a int, KEY idx(a));
+INSERT INTO t1 VALUES (NULL), (1), (2), (3);
+
+SELECT * FROM t1;
+DELETE FROM t1 WHERE NOT(a <=> 2);
+SELECT * FROM t1;
+
+DROP TABLE t1;