MySQL Bugs: #3831: SELECT a FROM WHERE a NOT IN (SELECT...) fails

Bug #3831	SELECT a FROM WHERE a NOT IN (SELECT...) fails
Submitted:	19 May 2004 22:33	Modified:	3 Jun 2004 18:32
Reporter:	Rob Blick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1 alpha	OS:	Linux (Redhat Linux 9)
Assigned to:		CPU Architecture:	Any

Description:
Some SELECT queries of the form:
SELECT a
FROM t1
WHERE a NOT IN (SELECT b FROM t2)

do not return all records that should be returned.

How to repeat:
Here's a dump:

mysql> select orfID FROM ORF WHERE orfID NOT IN (SELECT schu4orfId FROM XMATCH_S_VS_L);
+-------+
| orfID |
+-------+
|     1 |
|     5 |
|     8 |
|     9 |
|    13 |
|    18 |
|    71 |
|   117 |
|   121 |
|   143 |
|   165 |
|   170 |
+-------+
12 rows in set (0.03 sec)

But, this should return a total of 66 records.  As an example, orfID 2138 should be returned:
mysql> select orfID FROM ORF WHERE orfID = 2138;
+-------+
| orfID |
+-------+
|  2138 |
+-------+
1 row in set (0.00 sec)

mysql> select schu4orfId FROM XMATCH_S_VS_L WHERE schu4orfID = 2138;
Empty set (0.01 sec)

Using LEFT JOIN, you'll see the total of 66 records:
mysql> select O.orfID FROM ORF O LEFT OUTER JOIN XMATCH_S_VS_L X ON O.orfID = X.schu4orfId WHERE X.schu4orfID IS NULL;
+-------+
| orfID |
+-------+
|     1 |
|     5 |
|     8 |
|     9 |
|    13 |
|    18 |
|    71 |
...snip...
|  2015 |
|  2105 |
|  2110 |
|  2138 |
+-------+
66 rows in set (0.03 sec)

Obviously, these results reflect my particular database.  I will try to replicate the error with a more simple example, but if necessary, I will be happy to dump the tables to a file and send them off to you.

Suggested fix:
Let's figure out how to replicate it with an easier example first - it'll probably be easier to fix that way.

Yes please send for the us the files for to make
our life easy. BTW I guess this was already fixed
on 4.1.2.

mysqldump of two tables that can be used to replicate the problem

Attachment: debugTestTables.sql (application/octet-stream, text), 26.96 KiB.

I've uploaded a .sql script called debugTestTables.sql.  It creates and populates two simple tables.  Afterwards, you can run the following two queries to see the problem:

--the following should return 66 rows
SELECT fieldA FROM tableA WHERE fieldA NOT IN (SELECT fieldB FROM tableB);

--the following works correctly
SELECT fieldA FROM tableA LEFT OUTER JOIN tableB ON fieldA = fieldB WHERE fieldB IS NULL;

Miguel-
     You mentioned this was fixed on 4.1.2.  I just checked the downloads page and didn't see 4.1.2.  Has it not been released yet?

Just found 4.1.2 alpha on the downloads page.  Tried it - the above query now works.  This bug report can be closed :-)

Thank you for the feedback.