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

[SQL] Support LEFT JOIN UNNEST - #7932

Merged
mihaibudiu merged 3 commits into
mainfrom
issue7930
Nov 11, 2026
Merged

mihaibudiu merged 3 commits into
mainfrom
issue7930

Conversation

@mihaibudiu

Copy link
Copy Markdown
Contributor

Linked Issue

Fixes #7930

This is made much simpler by the recently merged #6599
There are two additional commits making small improvements and bug fixes.

Checklist

  • Unit tests added/updated
  • Documentation updated

@mythical-fred-oss

Copy link
Copy Markdown
Contributor

fred-review did not finish; see https://github.com/feldera/feldera/actions/runs/42923448544

@mythical-fred-oss

Copy link
Copy Markdown
Contributor

LGTM. No gate failures: tests are added, docs are updated (array.md, unsupported-operations.md), and the change touches no deps, unsafe code or CI.

What I ran (./build.sh, then mvn test -pl SQL-compiler)

Suite Result
PR's new/changed tests (ArrayTests#testLeftJoin*, SerializationTests#testLeftJoinUnnest, Regression3Tests x2, RegressionTests#issue2736b, IncrementalRegressionTests#unnestOrdinalityLateness) ✅
Full ArrayTests, Regression3Tests, SerializationTests (77 tests; covers the new PruneEmptyRules.CORRELATE_*) ✅ (1 pre-existing skip)
11 extra QA cases (below) ✅

missing (low–medium): cases the PR's tests don't cover. I checked each one end to end and they all pass. They might be worth adding as regression guards:

  • a projection over the outer UNNEST (m.x + 1), and a WHERE m.x IS NULL filter after the join
  • only left columns selected (multiplicity: an array of 2 elements gives 2 rows, NULL/empty gives 1)
  • COUNT(m.x) vs COUNT(*) grouped after LEFT JOIN UNNEST
  • two chained LEFT JOIN UNNESTs
  • a struct array WITH ORDINALITY (struct + ordinality + outer is not tested)
  • an ARRAY NOT NULL column with an empty array, and arrays of arrays
  • LATENESS column + LEFT JOIN UNNEST WITH ORDINALITY (outer path through the monotonicity analysis)
  • LEFT JOIN LATERAL (SELECT x+1 FROM UNNEST(...)) reports "Not yet implemented" (no internal error)
Test file used (LeftUnnestQATests.java, excerpt)
@Test public void aggregate() {
    var ccs = this.getCCS(DDL + """
            CREATE VIEW v AS SELECT d.id, COUNT(m.x) AS c, COUNT(*) AS n
            FROM data d LEFT JOIN UNNEST(d.arr) AS m(x) ON TRUE GROUP BY d.id;""");
    ccs.stepWeightOne(INSERT, """
             id | c | n
            ------------
             1  | 2 | 2
             2  | 0 | 1
             3  | 0 | 1""");
}
@Test public void twoLeftUnnests() {
    var ccs = this.getCCS(DDL + """
            CREATE VIEW v AS SELECT d.id, m.x, n.y FROM data d
            LEFT JOIN UNNEST(d.arr) AS m(x) ON TRUE
            LEFT JOIN UNNEST(d.arr2) AS n(y) ON TRUE;""");
    ccs.stepWeightOne(INSERT, """
             id | x   | y
            --------------
             1  | 10  | 7
             1  | 20  | 7
             2  |NULL |NULL
             3  |NULL | 8
             3  |NULL | 9""");
}
@Test public void structOrdinality() {
    var ccs = this.getCCS("""
            CREATE TYPE elem AS (a INT, b VARCHAR);
            CREATE TABLE data(id INT NOT NULL, rarr elem ARRAY);
            CREATE VIEW v AS SELECT d.id, e.a, e.b, e.o
            FROM data d LEFT JOIN UNNEST(d.rarr) WITH ORDINALITY AS e(a, b, o) ON TRUE;""").withStringTrim();
    ccs.stepWeightOne("INSERT INTO data VALUES (1, ARRAY[ROW(1, 'a'), NULL]), (2, ARRAY()), (3, NULL);", """
             id | a   | b    | o
            -----------------------
             1  | 1   | a    | 1
             1  |NULL |NULL  | 2
             2  |NULL |NULL  |NULL
             3  |NULL |NULL  |NULL""");
}
// DDL: CREATE TABLE data(id INT NOT NULL, arr INT ARRAY, arr2 INT ARRAY);
// INSERT: (1, ARRAY[10, 20], ARRAY[7]), (2, ARRAY(), NULL), (3, NULL, ARRAY[8, 9])

@mihaibudiu
mihaibudiu force-pushed the issue7930 branch 2 times, most recently from 0ed74bd to d62e0d1 Compare November 11, 2026 04:20
@mihaibudiu
mihaibudiu added this pull request to the merge queue Nov 11, 2026
@github-merge-queue
github-merge-queue Bot removed this pull request from the merge queue due to a conflict with the base branch Nov 11, 2026
Use Calcite's rules:
PruneEmptyRules.CORRELATE_LEFT_INSTANCE and CORRELATE_RIGHT_INSTANCE.

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

A join condition other than TRUE, and LEFT JOIN LATERAL of a subquery over
UNNEST, are still not supported. Any other LEFT correlate that the compiler
cannot implement is now reported as a decorrelation failure, not as
LEFT JOIN UNNEST.

Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
@mihaibudiu
mihaibudiu added this pull request to the merge queue Nov 11, 2026
Merged via the queue into main with commit 275e49b Nov 11, 2026
1 check passed
@mihaibudiu
mihaibudiu deleted the issue7930 branch November 11, 2026 06:45
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 LEFT JOIN UNNEST

2 participants