MySQL Bugs: #103494: ALL quantifier doesn't work with subqueries of degree

Bug #103494	ALL quantifier doesn't work with subqueries of degree > 1
Submitted:	27 Apr 2021 7:33	Modified:	27 Apr 2021 9:14
Reporter:	Lukas Eder	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.24, 5.7.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The ALL quantifier used in quantified comparison predicates does not seem to accept subqueries of degree > 1

How to repeat:
Run this query:

--
select (1, 1) = all (select 1 as `a`, 1 as `b`) as a;
--

To get this error:

> SQL Error [1241] [21000]: Operand should contain 1 column(s)

This query works just fine:

--
select (1, 1) = any (select 1 as `a`, 1 as `b`) as a;
--

Hello Lukas Eder,

Thank you for the report and test case.

regards,
Umesh

This also doesn't work:

--
select (1, 1) < any (select 1 as `a`, 1 as `b`) as a;
--

But this does:

--
select (1, 1) != all (select 1 as `a`, 1 as `b`) as a;
--

It seems like the operands that map to IN or NOT IN are implemented, but not the others. If the correct implementation of the feature is not a high priority, I would at least suggest a fix in the error message, to hint at a missing implementation, rather than a syntax error.

Posted by developer:
 
Not a bug, this functionality is not implemented.
However, I do not find it documented as a limitation. It should be part of "Subqueries with ANY, IN, or SOME" and "Subqueries with ALL".

I'll try my luck again :) The error message was quite misleading to me. Try this query, for example:

--
select 1 in (select 1 limit 1)
--

The error message is much more helpful:

> SQL Error [1235] [42000]: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Now, I don't have much of an opinion whether to label things as bugs or feature requests, but I think that the current error message could be improved, not just the documentation.