Skip to content

Fix union semantics in recursive CTEs - #19017

Merged
Mytherin merged 2 commits into
duckdb:mainfrom
cryoEncryp:fix-union-semantik
Sep 16, 2025
Merged

Fix union semantics in recursive CTEs#19017
Mytherin merged 2 commits into
duckdb:mainfrom
cryoEncryp:fix-union-semantik

Conversation

@cryoEncryp

@cryoEncryp cryoEncryp commented Sep 16, 2025

Copy link
Copy Markdown
Contributor

We found a bug relating to recursive CTEs. When the recursive keyword is used without a recursive reference, the logical planner creates a logical set operator. Typically, we would expect the UNION or UNION ALL semantics of the CTE to be retained. However, the bug causes it to always use the UNION ALL semantic, resulting in differences in queries that we wouldn't expect.

WITH RECURSIVE rec(a, b, c) AS (
  SELECT a,b,c 
  FROM (VALUES(1,2,3),(1,2,3)) s(a,b,c) 
    UNION 
  SELECT 1,2,3) 
TABLE rec;
┌───────┬───────┬───────┐
│   a   │   b   │   c   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │     2 │     3 │
│     1 │     2 │     3 │
│     1 │     2 │     3 │
└───────┴───────┴───────┘
WITH rec(a, b, c) AS (
  SELECT a,b,c 
  FROM (VALUES(1,2,3),(1,2,3)) s(a,b,c) 
    UNION 
  SELECT 1,2,3) 
TABLE rec;
┌───────┬───────┬───────┐
│   a   │   b   │   c   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│   1   │   2   │   3   │
└───────┴───────┴───────┘

Rather than using the UNION semantics specified in the recursive anchor, we use UNION ALL.

This PR fixes the deviation by using the UNION semantics given in the CTE.

@cryoEncryp cryoEncryp changed the title Fix union semantik in recursive CTEs Fix union semantic in recursive CTEs Sep 16, 2025
@cryoEncryp cryoEncryp changed the title Fix union semantic in recursive CTEs Fix union semantics in recursive CTEs Sep 16, 2025
@Teggy

Teggy commented Sep 16, 2025

Copy link
Copy Markdown
Contributor

Thanks to Thomas Neumann who pointed us at this bug.

@duckdb-draftbot
duckdb-draftbot marked this pull request as draft September 16, 2025 13:12
@cryoEncryp
cryoEncryp marked this pull request as ready for review September 16, 2025 13:12
@Mytherin
Mytherin changed the base branch from main to v1.4-andium September 16, 2025 18:55
@Mytherin
Mytherin changed the base branch from v1.4-andium to main September 16, 2025 18:55
@Mytherin
Mytherin merged commit 05a2403 into duckdb:main Sep 16, 2025
60 checks passed
@Mytherin

Mytherin commented Sep 16, 2025

Copy link
Copy Markdown
Collaborator

Thanks!

github-actions Bot pushed a commit to duckdb/duckdb-r that referenced this pull request Sep 16, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@05a2403
0f65c51 
Fix union semantics in recursive CTEs (duckdb/duckdb#19017)
Expected errors 2053 (duckdb/duckdb#18960)
@github-actions github-actions Bot mentioned this pull request Sep 16, 2025
Merged
github-actions Bot added a commit to duckdb/duckdb-r that referenced this pull request Sep 16, 2025
@github-actions @krlmlr
vendor: Update vendored sources to duckdb/duckdb@05a2403 (#1566)
b58018b 
Fix union semantics in recursive CTEs (duckdb/duckdb#19017)
Expected errors 2053 (duckdb/duckdb#18960)

Co-authored-by: krlmlr <krlmlr@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@cryoEncryp @Teggy @Mytherin