Bug #25549 Bug in evaluation of NOT IN statements

• Submitted: 11 Jan 2007 14:57
• Modified: 15 Jan 2007 19:23
• Reporter: [ name withheld ]
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.21-community-nt
• OS: Windows (Windows NT)
• CPU Architecture: Any
• Tags: IN, SELECT, where

[11 Jan 2007 14:57] [ name withheld ]

Description:
SELECT statement returns incorrect result when using NOT IN with many ordered parameters and the last parameter out of order in WHERE clause ONLY on indexed columns.

How to repeat:
Execute the following statements:
CREATE TABLE bugin (value MEDIUMINT(9) NOT NULL);
INSERT INTO bugin (value) VALUES (?); -> 2300 times with sequential values starting in 1
ALTER TABLE bugin CHANGE value value MEDIUMINT(9) DEFAULT 0 NOT NULL PRIMARY KEY

Make sure that "SELECT COUNT(*) FROM bugin" returns 2299.
Make sure that "SELECT * FROM bugin" return values from 1 to 2299 inclusive.

Execute the query attached in the query_bugin.txt file.

It returns 0 (zero), but it should return 329.
Remove the last parameter (2299) and see that it returns 328 as expected.
Change the last parameter (2299) to 2298 and see that it return 1, but it should return 329.

Suggested fix:
Fix your code.

[11 Jan 2007 14:57] [ name withheld ]

Query to test

Attachment: query_bugin.txt (text/plain), 10.67 KiB.

[11 Jan 2007 16:28] MySQL Verification Team

Thank you for the bug report.

C:\mydb>bin\mysql -uroot -P3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.27-community-nt MySQL Community Edition (GPL)

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

mysql> CREATE TABLE bugin (value MEDIUMINT(9) NOT NULL);
Query OK, 0 rows affected (0.13 sec)

mysql> select * from bugin limit 1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bugin CHANGE value value MEDIUMINT(9) DEFAULT 0 NOT NULL PRIMARY
    -> KEY;
Query OK, 2299 rows affected (0.41 sec)
Records: 2299  Duplicates: 0  Warnings: 0

mysql> select count(*) from bugin;
+----------+
| count(*) |
+----------+
|     2299 |
+----------+
1 row in set (0.00 sec)

mysql> source c:/a/query_bugin.txt
+----------+
| COUNT(*) |
+----------+
|      329 |
+----------+
1 row in set (0.28 sec)

mysql>

[11 Jan 2007 17:33] [ name withheld ]

I executed the query using "source c:/query_bugin.txt" like you did and it returned 0 again. Do you need more information?

[11 Jan 2007 22:05] MySQL Verification Team

Thank you for the feedback. Please upgrade your MySQL to latest
released version and try again. Thanks in advance.

[15 Jan 2007 18:27] [ name withheld ]

Using the latest version (5.0.27) the correct values were returned.

Thank you

[15 Jan 2007 19:23] MySQL Verification Team

Thank you for the feedback.