Bug #20523 Correlated subquery treats <=> as =

• Submitted: 18 Jun 2006 18:02
• Modified: 6 Jul 2006 0:15
• Reporter: Peter Gulutzan
• Status: Not a Bug
• 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.