pingcap / pingcap/tidb

TiDB returns internal error 1105 for a window over a window-derived table

Open
#70,745 4 comments 0 reactions 0 assignees View on GitHub
affects-25.10 affects-8.5 contribution severity/major sig/planner type/bug
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!

TiDB returns an internal planner error when an outer window function reads a
derived table that also computes a window function. The reduced query uses
`NTILE()` in the derived table and `FIRST_VALUE()` in the outer query. Both
window functions are in separate query blocks, so this is not a prohibited
nesting of window functions in one select list.

Both derived columns are used by the outer query. TiDB nevertheless loses an
internal column while constructing the plan and reports `Can't find column
Column#... in schema`. MySQL 9.7.1 executes the same query and returns the
expected two rows.

Removing either window function makes the query execute successfully on TiDB.
This isolates the failure to schema propagation or column pruning across the
window/derived-table boundary.

### 1. Minimal reproduce step (Required)

```sql
DROP DATABASE IF EXISTS tidb_window_schema_repro;
CREATE DATABASE tidb_window_schema_repro;
USE tidb_window_schema_repro;

CREATE TABLE t (
a INT NOT NULL
);

INSERT INTO t VALUES (1), (2);

SELECT
d.tile,
FIRST_VALUE(1) OVER (ORDER BY d.v) AS result_value
FROM (
SELECT
NTILE(2) OVER (ORDER BY a) AS tile,
2 AS v
FROM t
) AS d
ORDER BY d.tile;
```

TiDB returns:

```text
ERROR 1105 (HY000): Can't find column Column#7 in schema
Column: [Column#4,tidb_window_schema_repro.t.a,Column#5] Unique key: []
```

The internal column numbers can vary between builds, but the error remains
`Can't find column ... in schema`.

MySQL 9.7.1 returns:

```text
tile result_value
1 1
2 1
```

Removing the outer window function succeeds on TiDB:

```sql
SELECT d.tile, d.v
FROM (
SELECT
NTILE(2) OVER (ORDER BY a) AS tile,
2 AS v
FROM t
) AS d
ORDER BY d.tile;
```

```text
tile v
1 2
2 2
```

Removing the inner window function also succeeds:

```sql
SELECT
d.tile,
FIRST_VALUE(1) OVER (ORDER BY d.v) AS result_value
FROM (
SELECT a AS tile, 2 AS v
FROM t
) AS d
ORDER BY d.tile;
```

```text
tile result_value
1 1
2 1
```
### 2. What did you expect to see? (Required)

The original query should complete normally and return:

```text
tile result_value
1 1
2 1
```

A valid query must not expose an internal optimizer schema-consistency error.

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

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

```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-08-19 04:12:51
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: unistore
```

Contributor guide

Open the contributing guide

Research direction

Start by running the minimal SQL reproduction on TiDB v8.5.7 and compare it with the successful simplified queries. Trace the planner's schema propagation and column-pruning logic across the inner and outer window query blocks; done means the original query returns the two expected rows without the internal schema error.

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
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.