TiDB produces different results for SELECT statements (Subquery returns more than 1 row)

[JZuming]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Setup the environment:

tiup playground --db.binpath /path/to/latest/tidb-server &
mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

mysql_bk.sql:
mysql_bk.sql.txt

Testcase 1

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> insert into t_dpjl2b values
(83, 82, 'a', 0, 68.100, 65.63, 39, null);

mysql> select 0 as c0 
union
select 0 as c0
  from
    (t_ragkdc as ref_2
      left outer join (t__z4n6d as ref_3
        inner join t_dpjl2b as ref_4
        on (ref_3.c2 = ref_4.c_l4g6hb ))
      on (ref_2.c0 = ref_4.c_hev4cb ))
  where ref_4.c_vtks > 0;

Testcase 2

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> start transaction;
mysql> insert into t_dpjl2b values
(83, 82, 'a', 0, 68.100, 65.63, 39, null);

mysql> select 0 as c0 
union
select 0 as c0
  from
    (t_ragkdc as ref_2
      left outer join (t__z4n6d as ref_3
        inner join t_dpjl2b as ref_4
        on (ref_3.c2 = ref_4.c_l4g6hb ))
      on (ref_2.c0 = ref_4.c_hev4cb ))
  where ref_4.c_vtks > 0;
mysql> commit;

2. What did you expect to see? (Required)

The outputs of SELECT statements in Testcase 1 and Testcase 2 are same.

3. What did you see instead (Required)

Output of SELECT statement in Testcase 1

+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 26 warnings (0.14 sec)

Output of SELECT statement in Testcase 2

ERROR 1242 (21000): Subquery returns more than 1 row

Their results are different.

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-311-g28446605c
Edition: Community
Git Commit Hash: 28446605c2e019e84e934602f4bd136294a61ff3
Git Branch: master
UTC Build Time: 2021-12-03 08:12:32
GoVersion: go1.16
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[qw4990]

I simplified this case:

mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

-- MySQL returns 88 rows as the result
SELECT CASE WHEN c0 <= (SELECT 1 FROM t_dpjl2b) THEN 1 ELSE 2 END FROM t_oc6jub; 

-- TiDB returns ERROR 1242 (21000): Subquery returns more than 1 row
SELECT CASE WHEN c0 <= (SELECT 1 FROM t_dpjl2b) THEN 1 ELSE 2 END FROM t_oc6jub; 

Actually, TiDB's behavior is more reasonable since the subquery returns 11 rows:

mysql> SELECT 1 FROM t_dpjl2b;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
11 rows in set (0.00 sec)

I also tried some similar cases in MySQL and you can see it returns the error too:

mysql> CREATE ALGORITHM = UNDEFINED DEFINER = `root`@` % ` SQL SECURITY DEFINER VIEW t1 (a) AS SELECT 1 AS a; 
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> select case when t1.a <= (select 1 from t2) then 1 else 2 end as x from t1;
ERROR 1242 (21000): Subquery returns more than 1 row

So I think it's a bug of MySQL instead of TiDB. I'll close this issue now, if I'm not correct, please reopen it again and let me know.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.