The limit subquery in the select position prevents Indexjoin plan

[Chad20N13]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

MySQL [chad]> show create table ta\G
*************************** 1. row ***************************
Table: ta
Create Table: CREATE TABLE ta (
id int(10) NOT NULL,
code varchar(20) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
status varchar(20) DEFAULT NULL,
KEY idx_ta_id (id),
KEY idx_ta_code (code),
KEY idx_ta_name (name),
PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

MySQL [chad]> show create table tb\G
*************************** 1. row ***************************
Table: tb
Create Table: CREATE TABLE tb (
id int(10) NOT NULL,
code varchar(20) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
status varchar(20) DEFAULT NULL,
KEY idx_tb_id (id),
KEY idx_tb_code (code),
KEY idx_tb_name (name),
PRIMARY KEY (id) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

MySQL [chad]> explain select distinct a.code,(select b.code from tb b where a.id = b.id)
-> from ta a
-> where a.code = 'code10';
+--------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| HashAgg_11 | 1.00 | root | | group by:chad.ta.code, chad.tb.code, funcs:firstrow(chad.ta.code)->chad.ta.code, funcs:firstrow(chad.tb.code)->chad.tb.code |
| └─IndexJoin_17 | 1.00 | root | | left outer join, inner:IndexLookUp_16, outer key:chad.ta.id, inner key:chad.tb.id, equal cond:eq(chad.ta.id, chad.tb.id) |
| ├─IndexLookUp_51(Build) | 1.00 | root | | |
| │ ├─IndexRangeScan_49(Build) | 1.00 | cop[tikv] | table:a, index:idx_ta_code(code) | range:["code10","code10"], keep order:false |
| │ └─TableRowIDScan_50(Probe) | 1.00 | cop[tikv] | table:a | keep order:false |
| └─IndexLookUp_16(Probe) | 1.00 | root | | |
| ├─IndexRangeScan_14(Build) | 1.00 | cop[tikv] | table:b, index:idx_tb_id(id) | range: decided by [eq(chad.tb.id, chad.ta.id)], keep order:false |
| └─TableRowIDScan_15(Probe) | 1.00 | cop[tikv] | table:b | keep order:false |
+--------------------------------------+---------+-----------+----------------------------------+------------------------------------------------------------------------------------------------------------------

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

select distinct a.code,(select b.code from tb b where a.id = b.id limit 1)
from ta a
where a.code = 'code10';

expect to use indexJoin Plan juse like step 1;

3. What did you see instead (Required)

select distinct a.code,(select b.code from tb b where a.id = b.id limit 1)
from ta a
where a.code = 'code10';

+--------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| HashAgg_14 | 1.00 | root | | group by:chad.ta.code, chad.tb.code, funcs:firstrow(chad.ta.code)->chad.ta.code, funcs:firstrow(chad.tb.code)->chad.tb.code |
| └─Apply_16 | 1.00 | root | | CARTESIAN left outer join |
| ├─IndexLookUp_22(Build) | 1.00 | root | | |
| │ ├─IndexRangeScan_20(Build) | 1.00 | cop[tikv] | table:a, index:idx_ta_code(code) | range:["code10","code10"], keep order:false |
| │ └─TableRowIDScan_21(Probe) | 1.00 | cop[tikv] | table:a | keep order:false |
| └─Projection_35(Probe) | 1.00 | root | | chad.tb.code |
| └─IndexLookUp_34 | 1.00 | root | | limit embedded(offset:0, count:1) |
| ├─Limit_33(Build) | 1.00 | cop[tikv] | | offset:0, count:1 |
| │ └─IndexRangeScan_29 | 1.00 | cop[tikv] | table:b, index:idx_tb_id(id) | range: decided by [eq(chad.ta.id, chad.tb.id)], keep order:false |
| └─TableRowIDScan_30(Probe) | 1.00 | cop[tikv] | table:b | keep order:false |
+--------------------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.3-20230628-0b5e28b
Edition: Enterprise
Git Commit Hash: 0b5e28b
Git Branch: heads/refs/tags/v6.5.3-20230628-0b5e28b
UTC Build Time: 2023-06-28 09:56:04
GoVersion: go1.19.10
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)