github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/as_of (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE t (i INT) 5 6 statement ok 7 INSERT INTO t VALUES (2) 8 9 # Verify strings can be parsed as intervals. 10 query I 11 SELECT * FROM t AS OF SYSTEM TIME '-1us' 12 ---- 13 2 14 15 # Verify a forced interval type works. 16 query I 17 SELECT * FROM t AS OF SYSTEM TIME INTERVAL '-1us' 18 ---- 19 2 20 21 # Verify that we can use computed expressions. 22 query I 23 SELECT * FROM t AS OF SYSTEM TIME -( ('1000' || 'us')::INTERVAL ) 24 ---- 25 2 26 27 statement error pq: AS OF SYSTEM TIME: only constant expressions or experimental_follower_read_timestamp are allowed 28 SELECT * FROM t AS OF SYSTEM TIME cluster_logical_timestamp() 29 30 statement error pq: subqueries are not allowed in AS OF SYSTEM TIME 31 SELECT * FROM t AS OF SYSTEM TIME (SELECT '-1h'::INTERVAL) 32 33 statement error pq: relation "t" does not exist 34 SELECT * FROM t AS OF SYSTEM TIME '-1h' 35 36 statement error pq: experimental_follower_read_timestamp\(\): experimental_follower_read_timestamp is only available in ccl distribution 37 SELECT * FROM t AS OF SYSTEM TIME experimental_follower_read_timestamp() 38 39 statement error pq: unknown signature: experimental_follower_read_timestamp\(string\) \(desired <timestamptz>\) 40 SELECT * FROM t AS OF SYSTEM TIME experimental_follower_read_timestamp('boom') 41 42 statement error pq: AS OF SYSTEM TIME: only constant expressions or experimental_follower_read_timestamp are allowed 43 SELECT * FROM t AS OF SYSTEM TIME now() 44 45 statement error cannot specify timestamp in the future 46 SELECT * FROM t AS OF SYSTEM TIME '10s' 47 48 # Verify that the TxnTimestamp used to generate now() and current_timestamp() is 49 # set to the historical timestamp. 50 51 query T 52 SELECT * FROM (SELECT now()) AS OF SYSTEM TIME '2018-01-01' 53 ---- 54 2018-01-01 00:00:00 +0000 UTC 55 56 # Verify that zero intervals indistinguishable from zero cause an error. 57 58 statement error pq: AS OF SYSTEM TIME: interval value '0.1us' too small, absolute value must be >= 1µs 59 SELECT * FROM t AS OF SYSTEM TIME '0.1us' 60 61 statement error pq: AS OF SYSTEM TIME: interval value '0-0' too small, absolute value must be >= 1µs 62 SELECT * FROM t AS OF SYSTEM TIME '0-0' 63 64 statement error pq: AS OF SYSTEM TIME: interval value '-0.1us' too small, absolute value must be >= 1µs 65 SELECT * FROM t AS OF SYSTEM TIME '-0.1us' 66 67 statement error pq: AS OF SYSTEM TIME: zero timestamp is invalid 68 SELECT * FROM t AS OF SYSTEM TIME '0' 69 70 # Verify we can explain a statement that has AS OF. 71 statement ok 72 EXPLAIN SELECT * FROM t AS OF SYSTEM TIME '-1us'