dml: unnecessary error reporting for select statement with redundant where clause in TiDB while MySQL returns correct results

[PragmaTwice]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1 (i1 integer, f1 float);
insert into t1 values (2, 41), (1, 42), (3, 43), (0, null);
create table t2 (i2 integer, ii2 integer, f2 float);
insert into t2 values (0, 43, null), (1, null, 0.1), (3, 42, 0.01), (2, 51, 0.12), (null, 41, -0.1), (null, null, null);
select count(*) from t1 as _tmp_1 join (select * from t2 where false) as _tmp_2 where _tmp_2.i2=(select i1 from t1);

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

in MySQL:

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.02 sec)

3. What did you see instead (Required)

in TiDB:

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

4. What is your TiDB version? (Required)

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()
                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.1.0-alpha-13-gd5e0ed0aa-dirty
Edition: Community
Git Commit Hash: d5e0ed0aaed72d2f2dfe24e9deec31cb6cb5fdf0
Git Branch: master
UTC Build Time: 2021-05-24 14:39:20
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[eurekaka]

This should be a compatibility issue. The behavior difference comes from the different execution order of the SQL clauses. For MySQL, it firstly evaluates t1 as _tmp_1 join (select * from t2 where false) and finds that the result is empty, so the where _tmp_2.i2=(select i1 from t1) is not evaluated at all, while TiDB would firstly execute the uncorrelated scalar subquery where _tmp_2.i2=(select i1 from t1) in optimization phase, so TiDB would report error while MySQL would not.

If we remove the where false filter from the query, MySQL would report error as well:

MySQL [test]> select count(*) from t1 as _tmp_1 join (select * from t2 where false) as _tmp_2 where _tmp_2.i2=(select i1 from t1);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

MySQL [test]> select count(*) from t1 as _tmp_1 join (select * from t2) as _tmp_2 where _tmp_2.i2=(select i1 from t1);
ERROR 1242 (21000): Subquery returns more than 1 row

[eurekaka]

We had a discussion about the optimization strategy regarding uncorrelated subqueries before, but did not reach an agreement on a plan to change that implementation, so this compatibility problem may not be solved in a short time.