pingcap / pingcap/tidb

Projection panics with "index out of range" when ORDER BY sorts on a derived table joined back to its base table

Open
#70,695 5 comments 0 reactions 0 assignees View on GitHub
contribution first-time-contributor severity/major sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE ti (
id CHAR(32) PRIMARY KEY CLUSTERED,
task_id VARCHAR(250) NOT NULL,
dag_id VARCHAR(250) NOT NULL,
run_id VARCHAR(250) NOT NULL,
map_index INT NOT NULL DEFAULT -1,
state VARCHAR(20),
priority_weight INT,
KEY ti_state (state)
);
CREATE TABLE dr (
id INT PRIMARY KEY,
dag_id VARCHAR(250) NOT NULL,
run_id VARCHAR(250) NOT NULL,
logical_date DATETIME(6),
state VARCHAR(20),
UNIQUE KEY (dag_id, run_id)
);
CREATE TABLE dm (
dag_id VARCHAR(250) PRIMARY KEY CLUSTERED,
max_active_tasks INT NOT NULL,
is_paused BOOL NOT NULL
);

INSERT INTO ti VALUES ('00000000000000000000000000000001', 'task', 'dag', 'run', -1, 'scheduled', 1);
INSERT INTO dr VALUES (1, 'dag', 'run', '2026-01-01 00:00:00', 'running');
INSERT INTO dm VALUES ('dag', 16, false);

SELECT ti.id
FROM (
SELECT ti.id AS id, ti.task_id AS task_id, ti.dag_id AS dag_id,
ti.run_id AS run_id, ti.map_index AS map_index,
ti.priority_weight AS priority_weight,
ROW_NUMBER() OVER (
PARTITION BY ti.dag_id, ti.run_id
ORDER BY -ti.priority_weight, dr.logical_date, ti.map_index
) AS row_num,
dm.max_active_tasks AS dr_max_active_tasks,
ti.priority_weight AS priority_weight_for_ordering,
dr.logical_date AS logical_date_for_ordering,
ti.map_index AS map_index_for_ordering
FROM ti
JOIN dr ON dr.dag_id = ti.dag_id AND dr.run_id = ti.run_id
JOIN dm ON dm.dag_id = ti.dag_id
WHERE dr.state = 'running' AND dm.is_paused = false AND ti.state = 'scheduled'
ORDER BY -ti.priority_weight, dr.logical_date, ti.map_index
) candidates
JOIN ti ON ti.dag_id = candidates.dag_id
AND ti.task_id = candidates.task_id
AND ti.run_id = candidates.run_id
AND ti.map_index = candidates.map_index
WHERE candidates.dr_max_active_tasks >= candidates.row_num
ORDER BY -candidates.priority_weight_for_ordering,
candidates.logical_date_for_ordering,
candidates.map_index_for_ordering
LIMIT 16;
```

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

One row:

```
+----------------------------------+
| id |
+----------------------------------+
| 00000000000000000000000000000001 |
+----------------------------------+
```

### 3. What did you see instead (Required)

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

Panic path:

```
chunk.(*Chunk).swapColumn pkg/util/chunk/chunk.go:269
chunk.(*ColumnSwapHelper).SwapColumns pkg/util/chunk/chunk_util.go:306
expression.(*EvaluatorSuite).Run pkg/expression/evaluator.go:139
executor.(*ProjectionExec).unParallelExecute pkg/executor/projection.go:227
```

The three ingredients are a derived table that computes a scalar sort key, a
join back onto that derived table, and an outer `ORDER BY` over the derived
table's columns together with `LIMIT`. Dropping the outer `ORDER BY` (keeping
`LIMIT`) makes the query return correctly, so the sort is what triggers it.

#### Cause

`postOptimize` runs `InjectExtraProjection` after `ResolveIndices`
(`pkg/planner/core/optimizer.go`), so any index rewriting done there has to keep
already-resolved indices valid.

For a `TopN`/`Sort` whose `ORDER BY` contains a scalar function,
`InjectProjBelowSort` builds a `bottomProj` under the sort and a `topProj` above
it, producing `topProj -> TopN -> bottomProj`. It then calls:

```go
refine4NeighbourProj(topProj, bottomProj)
```

`refine4NeighbourProj` is documented as operating on "two neighbouring
Projections", but `topProj` and `bottomProj` are not neighbours here: the
`TopN`/`Sort` sits between them. It remaps `topProj`'s column indexes through a
union-find built over `bottomProj`'s duplicate output columns, so an index of 0
becomes the union root 4. `topProj` actually reads the `TopN`'s output, whose
schema has one column, so the projection then indexes past the end of its input
chunk and panics.

The runtime already handles genuine column aliasing: `ColumnSwapHelper`
detects it in `mergeInputIdxToOutputIdxes` by comparing column pointers in the
input chunk, which is why the static remap is not needed for correctness here.

I have a fix and a regression test, and will open a PR referencing this issue.

### 4. What is your TiDB version? (Required)

Reproduced on `master` at commit `fa2a89347ba4071a31f9c3525293af25f91570d9`
(unistore, via `pkg/executor` testkit).

This was originally hit by the Apache Airflow 3 scheduler, whose task-claim
query has exactly this shape.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the query with the pkg/executor testkit, then read InjectExtraProjection in pkg/planner/core/optimizer.go, especially InjectProjBelowSort and refine4NeighbourProj. Check the projection path in pkg/executor/projection.go and the reported regression test; done means the query returns the expected row without an index-out-of-range panic.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.