Repository navigation
[SQL] Support LEFT JOIN UNNEST - #7932
Merged
Merged
Conversation
anandbraman
approved these changes
Nov 10, 2026
Contributor
|
fred-review did not finish; see https://github.com/feldera/feldera/actions/runs/42923448544 |
Contributor
|
LGTM. No gate failures: tests are added, docs are updated ( What I ran (
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:
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
force-pushed
the
issue7930
branch
2 times, most recently
from
November 11, 2026 04:20
0ed74bd to
d62e0d1
Compare
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
force-pushed
the
issue7930
branch
from
November 11, 2026 05:42
d62e0d1 to
b2f1f53
Compare
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
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