[
https://issues.apache.org/jira/browse/SPARK-59608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-59608:
-----------------------------------
Labels: pull-request-available (was: )
> OptimizeWindowFunctions drops FILTER when rewriting first_value to nth_value
> ----------------------------------------------------------------------------
>
> Key: SPARK-59608
> URL: https://issues.apache.org/jira/browse/SPARK-59608
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
> Affects Versions: 5.0.0
> Environment: {code}
> Spark: 5.0.0-SNAPSHOT
> Commit: 9b4e4f7547c07c5eba3d2b0822a6d3dbb023a150
> Java: OpenJDK 17.0.17
> OS: macOS 15.7.4
> {code}
> Reporter: Yibo Dong
> Priority: Major
> Labels: pull-request-available
>
> h2. What happened
> {{first_value}} with a {{FILTER}} clause returns different results depending
> on whether {{OptimizeWindowFunctions}} is enabled.
> With the optimizer rule enabled, the filter condition is ignored and Spark
> returns the first unfiltered value in the window frame. When only
> {{OptimizeWindowFunctions}} is excluded, Spark returns the expected filtered
> result.
> h2. How to reproduce
> Run the following SQL:
> {code:sql}
> SELECT id, grp, v, flag,
> first_value(v) FILTER (WHERE flag) OVER (
> PARTITION BY grp
> ORDER BY id
> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> ) AS first_flagged
> FROM VALUES
> (1, 1, 10, false),
> (2, 1, 20, true),
> (3, 1, 30, false),
> (1, 2, 40, false),
> (2, 2, 50, true)
> AS t(id, grp, v, flag)
> ORDER BY grp, id;
> {code}
> Run it once with the default optimizer configuration:
> {code:bash}
> spark-sql
> --master 'local[2]'
> --conf spark.ui.enabled=false
> --conf spark.sql.adaptive.enabled=false
> --conf spark.sql.shuffle.partitions=1
> -f repro.sql
> {code}
> Then run the same query with only {{OptimizeWindowFunctions}} excluded:
> {code:bash}
> spark-sql
> --master 'local[2]'
> --conf spark.ui.enabled=false
> --conf spark.sql.adaptive.enabled=false
> --conf spark.sql.shuffle.partitions=1
> --conf
> spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.OptimizeWindowFunctions
>
> -f repro.sql
> {code}
> h2. Expected result
> The {{FILTER (WHERE flag)}} condition should be applied before selecting the
> first value in each window frame.
> The query should return:
> || id || grp || v || flag || first_flagged ||
> | 1 | 1 | 10 | false | NULL |
> | 2 | 1 | 20 | true | 20 |
> | 3 | 1 | 30 | false | 20 |
> | 1 | 2 | 40 | false | NULL |
> | 2 | 2 | 50 | true | 50 |
> Both optimizer configurations should produce the same result.
> h2. Actual result
> With {{OptimizeWindowFunctions}} enabled, Spark returns:
> || id || grp || v || flag || first_flagged ||
> | 1 | 1 | 10 | false | 10 |
> | 2 | 1 | 20 | true | 10 |
> | 3 | 1 | 30 | false | 10 |
> | 1 | 2 | 40 | false | 40 |
> | 2 | 2 | 50 | true | 40 |
> With only {{OptimizeWindowFunctions}} excluded, Spark returns the expected
> result:
> || id || grp || v || flag || first_flagged ||
> | 1 | 1 | 10 | false | NULL |
> | 2 | 1 | 20 | true | 20 |
> | 3 | 1 | 30 | false | 20 |
> | 1 | 2 | 40 | false | NULL |
> | 2 | 2 | 50 | true | 50 |
> The only changed setting between the two runs is:
> {code}
> spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.OptimizeWindowFunctions
> {code}
> The observed result is consistent with the {{FILTER}} condition being lost
> when {{OptimizeWindowFunctions}} rewrites the windowed {{first_value}}
> expression.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
