func_in.result, func_in.test:

  Fixed bug #11885.
sql_select.cc:
  Fixed bug #11885.
  Predicates of the forms 'a IN (v)' 'a NOT IN (v)' now
  is replaced by 'a=v' and 'a<>v' at the parsing stage.
sql_yacc.yy:
  Fixed bug #11885.
  Predicates of the forms 'a IN (v)' 'a NOT IN (v)' now 
  is replaced by 'a=v' and 'a<>v' at the parsing stage.


sql/sql_yacc.yy:
  Fixed bug #11885.
  Predicates of the forms 'a IN (v)' 'a NOT IN (v) now 
  is replaced by 'a=v' and 'a<>v' at the parsing stage.
sql/sql_select.cc:
  Fixed bug #11885.
  Predicates of the forms 'a IN (v)' 'a NOT IN (v) now
  is replaced by 'a=v' and 'a<>v' at the parsing stage.
mysql-test/t/func_in.test:
  Fixed bug #11885.
mysql-test/r/func_in.result:
  Fixed bug #11885.

4 files changed, 63 insertions, 6 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index f75fe0d1627..b0c0178328e 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -119,7 +119,7 @@ c char(1) character set latin1 collate latin1_danish_ci
 insert into t1 values ('A','B','C');
 insert into t1 values ('a','c','c');
 select * from t1 where a in (b);
-ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ' IN '
+ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
 select * from t1 where a in (b,c);
 ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT), (latin1_swedish_ci,IMPLICIT), (latin1_danish_ci,IMPLICIT) for operation ' IN '
 select * from t1 where 'a' in (a,b,c);
@@ -193,3 +193,26 @@ select * from t1 where a in (NULL, 'aa');
 a
 aa
 drop table t1;
+CREATE TABLE t1 (a int PRIMARY KEY);
+INSERT INTO t1 VALUES (44), (45), (46);
+SELECT * FROM t1 WHERE a IN (45);
+a
+45
+SELECT * FROM t1 WHERE a NOT IN (0, 45);
+a
+44
+46
+SELECT * FROM t1 WHERE a NOT IN (45);
+a
+44
+46
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45);
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` <> 45)
+SELECT * FROM v1;
+a
+44
+46
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 6e0883b821f..3a6b6653098 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -101,3 +101,21 @@ create table t1 (a char(20) character set binary);
 insert into t1 values ('aa'), ('bb');
 select * from t1 where a in (NULL, 'aa');
 drop table t1;
+
+#
+# Bug #11885: WHERE condition with NOT IN (one element)          
+#             
+
+CREATE TABLE t1 (a int PRIMARY KEY);
+INSERT INTO t1 VALUES (44), (45), (46);
+
+SELECT * FROM t1 WHERE a IN (45);
+SELECT * FROM t1 WHERE a NOT IN (0, 45);
+SELECT * FROM t1 WHERE a NOT IN (45);
+
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45);
+SHOW CREATE VIEW v1;
+SELECT * FROM v1; 
+
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 87ce26b24d5..e60688fe574 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2834,11 +2834,11 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
         cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
 	     !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
         values--;
+      DBUG_ASSERT(cond_func->functype() != Item_func::IN_FUNC ||
+                  cond_func->argument_count() != 2);
       add_key_equal_fields(key_fields, *and_level, cond_func,
                            (Item_field*) (cond_func->key_item()->real_item()),
-                           cond_func->argument_count() == 2 &&
-                           cond_func->functype() == Item_func::IN_FUNC,
-                           values,
+                           0, values,
                            cond_func->argument_count()-1,
                            usable_tables);
     }
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 2397540deae..756a8a6ee3d 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4227,9 +4227,25 @@ bool_pri:
 
 predicate:
 	 bit_expr IN_SYM '(' expr_list ')'
-	  { $4->push_front($1); $$= new Item_func_in(*$4); }
+	  { 
+            if ($4->elements == 1)
+              $$= new Item_func_eq($1, $4->head());
+            else
+            {
+              $4->push_front($1);
+              $$= new Item_func_in(*$4);
+            }
+          }
 	| bit_expr not IN_SYM '(' expr_list ')'
-	  { $5->push_front($1); $$= negate_expression(YYTHD, new Item_func_in(*$5)); }
+          {
+            if ($5->elements == 1)
+              $$= new Item_func_ne($1, $5->head());
+            else
+            {
+              $5->push_front($1);
+              $$= negate_expression(YYTHD, new Item_func_in(*$5));
+            }            
+          }
         | bit_expr IN_SYM in_subselect
 	  { $$= new Item_in_subselect($1, $3); }
 	| bit_expr not IN_SYM in_subselect