Bug #1481 ROW (...) comparison-operator ANY|ALL (subquery) fails

• Submitted: 4 Oct 2003 17:26
• Modified: 3 Nov 2003 3:12
• Reporter: Peter Gulutzan
• Status: Won't fix
• Category: MySQL Server
• Severity: S4 (Feature request)
• OS: Linux (SuSE 8.2)
• Assigned to: Peter Gulutzan
• CPU Architecture: Any

[4 Oct 2003 17:26] Peter Gulutzan

Description:
I believe this statement should be legal: 
select * from t1 where (1,2,3) = any (select s1,s2,s3 from t2); 
My reasoning is that the following statement is legal: 
select * from t1 where (1,2,3) =     (select s1,s2,s3 from t2); 
So I don't see why it makes a difference if you say "ANY" here. 
 
Ignore the fact that the book "SQL-99 Complete, Really" says 
ANY, ALL, and IN (subquery) is to be compared with a "scalar 
expression". The SQL:2003 standard says rows can be 
compared too. 

How to repeat:
create table t1 (s1 int,s2 int,s3 int); 
insert into t1 values (1,2,3); 
select * from t1 where (1,2,3) = (select s1,s2,s3 from t1);         -- succeeds 
select * from t1 where (1,2,3) = any (select s1,s2,s3 from t1);  -- fails 
 
You can use "row(1,2,3)" instead of "(1,2,3)" -- same result.

[3 Nov 2003 3:12] Peter Gulutzan

I have detected no enthusiasm for this addition, and decided it's a trivial complaint anyway.