Bug #7604 Row subqueries don't work with ALL

• Submitted: 31 Dec 2004 20:13
• Modified: 14 Dec 2009 16:20
• Reporter: Peter Gulutzan
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.21-BK, 6.0.14
• OS: Linux (Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[31 Dec 2004 20:13] Peter Gulutzan

Description:
Expressions like "(1,1)=(x,x)" work. 
Expressions like "(1,1)=ANY(x,x)" work. 
But: 
Expressions like "(1,1)=ALL(x,x)" don't work. 
 

How to repeat:
mysql> create table tp (s1 int, s2 int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from tp where (1,1) = (select s1,s2 from tp); 
Empty set (0.00 sec) 
 
mysql> select * from tp where (1,1) = any (select s1,s2 from tp); 
Empty set (0.01 sec) 
 
mysql> select * from tp where (1,1) = some (select s1,s2 from tp); 
Empty set (0.00 sec) 
 
mysql> select * from tp where (1,1) = all (select s1,s2 from tp); 
ERROR 1241 (21000): Operand should contain 1 column(s)

[14 Dec 2009 16:20] Valeriy Kravchuk

We still have this problem in recent mysql-6.0-codebase:

77-52-7-73:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tp (s1 int, s2 int); 
Query OK, 0 rows affected (0.07 sec)

mysql> select * from tp where (1,1) = (select s1,s2 from tp); 
Empty set (0.06 sec)

mysql> select * from tp where (1,1) = any (select s1,s2 from tp); 
Empty set (0.00 sec)

mysql> select * from tp where (1,1) = some (select s1,s2 from tp); 
Empty set (0.00 sec)

mysql> select * from tp where (1,1) = all (select s1,s2 from tp); 
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select * from tp where (1) = all (select s1 from tp); 
Empty set (0.03 sec)

Maybe it's time to set clear target for this?

[11 Nov 2017 19:30] Federico Razzoli

Still in 8.0.3.