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)
