TiDB returns internal error 1105 for a window over a window-derived table
- 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
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