temporal.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.

# Test temporal filtering operators

mode cockroach

# Two columns here represent a validity interval [a, b].
statement ok
CREATE VIEW intervals (a, b) AS VALUES (1, 10), (1, 2), (2, 13), (3, 1), (-3, 10), (5, 18446744073709551616)

# Select out rows when each are valid.
statement ok
CREATE MATERIALIZED VIEW valid AS
SELECT *
FROM intervals
WHERE mz_logical_timestamp() BETWEEN a AND b;

query II rowsort
select * from valid AS OF 2;
----
-3  10
1  10
1  2
2  13

query II rowsort
select * from valid AS OF 3;
----
-3  10
1  10
2  13

query II rowsort
select * from valid AS OF 11;
----
2  13
5  18446744073709551616

query II rowsort
select * from valid AS OF 14;
----
5  18446744073709551616

# Test that rows are not dropped just before the end of time.
# That big number there should be u64::MAX.
query II rowsort
select * from valid AS OF 18446744073709551615;
----
5  18446744073709551616

#
# Regression test for #6635
#
statement ok
CREATE TABLE events (
    content text,
    insert_ts numeric,
    delete_ts numeric
);

statement ok
CREATE MATERIALIZED VIEW valid_events AS
SELECT content, count(*)
FROM events
WHERE mz_logical_timestamp() >= insert_ts
  AND mz_logical_timestamp()  < delete_ts
GROUP BY content;


query TI rowsort
select * from valid_events;
----