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 f31fc8c
Browse filesBrowse the repository at this point in the historyBrowse files
fix: pull correlated filters through Window and Unnest when safe
Address review feedback:
- Window: pull a correlated filter above the window when it reads only
plain PARTITION BY columns of every window function, so it keeps or
drops whole partitions.
- Unnest: recompute the Unnest schema so it carries the columns the pull
up adds below it. Stop when the filter reads an unnested column.
- Stop at a Window or Unnest over an aggregate with count bug handling.
- Use holds_outer_reference for Union, Sort, Extension and Limit.
- Note that DISTINCT shares the Aggregate exemption and link apache#25808.
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/lateral_join.slt
+51Lines changed: 51 additions & 0 deletions
Original file line number
Diff line number
Diff line change
@@ -810,6 +810,57 @@ ORDER BY t1.id, sub.id;
810
810
1 20 2
811
811
2 30 3
812
812
813
+
# A correlated filter below the window that reads only a `PARTITION BY` column
814
+
# keeps or drops whole partitions, so it is pulled above the window.
815
+
query TT
816
+
EXPLAIN SELECT t1.id, sub.id, sub.rn
817
+
FROM t1, LATERAL (
818
+
SELECT t2.id, row_number() OVER (PARTITION BY t2.t1_id ORDER BY t2.id) AS rn
819
+
FROM t2 WHERE t2.t1_id = t1.id
820
+
) AS sub
821
+
ORDER BY t1.id, sub.id;
822
+
----
823
+
logical_plan
824
+
01)Sort: t1.id ASC NULLS LAST, sub.id ASC NULLS LAST
825
+
02)--Projection: t1.id, sub.id, sub.rn
826
+
03)----Inner Join: t1.id = sub.t1_id
827
+
04)------TableScan: t1 projection=[id]
828
+
05)------SubqueryAlias: sub
829
+
06)--------Projection: t2.id, row_number() PARTITION BY [t2.t1_id] ORDER BY [t2.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS rn, t2.t1_id
830
+
07)----------WindowAggr: windowExpr=[[row_number() PARTITION BY [t2.t1_id] ORDER BY [t2.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
03)----ProjectionExec: expr=[id@0 as id, row_number() PARTITION BY [t2.t1_id] ORDER BY [t2.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW@2 as rn, t1_id@1 as t1_id]
836
+
04)------BoundedWindowAggExec: wdw=[row_number() PARTITION BY [t2.t1_id] ORDER BY [t2.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { "row_number() PARTITION BY [t2.t1_id] ORDER BY [t2.id ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW": UInt64 }, frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW], mode=[Sorted]
837
+
05)--------SortExec: expr=[t1_id@1 ASC NULLS LAST, id@0 ASC NULLS LAST], preserve_partitioning=[false]
Copy file name to clipboardExpand all lines: datafusion/sqllogictest/test_files/subquery.slt
+46-20Lines changed: 46 additions & 20 deletions
Original file line number
Diff line number
Diff line change
@@ -3108,9 +3108,10 @@ DROP TABLE win_outer;
3108
3108
statement ok
3109
3109
DROP TABLE win_inner;
3110
3110
3111
-
# A correlated filter below an Unnest is not pulled above the Unnest. The pull
3112
-
# up does not carry the columns of the filter through the Unnest, so it failed
3113
-
# with a schema error.
3111
+
# A correlated filter below an Unnest is pulled above the Unnest. An Unnest maps
3112
+
# each input row to its own output rows, so a filter on a column it passes
3113
+
# through keeps the same rows. The Unnest must output the columns the pull up
3114
+
# adds below it, which failed with a schema error before.
3114
3115
statement ok
3115
3116
CREATE TABLE unnest_outer(k INT) AS VALUES (1), (2);
3116
3117
@@ -3124,24 +3125,49 @@ query TT
3124
3125
EXPLAIN SELECT unnest_outer.k FROM unnest_outer WHERE EXISTS (SELECT 1 FROM (SELECT unnest(t.arr) AS u FROM (SELECT * FROM unnest_inner WHERE unnest_inner.id = unnest_outer.k) AS t) AS x WHERE x.u = 3);
3125
3126
----
3126
3127
logical_plan
3127
-
01)Filter: EXISTS (<subquery>)
3128
-
02)--Subquery:
3129
-
03)----Projection: Int64(1)
3130
-
04)------SubqueryAlias: x
3131
-
05)--------Projection: __unnest_placeholder(t.arr,depth=1) AS UNNEST(t.arr) AS u
SELECT unnest_outer.k FROM unnest_outer WHERE EXISTS (SELECT 1 FROM (SELECT unnest(t.arr) AS u FROM (SELECT * FROM unnest_inner WHERE unnest_inner.id = unnest_outer.k) AS t) AS x WHERE x.u = 3);
3141
+
----
3142
+
1
3143
+
3144
+
query I
3145
+
SELECT unnest_outer.k FROM unnest_outer WHERE NOT EXISTS (SELECT 1 FROM (SELECT unnest(t.arr) AS u FROM (SELECT * FROM unnest_inner WHERE unnest_inner.id = unnest_outer.k) AS t) AS x WHERE x.u = 3);
3146
+
----
3147
+
2
3148
+
3149
+
# The correlated column is selected next to the unnested one.
3150
+
query TT
3151
+
EXPLAIN SELECT unnest_outer.k, s.u FROM unnest_outer, LATERAL (SELECT i.id, unnest(i.arr) AS u FROM unnest_inner AS i WHERE i.id = unnest_outer.k) AS s ORDER BY unnest_outer.k, s.u;
3152
+
----
3153
+
logical_plan
3154
+
01)Sort: unnest_outer.k ASC NULLS LAST, s.u ASC NULLS LAST
3155
+
02)--Projection: unnest_outer.k, s.u
3156
+
03)----Inner Join: unnest_outer.k = s.id
3157
+
04)------TableScan: unnest_outer projection=[k]
3158
+
05)------SubqueryAlias: s
3159
+
06)--------Projection: i.id, __unnest_placeholder(i.arr,depth=1) AS UNNEST(i.arr) AS u
SELECT unnest_outer.k, s.u FROM unnest_outer, LATERAL (SELECT i.id, unnest(i.arr) AS u FROM unnest_inner AS i WHERE i.id = unnest_outer.k) AS s ORDER BY unnest_outer.k, s.u;
0 commit comments