Bug #121645 Wrong correlated COUNT result with a window function and redundant GROUP BY
Description:
MySQL Community Server 9.7.1 returns an incorrect `COUNT(*)` value from a correlated scalar subquery when the outer query combines `DENSE_RANK()` with grouping by its primary key. The first row in window order receives a count of `0` even though one inner row satisfies the correlation predicate. The query completes successfully without reporting a SQL error.
The reduced reproduction uses two temporary tables and three rows. Removing the outer GROUP BY, removing the window function, or replacing the constant HAVING condition with an always-true aggregate condition returns the correct counts. Reversing the window order moves the incorrect count to the other outer row.
## Expected behaviour
Both outer values, `'a'` and `'b'`, differ from the only inner value, `'z'`. The correlated COUNT must therefore return `1` for both outer rows. Each outer primary key identifies a singleton group, and HAVING retains both groups.
Expected rows, displayed by increasing rank:
```text
+----+---+---+
| id | r | n |
+----+---+---+
| 2 | 1 | 1 |
| 1 | 2 | 1 |
+----+---+---+
```
The GROUP BY includes both outer columns and satisfies `ONLY_FULL_GROUP_BY`.
## Actual behaviour
```text
+----+---+---+
| id | r | n |
+----+---+---+
| 2 | 1 | 0 |
| 1 | 2 | 1 |
+----+---+---+
```
The count for `id = 2` is incorrect. Adding an outer `ORDER BY r` produces the same incorrect values, so the discrepancy is not a difference in output row order.
## Execution plans
`EXPLAIN FORMAT=TREE` for the failing query:
```text
-> Window aggregate: dense_rank() OVER (ORDER BY o.v ) (cost=0 rows=2)
-> Sort: o.v (cost=1.2 rows=2)
-> Table scan on o (cost=1.2 rows=2)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(0) (cost=0.58 rows=1)
-> Filter: (o.v <> i.w) (cost=0.35 rows=1)
-> Table scan on i (cost=0.35 rows=1)
```
For the equivalent `HAVING VARIANCE(o.id) <> 71` control:
```text
-> Window aggregate: dense_rank() OVER (ORDER BY o.v )
-> Filter: (variance(o.id) <> 71)
-> Sort: o.v
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Table scan on o (cost=1.2 rows=2)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(0) (cost=0.58 rows=1)
-> Filter: (o.v <> i.w) (cost=0.35 rows=1)
-> Table scan on i (cost=0.35 rows=1)
```
The failing plan eliminates grouping and places the dependent subquery under the window iterator. The successful control retains aggregation through a temporary table. These observations identify a failing execution path involving redundant-group elimination, window evaluation, and the correlated aggregate; they do not establish the exact internal implementation cause.
How to repeat:
CREATE TEMPORARY TABLE valscope_window_outer (
id INT NOT NULL PRIMARY KEY,
v VARCHAR(10)
);
CREATE TEMPORARY TABLE valscope_window_inner (w VARCHAR(10));
INSERT INTO valscope_window_outer VALUES (1, 'b'), (2, 'a');
INSERT INTO valscope_window_inner VALUES ('z');
SELECT o.id,
DENSE_RANK() OVER (ORDER BY o.v) AS r,
(SELECT COUNT(*)
FROM valscope_window_inner AS i
WHERE o.v <> i.w) AS n
FROM valscope_window_outer AS o
GROUP BY o.id, o.v
HAVING 1 = 1;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL Community Server 9.7.1 returns an incorrect `COUNT(*)` value from a correlated scalar subquery when the outer query combines `DENSE_RANK()` with grouping by its primary key. The first row in window order receives a count of `0` even though one inner row satisfies the correlation predicate. The query completes successfully without reporting a SQL error. The reduced reproduction uses two temporary tables and three rows. Removing the outer GROUP BY, removing the window function, or replacing the constant HAVING condition with an always-true aggregate condition returns the correct counts. Reversing the window order moves the incorrect count to the other outer row. ## Expected behaviour Both outer values, `'a'` and `'b'`, differ from the only inner value, `'z'`. The correlated COUNT must therefore return `1` for both outer rows. Each outer primary key identifies a singleton group, and HAVING retains both groups. Expected rows, displayed by increasing rank: ```text +----+---+---+ | id | r | n | +----+---+---+ | 2 | 1 | 1 | | 1 | 2 | 1 | +----+---+---+ ``` The GROUP BY includes both outer columns and satisfies `ONLY_FULL_GROUP_BY`. ## Actual behaviour ```text +----+---+---+ | id | r | n | +----+---+---+ | 2 | 1 | 0 | | 1 | 2 | 1 | +----+---+---+ ``` The count for `id = 2` is incorrect. Adding an outer `ORDER BY r` produces the same incorrect values, so the discrepancy is not a difference in output row order. ## Execution plans `EXPLAIN FORMAT=TREE` for the failing query: ```text -> Window aggregate: dense_rank() OVER (ORDER BY o.v ) (cost=0 rows=2) -> Sort: o.v (cost=1.2 rows=2) -> Table scan on o (cost=1.2 rows=2) -> Select #2 (subquery in projection; dependent) -> Aggregate: count(0) (cost=0.58 rows=1) -> Filter: (o.v <> i.w) (cost=0.35 rows=1) -> Table scan on i (cost=0.35 rows=1) ``` For the equivalent `HAVING VARIANCE(o.id) <> 71` control: ```text -> Window aggregate: dense_rank() OVER (ORDER BY o.v ) -> Filter: (variance(o.id) <> 71) -> Sort: o.v -> Table scan on <temporary> -> Aggregate using temporary table -> Table scan on o (cost=1.2 rows=2) -> Select #2 (subquery in projection; dependent) -> Aggregate: count(0) (cost=0.58 rows=1) -> Filter: (o.v <> i.w) (cost=0.35 rows=1) -> Table scan on i (cost=0.35 rows=1) ``` The failing plan eliminates grouping and places the dependent subquery under the window iterator. The successful control retains aggregation through a temporary table. These observations identify a failing execution path involving redundant-group elimination, window evaluation, and the correlated aggregate; they do not establish the exact internal implementation cause. How to repeat: CREATE TEMPORARY TABLE valscope_window_outer ( id INT NOT NULL PRIMARY KEY, v VARCHAR(10) ); CREATE TEMPORARY TABLE valscope_window_inner (w VARCHAR(10)); INSERT INTO valscope_window_outer VALUES (1, 'b'), (2, 'a'); INSERT INTO valscope_window_inner VALUES ('z'); SELECT o.id, DENSE_RANK() OVER (ORDER BY o.v) AS r, (SELECT COUNT(*) FROM valscope_window_inner AS i WHERE o.v <> i.w) AS n FROM valscope_window_outer AS o GROUP BY o.id, o.v HAVING 1 = 1;