Bug #20523 Correlated subquery treats <=> as =
Submitted: 18 Jun 2006 18:02 Modified: 6 Jul 2006 0:15
Reporter: Peter Gulutzan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.12-beta-debug/4.1BK/5.0BK/5.1BK OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Igor Babaev CPU Architecture:Any

[18 Jun 2006 18:02] Peter Gulutzan
Description:
I have an expression of the form "x <=> (select ... where ... = ...)".
When x is NULL, the <=> comparison is TRUE but the = is not TRUE.
However, MySQL returns a row, as if the = comparison is redundant.

How to repeat:
mysql> create table t1 (s1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> create table t2 (s1 int);
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> select * from t1 where s1 <=> (select s1 from t2 where t2.s1=t1.s1);
+------+
| s1   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)
[18 Jun 2006 23:52] MySQL Verification Team
Thank you for the bug report.

mysql> select * from t1 where s1 <=> (select s1 from t2
    -> where t2.s1=t1.s1);
+------+
| s1   |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------
mysql> select * from t1 where s1 <=> (select s1 from t2
    -> where t2.s1=t1.s1);
+------+
| s1   |
+------+
| NULL | 
+------+
1 row in set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.23-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> 
--------------------------------------------------------------------
mysql> select * from t1 where s1 <=> (select s1 from t2
    -> where t2.s1=t1.s1);
+------+
| s1   |
+------+
| NULL | 
+------+
1 row in set (0.00 sec)

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

mysql>
[6 Jul 2006 0:15] Igor Babaev
This is not a bug.
The subquery returns zero rows and it is a scalar subquery. The result of such a scalar subquery is evaluated to NULL. (see Peter Gulutzan "SQL-99 Complete, Really", page 595) and NULL <=> NULL == TRUE.
[6 Jul 2006 0:15] Igor Babaev
This is not a bug.
The subquery returns zero rows and it is a scalar subquery. The result of such a scalar subquery is evaluated to NULL. (see Peter Gulutzan "SQL-99 Complete, Really", page 595) and NULL <=> NULL == TRUE.