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'