MySQL Bugs: #39046: "not in" returns empty set when subquery result is large

Bug #39046	"not in" returns empty set when subquery result is large
Submitted:	26 Aug 2008 15:30	Modified:	26 Aug 2008 19:19
Reporter:	Vadim Valuev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.26	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	SELECT, subquery

Description:
I have 2 tables: 'parks' and 'teams'. Table 'teams' has a field 'park', table 'parks' has a field 'name'. The sets of values in these fields are alike, but not completely overlapping.
I run the following:
select name from parks where name not in (select park from teams);

When the subquery result is rather small (~100 rows), this works fine. But if table 'teams' counts several hundreds of rows, the result is an empty set - though it should not be empty by any means.

How to repeat:
Probably, the overall size of tables matters. I can send you the dump of tables where I've got this.

Thank you for the bug report. Open this bug report when providing the test case (dump file and my.ini file). Thanks in advance.

does this testcase show the problem?  notice the presence of null causes the last select to return no rows:

drop table if exists t1,t2;
create table t1(a int,key(a));
create table t2(b int,key(b));
insert into t1 values (1),(2),(3),(4),(5);
insert into t2 values (2),(3),(4),(5);
select a from t1 where a not in (select b from t2);
insert into t2 values (null);
select a from t1 where a not in (select b from t2);

/*
mysql> select a from t1 where a not in (select b from t2);
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into t2 values (null);
Query OK, 1 row affected (0.02 sec)

mysql> select a from t1 where a not in (select b from t2);
Empty set (0.02 sec)
*/

Yes, the above test case describes the problem - there was one row with null value. Thank you for pointing this out.

If you have this problem only if NULL is returned from subquery in some row(s), then it is NOT a bug (see http://dev.mysql.com/doc/refman/5.0/en/all-subqueries.html). Please, check if this is the case.

Yes, removing row with NULL solves the issue. So the topic is closed. I apologize for overlooking that and submitting the report.