Materialize/test/sqllogictest/filter-pushdown.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET persist_stats_filter_enabled = true
----
COMPLETE 0

# Verify filter pushdown information for various temporal filters.
# For straightforward temporal filters like these, every column mentioned in the filter
# should be present in the pushdown list.

statement ok
CREATE TABLE events (
    content text,
    insert_ms numeric,
    delete_ms numeric
);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT count(*)
FROM events
WHERE mz_now() >= insert_ms
  AND mz_now() < delete_ms;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(*)]
        Project ()
          Filter (mz_now() < numeric_to_mz_timestamp(#2{delete_ms})) AND (mz_now() >= numeric_to_mz_timestamp(#1{insert_ms}))
            ReadStorage materialize.public.events
  Return
    Union
      Get l0
      Map (0)
        Union
          Negate
            Project ()
              Get l0
          Constant
            - ()

Source materialize.public.events
  filter=((mz_now() >= numeric_to_mz_timestamp(#1{insert_ms})) AND (mz_now() < numeric_to_mz_timestamp(#2{delete_ms})))
  pushdown=((mz_now() >= numeric_to_mz_timestamp(#1{insert_ms})) AND (mz_now() < numeric_to_mz_timestamp(#2{delete_ms})))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, insert_ms
FROM events
-- The event should appear in only one interval of duration `10000`.
-- The interval begins here ...
WHERE mz_now() >= 10000 * (insert_ms / 10000)
-- ... and ends here.
  AND mz_now() < 10000 * (1 + insert_ms / 10000)
----
Explained Query:
  Project (#0{content}, #1{insert_ms})
    Filter (mz_now() >= numeric_to_mz_timestamp((10000 * #3))) AND (mz_now() < numeric_to_mz_timestamp((10000 * (1 + #3))))
      Map ((#1{insert_ms} / 10000))
        ReadStorage materialize.public.events

Source materialize.public.events
  filter=((mz_now() < numeric_to_mz_timestamp((10000 * (1 + #3)))) AND (mz_now() >= numeric_to_mz_timestamp((10000 * #3))))
  map=((#1{insert_ms} / 10000))
  pushdown=((mz_now() < numeric_to_mz_timestamp((10000 * (1 + #3)))) AND (mz_now() >= numeric_to_mz_timestamp((10000 * #3))))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, insert_ms
FROM events
-- The event should appear in `6` intervals each of width `10000`.
-- The interval begins here ...
WHERE mz_now() >= 10000 * (insert_ms / 10000)
-- ... and ends here.
  AND mz_now() < 6 * (10000 + insert_ms / 10000)
----
Explained Query:
  Project (#0{content}, #1{insert_ms})
    Filter (mz_now() >= numeric_to_mz_timestamp((10000 * #3))) AND (mz_now() < numeric_to_mz_timestamp((6 * (10000 + #3))))
      Map ((#1{insert_ms} / 10000))
        ReadStorage materialize.public.events

Source materialize.public.events
  filter=((mz_now() < numeric_to_mz_timestamp((6 * (10000 + #3)))) AND (mz_now() >= numeric_to_mz_timestamp((10000 * #3))))
  map=((#1{insert_ms} / 10000))
  pushdown=((mz_now() < numeric_to_mz_timestamp((6 * (10000 + #3)))) AND (mz_now() >= numeric_to_mz_timestamp((10000 * #3))))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, insert_ms
FROM events
-- The event should appear inside the interval that begins at
-- `insert_ms` and ends at  `insert_ms + 30000`.
-- The interval begins here ..
WHERE mz_now() >= insert_ms
-- ... and ends here.
  AND mz_now() < insert_ms + 30000
----
Explained Query:
  Project (#0{content}, #1{insert_ms})
    Filter (mz_now() >= numeric_to_mz_timestamp(#1{insert_ms})) AND (mz_now() < numeric_to_mz_timestamp((#1{insert_ms} + 30000)))
      ReadStorage materialize.public.events

Source materialize.public.events
  filter=((mz_now() < numeric_to_mz_timestamp((#1{insert_ms} + 30000))) AND (mz_now() >= numeric_to_mz_timestamp(#1{insert_ms})))
  pushdown=((mz_now() < numeric_to_mz_timestamp((#1{insert_ms} + 30000))) AND (mz_now() >= numeric_to_mz_timestamp(#1{insert_ms})))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, insert_ms, delete_ms
FROM events
WHERE mz_now() >= insert_ms + 60000
  AND mz_now() < delete_ms + 60000;
----
Explained Query:
  Filter (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 60000))) AND (mz_now() >= numeric_to_mz_timestamp((#1{insert_ms} + 60000)))
    ReadStorage materialize.public.events

Source materialize.public.events
  filter=((mz_now() >= numeric_to_mz_timestamp((#1{insert_ms} + 60000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 60000))))
  pushdown=((mz_now() >= numeric_to_mz_timestamp((#1{insert_ms} + 60000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 60000))))

Target cluster: quickstart

EOF

# Verify explain behaviour for functions with many arguments. In theory, we can't push down
# non-associative functions with long argument lists... but in practice all the functions we
# can push down are also associative, so this is moot. Let's at least check that an associative
# function _does_ report pushdown even when the argument list is long.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, insert_ms, delete_ms
FROM events
WHERE COALESCE(delete_ms, insert_ms) < mz_now();
----
Explained Query:
  Filter (numeric_to_mz_timestamp(coalesce(#2{delete_ms}, #1{insert_ms})) < mz_now())
    ReadStorage materialize.public.events

Source materialize.public.events
  filter=((numeric_to_mz_timestamp(coalesce(#2{delete_ms}, #1{insert_ms})) < mz_now()))
  pushdown=((numeric_to_mz_timestamp(coalesce(#2{delete_ms}, #1{insert_ms})) < mz_now()))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, insert_ms, delete_ms
FROM events
WHERE mz_now() < delete_ms + 10000
  AND mz_now() < delete_ms + 20000
  AND mz_now() < delete_ms + 30000
  AND mz_now() < delete_ms + 40000
  AND mz_now() < delete_ms + 50000
  AND mz_now() < delete_ms + 60000
  AND mz_now() < delete_ms + 70000
  AND mz_now() < delete_ms + 80000
  AND mz_now() < delete_ms + 90000;
----
Explained Query:
  Filter (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 10000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 20000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 30000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 40000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 50000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 60000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 70000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 80000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 90000)))
    ReadStorage materialize.public.events

Source materialize.public.events
  filter=((mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 10000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 20000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 30000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 40000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 50000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 60000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 70000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 80000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 90000))))
  pushdown=((mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 10000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 20000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 30000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 40000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 50000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 60000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 70000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 80000))) AND (mz_now() < numeric_to_mz_timestamp((#2{delete_ms} + 90000))))

Target cluster: quickstart

EOF

statement ok
CREATE TABLE events_timestamped (
    content text,
    inserted_at timestamp,
    deleted_at timestamp
);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, inserted_at
FROM events_timestamped
WHERE EXTRACT(YEAR FROM inserted_at) = 2021;
----
Explained Query:
  Project (#0{content}, #1{inserted_at})
    Filter (2021 = extract_year_ts(#1{inserted_at}))
      ReadStorage materialize.public.events_timestamped

Source materialize.public.events_timestamped
  filter=((2021 = extract_year_ts(#1{inserted_at})))
  pushdown=((2021 = extract_year_ts(#1{inserted_at})))

Target cluster: quickstart

EOF

# Verify that try_parse_monotonic_iso8601_timestamp gets pushdown (the whole
# point of that func)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, filter pushdown) AS VERBOSE TEXT FOR
SELECT content, inserted_at
FROM events_timestamped
WHERE mz_now() < try_parse_monotonic_iso8601_timestamp(content);
----
Explained Query:
  Project (#0{content}, #1{inserted_at})
    Filter (mz_now() < timestamp_to_mz_timestamp(try_parse_monotonic_iso8601_timestamp(#0{content})))
      ReadStorage materialize.public.events_timestamped

Source materialize.public.events_timestamped
  filter=((mz_now() < timestamp_to_mz_timestamp(try_parse_monotonic_iso8601_timestamp(#0{content}))))
  pushdown=((mz_now() < timestamp_to_mz_timestamp(try_parse_monotonic_iso8601_timestamp(#0{content}))))

Target cluster: quickstart

EOF

# Regression tests for https://github.com/MaterializeInc/database-issues/issues/6640

statement ok
create table t(x int, t timestamp);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
select * from t
where t < '2023-10-02 15:55:31.918 UTC';
----
Explained Query:
  Filter (#1{t} < 2023-10-02 15:55:31.918)
    ReadStorage materialize.public.t

Source materialize.public.t
  filter=((#1{t} < 2023-10-02 15:55:31.918))
  pushdown=((#1{t} < 2023-10-02 15:55:31.918))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
select * from t
where case when x=0 then t < '2023-10-02 15:55:31.918 UTC' else t > '2023-10-02 15:55:31.918 UTC' end;
----
Explained Query:
  Filter case when (#0{x} = 0) then (#1{t} < 2023-10-02 15:55:31.918) else (#1{t} > 2023-10-02 15:55:31.918) end
    ReadStorage materialize.public.t

Source materialize.public.t
  filter=(case when (#0{x} = 0) then (#1{t} < 2023-10-02 15:55:31.918) else (#1{t} > 2023-10-02 15:55:31.918) end)
  pushdown=(case when (#0{x} = 0) then (#1{t} < 2023-10-02 15:55:31.918) else (#1{t} > 2023-10-02 15:55:31.918) end)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
with cte as (
  select x, t, case when x=0 then t - INTERVAL '1' day else t - INTERVAL '2' day end as case_statement from t
)
select x, t, case_statement from cte
where case_statement < '2023-10-02 15:55:31.918 UTC';
----
Explained Query:
  Filter (#2 < 2023-10-02 15:55:31.918)
    Map ((#1{t} - case when (#0{x} = 0) then 1 day else 2 days end))
      ReadStorage materialize.public.t

Source materialize.public.t
  filter=(((#1{t} - case when (#0{x} = 0) then 1 day else 2 days end) < 2023-10-02 15:55:31.918))
  pushdown=(((#1{t} - case when (#0{x} = 0) then 1 day else 2 days end) < 2023-10-02 15:55:31.918))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
with cte as (
  select x, t, case when x=0 then t - INTERVAL '1' day else t - INTERVAL '2' day end as case_statement from t
)
select x, t from cte
where case_statement < mz_now();
----
Explained Query:
  Filter (timestamp_to_mz_timestamp((#1{t} - case when (#0{x} = 0) then 1 day else 2 days end)) < mz_now())
    ReadStorage materialize.public.t

Source materialize.public.t
  filter=((timestamp_to_mz_timestamp((#1{t} - case when (#0{x} = 0) then 1 day else 2 days end)) < mz_now()))
  pushdown=((timestamp_to_mz_timestamp((#1{t} - case when (#0{x} = 0) then 1 day else 2 days end)) < mz_now()))

Target cluster: quickstart

EOF

# Regression test: should not report pushdown when one case can't be pushed down / might throw an exception.
# (We don't infer a range for the result of EXTRACT, so the overall expression may overflow.)

statement ok
CREATE TABLE items(id int, ship_time timestamp);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
SELECT * from items
WHERE mz_now() <= date_trunc(
    'month',
    ship_time
    - (
        CASE WHEN EXTRACT(MONTH FROM ship_time) < 6 THEN EXTRACT(MONTH FROM ship_time) + 6 ELSE 0 END
        + CASE WHEN EXTRACT(MONTH FROM ship_time) >= 6 THEN EXTRACT(MONTH FROM ship_time) - 6 ELSE 0 END
    )
    * INTERVAL '1 months'
)
----
Explained Query:
  Project (#0{id}, #1{ship_time})
    Filter (mz_now() <= timestamp_to_mz_timestamp(date_trunc_month_ts((#1{ship_time} - (1 month * numeric_to_double((case when (#2 < 6) then (extract_month_ts(#1{ship_time}) + 6) else 0 end + case when (#2 >= 6) then (extract_month_ts(#1{ship_time}) - 6) else 0 end)))))))
      Map (extract_month_ts(#1{ship_time}))
        ReadStorage materialize.public.items

Source materialize.public.items
  filter=((mz_now() <= timestamp_to_mz_timestamp(date_trunc_month_ts((#1{ship_time} - (1 month * numeric_to_double((case when (#2 < 6) then (extract_month_ts(#1{ship_time}) + 6) else 0 end + case when (#2 >= 6) then (extract_month_ts(#1{ship_time}) - 6) else 0 end))))))))
  map=(extract_month_ts(#1{ship_time}))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
SELECT * from items
WHERE CASE WHEN id = 10 THEN EXTRACT(MONTH FROM ship_time) ELSE 0 END < mz_now();
----
Explained Query:
  Filter (numeric_to_mz_timestamp(case when (#0{id} = 10) then extract_month_ts(#1{ship_time}) else 0 end) < mz_now())
    ReadStorage materialize.public.items

Source materialize.public.items
  filter=((numeric_to_mz_timestamp(case when (#0{id} = 10) then extract_month_ts(#1{ship_time}) else 0 end) < mz_now()))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(filter pushdown, humanized expressions) AS VERBOSE TEXT FOR
SELECT * from items
WHERE CASE WHEN EXTRACT(MONTH FROM ship_time) >= 6 THEN 12 ELSE 0 END < mz_now();
----
Explained Query:
  Filter (integer_to_mz_timestamp(case when (extract_month_ts(#1{ship_time}) >= 6) then 12 else 0 end) < mz_now())
    ReadStorage materialize.public.items

Source materialize.public.items
  filter=((integer_to_mz_timestamp(case when (extract_month_ts(#1{ship_time}) >= 6) then 12 else 0 end) < mz_now()))
  pushdown=((integer_to_mz_timestamp(case when (extract_month_ts(#1{ship_time}) >= 6) then 12 else 0 end) < mz_now()))

Target cluster: quickstart

EOF