You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 1a2ad03
Browse filesBrowse the repository at this point in the historyBrowse files
feat: decorrelate a window below a correlated equality filter
When a correlated filter below a Window fixes the value of each column it
reads for each outer row (`inner.y = outer.k`), add those columns to the
PARTITION BY of every window function and pull the filter above the
window. This is what Spark and DuckDB do, and it decorrelates apache#25792. A
Projection on top keeps the old window column names. Other filters on
non-partition columns still keep the subquery correlated.
Co-Authored-By: Claude Opus 5.5 <[email protected]>
Claude-Session: https://claude.ai/code/session_01NZEssuMQANC3qLy1k2zUus
Copy file name to clipboardExpand all lines: datafusion/sqllogictest/test_files/subquery.slt
+43-27Lines changed: 43 additions & 27 deletions
Original file line number
Diff line number
Diff line change
@@ -3032,10 +3032,11 @@ DROP TABLE oj_a;
3032
3032
statement ok
3033
3033
DROP TABLE oj_b;
3034
3034
3035
-
# Regression test for #25792: a correlated filter below a window function must
3036
-
# not be pulled above the window. The filter selects the rows the window
3037
-
# function sees for each outer row, so once it is above the window the window
3038
-
# function runs over all rows and computes different values.
3035
+
# Regression test for #25792: a correlated filter below a window function
3036
+
# selects the rows the window function sees for each outer row. Pulled above
3037
+
# the window as it is, the window function would run over all rows and compute
3038
+
# different values. The filter `y = k` fixes `y` for each outer row, so the pull
3039
+
# up adds `y` to the `PARTITION BY` of the window, which keeps the values.
3039
3040
statement ok
3040
3041
CREATE TABLE win_outer(k INT) AS VALUES (1), (2), (5);
3041
3042
@@ -3045,39 +3046,49 @@ CREATE TABLE win_inner(id INT, y INT) AS VALUES (1, 1), (2, 2);
3045
3046
statement ok
3046
3047
set datafusion.explain.logical_plan_only = true;
3047
3048
3048
-
# The correlated filter is below the window, so the subquery stays correlated.
3049
+
# The correlated filter is below the window, so the window is partitioned by b.y.
3049
3050
query TT
3050
3051
EXPLAIN SELECT win_outer.k FROM win_outer WHERE EXISTS (SELECT 1 FROM (SELECT b.id, row_number() OVER (ORDER BY b.id) AS rn FROM (SELECT * FROM win_inner WHERE win_inner.y = win_outer.k) AS b) AS w WHERE w.rn = 1);
3051
3052
----
3052
3053
logical_plan
3053
-
01)Filter: EXISTS (<subquery>)
3054
-
02)--Subquery:
3055
-
03)----Projection: Int64(1)
3056
-
04)------SubqueryAlias: w
3057
-
05)--------Projection: b.id, row_number() ORDER BY [b.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS rn
3058
-
06)----------Filter: row_number() ORDER BY [b.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = UInt64(1)
3059
-
07)------------WindowAggr: windowExpr=[[row_number() ORDER BY [b.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
06)--------Filter: row_number() PARTITION BY [b.y] ORDER BY [b.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = UInt64(1)
3060
+
07)----------Projection: b.y, row_number() PARTITION BY [b.y] ORDER BY [b.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
3061
+
08)------------WindowAggr: windowExpr=[[row_number() PARTITION BY [b.y] ORDER BY [b.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
SELECT win_outer.k FROM win_outer WHERE EXISTS (SELECT 1 FROM (SELECT b.id, row_number() OVER (ORDER BY b.id) AS rn FROM (SELECT * FROM win_inner WHERE win_inner.y = win_outer.k) AS b) AS w WHERE w.rn = 1) ORDER BY win_outer.k;
3068
+
----
3069
+
1
3070
+
2
3069
3071
3070
3072
# The same for NOT EXISTS. The right answer is `5`.
3071
-
statement error DataFusion error: This feature is not implemented: Physical plan does not support logical expression Exists
3073
+
query I
3072
3074
SELECT win_outer.k FROM win_outer WHERE NOT EXISTS (SELECT 1 FROM (SELECT b.id, row_number() OVER (ORDER BY b.id) AS rn FROM (SELECT * FROM win_inner WHERE win_inner.y = win_outer.k) AS b) AS w WHERE w.rn = 1) ORDER BY win_outer.k;
3075
+
----
3076
+
5
3073
3077
3074
3078
# The same for IN. The right answer is `1, 2`.
3075
-
statement error DataFusion error: This feature is not implemented: Physical plan does not support logical expression InSubquery
3079
+
query I
3076
3080
SELECT win_outer.k FROM win_outer WHERE 1 IN (SELECT row_number() OVER (ORDER BY win_inner.id) FROM win_inner WHERE win_inner.y = win_outer.k) ORDER BY win_outer.k;
3081
+
----
3082
+
1
3083
+
2
3077
3084
3078
3085
# And for a scalar subquery. The right answer is `(1, 1), (2, 1), (5, NULL)`.
3079
-
statement error DataFusion error: This feature is not implemented: Physical plan does not support logical expression ScalarSubquery
3086
+
query II
3080
3087
SELECT win_outer.k, (SELECT max(w.rn) FROM (SELECT row_number() OVER (ORDER BY win_inner.id) AS rn FROM win_inner WHERE win_inner.y = win_outer.k) AS w) AS m FROM win_outer ORDER BY win_outer.k;
3088
+
----
3089
+
1 1
3090
+
2 1
3091
+
5 NULL
3081
3092
3082
3093
# A correlated filter above the window is still pulled up, because the window
3083
3094
# input does not depend on the outer row.
@@ -3100,8 +3111,8 @@ SELECT win_outer.k FROM win_outer WHERE EXISTS (SELECT 1 FROM (SELECT win_inner.
3100
3111
2
3101
3112
3102
3113
# A correlated filter below the window that reads only a `PARTITION BY` column
3103
-
# keeps or drops whole partitions, so it is pulled above the window. This holds
3104
-
# for a non-equality filter too. The right answer is `1`.
3114
+
# keeps or drops whole partitions, so it is pulled above the window as it is.
3115
+
# This holds for a non-equality filter too. The right answer is `1`.
3105
3116
query I
3106
3117
SELECT win_outer.k FROM win_outer WHERE EXISTS (SELECT 1 FROM (SELECT row_number() OVER (PARTITION BY win_inner.y ORDER BY win_inner.id) AS rn FROM win_inner WHERE win_inner.y > win_outer.k) AS w WHERE w.rn = 1) ORDER BY win_outer.k;
3107
3118
----
@@ -3122,9 +3133,14 @@ SELECT win_outer.k, (SELECT max(w.rn) FROM (SELECT row_number() OVER (PARTITION
3122
3133
2 1
3123
3134
5 NULL
3124
3135
3125
-
# A window that partitions by an expression of the column does not count.
3126
-
statement error DataFusion error: This feature is not implemented: Physical plan does not support logical expression ScalarSubquery
3136
+
# A window that partitions by an expression of the column is partitioned by
3137
+
# the column as well.
3138
+
query II
3127
3139
SELECT win_outer.k, (SELECT max(w.rn) FROM (SELECT row_number() OVER (PARTITION BY win_inner.y + 0 ORDER BY win_inner.id) AS rn FROM win_inner WHERE win_inner.y = win_outer.k) AS w) AS m FROM win_outer ORDER BY win_outer.k;
3140
+
----
3141
+
1 1
3142
+
2 1
3143
+
5 NULL
3128
3144
3129
3145
# A window over an aggregate with count bug handling stays correlated.
3130
3146
statement error DataFusion error: This feature is not implemented: Physical plan does not support logical expression ScalarSubquery
0 commit comments