MySQL Bugs: #11885: error in select with NOT IN of one item if it's a primary key

Bug #11885	error in select with NOT IN of one item if it's a primary key
Submitted:	12 Jul 2005 13:36	Modified:	29 Jul 2005 17:13
Reporter:	Damian Burke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.7	OS:	Linux (Fedora Core 4)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
SELECT ... NOT IN() with only one value in the parens
returns zero rows even though some should be returned.
Bug was not present until I made the column a primary key.
If I add a second value within the parens, it works fine.

Bug not present in 4.1.9

How to repeat:
mysql> create table test(a int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(44);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(45);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(46);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where a NOT IN(45);
Empty set (0.00 sec)    -- SHOULD HAVE ROWS

mysql> select * from test where a IN(45);
+----+
| a  |
+----+
| 45 |
+----+
1 row in set (0.00 sec)

mysql> select * from test where a NOT IN(0,45);
+----+
| a  |
+----+
| 44 |
| 46 |
+----+
2 rows in set (0.00 sec)

10:39 miguel@hegel:~/dbs/5.0>bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test(a int primary key);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test values(44);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(45);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(46);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where a NOT IN(45);
Empty set (0.00 sec)

mysql> alter table test drop primary key;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test where a NOT IN(45);
+----+
| a  |
+----+
| 44 |
| 46 |
+----+
2 rows in set (0.01 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27229

ChangeSet
  1.1950 05/07/16 18:06:34 igor@rurik.mysql.com +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.

The fix will appear in 5.0.10

Documented in 5.0.10 changelog:

<listitem><para><literal>SELECT ... NOT IN()</literal> gave unexpected results when only static value present between the <literal>()</literal>. (Bug #11885)</para></listitem>

I have had a similar problem but with a dynamic "not in" select list, e.g.
"select id from test1 where cola not in (select colb from test2)".
Everything was working OK until presumably we reached a value somwhere around the 658 from the second select list. It was fixed by modify the second select to: "select colb from test2 where colb like '%'".
We are using version 5.0.22