Bug #79063 Does row operation for ALL/ANY is supported or not?
Submitted: 2 Nov 2015 4:21 Modified: 3 Nov 2015 14:41
Reporter: YYY YYy Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql 5.6.23 OS:Any
Assigned to: CPU Architecture:Any

[2 Nov 2015 4:21] YYY YYy
Description:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.23    |
+-----------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (c1 int, c2 varchar(64) charset utf8 collate utf8_bin, c3 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 value (1, "Abc", 3),( 2, "ABC", 3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select (2, "Abc", 3) != all(select * from t1 where c1 = 2);
+-----------------------------------------------------+
| (2, "Abc", 3) != all(select * from t1 where c1 = 2) |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (1, "Abc", 3) != any(select * from t1 where c1 = 1);
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select (2, "Abc", 3) = any(select * from t1);
+---------------------------------------+
| (2, "Abc", 3) = any(select * from t1) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select (2, "ABC", 3) = all(select * from t1);
ERROR 1241 (21000): Operand should contain 1 column(s)

AS you can see ,  I am confused by the results, whether row operation is supported for subquery with ALL/ANY, or are there any rules that can explain the results.thanks!

How to repeat:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (c1 int, c2 varchar(64) charset utf8 collate utf8_bin, c3 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 value (1, "Abc", 3),( 2, "ABC", 3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select (2, "Abc", 3) != all(select * from t1 where c1 = 2);
+-----------------------------------------------------+
| (2, "Abc", 3) != all(select * from t1 where c1 = 2) |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (1, "Abc", 3) != any(select * from t1 where c1 = 1);
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select (2, "Abc", 3) = any(select * from t1);
+---------------------------------------+
| (2, "Abc", 3) = any(select * from t1) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select (2, "ABC", 3) = all(select * from t1);
ERROR 1241 (21000): Operand should contain 1 column(s)
[3 Nov 2015 14:41] Sinisa Milivojevic
Thank you for your bug report, but it is not a bug ....

Chapter 13 of our manual clearly states:

"
Legal operators for row subquery comparisons are:

"

Hence, not a bug !!