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

mode cockroach

# Start from a pristine server
reset-server

query T
SHOW extra_float_digits
----
3

statement ok
SET extra_float_digits=2
----

query T
SHOW extra_float_digits
----
2

simple
SET extra_float_digits=1;
SHOW extra_float_digits;
----
COMPLETE 0
1
COMPLETE 1

query T
SHOW extra_float_digits
----
1

simple
SET extra_float_digits=0;
SHOW extra_float_digits;
ROLLBACK;
----
COMPLETE 0
0
COMPLETE 1
COMPLETE 0

query T
SHOW extra_float_digits
----
1

simple
SET LOCAL extra_float_digits=-1;
SHOW extra_float_digits;
----
COMPLETE 0
-1
COMPLETE 1

query T
SHOW extra_float_digits
----
1

simple
SET LOCAL extra_float_digits=-2;
SHOW extra_float_digits;
ROLLBACK
----
COMPLETE 0
-2
COMPLETE 1
COMPLETE 0

query T
SHOW extra_float_digits
----
1

# Test resetting a variable.

statement ok
SET extra_float_digits=42

statement ok
SET extra_float_digits=DEFAULT

query T
SHOW extra_float_digits
----
3

statement ok
SET extra_float_digits=42

simple
SET LOCAL extra_float_digits=DEFAULT;
SHOW extra_float_digits;
----
COMPLETE 0
3
COMPLETE 1

query T
SHOW extra_float_digits
----
42

statement ok
RESET extra_float_digits

query T
SHOW extra_float_digits
----
3

# Test that resetting a read-only variable succeeds.

statement ok
SET server_version=DEFAULT

statement ok
RESET server_version

# Test that resetting an unknown variable fails.

statement error unrecognized configuration parameter
SET does_not_exist = DEFAULT

statement error unrecognized configuration parameter
RESET does_not_exist

# Test transaction isolation

query T
SHOW transaction_isolation
----
strict serializable

statement ok
SET transaction_isolation = serializable

query T
SHOW transaction_isolation
----
serializable

statement ok
SET transaction_isolation = 'read committed'

query T
SHOW transaction_isolation
----
serializable

statement error invalid value for parameter "transaction_isolation": "snapshot isolation"
SET transaction_isolation = 'snapshot isolation'

statement ok
SET transaction_isolation = 'strict serializable'

query T
SHOW transaction_isolation
----
strict serializable

statement ok
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE

query T
SHOW transaction_isolation
----
serializable

statement ok
SET TRANSACTION ISOLATION LEVEL STRICT SERIALIZABLE

query T
SHOW transaction_isolation
----
serializable

statement ok
BEGIN;

statement ok
SET TRANSACTION ISOLATION LEVEL STRICT SERIALIZABLE

query T
SHOW transaction_isolation
----
strict serializable

statement ok
COMMIT

query T
SHOW transaction_isolation
----
serializable

## Last isolation level write wins
statement ok
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE, ISOLATION LEVEL STRICT SERIALIZABLE

query T
SHOW transaction_isolation
----
strict serializable

statement error Expected transaction mode, found EOF
SET TRANSACTION

statement error SET TRANSACTION <access-mode> are not supported
SET TRANSACTION READ ONLY

# Test that a failed transaction will not commit var changes.

statement ok
CREATE TABLE t (i INT);

simple conn=1
SET extra_float_digits=-3;
COMMIT;
BEGIN;
SET extra_float_digits=-4;
INSERT INTO t VALUES (1);
----
COMPLETE 0
COMPLETE 0
COMPLETE 0
COMPLETE 0
COMPLETE 1

simple conn=2
DROP TABLE t;
----
COMPLETE 0

simple conn=1
COMMIT;
----
db error: ERROR: unknown catalog item 'u1'

simple conn=1
SHOW extra_float_digits
----
-3
COMPLETE 1

# Test the user= syntax for sqllogictest itself.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET max_tables = 100
----
COMPLETE 0

statement error parameter "idle_in_transaction_session_timeout" requires a "duration" value
SET idle_in_transaction_session_timeout TO '-10ms'

statement error unrecognized configuration parameter
SELECT current_setting('unknown')

statement error does not exist
SELECT current_setting(true)

query T
SELECT current_setting('unknown', true)
----
NULL

query T
SELECT current_setting('unknown', 'true')
----
NULL

statement error does not exist
SELECT current_setting('datestyle', 3)

statement error unrecognized configuration parameter
SELECT current_setting('unknown', false)

query T
SELECT current_setting('dateSTYLE')
----
ISO, MDY

statement ok
SET cluster_replica = 'r1'

