MySQL Bugs: #16302: Non-table subquery with ANY gives wrong results

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:	Windows (Windows XP Professional/Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

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.

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)

Bug: http://bugs.mysql.com/bug.php?id=19731 was marked as duplicate
of this one.

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

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

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

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.

Fixed in 4.1.22, 5.0.25

Noted in 4.1.22, 5.0.25 changelogs.