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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.7.19
• OS: Any
• 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.