SQL fails with error Subquery returns more than 1 row

[wshwsh12]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Same environment with #30326

tidb/bin/tidb-server &
mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

mysql_bk.sql: mysql_bk.sql.txt

Run the following sql:

select
    (
        select
            c4
        from
            t_cpsvpb
    ) as c7
from
    (
        select
            ref_0.c_13sfid as c0
        from
            t_x7zqmd as ref_0
        where
            0 <> 0
    ) as subq_0;

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

Empty set (0.001 sec)

3. What did you see instead (Required)

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

4. What is your TiDB version? (Required)

4.0,5.0,5.1,5.2,5.3, master

[wshwsh12]

Another case:

drop table t; 
drop table empty_table;
create table t(a int);
insert into t values(1),(2);
create table empty_table(a int);
select 
    (select a as a3 from t) 
from (
    select a as a2 from empty_table
) as subq_1;

TiDB: ERROR 1242 (21000): Subquery returns more than 1 row
Mysql: Empty set (0.001 sec)

[sylzd]

/assign

[sylzd]

/type compatibility

[sylzd]

• the case can be simplified further

select      (select a as a3 from t)  from empty_table;

• the reason is:

error ERROR 1242 (21000): Subquery returns more than 1 row is returned at preprocessing(rewriteWithPreprocess) and (expressionRewriter).handleScalarSubquery.
I'm thinking about how to know if the subQuery or table is empty at preprocessing stage. 🤔 Besides, is it really a bug? I think the result of tidb is reasonable, and it's a compatibility issue.

[yudongusa]

@winoros this seems to be a candidate of subq rewrite refactor feature.

[winoros]

@winoros this seems to be a candidate of subq rewrite refactor feature.

yes, this is an issue about how we deal with the subquery in the rewriting/resolving phase.
I use another issue to track it.
Remove the bug label, use the compatibility label instead.

[winoros]

It's not a one that can be easily backported.
So we also remove the affects-* label.
This would be re-considered when we implement this feature.