[jira] [Resolved] (SPARK-59434) Non-deterministic predicates should not be pushed down to CTE definitions

["Cheng Pan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-59434?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Cheng Pan resolved SPARK-59434.
-------------------------------
    Fix Version/s: 4.0.5
                   4.1.4
                   4.2.1
                   4.3.0
       Resolution: Fixed

Issue resolved by pull request 58735
[https://github.com/apache/spark/pull/58735]

> Non-deterministic predicates should not be pushed down to CTE definitions
> -------------------------------------------------------------------------
>
>                 Key: SPARK-59434
>                 URL: https://issues.apache.org/jira/browse/SPARK-59434
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 3.2.2
>            Reporter: Cheng Pan
>            Assignee: Cheng Pan
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 4.0.5, 4.1.4, 4.2.1, 4.3.0
>
>
> {{PushdownPredicatesAndPruneColumnsForCTEDef}} collects the predicates at each CTE reference and pushes their OR-merged combination into the shared CTE definition. Each reference keeps its own predicates, so a non-deterministic predicate pushed into the definition as well is evaluated a second time, which can produce wrong results.
> {code:sql}
> create or replace temp view t as select * from values (0), (1), (2) as t(c1);
> with v as (select c1, rand(1) r from t)
> select c1 from v where rand(2) < 0.5
> union all
> select c1 from v where rand(3) < 0.5;
> {code}
> The definition is non-deterministic and referenced twice, so it survives {{InlineCTE}}. {{(rand(2) < 0.5) OR (rand(3) < 0.5)}} lands in the definition while both references keep their own filter, so the optimized plan holds four {{rand}} filters instead of two, and rows are dropped twice.
> No {{MATERIALIZED}} option is needed to reach this: any definition that survives {{InlineCTE}} has the same shape.
> The rule has behaved this way since it was added in SPARK-37670 (3.2.2). Its scaladoc claimed determinism was taken care of by {{ScanOperation}}, but that was never true -- the filter-collection guard admits the first filter whatever it is. {{PhysicalOperation}}, swapped in later by SPARK-39764 (3.4.0), behaves the same way here: it returns a single filter even when it is non-deterministic.
> Fix: only push deterministic predicates into the CTE definition. When that leaves a reference with nothing pushable, the combined predicate becomes TRUE and the definition gets no push-down at all, including for its other references.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org