Yibo Dong created SPARK-59608:
---------------------------------
Summary: 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
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]