query T
SELECT current_setting('cluster') || '.' || current_setting('cluster_replica')
----
quickstart.r1

query T
SELECT current_setting('max_tables')
----
100

# Test multi-valued variable planning.

simple
set datestyle = iso,mdy;
show datestyle;
set datestyle = iso, mdy;
show datestyle;
set datestyle = "iso,mdy";
show datestyle;
set datestyle = "iso, mdy";
show datestyle;
set datestyle = 'iso,mdy';
show datestyle;
set datestyle = 'iso, mdy';
show datestyle;
set datestyle = '"iso", "mdy", "iso", "mdy"', "mdy", "iso", "ISO", "IsO", "mDy";
show datestyle;
----
COMPLETE 0
ISO, MDY
COMPLETE 1
COMPLETE 0
ISO, MDY
COMPLETE 1
COMPLETE 0
ISO, MDY
COMPLETE 1
COMPLETE 0
ISO, MDY
COMPLETE 1
COMPLETE 0
ISO, MDY
COMPLETE 1
COMPLETE 0
ISO, MDY
COMPLETE 1
COMPLETE 0
ISO, MDY
COMPLETE 1

statement error parameter "database" cannot have value "one","two": expects a single value
set database = one, two

# Test invalid values for float vars
# TODO(jkosh44) the error messages here are bad, but the best we can do right now. A user will
# never see this anyway since it's only exposed to admins.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET max_credit_consumption_rate = '-100.5'
----
db error: ERROR: parameter "max_credit_consumption_rate" cannot have value "-100.5": only supports non-negative, non-NaN numeric values

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET max_credit_consumption_rate = NaN
----
db error: ERROR: parameter "max_credit_consumption_rate" cannot have value "NaN": only supports non-negative, non-NaN numeric values

statement ok
SET SCHEMA 'non-resolvable-name'

statement ok
SET SCHEMA TO 'non-resolvable-name'

statement ok
SET SCHEMA = 'non-resolvable-name'

query T
SHOW SCHEMA
----
NULL

statement ok
SET SCHEMA TO 'public'

query T
SHOW SCHEMA
----
public

statement error Expected end of statement, found comma
SET SCHEMA TO public, private, playground

statement ok
CREATE SCHEMA s

statement ok
SET SCHEMA TO s

query T
SHOW SCHEMA
----
s

statement ok
RESET SCHEMA

query T
SHOW SCHEMA
----
public

statement ok
SET SCHEMA TO s

query T
SHOW SCHEMA
----
s

statement ok
SET SCHEMA TO DEFAULT

query T
SHOW SCHEMA
----
public

query error db error: ERROR: SHOW variable in subqueries not yet supported
SELECT * FROM (show client_encoding)

# 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

statement ok
SET max_query_result_size = 100

query T
SELECT generate_series(1, 2)
----
1
2

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

# Regression for database-issues#6866
# Ensure duplicate rows don't overcount bytes in the presence of LIMIT.
query T
SELECT x FROM (VALUES ('{"row": 1}')) AS a (x), generate_series(1, 50000) LIMIT 1
----
{"row": 1}

# Ensure that a large ordering key but small projection does not count against the result size limit.
query I
select 1 from (select array_agg(generate_series) x from generate_series(1, 1000000)) order by x limit 1
----
1

statement ok
RESET max_query_result_size

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

statement ok
CREATE ROLE parker;

statement ok
ALTER ROLE parker SET cluster TO foo;

statement error invalid value for parameter "transaction_isolation": "garbage"
ALTER ROLE parker SET transaction_isolation TO 'garbage';

query T
SHOW cluster;
----
quickstart

query TTT
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----
parker      cluster     foo

simple conn=parker_1,user=parker
SHOW cluster;
----
foo
COMPLETE 1

simple conn=parker_1,user=parker
ALTER ROLE parker RESET cluster;
----
COMPLETE 0

query TTT
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----

# Altering the Role defaults does not take effect until you restart your session, this matches
# Postgres.
simple conn=parker_1,user=parker
SHOW cluster;
----
foo
COMPLETE 1

# A new connection gets us a new session.
simple conn=parker_2,user=parker
SHOW cluster;
----
quickstart
COMPLETE 1

# Roles can alter their own variables.
simple conn=parker_2,user=parker
ALTER ROLE parker SET cluster TO bar;
----
COMPLETE 0

query TTT
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----
parker      cluster         bar

simple conn=parker_3,user=parker
SHOW cluster;
----
bar
COMPLETE 1

simple conn=parker_3,user=parker
SET cluster TO session_var;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
session_var
COMPLETE 1

simple conn=parker_3,user=parker
RESET cluster;
----
COMPLETE 0

