bk commit into 5.0 tree (igor:1.1950) BUG#11885

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1950 05/07/16 18:06:34 igor@stripped +4 -0
  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.

  mysql-test/r/func_in.result
    1.18 05/07/16 18:04:30 igor@stripped +24 -1
    Fixed bug #11885.

  mysql-test/t/func_in.test
    1.15 05/07/16 18:04:01 igor@stripped +18 -0
    Fixed bug #11885.

  sql/sql_select.cc
    1.336 05/07/16 18:03:01 igor@stripped +3 -3
    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
    1.407 05/07/16 17:58:15 igor@stripped +18 -2
    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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-5.0-0

--- 1.335/sql/sql_select.cc	Sat Jul 16 02:42:31 2005
+++ 1.336/sql/sql_select.cc	Sat Jul 16 18:03:01 2005
@@ -2834,11 +2834,11 @@
         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);
     }

--- 1.406/sql/sql_yacc.yy	Fri Jul 15 14:06:12 2005
+++ 1.407/sql/sql_yacc.yy	Sat Jul 16 17:58:15 2005
@@ -4227,9 +4227,25 @@
 
 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

--- 1.17/mysql-test/r/func_in.result	Thu Jan 20 04:08:25 2005
+++ 1.18/mysql-test/r/func_in.result	Sat Jul 16 18:04:30 2005
@@ -119,7 +119,7 @@
 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 @@
 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;

--- 1.14/mysql-test/t/func_in.test	Thu Jan 20 03:38:31 2005
+++ 1.15/mysql-test/t/func_in.test	Sat Jul 16 18:04:01 2005
@@ -101,3 +101,21 @@
 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;