pingcap / pingcap/tidb

TiDB loses an unprojected GROUP BY key in a correlated HAVING subquery

Open
#70,746 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-25.10 may-affects-26.3 may-affects-7.5 may-affects-8.1 may-affects-8.5 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 incorrectly returns error 1054 when a correlated subquery in `HAVING`
references an outer `GROUP BY` key that is not also present in the outer select
list. The grouped column is valid in the outer query block and must remain
visible to the correlated subquery independently of whether it is projected.

Adding the same group key to the select list makes the query succeed without
changing the grouping or `HAVING` predicate. MySQL 9.7.1 executes both forms
under `ONLY_FULL_GROUP_BY`; the unprojected form returns the expected aggregate
row. This shows that the failure is caused by TiDB's grouped-column visibility
or decorrelation logic rather than an invalid column reference.

### 1. Minimal reproduce step (Required)

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

CREATE TABLE t (
g INT NOT NULL,
v INT NOT NULL
);

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

SELECT SUM(o.v) AS total
FROM t AS o
GROUP BY o.g
HAVING EXISTS (
SELECT 1
FROM t AS i
WHERE i.g = o.g
AND i.v = 10
);
```

TiDB returns:

```text
ERROR 1054 (42S22): Unknown column 'o.g' in 'having clause'
```

MySQL 9.7.1 returns the expected result:

```text
total
10
```

Projecting the already-grouped correlation key is a workaround on TiDB:

```sql
SELECT o.g, SUM(o.v) AS total
FROM t AS o
GROUP BY o.g
HAVING EXISTS (
SELECT 1
FROM t AS i
WHERE i.g = o.g
AND i.v = 10
);
```

TiDB then returns:

```text
g total
1 10
```

The session used for reproduction included `ONLY_FULL_GROUP_BY`:

```text
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
```

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

An outer grouping key remains a valid correlation source even when it is not
part of the select list. The original query should complete and return one row
whose `total` is `10`.

### 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 reproducing the SQL query on TiDB v8.5.7, then trace the grouped-column visibility or decorrelation logic involved in the HAVING subquery. Done means the unprojected GROUP BY form returns the row with total 10, while the projected workaround continues to work; add a regression test covering both forms.

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
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.