← 输入框|github.com|刷新
Skip to content

[SQL] Support the PostgresSQL extension DISTINCT ON - #7904

Merged
mihaibudiu merged 2 commits into
mainfrom
issue7644
Nov 13, 2026
Merged

mihaibudiu merged 2 commits into
mainfrom
issue7644

Conversation

@mihaibudiu

Copy link
Copy Markdown
Contributor

Linked Issue

Fixes #7644

This branch is rebased already on #6599 which advances the Calcite version, and which is supposed to merge first.
Calcite already rewrites DISTINCT ON into other operators we support; this PR just enables the transformation and adds tests.

Checklist

  • Unit tests added/updated
  • Documentation updated

Comment thread docs.feldera.com/docs/sql/grammar.md Resolved
@mythical-fred-oss

Copy link
Copy Markdown
Contributor

What I ran: ./build.sh (with Calcite at 4bf922b), then mvn test -Dtest=TopKTests,FloatingPointEqualityTests,NegativeParserTests,ArrayTests,DocumentationTests and pre-commit run on the changed files. pre-commit passes, and so do all the new tests.

bug (high): NegativeParserTests.errorTest fails. The Calcite bump changes what "This is not SQL" reports. It used to be Non-query expression encountered in illegal context; now it is Encountered "not SQL". Was expecting one of: .... I set isDistinctOnAllowed() back to false and the test still failed, so the bump causes it, not DISTINCT ON. It probably affects #6599 too. Please update the test's expected message.

missing (medium): test coverage. I wrote these QA cases as incremental tests (StreamingTestBase). None are in the PR:

Case Result
Insert a better row (old row retracted), delete it (runner-up comes back), delete a group's last row ✅
Expression key DISTINCT ON (a % 2) ✅
Multiple keys (a, b) ✅
Key is a SELECT alias ✅
DISTINCT ON over GROUP BY / aggregate ✅
ORDER BY has only the key (every row ties): one row per group ✅
Keys are not a prefix of ORDER BY: rejected ✅ (no negative test in PR)
ORDER BY 1, 2 ordinals, or keys in a different order ❌ rejected; see inline comment

The PR's distinctOn tests only run in the non-incremental SqlIoTest, so nothing checks retractions. I suggest adding at least the retraction case to TopKUnusedFieldsIncrementalTests or a StreamingTestBase class:

@Test
public void distinctOnIncremental() {
    var ccs = this.getCCS("""
            CREATE TABLE T(a INT, b INT, c INT);
            CREATE VIEW V AS SELECT DISTINCT ON (a) a, b, c FROM T ORDER BY a, b DESC;""");
    ccs.step("INSERT INTO T VALUES (1, 1, 10), (1, 2, 20), (2, 5, 30);", """
             a | b | c  | weight
            ---------------------
             1 | 2 | 20 | 1
             2 | 5 | 30 | 1""");
    ccs.step("INSERT INTO T VALUES (1, 3, 40);", """
             a | b | c  | weight
            ---------------------
             1 | 2 | 20 | -1
             1 | 3 | 40 | 1""");
    ccs.step("REMOVE FROM T VALUES (1, 3, 40);", """
             a | b | c  | weight
            ---------------------
             1 | 3 | 40 | -1
             1 | 2 | 20 | 1""");
}

Also, this PR still contains the UNNEST commit from #6599, so it should be rebased once #6599 merges.

…. = 1'

Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
Fixes #7644

Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
@mihaibudiu
mihaibudiu added this pull request to the merge queue Nov 13, 2026
Merged via the queue into main with commit a4fb882 Nov 13, 2026
1 check passed
@mihaibudiu
mihaibudiu deleted the issue7644 branch November 13, 2026 21:58
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

[SQL] Support DISTINCT ON window queries

2 participants