Calcite execution planner misses timewindow filtering

[SebastianPuscas]

Please provide a detailed title (e.g. "Broker crashes when using TopN query with Bound filter" instead of just "Broker crashes").

Affected Version

34
The Druid version where the problem was encountered.
34

Description

We have a production query using IN LIST, the query is extracting data for a given time window when being executed, the main issue is that the timefilter is being lost when execution planner parses the sql query and transfers it to other nodes.

First we have a hot tier with latest 3 months and a cold tier with 3 months to 2 years segments.
It happens that we have many queries inside the cold tier for a datasource which barely has any time-window query specified for more than 3 months.

So we are using on the service side a spring java project which transfers sql statements to druid.
I inspected historical cold nodes and seen native queries, asking claude to share similar sql query i got the following

Query 1 (88% of traffic — 47,363 hits)

SELECT pbx,
APPROX_COUNT_DISTINCT_DS_HLL(j.callId) AS a0
FROM source_v1
INNERJOIN inline_data j ON c.callId = j.d0
WHERE c.addrType = 'xxxxa'
AND c.addr = 'xxxx'
GROUPBY pbx

on the service side we are building following which return correct data as broker still does the filtering but on cold tier side, I don't see the __time filtering.

SELECT COUNT(DISTINCT callId), pbx
* FROM source_v1
* WHERE ( pbx = :pbx AND addrType = 'xxx' AND addr IN (:LIST)
* AND callId NOT IN ('id1', 'id2', ...) AND __time >= ? AND __time <= ? )
* GROUP BY pbx

[gianm]

This looks like the transformation that happens when the size of an IN or NOT IN is above inSubQueryThreshold. By default it's Integer.MAX_VALUE which means we don't do this transformation. Are you setting inSubQueryThreshold in your setup?