| Bug #7294 | = ALL <subquery> might give wrong result, if subquery contains NULL | ||
|---|---|---|---|
| Submitted: | 14 Dec 2004 20:01 | Modified: | 19 Apr 2006 0:03 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | |
| Assigned to: | CPU Architecture: | Any | |
[14 Dec 2004 22:58]
MySQL Verification Team
Verified with BK source 4.1.8 and 5.0.3.
[15 Dec 2004 2:02]
Peter Gulutzan
This might have something in common with bug#6247 which was fixed in 4.1 last week.
[19 Apr 2006 0:03]
MySQL Verification Team
Thank you for the bug report.
I was unable to repeat anymore with current 4.1/5.0 source server.
miguel@hegel:~/dbs/4.1> bin/mysqladmin -uroot create db88
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db88
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.19-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (f1 BIGINT); CREATE TABLE t2 (f1 BIGINT);
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 SET f1= 1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 SET f1= 1; INSERT INTO t2 SET f1= NULL;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
mysql> SELECT f1 FROM t1
-> WHERE f1 = ALL (SELECT f1 FROM t2);
Empty set (0.01 sec)
mysql>
-------------------------------------------------------------
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db88
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db88
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (f1 BIGINT); CREATE TABLE t2 (f1 BIGINT);
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 SET f1= 1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 SET f1= 1; INSERT INTO t2 SET f1= NULL;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
mysql> SELECT f1 FROM t1
-> WHERE f1 = ALL (SELECT f1 FROM t2);
Empty set (0.01 sec)
mysql>
[1 Nov 2006 17:01]
Matthias Leich
I can confirm that all problems within the NIST tests related to this bug disappeared. mysql-5.0 ChangeSet@1.2290, 2006-10-24
Description: CREATE TABLE t1 (f1 BIGINT); CREATE TABLE t2 (f1 BIGINT); INSERT INTO t1 SET f1= 1; INSERT INTO t2 SET f1= 1; INSERT INTO t2 SET f1= NULL; SELECT f1 FROM t1 WHERE f1 = ALL (SELECT f1 FROM t2); f1 1 <----- I expect ROW NOT FOUND . This result does not conform to the manual, which states 13.1.8.4 Subqueries with ALL <cut> The word ALL, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.' <cut> My conclusion: That means the one and only row of t1 with f1=1 has to be compared ('=') with the two result rows of the subquery containing 1 and NULL. So the check must be: 1(record from t1) = 1(first record from t2) AND 1(record from t1) = NULL(second record from t2) 12.1.3 Comparison Functions and Operators ... If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. So I expect to get a ROW NOT FOUND (like in MySQL 4.1 !) for my SELECT ... = ALL <Subquery> . BTW: The NIST tests expect also to get a row not found in this situation. My environment: - Intel PC with Linux(SuSE 9.1) - MySQL compiled from source Version 5.0 ChangeSet@1.1741, 2004-12-14 (shows this bug) Version 4.1 ChangeSet@1.2092.151.1, 2004-12-14 (works correct) How to repeat: Please use the statements above.