pingcap / pingcap/tidb

An aggregate window function inside a view breaks correlated quantified subqueries (`< ANY` / `= ALL` / `IN (SELECT …)`)

Open
#70,362 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/critical sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report
When a relation is a **view whose body computes an aggregate window function**
(`MAX(col) OVER (PARTITION BY k)`), a query that outer-joins that relation to itself and filters with a
**correlated quantified comparison subquery** returns wrong results. The correlated predicate inside
the subquery stops constraining it, which might be unexpected

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE k (id BIGINT, name VARCHAR(255), created_at VARCHAR(255), eq_key_1 BIGINT);
INSERT INTO k (id, name, created_at, eq_key_1) VALUES (1, 'a', 'a', 1);
INSERT INTO k (id, name, created_at, eq_key_1) VALUES (NULL, 'b', 'b', 2);

CREATE VIEW t AS SELECT id, name, created_at FROM (
SELECT eq_key_1,
MAX(id) OVER (PARTITION BY eq_key_1) AS id,
MAX(name) OVER (PARTITION BY eq_key_1) AS name,
MAX(created_at) OVER (PARTITION BY eq_key_1) AS created_at
FROM k) AS w;

SELECT t2.id
FROM t AS t2 RIGHT OUTER JOIN t AS t3 ON t2.id >= t3.id
WHERE (t2.id IS NULL) < ANY (SELECT 1 FROM t AS t4 WHERE t3.id IS NULL)
GROUP BY t2.id;
-- Expected: 0 rows. Actual: 1 row, (1).
```

### 2. What did you expect to see? (Required)
0 rows (MySQL also returns 0 rows)
### 3. What did you see instead (Required)
1 row, (1).
### 4. What is your TiDB version? (Required)

tidb `8.0.11-TiDB-v9.0.0-beta.2.pre-2051-g3bea8196a5` @ `3bea8196`

Contributor guide

Open the contributing guide

Research direction

Start by running the provided CREATE TABLE, CREATE VIEW, and SELECT statements on the reported TiDB version, then compare the result with MySQL or the expected zero-row result. Trace the handling of correlated quantified subqueries when the referenced view contains aggregate window functions; done means the query returns zero rows without regressing related subquery and window-function cases.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
50/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.