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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.18-nt/5.0.19 BK/4.1 BK OS:Microsoft Windows (Windows XP Professional/Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[9 Jan 2006 14:50] Ulf Magnusson
Description:
Using ANY with "non-table" subqueries such as "select 1" seems to give wrong results under certain circumstances.

How to repeat:
CREATE TABLE foo (bar int);
INSERT INTO foo VALUES (1);
SELECT * FROM foo WHERE bar < ANY (select 2);

This should yield the scalar result 1, but no rows are returned.
[9 Jan 2006 15:13] Miguel Solorzano
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] Miguel Solorzano
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.