MySQL Bugs: #12022: WHERE x NOT IN () returns false negative result

Bug #12022	WHERE x NOT IN () returns false negative result
Submitted:	18 Jul 2005 22:20	Modified:	18 Jul 2005 23:45
Reporter:	Anthony Borrow	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.9b	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
I am using Moodle.org's software and noticed that I was getting an incorrectly reported result from one of the queries. The basic format of the query is:
SELECT * FROM `mdl_user`
WHERE
username <> 'guest' AND 
deleted = 0 AND 
confirmed = 1 
AND id NOT IN (3)

If I query without the AND id NOT IN (3) I get a result list of about 1000 records.
If I query with the AND id NOT IN (3) I get 0 results.
If I query with AND id NOT IN (3) with MySQL <5.0.0 I get a result list of about 1000 records.

It appears that something has changed with 5.0.0+ but I am not sure what it is. The query should return the results. I am not aware of any SQL changes regarding IN and NOT IN.

How to repeat:
On a <=5.0.0 MySQL server (I used MySQL 4.1.12), create and populate the mdl_user table using the attached sql script (I did not include all 1000 records).

Execute the query:

SELECT * FROM `mdl_user`
WHERE username <> 'guest' AND 
deleted = 0 AND 
confirmed = 1 
AND id NOT IN (3);

It should return 4 rows (ids 2, 4, 5, 6).

Execute the same query on a MySQL 5.0.9-beta-nt server and observe that there are no rows returned.

Suggested fix:
???

sql script to create and populate mdl152_user table for testing

Attachment: mysqlbug_notin.sql (text/x-sql), 4.76 KiB.

This is related to Moodle Bug #3781 available at http://bugs.moodle.org

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

mysql> SELECT * FROM `mdl152_user`
    -> WHERE
    -> username <> 'guest' AND
    -> deleted = 0 AND
    -> confirmed = 1
    -> AND id NOT IN (3);
Empty set (0.00 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.9-beta-nt |
+---------------+
1 row in set (0.00 sec)

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-nt

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

mysql> SELECT * FROM `mdl152_user`
    -> WHERE
    -> username <> 'guest' AND
    -> deleted = 0 AND
    -> confirmed = 1
    -> AND id NOT IN (3)\G
*************************** 1. row ***************************
           id: 2
         auth: manual
    confirmed: 1
 policyagreed: 0
      deleted: 0
     username: admin
     password: ee10c315eba2c75b403ea99136f5b48d
     idnumber:
    firstname: Admin
     lastname: User
        email: admin@jesuits.net
    emailstop: 0
          icq:
        skype:
        yahoo:
          aim:
          msn:
       phone1:
       phone2:
  institution: Dallas JCP
   department:
      address:
         city: Dallas
      country: US
         lang: en
        theme:
     timezone: 99
  firstaccess: 0
   lastaccess: 1121724050
    lastlogin: 1121706628
 currentlogin: 1121718278
       lastIP: 10.1.3.107
       secret: NULL
      picture: NULL
          url:
  description:
   mailformat: 1
   maildigest: 0
  maildisplay: 1
   htmleditor: 1
autosubscribe: 1
  trackforums: 0
 timemodified: 1121706450
*************************** 2. row ***************************

 <cut>

4 rows in set (0.11 sec)

mysql>

Was this related to:

A recent optimizer change caused DELETE … WHERE … NOT LIKE and DELETE … WHERE … NOT BETWEEN to not properly identify the rows to be deleted. (Bug #11853)

???