Materialize/test/sqllogictest/as_of.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

statement ok
CREATE VIEW data (a, b) AS VALUES (1, 1), (2, 1), (3, 1), (1, 2)

# Don't parse 'AS OF' as a table alias.
statement error Expected a timestamp value after 'AS OF', found EOF
SELECT * FROM data AS OF;

query II
SELECT * FROM data
----
1 1
1 2
2 1
3 1

query error cannot call current_timestamp in AS OF
SELECT * FROM data AS OF now()

query II
SELECT * FROM data ORDER BY a, b AS OF AT LEAST 1
----
1 1
1 2
2 1
3 1

# This previously would panic on an internal conversion from numeric to
# primitive int

query II
SELECT * FROM data AS OF 192741824E4::numeric;
----
1 1
1 2
2 1
3 1

query error out of range integral type conversion attempted
SELECT * FROM data AS OF -1;

query error decimal cannot be expressed in target primitive type
SELECT * FROM data AS OF -1::numeric;

query error decimal cannot be expressed in target primitive type
SELECT * FROM data AS OF 1E38;

query error decimal cannot be expressed in target primitive type
SELECT * FROM data AS OF 1.2;

query error cannot call mz_now in AS OF
SELECT * FROM data AS OF mz_now();

statement ok
CREATE TABLE t (i INT);

statement ok
CREATE DEFAULT INDEX ON t;

statement ok
INSERT INTO t VALUES (1);

query I
SELECT * FROM t;
----
1

query error Timestamp \(1\) is not valid for all inputs
SELECT * FROM t AS OF 1

# AS OF escapes linearizability, so this could choose a timestamp before the INSERT. We're just
# testing that we can type AS OF AT LEAST 1. Use a query that has the same output regardless of chosen
# timestamp.
query B
SELECT count(*) = 2 FROM t AS OF AT LEAST 1
----
false

query error can't use null as a mz_timestamp for AS OF
SELECT 1 AS OF NULL::timestamp;

query error can't use null as a mz_timestamp for AS OF
SELECT * FROM data AS OF NULL::numeric;

query error can't use null as a mz_timestamp for AS OF
SUBSCRIBE (SELECT 1) AS OF NULL::timestamptz;

# Test that timestamps are used for constant queries with temporal filters

statement ok
create view events_over_time as values ('joe', 100), ('mike', 101), ('sam', 200), ('end', 18446744073709551615);

statement ok
create view events as select * from events_over_time where mz_now() >= column2;

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE events AS OF 0

query IITI
FETCH ALL c
----
100                   1  joe   100
101                   1  mike  101
200                   1  sam   200
18446744073709551615  1  end  18446744073709551615

statement ok
COMMIT

query TI
SELECT * FROM events AS OF 0
----

query TI
SELECT * FROM events AS OF 100
----
joe  100

query TI
SELECT * FROM events AS OF 101
----
joe  100
mike 101

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE (SELECT 1) AS OF 42

# TODO(jkosh44) It's not entirely clear what the answer to this should be.
query III
FETCH ALL c
----
18446744073709551615  1  1

statement ok
COMMIT

statement ok
BEGIN

statement ok
DECLARE c CURSOR FOR SUBSCRIBE (SELECT 1)

query III
FETCH ALL c
----
18446744073709551615  1  1

statement ok
COMMIT