Got index out of range error for subquery

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1, t2, t3;
create table t1(a varchar(20), b varchar(20), c varchar(20), primary key(a, b, c));
create table t2(a varchar(20), b varchar(20), c varchar(20), primary key(a));
create table t3(aa varchar (20), bb varchar (20), cc varchar(20), primary key(aa, bb));
insert into t1 values('1','1','1');
insert into t2 values('1','1','1');
insert into t3 values('1','1','1');
select (select max(b) from t2 where t2.a = t1.a), (select cc from t3 where t3.bb = t1.b and t3.aa = '1'), a from t1 where t1.a = '1' group by t1.a,  t1.b;

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

+-------------------------------------------+--------------------------------------------------------+---+
| (select max(b) from t2 where t2.a = t1.a) | (select cc from t3 where t3.bb = t1.b and t3.aa = '1') | a |
+-------------------------------------------+--------------------------------------------------------+---+
| 1                                         | 1                                                      | 1 |
+-------------------------------------------+--------------------------------------------------------+---+
1 row in set (0.001 sec)

3. What did you see instead (Required)

ERROR 1105 (HY000): runtime error: index out of range [0] with length 0

4. What is your TiDB version? (Required)

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                          |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.3.0-alpha-94-gc44ba1c1a8
Edition: Community
Git Commit Hash: c44ba1c1a868220764d9f11274b683d1aaecacb6
Git Branch: master
UTC Build Time: 2023-07-02 08:50:32
GoVersion: go1.20.1
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)

[guo-shaoge]

If disable projection_eliminate, query runs ok. The corresponding plan is:

MySQL [test]> explain select (select max(b) from t2 where t2.a = t1.a), (select cc from t3 where t3.bb = t1.b and t3.aa = '1'), a from t1 where t1.a = '1' group by t1.a,  t1.b;
+---------------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
| id                                    | estRows | task      | access object | operator info                                                                                             |
+---------------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
| Projection_19                         | 1.00    | root      |               | Column#13, test.t3.cc, test.t1.a                                                                          |
| └─HashJoin_32                         | 1.00    | root      |               | left outer join, equal:[eq(test.t1.b, test.t3.bb)]                                                        |
|   ├─Projection_33(Build)              | 1.00    | root      |               | test.t1.a, test.t1.b, Column#13                                                                           |
|   │ └─MergeJoin_35                    | 1.00    | root      |               | left outer join, left key:test.t1.a, right key:test.t2.a                                                  |
|   │   ├─TableReader_55(Build)         | 1.00    | root      |               | data:TableFullScan_54                                                                                     |
|   │   │ └─TableFullScan_54            | 1.00    | cop[tikv] | table:t2      | keep order:true, stats:pseudo                                                                             |
|   │   └─StreamAgg_50(Probe)           | 1.00    | root      |               | group by:test.t1.a, test.t1.b, funcs:firstrow(test.t1.a)->test.t1.a, funcs:firstrow(test.t1.b)->test.t1.b |
|   │     └─TableReader_51              | 1.00    | root      |               | data:StreamAgg_47                                                                                         |
|   │       └─StreamAgg_47              | 1.00    | cop[tikv] |               | group by:test.t1.a, test.t1.b,                                                                            |
|   │         └─TableRangeScan_49       | 1.00    | cop[tikv] | table:t1      | range:["1","1"], keep order:true, stats:pseudo                                                            |
|   └─TableReader_71(Probe)             | 1.00    | root      |               | data:TableRangeScan_70                                                                                    |
|     └─TableRangeScan_70               | 1.00    | cop[tikv] | table:t3      | range:["1","1"], keep order:false, stats:pseudo                                                           |
+---------------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
12 rows in set (0.004 sec)

The error plan is:

MySQL [test]> explain select (select max(b) from t2 where t2.a = t1.a), (select cc from t3 where t3.bb = t1.b and t3.aa = '1'), a from t1 where t1.a = '1' group by t1.a,  t1.b;
+---------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
| id                              | estRows | task      | access object | operator info                                                                                             |
+---------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
| Projection_19                   | 1.00    | root      |               | Column#13, test.t3.cc, test.t1.a                                                                          |
| └─HashJoin_30                   | 1.00    | root      |               | left outer join, equal:[eq(test.t1.b, test.t3.bb)]                                                        |
|   ├─TableReader_69(Build)       | 1.00    | root      |               | data:TableRangeScan_68                                                                                    |
|   │ └─TableRangeScan_68         | 1.00    | cop[tikv] | table:t3      | range:["1","1"], keep order:false, stats:partial[ID 14:unInitialized]                                     |
|   └─MergeJoin_33(Probe)         | 1.00    | root      |               | left outer join, left key:test.t1.a, right key:test.t2.a                                                  |
|     ├─TableReader_53(Build)     | 1.00    | root      |               | data:TableFullScan_52                                                                                     |
|     │ └─TableFullScan_52        | 1.00    | cop[tikv] | table:t2      | keep order:true, stats:pseudo                                                                             |
|     └─StreamAgg_46(Probe)       | 1.00    | root      |               | group by:test.t1.a, test.t1.b, funcs:firstrow(test.t1.a)->test.t1.a, funcs:firstrow(test.t1.b)->test.t1.b |
|       └─TableReader_51          | 1.00    | root      |               | data:TableRangeScan_50                                                                                    |
|         └─TableRangeScan_50     | 1.00    | cop[tikv] | table:t1      | range:["1","1"], keep order:true, stats:partial[a:unInitialized]                                          |
+---------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------+
10 rows in set (0.004 sec)

Why got error: HashJoin_30 needs col#13 as input, but Proj_33 is eliminated, whose output include col#13.

[time-and-fate]

Related to the checking here:

tidb/planner/core/rule_eliminate_projection.go

Line 92 in d01f0c0

if !ok || !col.Equal(nil, child.Schema().Columns[i]) {

[time-and-fate]

It's possible that child.Schema().Columns[i] and p.Exprs[i] are the same column (which means the same UniqueID), but p.Schema().Columns[i] has another UniqueID. In such case, we can't eliminate the PhysicalProjection directly.

[winoros]

The problem is introduced by the optimization for the inline projection for executors

Normally, we can use the UniqueID to identify the column. But there'll be the projection like proj(col#1->col#2, col#3->col#4). This projection only maps the column. It can be eliminated to improve performance. But eliminating it will cause the output columns' UniqueID cannot be aligned.

The optimizer does this elimination after all optimization is done. So when we enter the execution phase, you cannot use UniqueID to find one column. But the inline projection optimization did it, causing this issue.