Hacktoberfest 2026：メンテナが10月に向けて印を付けた、オープンで初心者向けの issue。 Hacktoberfest の issue を見る

[Bug] Wrong results: GPORCA evaluates a window function in a correlated aggregate subquery over all groups

オープン
  • 何か問題がありますか？
#2,047 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
4/5
見積もり時間
3〜5日
初心者へのやさしさ
40/100
issue の種類
バグ
明瞭さ
明確に書かれている
活発さ
活発
技術スタック
c, postgresql, sql
領域
compilers, databases

調査の方向性

The issue is in GPORCA's decorrelation logic for subqueries containing window functions. Start by examining the query planner code that transforms correlated subqueries, particularly around GroupAggregate and WindowAgg nodes. Look at the test case in the issue to understand the wrong grouping. The fix likely involves ensuring the window function is evaluated after aggregation, not across groups. Run the provided SQL to reproduce the bug and verify with optimizer on/off.

索引モデルが issue の本文から書いたものです。

説明

type: Bug
Apache Cloudberry version

main; REL_2_STABLE

What happened

With GPORCA (optimizer = on), a correlated scalar subquery that combines an aggregate with a window function returns a wrong value. The subquery has no GROUP BY, so it produces exactly one row per outer row, and count(*) over () inside it must be 1. GPORCA decorrelates the subquery into a GroupAggregate grouped by the correlation column and puts the WindowAgg on top. The window then runs over all groups instead of the single aggregate row.

This gives a wrong value in the target list and wrong rows in WHERE. The Postgres planner (optimizer = off) computes the target-list form correctly (see below for WHERE).

What you think should happen instead

count(*) over () inside the subquery should see only the single result row of the ungrouped aggregate, as it does with the Postgres planner.

How to reproduce
create table w1(a int, b int); insert into w1 values (4,1);
create table w2(a int);        insert into w2 values (1),(1),(2);
analyze w1; analyze w2;

set optimizer = on;

select a, b, (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b) from w1;
--  a | b | ?column?
-- ---+---+----------
--  4 | 1 |        4      <-- WRONG, expected 3 (sum = 2, count(*) over () = 1)

select a, b from w1 where w1.a > (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b);
-- (0 rows)                <-- WRONG, expected 4 | 1

With optimizer = off, the target-list query returns the expected 4 | 1 | 3. The WHERE query is correct on main only when the pull-up is guarded against window functions (with patch from #1933). On REL_2_STABLE, the Postgres planner also returns 0 rows for it.

Plan with optimizer = on:

explain (costs off)
select a, b from w1 where w1.a > (select sum(w2.a) + count(*) over () from w2 where w2.a = w1.b);

 Hash Join
   Join Filter: (w1.a > ((sum(w2.a)) + count(*) OVER (?)))
   ->  WindowAgg
         ->  GroupAggregate
               Group Key: w2.a        <-- window is computed across all groups

SET optimizer = off; fixes the target-list form. On REL_2_STABLE it does not fix the WHERE form.

Operating System

any

Anything else

No response

Are you willing to submit PR?
  • Yes, I am willing to submit a PR!
Code of Conduct
主要言語
C
スター
1.4k
フォーク
248
平均マージ
4日 10時間
マージ済み PR（30日）
40

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

apache/cloudberry のほかの issue

apache/cloudberry の issue をすべて見る

似ている issue

C の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。