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: | |
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
[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.