# 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. # # Using mz_now() with a VIEW source # > CREATE VIEW one_ts (ts) AS VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(NULL) > CREATE MATERIALIZED VIEW one_bound1 AS SELECT * FROM one_ts WHERE mz_now() >= ts > SELECT * FROM one_bound1 AS OF 0 > SELECT * FROM one_bound1 AS OF 2 1 2 > CREATE MATERIALIZED VIEW one_bound2 AS SELECT * FROM one_ts WHERE mz_now() <= ts > SELECT * FROM one_bound2 AS OF 0 1 2 3 4 5 6 7 8 9 10 > SELECT * FROM one_bound2 AS OF 2 2 3 4 5 6 7 8 9 10 > SELECT * FROM one_bound2 AS OF 10 10 > SELECT * FROM one_bound2 AS OF 11 > CREATE MATERIALIZED VIEW two_bounds AS SELECT * FROM one_ts WHERE mz_now() >= ts AND mz_now() >= ts + 3 > SELECT * FROM two_bounds AS OF 2 > SELECT * FROM two_bounds AS OF 5 ts --- 1 2 > CREATE MATERIALIZED VIEW two_bounds2 AS SELECT * FROM one_ts WHERE mz_now() <= ts AND mz_now() <= ts + 3 > SELECT * FROM two_bounds2 AS OF 2 ts --- 2 3 4 5 6 7 8 9 10 > SELECT * FROM two_bounds2 AS OF 9 ts --- 9 10 > CREATE MATERIALIZED VIEW two_bounds_between AS SELECT * FROM one_ts WHERE mz_now() BETWEEN ts AND ts + 3 > SELECT * FROM two_bounds_between AS OF 3 ts --- 1 2 3 > SELECT * FROM two_bounds_between AS OF 5 ts --- 2 3 4 5 > CREATE MATERIALIZED VIEW two_bounds_and AS SELECT * FROM one_ts WHERE mz_now() >= ts AND mz_now() <= ts + 3 > SELECT * FROM two_bounds_and AS OF 3 ts --- 1 2 3 > SELECT * FROM two_bounds_and AS OF 5 ts --- 2 3 4 5 > CREATE VIEW two_ts (ts1,ts2) AS VALUES (1,1+5),(2,2+5),(3,3+5),(4,4+5),(5,5+5),(6,6+5),(7,7+5),(8,8+5),(9,9+5),(10,10+5) > DROP VIEW IF EXISTS two_bounds_two_cols CASCADE > CREATE MATERIALIZED VIEW two_bounds_two_cols AS SELECT * FROM two_ts WHERE mz_now() >= ts1 AND mz_now() >= ts2 > SELECT * FROM two_bounds_two_cols AS OF 3 > SELECT * FROM two_bounds_two_cols AS OF 6 1 6 > SELECT * FROM two_bounds_two_cols AS OF 15 1 6 2 7 3 8 4 9 5 10 6 11 7 12 8 13 9 14 10 15 > CREATE MATERIALIZED VIEW two_bounds_two_cols2 AS SELECT * FROM two_ts WHERE mz_now() >= ts1 AND mz_now() <= ts2; > SELECT * FROM two_bounds_two_cols2 AS OF 3 1 6 2 7 3 8 > SELECT * FROM two_bounds_two_cols2 AS OF 6 1 6 2 7 3 8 4 9 5 10 6 11 > SELECT * FROM two_bounds_two_cols2 AS OF 15 10 15 > SELECT * FROM two_bounds_two_cols2 AS OF 16 > DROP VIEW one_ts CASCADE; > DROP VIEW two_ts CASCADE; # # Using a TABLE source # > CREATE TABLE one_ts (ts mz_timestamp) > INSERT INTO one_ts VALUES ('2000-01-01'::timestamp) > INSERT INTO one_ts VALUES ('2199-12-31'::timestamp) > INSERT INTO one_ts VALUES (NULL) > CREATE MATERIALIZED VIEW one_bound1 AS SELECT to_timestamp(ts::string::numeric / 1000) FROM one_ts WHERE ts >= mz_now() > SELECT * FROM one_bound1 to_timestamp "2199-12-31 00:00:00 UTC" > INSERT INTO one_ts VALUES ('2000-01-01'::timestamp) > INSERT INTO one_ts VALUES ('2199-12-31'::timestamp) > INSERT INTO one_ts VALUES (NULL) > SELECT * FROM one_bound1 to_timestamp "2199-12-31 00:00:00 UTC" "2199-12-31 00:00:00 UTC" > CREATE MATERIALIZED VIEW one_bound2 AS SELECT to_timestamp(ts::string::numeric / 1000) FROM one_ts WHERE ts <= mz_now() > SELECT * FROM one_bound2 to_timestamp "2000-01-01 00:00:00 UTC" "2000-01-01 00:00:00 UTC" > INSERT INTO one_ts VALUES ('2000-01-01'::timestamp) > INSERT INTO one_ts VALUES ('2199-12-31'::timestamp) > INSERT INTO one_ts VALUES (NULL) > SELECT * FROM one_bound2 to_timestamp "2000-01-01 00:00:00 UTC" "2000-01-01 00:00:00 UTC" "2000-01-01 00:00:00 UTC" > CREATE TABLE first_ts (ts mz_timestamp) > INSERT INTO first_ts VALUES ('2000-01-01'::timestamp) > INSERT INTO first_ts VALUES ('2199-12-31'::timestamp) > INSERT INTO first_ts VALUES (NULL) > CREATE TABLE second_ts (ts mz_timestamp) > INSERT INTO second_ts VALUES ('2000-01-01'::timestamp) > INSERT INTO second_ts VALUES ('2199-12-31'::timestamp) > INSERT INTO second_ts VALUES (NULL) > CREATE MATERIALIZED VIEW both_ts (first_ts_to_ts, second_ts_to_ts) AS SELECT to_timestamp(first_ts.ts::string::numeric / 1000), to_timestamp(second_ts.ts::string::numeric / 1000) FROM first_ts, second_ts WHERE mz_now() BETWEEN first_ts.ts AND second_ts.ts > SELECT * FROM both_ts to_timestamp "2000-01-01 00:00:00 UTC" "2199-12-31 00:00:00 UTC" > INSERT INTO first_ts VALUES ('2000-01-01'::timestamp) > INSERT INTO first_ts VALUES (NULL) > INSERT INTO second_ts VALUES ('2199-12-31'::timestamp) > INSERT INTO second_ts VALUES (NULL) > SELECT * FROM both_ts "2000-01-01 00:00:00 UTC" "2199-12-31 00:00:00 UTC" "2000-01-01 00:00:00 UTC" "2199-12-31 00:00:00 UTC" "2000-01-01 00:00:00 UTC" "2199-12-31 00:00:00 UTC" "2000-01-01 00:00:00 UTC" "2199-12-31 00:00:00 UTC" > CREATE MATERIALIZED VIEW v2 (first_ts_a, first_ts_b) AS SELECT * FROM first_ts a, first_ts b WHERE mz_now() = a.ts; > CREATE MATERIALIZED VIEW v3 (first_ts_a, first_ts_b) AS SELECT * FROM first_ts a, first_ts b WHERE mz_now() = a.ts AND mz_now() = b.ts; # # Various errors in the placement of mz_now() # !CREATE MATERIALIZED VIEW v1 AS SELECT * FROM first_ts WHERE mz_now() != ts; contains:Unsupported binary temporal operation: NotEq !CREATE MATERIALIZED VIEW v1 AS SELECT * FROM first_ts WHERE mz_now() + 1 = ts; contains:operator does not exist: mz_timestamp + integer !CREATE MATERIALIZED VIEW v1 AS SELECT * FROM first_ts WHERE mz_now() > ts OR ts = 1; contains:Unsupported temporal predicate. Note: `mz_now()` must be directly compared to a non-temporal expression of mz_timestamp-castable type. Expression found: ((#0{ts} = 1) OR (mz_now() > #0{ts})) !CREATE MATERIALIZED VIEW v1 AS SELECT * FROM first_ts WHERE ts BETWEEN mz_now() AND mz_now() + 1; contains:operator does not exist: mz_timestamp + integer # # Numeric comparisons # # Checks that comparisons against mz_now using values with # fractional components fail due to lossy conversion of numeric to u64. > CREATE OR REPLACE MATERIALIZED VIEW numeric_trunc AS SELECT 1 WHERE mz_now() > 1927418240000.1; ! SELECT * FROM numeric_trunc; contains:Evaluation error: "1927418240000.1" mz_timestamp out of range