Bug #16302 | Non-table subquery with ANY gives wrong results | ||
---|---|---|---|
Submitted: | 9 Jan 2006 14:50 | Modified: | 23 Jul 2006 3:13 |
Reporter: | Ulf Magnusson | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0.18-nt/5.0.19 BK/4.1 BK | OS: | Windows (Windows XP Professional/Linux) |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[9 Jan 2006 14:50]
Ulf Magnusson
[9 Jan 2006 15:13]
MySQL Verification Team
Thank you for the bug report. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.19-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE foo (bar int); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO foo VALUES (1); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM foo WHERE bar < ANY (select 2); Empty set (0.02 sec) Your MySQL connection id is 2 to server version: 4.1.17-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE foo (bar int); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO foo VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM foo WHERE bar < ANY (select 2); Empty set (0.00 sec)
[3 Jul 2006 22:31]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=19731 was marked as duplicate of this one.
[6 Jul 2006 22:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/8871
[8 Jul 2006 0:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/8947
[10 Jul 2006 20:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/9016
[10 Jul 2006 21:33]
Evgeny Potemkin
The ALL/ANY subqueries are the subject of MIN/MAX optimization. The matter of this optimization is to embed MIN() or MAX() function into the subquery in order to get only one row by which we can tell whether the expression with ALL/ANY subquery is true or false. But when it is applied to a subquery like 'select a_constant' the reported bug occurs. As no tables are specified in the subquery the do_select() function isn't called for the optimized subquery and thus no values have been added to a MIN()/MAX() function and it returns NULL instead of a_constant. This leads to a wrong query result.
[19 Jul 2006 0:12]
Evgeny Potemkin
Fixed in 4.1.22, 5.0.25
[23 Jul 2006 3:13]
Paul DuBois
Noted in 4.1.22, 5.0.25 changelogs.