Repository navigation
[SQL] Pushdown filters through UNNEST when possible - #7970
Merged
Merged
Conversation
Contributor
|
What I ran
Missing (medium): test coverage for the rule's other branches. The PR tests only cover an inner UNNEST with one passthrough conjunct. The tests below also pass and could be added to the PR. They cover:
UnnestPushdownQATests.javapackage org.dbsp.sqlCompiler.compiler.sql.simple;
import org.dbsp.sqlCompiler.compiler.sql.tools.SqlIoTest;
import org.junit.Test;
public class UnnestPushdownQATests extends SqlIoTest {
@Test
public void outerUnnestKeepsRows() {
var ccs = this.getCCS("""
CREATE TABLE t (id INT, arr INT ARRAY);
CREATE VIEW v AS
SELECT t.id, x FROM t LEFT JOIN UNNEST(t.arr) AS x ON TRUE
WHERE t.id > 1;""");
ccs.step("INSERT INTO t VALUES (1, ARRAY(1)), (2, ARRAY()), (3, NULL), (4, ARRAY(5, 6));", """
id | x | weight
-------------------
2 |NULL | 1
3 |NULL | 1
4 | 5 | 1
4 | 6 | 1""");
}
@Test
public void mixedConjuncts() {
var ccs = this.getCCS("""
CREATE TABLE t (id INT, d INT, arr INT ARRAY);
CREATE VIEW v AS
SELECT t.id, x FROM t, UNNEST(t.arr) AS x
WHERE t.id = 1 AND 10 / t.d > 1 AND x > 2;""");
ccs.step("INSERT INTO t VALUES (1, 2, ARRAY(1, 3, 4)), (2, 0, ARRAY(1)), (1, 0, ARRAY());", """
id | x | weight
--------
1 | 3 | 1
1 | 4 | 1""");
}
@Test
public void ordinalityAndPassthrough() {
var ccs = this.getCCS("""
CREATE TABLE t (id INT, arr INT ARRAY);
CREATE VIEW v AS
SELECT t.id, x, o FROM t, UNNEST(t.arr) WITH ORDINALITY AS m(x, o)
WHERE o = 2 AND t.id IN (1, 3);""");
ccs.step("INSERT INTO t VALUES (1, ARRAY(7, 8)), (2, ARRAY(9, 10)), (3, ARRAY(11));", """
id | x | o | weight
------------
1 | 8 | 2 | 1""");
}
@Test
public void orAcrossPassthroughAndElement() {
var ccs = this.getCCS("""
CREATE TABLE t (id INT, arr INT ARRAY);
CREATE VIEW v AS
SELECT t.id, x FROM t, UNNEST(t.arr) AS x
WHERE t.id = 1 OR x = 10;""");
ccs.step("INSERT INTO t VALUES (1, ARRAY(7)), (2, ARRAY(9, 10));", """
id | x | weight
--------
1 | 7 | 1
2 | 10 | 1""");
}
@Test
public void wideningCastPushed() {
var ccs = this.getCCS("""
CREATE TABLE t (id TINYINT, s VARCHAR, arr INT ARRAY);
CREATE VIEW v AS
SELECT t.id, x FROM t, UNNEST(t.arr) AS x
WHERE CAST(t.id AS BIGINT) = 2 AND t.s = 'a';""");
ccs.step("INSERT INTO t VALUES (1, 'a', ARRAY(7)), (2, 'a', ARRAY(9)), (2, NULL, ARRAY(3)), (NULL, 'a', ARRAY(1));", """
id | x | weight
--------
2 | 9 | 1""");
}
@Test
public void unsafeCastStaysAbove() {
var ccs = this.getCCS("""
CREATE TABLE t (id BIGINT, arr INT ARRAY);
CREATE VIEW v AS
SELECT t.id, x FROM t, UNNEST(t.arr) AS x
WHERE CAST(t.id AS TINYINT) = 2;""");
ccs.step("INSERT INTO t VALUES (1000, ARRAY()), (2, ARRAY(4));", """
id | x | weight
--------
2 | 4 | 1""");
}
}Commit messages (low): The subject of the first commit runs past 72 characters and ends in "…". The second commit, which adds the new rule, has no body explaining why. |
…rst is NULL Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
mihaibudiu
force-pushed
the
unnest-filter-pushdown
branch
from
November 13, 2026 04:41
47fe1f2 to
6c25d03
Compare
xiasongh
approved these changes
Nov 13, 2026
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.
There are two commits, the first one fixes an unrelated problem with Boolean filters.
We take care to push only safe filters above UNNEST expressions.
This may make programs more expensive when most arrays are empty or NULL.