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

[SQL] Pushdown filters through UNNEST when possible - #7970

Merged
mihaibudiu merged 2 commits into
mainfrom
unnest-filter-pushdown
Nov 13, 2026
Merged

mihaibudiu merged 2 commits into
mainfrom
unnest-filter-pushdown

Conversation

@mihaibudiu

Copy link
Copy Markdown
Contributor

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.

@mythical-fred-oss

Copy link
Copy Markdown
Contributor

What I ran

Command Result
./build.sh, then mvn test -Dtest=RexSafetyTests and the 3 new RegressionTests ✅ all pass
ArrayTests, ReorderTemporalFiltersTests, TemporalFilterTests ✅ 41 pass, 1 skipped
StreamingTests, LatenessTests, InternalNowTests (covers MergeFilters) ✅ 115 pass
My new QA tests (below) ✅ 6 pass. I checked that a wrong expected value makes them fail.

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:

  • an outer UNNEST (LEFT JOIN UNNEST ... ON TRUE) with empty and NULL arrays
  • a safe conjunct, an unsafe conjunct (10 / t.d) and an element conjunct together
  • WITH ORDINALITY with a filter on the ordinality column
  • an OR across a passthrough column and an element
  • a widening cast that is pushed (TINYINT→BIGINT) and a narrowing cast that stays above (BIGINT→TINYINT on an empty array)
UnnestPushdownQATests.java
package 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
mihaibudiu force-pushed the unnest-filter-pushdown branch from 47fe1f2 to 6c25d03 Compare November 13, 2026 04:41
@mihaibudiu
mihaibudiu added this pull request to the merge queue Nov 13, 2026
Merged via the queue into main with commit 27ae32f Nov 13, 2026
1 check passed
@mihaibudiu
mihaibudiu deleted the unnest-filter-pushdown branch November 13, 2026 06:51
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.

2 participants