Jefffrey commented on issue #14540: URL: https://github.com/apache/datafusion/issues/14540#issuecomment-4931534023
reproduction
```sql
CREATE OR REPLACE TABLE t1 (
date DATE,
timestamp TIMESTAMP,
ids STRUCT<
id1 VARCHAR,
extra INT4
>,
structs STRUCT<
var1 VARCHAR,
extra VARCHAR
>
);
explain verbose WITH events AS (
SELECT
ids.id1 as device,
structs.var1 as user,
timestamp
FROM t1
WHERE
date='2025-01-03'
)
SELECT
*,
LAG(user, 1) OVER (PARTITION BY device ORDER BY timestamp) AS prev
FROM events
WHERE
device IS NOT NULL AND device != ''
AND user IS NOT NULL AND user != ''
LIMIT 100;
```
looks like right now its `push_down_filter` and `push_down_leaf_projections`
fighting with each other
```
| logical_plan after push_down_filter | Projection:
events.device, events.user, events.timestamp, lag(events.user,Int64(1))
PARTITION BY [events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS prev |
| | Limit:
skip=0, fetch=100
|
| |
WindowAggr: windowExpr=[[lag(events.user, Int64(1)) PARTITION BY
[events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW]] |
| |
SubqueryAlias: events
|
| |
Filter: device IS NOT NULL AND device != Utf8View("") AND user IS NOT NULL AND
user != Utf8View("")
|
| |
Projection: get_field(t1.ids, Utf8("id1")) AS device, get_field(t1.structs,
Utf8("var1")) AS user, t1.timestamp
|
| |
Filter: t1.date = Date32("2025-01-03")
|
| |
TableScan: t1
|
| logical_plan after single_distinct_aggregation_to_group_by | SAME TEXT AS
ABOVE
|
...
| logical_plan after extract_leaf_expressions | SAME TEXT AS
ABOVE
|
| logical_plan after push_down_leaf_projections | Projection:
events.device, events.user, events.timestamp, lag(events.user,Int64(1))
PARTITION BY [events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS prev |
| | Limit:
skip=0, fetch=100
|
| |
WindowAggr: windowExpr=[[lag(events.user, Int64(1)) PARTITION BY
[events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW]] |
| |
SubqueryAlias: events
|
| |
Filter: device IS NOT NULL AND device != Utf8View("") AND user IS NOT NULL AND
user != Utf8View("")
|
| |
Projection: __datafusion_extracted_1 AS device, __datafusion_extracted_2 AS
user, t1.timestamp
|
| |
Filter: t1.date = Date32("2025-01-03")
|
| |
Projection: get_field(t1.ids, Utf8("id1")) AS __datafusion_extracted_1,
get_field(t1.structs, Utf8("var1")) AS __datafusion_extracted_2, t1.date,
t1.timestamp, t1.ids, t1.structs |
| |
TableScan: t1
|
| logical_plan after optimize_projections | Projection:
events.device, events.user, events.timestamp, lag(events.user,Int64(1))
PARTITION BY [events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS prev |
| | Limit:
skip=0, fetch=100
|
| |
WindowAggr: windowExpr=[[lag(events.user, Int64(1)) PARTITION BY
[events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW]] |
| |
SubqueryAlias: events
|
| |
Filter: device IS NOT NULL AND device != Utf8View("") AND user IS NOT NULL AND
user != Utf8View("")
|
| |
Projection: __datafusion_extracted_1 AS device, __datafusion_extracted_2 AS
user, t1.timestamp
|
| |
Filter: t1.date = Date32("2025-01-03")
|
| |
Projection: get_field(t1.ids, Utf8("id1")) AS __datafusion_extracted_1,
get_field(t1.structs, Utf8("var1")) AS __datafusion_extracted_2, t1.date,
t1.timestamp |
| |
TableScan: t1 projection=[date, timestamp, ids, structs]
|
| logical_plan after rewrite_set_comparison | SAME TEXT AS
ABOVE
|
...
| logical_plan after push_down_limit | SAME TEXT AS
ABOVE
|
| logical_plan after push_down_filter | Projection:
events.device, events.user, events.timestamp, lag(events.user,Int64(1))
PARTITION BY [events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS prev |
| | Limit:
skip=0, fetch=100
|
| |
WindowAggr: windowExpr=[[lag(events.user, Int64(1)) PARTITION BY
[events.device] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW]] |
| |
SubqueryAlias: events
|
| |
Projection: __datafusion_extracted_1 AS device, __datafusion_extracted_2 AS
user, t1.timestamp
|
| |
Filter: __datafusion_extracted_1 IS NOT NULL AND __datafusion_extracted_1 !=
Utf8View("") AND __datafusion_extracted_2 IS NOT NULL AND
__datafusion_extracted_2 != Utf8View("") |
| |
Projection: get_field(t1.ids, Utf8("id1")) AS __datafusion_extracted_1,
get_field(t1.structs, Utf8("var1")) AS __datafusion_extracted_2, t1.date,
t1.timestamp |
| |
Filter: t1.date = Date32("2025-01-03")
|
| |
TableScan: t1 projection=[date, timestamp, ids, structs]
|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
