Bug #1668 Subquery containing UNION occasional fails with ANY
Submitted: 26 Oct 2003 10:54 Modified: 27 Oct 2003 2:34
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.1 OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[26 Oct 2003 10:54] Peter Gulutzan 
Description:
If I use "> ANY (SELECT ... UNION [ALL] SELECT ...)", I can get no results. I suspect that 
MySQL is ignoring everything after the first SELECT, because if I add an illegal ORDER BY 
clause there is no error message. 

How to repeat:
mysql> create table t1 (s1 char); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t1 values ('e'); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> select * from t1 where 'f' > any (select s1 from t1); 
+------+ 
| s1   | 
+------+ 
| e    | 
+------+ 
1 row in set (0.01 sec) 
 
mysql> select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1); 
Empty set (0.01 sec) 
 
mysql> select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1 order by 
2); 
Empty set (0.01 sec)
[26 Oct 2003 15:07] Oleksandr Byelkin 
ChangeSet 
  1.1595 03/10/27 01:01:27 bell@sanja.is.com.ua +5 -0 
  fixed ALL/ANY optimisation with union (BUG#1668) 
  code cleanup 
 
Thank you for bug report! 
Above cset is pushed in 4.1 source tree and will be present in next release
[27 Oct 2003 2:00] Oleksandr Byelkin 
Ooops, I horgot about ORDER BY clause
[27 Oct 2003 2:34] Oleksandr Byelkin 
As far as ORDER BY have not sense without LIMIT clause (which is prohibited 
for such subqueries) it was just ignored.