Bug #87703 ERROR 1241 (21000): Operand should contain 1 column(s)
Submitted: 8 Sep 2017 8:41 Modified: 23 Oct 2020 14:16
Reporter: Huanting Liu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: subquery

[8 Sep 2017 8:41] Huanting Liu
Description:
Comparison Operators between Row Constructor Expression and Subquery not supported fully:

mysql> select * from t1 where row(a,b) > any(select a-1,b-1 from t2 );
ERROR 1241 (21000): Operand should contain 1 column(s)

but

mysql> select * from t1 where row(a,b) = any(select a-1,b-1 from t2 );
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
3 rows in set (0.00 sec)

How to repeat:
mysql> create table t1(a int,b int ,c int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(a int,b int ,c int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1,1,1),(2,2,2),(3,3,3),(4,4,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1,1,1),(2,2,2),(3,3,3),(4,4,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1 where row(a,b) > any(select a-1,b-1 from t2 );
ERROR 1241 (21000): Operand should contain 1 column(s)

mysql> select * from t1 where row(a,b) = any(select a-1,b-1 from t2 );
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
3 rows in set (0.00 sec)

Suggested fix:
support comparison between Row Constructor Expression and Subquery.
[23 Oct 2020 14:16] MySQL Verification Team
Hi Mr. Liu,

Thank you for your bug report.

However, this is not a bug.

The reason for this is fully described at the bottom of our sub-sub-chapter 13.2.11.3.

Not a bug.
[23 Oct 2020 14:16] MySQL Verification Team
Also, we are following SQL standards and this restriction is defined there.