planner: IndexJoin with aggregation inner still admitted when the join key only appears inside a GROUP BY expression
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
#66217 (ref #66167) only admits an aggregation on the inner side of an IndexJoin when the inner join keys are a subset of the GROUP BY items. The check builds the GROUP BY key set with `expression.ExtractColumnsMapFromExpressions(nil, la.GroupByItems...)`, so a column that only appears **inside** a GROUP BY expression (for example `GROUP BY c2 % 2`) is treated as a grouping key. The IndexJoin then probes per `c2` value and each probe aggregates only the rows of that value, splitting the `c2 % 2` group across probes.
```sql
create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int, key(c2));
insert into t2 values (1, 2), (1, 4), (1, 3);
insert into t1 with recursive n(i) as (select 1 union all select i+1 from n where i < 40) select i, 2 from n;
insert into t1 with recursive n(i) as (select 1 union all select i+1 from n where i < 40) select i, 4 from n;
set @@sql_mode='';
-- small chunks so that the outer rows are spread over several probe tasks
set @@tidb_init_chunk_size=32; set @@tidb_max_chunk_size=32; set @@tidb_index_join_batch_size=1;
explain format='brief' select /*+ INL_JOIN(a_t) */ t1.c2, a_t.c2, a_t.cnt
from t1 inner join (select c2, count(*) cnt from t2 group by c2 % 2) a_t on t1.c2 = a_t.c2;
select /*+ INL_JOIN(a_t) */ 'INL_JOIN' variant, t1.c2, a_t.c2 agg_c2, a_t.cnt, count(*) rows_
from t1 inner join (select c2, count(*) cnt from t2 group by c2 % 2) a_t on t1.c2 = a_t.c2
group by 1,2,3,4 order by 2,3,4;
select /*+ HASH_JOIN(a_t) */ 'HASH_JOIN' variant, t1.c2, a_t.c2 agg_c2, a_t.cnt, count(*) rows_
from t1 inner join (select c2, count(*) cnt from t2 group by c2 % 2) a_t on t1.c2 = a_t.c2
group by 1,2,3,4 order by 2,3,4;
```
Plan chosen on master (IndexJoin with the aggregation on the probe side)
```
| id | estRows | task | access object | operator info
| Projection | 9990.00 | root | | repro.t1.c2, repro.t2.c2, Column#9
| └─IndexJoin | 9990.00 | root | | inner join, inner:HashAgg, outer key:repro.t1.c2, inner key:repro.t2.c2, equal cond:eq(repro.t1.c2, repro.t2.c2)
| ├─TableReader(Build) | 9990.00 | root | | data:Selection
| │ └─Selection | 9990.00 | cop[tikv] | | not(isnull(repro.t1.c2))
| │ └─TableFullScan | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo
| └─HashAgg(Probe) | 9990.00 | root | | group by:Column#21, funcs:count(1)->Column#9, funcs:firstrow(Column#20)->repro.t2.c2
| └─Projection | 9990.00 | root | | repro.t2.c2->Column#20, mod(repro.t2.c2, 2)->Column#21
| └─IndexReader | 9990.00 | root | | index:Selection
| └─Selection | 9990.00 | cop[tikv] | | not(isnull(repro.t2.c2))
| └─IndexRangeScan | 10000.00 | cop[tikv] | table:t2, index:c2(c2) | range: decided by [eq(repro.t2.c2, repro.t1.c2)], keep order:false, stats:pseudo
```
### 2. What did you expect to see? (Required)
The INL_JOIN hint should be rejected (warning 1815) like the other unsafe cases fixed by #66217, or at least the INL_JOIN result should match HASH_JOIN. The `c2 % 2` group `{2, 4}` has `count(*) = 2` and `firstrow(c2) = 2`, so only the 40 `t1` rows with `c2 = 2` match:
```
| variant | c2 | agg_c2 | cnt | rows_ |
| HASH_JOIN | 2 | 2 | 2 | 40 |
```
### 3. What did you see instead (Required)
The IndexJoin is admitted and returns 56 rows with three different aggregate values, because each probe task only sees the `t2` rows of its own `c2` values:
```
| variant | c2 | agg_c2 | cnt | rows_ |
| INL_JOIN | 2 | 2 | 1 | 32 |
| INL_JOIN | 2 | 2 | 2 | 8 |
| INL_JOIN | 4 | 4 | 1 | 16 |
```
With the default chunk size and only a few outer rows all keys land in a single probe task and the result happens to be correct, which is why the tests in #66217 do not catch this.
### 4. What is your TiDB version? (Required)
master (`origin/master` 5316d43574, 2026-09-17); the check is in `checkIndexJoinInnerTaskWithAgg` in `pkg/planner/core/exhaust_physical_plans.go`. The local binary used for the repro was built from master on 2026-09-16 and reports placeholder version strings:
```
Release Version: v8.4.0-this-is-a-placeholder
Edition: Community
Git Commit Hash: None
Store: unistore
```
### Analysis
`checkIndexJoinInnerTaskWithAgg` should only treat direct `*expression.Column` GROUP BY items as grouping keys. Columns nested in expressions do not partition the groups by that column, so they must not admit an index join on that key. The release-8.5 cherry-pick of #66217 (#71332) already applies this stricter rule and adds the `GROUP BY c2 % 2` case to `tests/integrationtest/t/planner/core/indexjoin.test`; master needs the same change.
Contributor guide
Research direction
Start in pkg/planner/core/exhaust_physical_plans.go at checkIndexJoinInnerTaskWithAgg, then compare the release-8.5 change from #71332. Run tests/integrationtest/t/planner/core/indexjoin.test and add or adapt the GROUP BY c2 % 2 case; done means the unsafe INL_JOIN is rejected or produces the same result as HASH_JOIN.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 76/100