push_down_filter and common_sub_expression_eliminate fight between them
- Lingua principale
- Rust
- Stelle
- 9.3k
- Fork
- 2.4k
- Merge medio
- 3g 11h
- PR unite (30g)
- 360
Descrizione
### Describe the bug
With the following table structure:
```
CREATE OR REPLACE TABLE t1 (
date DATE,
timestamp TIMESTAMP_S,
ids STRUCT(
id1 VARCHAR,
extra INT4
),
struct STRUCT(
var1 VARCHAR,
extra VARCHAR
)
);
```
and the following query
```
WITH events AS (
SELECT
ids.id1 as device,
struct.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
```
I get into a situation where the 2 optimization steps given above fight between them:
1. First, push_down_filter pushes the filter down
```
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: get_field(t1.ids, Utf8("id1")) AS device, get_field(t1.struct, Utf8("var1")) AS user, t1.timestamp
Filter: get_field(t1.ids, Utf8("id1")) IS NOT NULL AND get_field(t1.ids, Utf8("id1")) != Utf8("") AND get_field(t1.struct, Utf8("var1")) IS NOT NULL AND get_field(t1.struct, Utf8("var1")) != Utf8("") AND t1._ACP_DATE = Date32("2025-01-03")
TableScan: t1, partial_filters=[get_field(t1.ids, Utf8("id1")) IS NOT NULL, get_field(t1.ids, Utf8("id1")) != Utf8(""), get_field(t1.struct, Utf8("var1")) IS NOT NULL, get_field(t1.struct, Utf8("var1")) != Utf8(""), t1._ACP_DATE = Date32("2025-01-03")]
```
2. Then the common_sub_expression_eliminate acts on the `Filter`, and of course, adds a `Projection` below it to contain the aliases:
```
Projection: events.DeviceId, events.UserId, events.timestamp, lag(events.UserId,Int64(1)) PARTITION BY [events.DeviceId] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS PreviousUserColName
Limit: skip=0, fetch=100
WindowAggr: windowExpr=[[lag(events.UserId, Int64(1)) PARTITION BY [events.DeviceId] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
SubqueryAlias: events
Projection: get_field(t1.endUserIDs, Utf8("id1")) AS DeviceId, get_field(t1.struct, Utf8("var1")) AS UserId, t1.timestamp
Filter: __common_expr_1 IS NOT NULL AND __common_expr_1 != Utf8("") AND get_field(t1.struct, Utf8("var1")) IS NOT NULL AND get_field(t1.struct, Utf8("var1")) != Utf8("") AND t1._ACP_DATE = Date32("2025-01-03")
Projection: get_field(t1.endUserIDs, Utf8("id1")) AS __common_expr_1, t1._ACP_DATE, t1.timestamp, t1.endUserIDs, t1.struct
TableScan: t1 projection=[_ACP_DATE, timestamp, endUserIDs, struct], partial_filters=[get_field(t1.endUserIDs, Utf8("id1")) IS NOT NULL, get_field(t1.endUserIDs, Utf8("id1")) != Utf8(""), get_field(t1.struct, Utf8("var1")) IS NOT NULL, get_field(t1.struct, Utf8("var1")) != Utf8(""), t1._ACP_DATE = Date32("2025-01-03")]
```
3. At the next run, `push_down_filter` sees a Filter on top a Projection and pushes it down, deleting the alias probably because Filters can't have aliases
```
Projection: events.DeviceId, events.UserId, events.timestamp, lag(events.UserId,Int64(1)) PARTITION BY [events.DeviceId] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS PreviousUserColName
Limit: skip=0, fetch=100
WindowAggr: windowExpr=[[lag(events.UserId, Int64(1)) PARTITION BY [events.DeviceId] ORDER BY [events.timestamp ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
SubqueryAlias: events
Projection: get_field(t1.endUserIDs, Utf8("id1")) AS DeviceId, get_field(t1.struct, Utf8("var1")) AS UserId, t1.timestamp
Projection: get_field(t1.endUserIDs, Utf8("id1")) AS __common_expr_1, t1._ACP_DATE, t1.timestamp, t1.endUserIDs, t1.struct
Filter: get_field(t1.endUserIDs, Utf8("id1")) IS NOT NULL AND get_field(t1.endUserIDs, Utf8("id1")) != Utf8("") AND get_field(t1.struct, Utf8("var1")) IS NOT NULL AND get_field(t1.struct, Utf8("var1")) != Utf8("") AND t1._ACP_DATE = Date32("2025-01-03")
TableScan: t1 projection=[_ACP_DATE, timestamp, endUserIDs, struct], partial_filters=[get_field(t1.endUserIDs, Utf8("id1")) IS NOT NULL, get_field(t1.endUserIDs, Utf8("id1")) != Utf8(""), get_field(t1.struct, Utf8("var1")) IS NOT NULL, get_field(t1.struct, Utf8("var1")) != Utf8(""), t1._ACP_DATE = Date32("2025-01-03")]
```
And no progress is made.
This seems like it could happen in other cases. Maybe we could have something like hints, like a flag on the logical plan that can say that the plan was added during a previous optimization step ? The problem seems complicated though.
### To Reproduce
_No response_
### Expected behavior
_No response_
### Additional context
_No response_
Guida per i contributori
Apri la guida per i contributori
Direzione di ricerca
Inizia con i pass dell’ottimizzatore push_down_filter e common_sub_expression_eliminate, quindi riproduci l’SQL fornito e ispeziona il piano logico dopo ogni pass. Traccia il motivo per cui la proiezione e l’alias generati vengono spostati ripetutamente e verifica che l’ottimizzazione raggiunga un piano stabile senza perdere l’espressione comune né oscillare inutilmente tra i pass.
Scritto dal modello di indicizzazione a partire dal testo della issue.
Valutazione
- Stack tecnologico
- rust
- Ambito
- databases
- Tipo di issue
- Bug
- Difficoltà
- 4/5
- Tempo stimato
- 3-5 giorni
- Stato di attività
- Tranquilla
- Chiarezza
- Abbastanza chiara
- Idoneità per principianti
- 48/100