Range scan with no overlapping time range in inner and outer query should not do any data scan

[jotarada]

Tables partition by timestamp and where inner query predicate combined with outer predicate are exclusive, but the engine still makes the scan for the inner query.

Ex:
This is a iceberg table and it is partition by server_timestamp day

The range server_timestamp > timestamp '2024-01-09 20:49:34' and server_timestamp < timestamp '2024-01-09 20:49:34' have no overlapping so the engine should not scan any data and return 0 records.

[jotarada]

I managed to get the problem reduced to this query

SELECT id, created, rownum
FROM (
        SELECT
          id,
          created,
          row_number() over (partition by id order by created ASC) as rownum
       FROM schema.table
       WHERE created >= timestamp '2024-06-30 17:00:00'
       and created < timestamp '2024-10-31 17:00:00'
    )
    where created > timestamp '2024-11-30 17:00:00

Exposing a calculated field, rownum, triggers a table scan

[martint]

In general, the optimizer cannot move a filter below a window function unless the filter applies to the window function partitioning clause. Otherwise, the filter might affect which rows are considered for the evaluation function and result in a different output.

This case is special because the filter causes all the outputs from the window function to be filtered out. Unfortunately, there's currently no mechanism in the optimizer to model this. We need support for traits to describe constraints that optimizer rules can use during plan transformations.