simple conn=parker_3,user=parker
BEGIN;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
bar
COMPLETE 1

simple conn=parker_3,user=parker
SET cluster TO in_transaction;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
in_transaction
COMPLETE 1

simple conn=parker_3,user=parker
COMMIT;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
in_transaction
COMPLETE 1

simple conn=parker_3,user=parker
RESET cluster;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
bar
COMPLETE 1

simple conn=parker_3,user=parker
BEGIN;
----
COMPLETE 0

simple conn=parker_3,user=parker
SET LOCAL cluster TO local_transaction;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
local_transaction
COMPLETE 1

simple conn=parker_3,user=parker
ROLLBACK;
----
COMPLETE 0

simple conn=parker_3,user=parker
SHOW cluster;
----
bar
COMPLETE 1

simple conn=parker_3,user=parker
SHOW search_path;
----
public
COMPLETE 1

simple conn=parker_3,user=parker
ALTER ROLE parker SET search_path TO foo, bar, baz;
----
COMPLETE 0

query TTT
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----
parker      cluster         bar
parker      search_path     foo,␠bar,␠baz

simple conn=parker_4,user=parker
SHOW search_path;
----
foo, bar, baz
COMPLETE 1

simple conn=parker_3,user=parker
ALTER ROLE parker SET search_path TO DEFAULT;
----
COMPLETE 0

simple conn=parker_5,user=parker
SHOW search_path;
----
public
COMPLETE 1

query TTT
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----
parker      cluster         bar

statement ok
CREATE ROLE joe;

# You can set variable values for yourself, but you need CREATEROLE privileges to set them for
# other roles.
simple conn=parker_3,user=parker
ALTER ROLE joe SET cluster TO wont_work;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'parker' role needs CREATEROLE privileges on SYSTEM

statement ok
ALTER ROLE joe SET cluster TO will_work

simple conn=joe,user=joe
SHOW cluster;
----
will_work
COMPLETE 1

query TTT rowsort
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----
parker      cluster         bar
joe         cluster         will_work

# Should not be able to set a system variable.
simple conn=parker_3,user=parker
ALTER ROLE parker SET metrics_retention TO 10;
----
db error: ERROR: unrecognized configuration parameter "metrics_retention"

# Should not be able to set a variable that does not exist.
simple conn=parker_3,user=parker
ALTER ROLE parker SET i_am_a_fake_variable TO 10;
----
db error: ERROR: unrecognized configuration parameter "i_am_a_fake_variable"

# mz_system cannot set a system variable.
simple conn=mz_system,user=mz_system
ALTER ROLE parker SET metrics_retention TO 10;
----
db error: ERROR: unrecognized configuration parameter "metrics_retention"

# Role defaults should override a change to the system default.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET cluster TO new_system_default
----
COMPLETE 0

simple conn=joe_1,user=joe
SHOW cluster;
----
will_work
COMPLETE 1

# Reset the role default, so new sessions should get the system default.
simple conn=joe_1,user=joe
ALTER ROLE joe RESET cluster;
----
COMPLETE 0

query TTT
SELECT r.name, rp.parameter_name, rp.parameter_value
FROM mz_role_parameters rp
LEFT JOIN mz_roles r ON rp.role_id = r.id;
----
parker      cluster         bar

simple conn=joe_2,user=joe
SHOW cluster;
----
new_system_default
COMPLETE 1

simple conn=joe_2,user=joe
BEGIN;
----
COMPLETE 0

simple conn=joe_2,user=joe
SET LOCAL cluster TO 'txn_specific_cluster';
----
COMPLETE 0

simple conn=joe_2,user=joe
SHOW cluster;
----
txn_specific_cluster
COMPLETE 1

simple conn=joe_2,user=joe
COMMIT;
----
COMPLETE 0

simple conn=joe_2,user=joe
SHOW cluster;
----
new_system_default
COMPLETE 1

simple conn=joe_2,user=joe
BEGIN;
----
COMPLETE 0

simple conn=joe_2,user=joe
SET cluster TO 'foo_bar';
----
COMPLETE 0

simple conn=joe_2,user=joe
SHOW cluster;
----
foo_bar
COMPLETE 1

simple conn=joe_2,user=joe
ROLLBACK;
----
COMPLETE 0

simple conn=joe_2,user=joe
SHOW cluster;
----
new_system_default
COMPLETE 1

# Not all session variables can be overriden.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET server_version TO this_wont_work
----
db error: ERROR: unrecognized configuration parameter "server_version"

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET emit_trace_id_notice TO true
----
db error: ERROR: unrecognized configuration parameter "emit_trace_id_notice"