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

# Verify that transaction-stable functions, like "now", don't change during a transaction.

statement ok
CREATE TABLE now_inc (a TIMESTAMP)

statement ok
INSERT INTO now_inc VALUES (now())

# Sleep 2ms to ensure now() has increased, due to its ms resolution.
statement ok
SELECT mz_unsafe.mz_sleep(0.002)

# These execute in a single txn, so should be the same, and should
# produce 3 identical rows.
simple
BEGIN;
INSERT INTO now_inc VALUES (now()), (now());
INSERT INTO now_inc VALUES (now());
COMMIT;
----
COMPLETE 0
COMPLETE 2
COMPLETE 1
COMPLETE 0

statement ok
SELECT mz_unsafe.mz_sleep(0.002)

statement ok
INSERT INTO now_inc VALUES (now())

query I
SELECT count(*) FROM now_inc GROUP BY a ORDER BY a
----
1
3
1

statement ok
CREATE TABLE dec (d mz_timestamp)

# Verify that mz_now cannot be used in INSERTs (until
# we decide what it should do in an INSERT). Previously it could, but
# would produce different times even in a transaction (hence the test
# in this file). Although this error message is misleading, we don't
# expect users to do this, so it's ok for now.
statement error db error: ERROR: calls to mz_now in write statements are not supported
INSERT INTO dec VALUES (mz_now())

statement ok
CREATE VIEW v AS SELECT mz_now()

# Check that read-then-write can look through transitive dependencies.
statement error db error: ERROR: calls to mz_now in write statements are not supported
INSERT INTO dec SELECT * FROM v

# DELETE and UPDATE also cannot use mz_now.
statement error db error: ERROR: calls to mz_now in write statements are not supported
UPDATE dec SET d = mz_now()

statement error db error: ERROR: calls to mz_now in write statements are not supported
DELETE FROM dec WHERE d = mz_now()

# RETURNING also nope.
statement error db error: ERROR: calls to mz_now in write statements are not supported
INSERT INTO dec VALUES (0) RETURNING mz_now()