MySQL Bugs: #19731: Incorrect results with ANY/ALL, < and >, and no table in the subquery

Bug #19731	Incorrect results with ANY/ALL, < and >, and no table in the subquery
Submitted:	11 May 2006 15:22	Modified:	3 Jul 2006 22:30
Reporter:	Edward Fisher	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.x, 5.0.x, 5.1.x	OS:	Any (All)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When the subquery does not reference a table, < ANY and < ALL return incorrect results.  If the subquery references a table, results are as we expect.

I can't imagine this is ever a problem in the real world.

Note that this does NOT happen with = ANY, which works fine.

How to repeat:
select IF(3 < ANY (select 4),1,0)\G
*************************** 1. row ***************************
IF(3 < ANY (select 4),1,0): 0
1 row in set (0.00 sec)

select IF(3 < ALL (select 4),1,0)\G
*************************** 1. row ***************************
IF(3 < ALL (select 4),1,0): 0
1 row in set (0.00 sec)

But if we reference a table in the subquery, all is well:

select IF(3 < ANY (select 4 from (select 1) t1),1,0)\G
*************************** 1. row ***************************
IF(3 < ANY (select 4 from (select 1) t1),1,0): 1
1 row in set (0.00 sec)

select IF(3 < ALL (select 4 from (select 1) t1),1,0)\G
*************************** 1. row ***************************
IF(3 < ALL (select 4 from (select 1) t1),1,0): 1
1 row in set (0.00 sec)

And = ANY doesn't have this problem at all:

select IF(3 = ANY (select 3),1,0)\G
*************************** 1. row ***************************
IF(3 = ANY (select 3),1,0): 1
1 row in set (0.00 sec)

Uh, I just realized my examples are needlessly verbose:

select 3 < ANY (select 3)

and 

select 3 < ALL (select 3)

should demonstrate this problem fine.

Thank you for a problem report. Verified just as described (on 5.0.22-BK on Linux, but does it really matter?):

mysql> select IF(3 < ANY (select 4),1,0);
+----------------------------+
| IF(3 < ANY (select 4),1,0) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select IF(3 < ALL (select 4),1,0);
+----------------------------+
| IF(3 < ALL (select 4),1,0) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.01 sec)

mysql> select IF(3 < ALL (select 4 from t1),1,0); -- t1 is a real table
+------------------------------------+
| IF(3 < ALL (select 4 from t1),1,0) |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select IF(4 = ANY (select 4),1,0);
+----------------------------+
| IF(4 = ANY (select 4),1,0) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select IF(3 < ANY (select * from (select 4) t1),1,0);
+-----------------------------------------------+
| IF(3 < ANY (select * from (select 4) t1),1,0) |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

It is a bug, at least because of inconsistency.

Duplicate of bug: http://bugs.mysql.com/bug.php?id=16302