MySQL Bugs: #21230: Problem with Not in and Null values

Bug #21230	Problem with Not in and Null values
Submitted:	22 Jul 2006 15:19	Modified:	23 Jul 2006 14:24
Reporter:	Andres Felipe Gutierrez	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	null, subquery

Description:
when a column in a subquery have a null value 'not in' and <> all fails returning a empty set while 'not exists' and left join works correctly.

How to repeat:
mysql> use x;
Database changed
mysql> create table x (
    -> x1 integer not null,
    -> x2 integer not null,
    -> primary key(x1)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into x values (1, 2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into x values (2, 12);
Query OK, 1 row affected (0.02 sec)

mysql> insert into x values (3, 4);
Query OK, 1 row affected (0.03 sec)

mysql> select * from x;
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
|  2 | 12 |
|  3 |  4 |
+----+----+
3 rows in set (0.00 sec)

mysql> select * from y;
+----+------+
| y1 | y2   |
+----+------+
|  1 |    2 |
|  2 |    3 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from x where x1 not in (select y2 from y);
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

mysql> select x.* from x left join y on x.x1 = y.y2 where y.y2 is null;
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

mysql> select * from x where not exists (select y2 from y where x.x1 = y.y2);
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

mysql> select * from x where x1 <> all (select y2 from y);
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

--That's Right
--Now I will insert a Null value on y2 column

mysql> insert into y(y1) values(3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'y(y1)
 values(3)' at line 1

--Is needed a White-space before left parentheses? ok

mysql> insert into y (y1) values(3);
Query OK, 1 row affected (0.03 sec)

mysql> select * from y;
+----+------+
| y1 | y2   |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 | NULL |
+----+------+
3 rows in set (0.00 sec)

mysql> select x.* from x left join y on x.x1 = y.y2 where y.y2 is null;
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

mysql> select * from x where not exists (select y2 from y where x.x1 = y.y2);
+----+----+
| x1 | x2 |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

mysql> select * from x where x1 not in (select y2 from y);
Empty set (0.00 sec)

mysql> select * from x where x1 <> all (select y2 from y);
Empty set (0.00 sec)

--A select using not in or <> all, fails returning a empty set

Suggested fix:
???

Hello!

I think that you are doing the same mistake as I did here:
http://bugs.mysql.com/bug.php?id=19635

There is nothing = NULL and nothing <> NULL.

But thanks for your case.  It is very illustrative!

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read:
http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html

I was readed documentation at 'http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html'; and I think that problem with 'not in' and '<> all' is about http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html when the paragraph says:

The queries: 

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Can be be rewritten using IN(): 

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

I think that everytime a query using 'left join' or 'not exists' should return the self than 'not in' or '<> all'