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

# This test may seem simple, but it is surprisingly good at verifying that
# logical timestamp handling for internal inputs is sane.

statement ok
CREATE TABLE t (a INT)

statement ok
INSERT INTO t VALUES (1)

query I rowsort
SELECT a FROM t
----
1

statement ok
INSERT INTO t VALUES (2)

query I rowsort
SELECT a FROM t
----
1
2

statement ok
CREATE TABLE t2 (a INT)

statement ok
INSERT INTO t2 VALUES (1), (3)

query I
SELECT a FROM t NATURAL JOIN t2
----
1

statement ok
INSERT INTO t VALUES (3)

query I rowsort
SELECT a FROM t NATURAL JOIN t2
----
1
3

# Test that updates work in Serializable mode

statement ok
CREATE TABLE t1( x INTEGER)

statement ok
SET transaction_isolation=serializable

statement ok
INSERT INTO t1 VALUES(1)

statement ok
CREATE INDEX t1i1 ON t1(x)

statement ok
UPDATE t1 SET x=1

statement ok
UPDATE t1 SET x=2

statement ok
UPDATE t1 SET x=3

statement ok
UPDATE t1 SET x=4

statement ok
SET transaction_isolation='strict serializable'

query I
SELECT * FROM t1
----
4

# Test that updates work in Strong Session Serializable mode

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_session_timelines = true;
----
COMPLETE 0

statement ok
SET transaction_isolation='strong session serializable'

statement ok
UPDATE t1 SET x=1

statement ok
UPDATE t1 SET x=2

statement ok
UPDATE t1 SET x=3

statement ok
UPDATE t1 SET x=4

statement ok
SET transaction_isolation='strict serializable'

query I
SELECT * FROM t1
----
4

# Test UPDATE AS

statement ok
UPDATE t1 AS m SET x = 5 WHERE m.x < 10

query I
SELECT * FROM t1
----
5

statement ok
UPDATE t1 AS m SET x = 6 WHERE x < 10

query I
SELECT * FROM t1
----
6

statement error db error: ERROR: column "t1\.x" does not exist
UPDATE t1 AS m SET x = 5 WHERE t1.x < 10

# Regression for database-issues#7334
simple
BEGIN;
SELECT 1;
INSERT INTO t VALUES (1) RETURNING 0;
COMMIT;
----
db error: ERROR: INSERT INTO t VALUES (1) RETURNING 0 cannot be run inside a transaction block

# Regression for non-constant VALUES
# VALUES cannot contain a query that references a table.
simple
ROLLBACK;
BEGIN;
INSERT INTO t1 VALUES((SELECT x FROM t1 LIMIT 1));
COMMIT;
----
db error: ERROR: INSERT INTO t1 VALUES ((SELECT x FROM t1 LIMIT 1)) cannot be run inside a transaction block

# VALUES can contain a subselect with no table references.
simple
ROLLBACK;
BEGIN;
INSERT INTO t1 VALUES((SELECT 1));
COMMIT;
----
COMPLETE 0
COMPLETE 0
COMPLETE 1
COMPLETE 0

# Make sure we can't trick the constant checker with things like an ORDER BY.
simple
BEGIN;
INSERT INTO t1 VALUES((SELECT 1 ORDER BY (SELECT x FROM t1 LIMIT 1)));
COMMIT;
----
db error: ERROR: INSERT INTO t1 VALUES ((SELECT 1 ORDER BY (SELECT x FROM t1 LIMIT 1))) cannot be run inside a transaction block

statement ok
ROLLBACK

# Need to disable the result stash, so that we actually exceed max result size
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_compute_peek_response_stash = false
----
COMPLETE 0

# Verify that max_query_result_size doesn't affect read part of RTW queries.
# See database-issues#8099
statement ok
SET max_query_result_size = '8B';

query error db error: ERROR: result exceeds max size of 8 B
SELECT generate_series(1, 1000000)

statement ok
CREATE TABLE t_big (a int);

# Slow in coverage
statement ok
SET statement_timeout = '120s'

statement ok
INSERT INTO t_big SELECT generate_series(1, 1000000)

statement ok
INSERT INTO t SELECT * FROM t_big

# But the internal var does limit.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET max_result_size = '1MB'
----
COMPLETE 0

statement error db error: ERROR: result exceeds max size of 1048.6 KB
INSERT INTO t SELECT * FROM t_big

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET enable_compute_peek_response_stash
----
COMPLETE 0


# Test unmat fns in the SET clause.
statement ok
CREATE TABLE dt (t TIMESTAMP)

statement count 1
INSERT INTO dt VALUES (now())

statement count 1
UPDATE dt SET t = now()

statement error db error: ERROR: calls to mz_now in write statements are not supported
UPDATE dt SET t = mz_now()