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

# This is to make the id in the EXPLAIN SHOW VIEWS test stable.
reset-server

statement ok
CREATE TABLE t (i INT);

# Transactions that don't start with sources or mz_now can later include sources.
simple
BEGIN;
SELECT row(1, 2);
SELECT 1 FROM mz_types LIMIT 1;
SELECT 3 FROM pg_type LIMIT 1; -- mz_catalog and pg_catalog should be treated as the same schema
----
COMPLETE 0
(1,2)
COMPLETE 1
1
COMPLETE 1
3
COMPLETE 1

# But we can only change timedomains once.
query error Transactions can only reference objects in the same timedomain.
SELECT * FROM t

# Referring to the timestamp prevents including sources later.
simple
ROLLBACK;
BEGIN;
SELECT mz_now() LIMIT 0;
----
COMPLETE 0
COMPLETE 0
COMPLETE 0

query error Transactions can only reference objects in the same timedomain.
SELECT 1 FROM mz_types LIMIT 1

simple
ROLLBACK;
BEGIN;
SELECT 1 FROM mz_types LIMIT 1;
-- Use a timestamp-independent statement here, which should not allow
-- the timedomain to change because the transaction's previous statement
-- established a timedomain.
SELECT 2;
----
COMPLETE 0
COMPLETE 0
1
COMPLETE 1
2
COMPLETE 1

query error db error: ERROR: querying the following items "materialize\.public\.t" is not allowed from the "mz_catalog_server" cluster
SELECT * FROM t;

statement ok
ROLLBACK

# Test that user table and mz_catalog system tables cannot be mixed in a transaction because they
# belong to different timedomains.

statement ok
BEGIN;

query I rowsort
SELECT * FROM t
----

# This may stop failing in the future if a view in pg_catalog starts to depend on this. In that case
# just change this query to select from an object that isn't referenced from pg_catalog.
query error Transactions can only reference objects in the same timedomain.
SELECT * FROM mz_internal.mz_show_default_privileges

statement ok
ROLLBACK

# Test that timeline dependent queries can be included in transaction.

statement ok
BEGIN

query I rowsort
SELECT * FROM t
----

query I rowsort
SELECT mz_now() LIMIT 0
----

statement ok
COMMIT

# Test that timeline independent queries can be included at the end transaction.

statement ok
BEGIN

query I rowsort
SELECT 1 FROM pg_attribute LIMIT 1
----
1

query I rowsort
SELECT 1 FROM pg_catalog.pg_am LIMIT 1
----

statement ok
COMMIT

# Test that timeline independent queries can be included at the start transaction.

statement ok
BEGIN

query I rowsort
SELECT 1 FROM pg_catalog.pg_am LIMIT 1
----

query I rowsort
SELECT 1 FROM pg_attribute LIMIT 1
----
1

statement ok
COMMIT

statement ok
CREATE VIEW v AS SELECT 1

statement ok
BEGIN

query I rowsort
SELECT 1 FROM v LIMIT 1
----
1

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

statement ok
COMMIT

statement ok
BEGIN

query I rowsort
SELECT 1
----
1

query I rowsort
SELECT 1 FROM pg_attribute LIMIT 1
----
1

statement ok
COMMIT

# Verify that system tables are always included in read txns, even if not
# mentioned in the first query.
simple
BEGIN;
SELECT * FROM t;
SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = 2249;
COMMIT;
----
COMPLETE 0
COMPLETE 0
t,pg_catalog,record
COMPLETE 1
COMPLETE 0

simple
BEGIN;
SELECT row(1, 2);
SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = 2249;
COMMIT;
----
COMPLETE 0
(1,2)
COMPLETE 1
t,pg_catalog,record
COMPLETE 1
COMPLETE 0

# Test that queries aren't auto-routed to mz_catalog_server mid-transaction.

statement ok
BEGIN

query I
SELECT * FROM t
----

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SHOW VIEWS
----
Explained Query:
  With
    cte l0 =
      Project (#0{id}..=#2{name}, #4{comment})
        Join on=(#0{id} = #3{id}) type=differential
          ArrangeBy keys=[[#0{id}]]
            Project (#0{id}, #2{schema_id}, #3{name})
              ReadStorage mz_catalog.mz_views
          ArrangeBy keys=[[#0{id}]]
            Project (#0{id}, #3{comment})
              Filter (#2{object_sub_id}) IS NULL AND (#1{object_type} = "view")
                ReadStorage mz_internal.mz_comments
    cte l1 =
      Project (#0{id}, #3{name})
        Filter (#2{schema_id} = "u3")
          ReadStorage mz_catalog.mz_views
  Return
    Project (#0{name}, #2)
      Map (coalesce(#1{comment}, ""))
        Union
          Map (null)
            Union
              Negate
                Project (#1{name})
                  Join on=(#0{id} = #2{id}) type=differential
                    ArrangeBy keys=[[#0{id}]]
                      Get l1
                    ArrangeBy keys=[[#0{id}]]
                      Distinct project=[#0{id}]
                        Project (#0{id})
                          Get l0
              Project (#1{name})
                Get l1
          Project (#2{name}, #3{comment})
            Filter (#1{schema_id} = "u3")
              Get l0

Source mz_catalog.mz_views
Source mz_internal.mz_comments
  filter=((#1{object_type} = "view") AND (#2{object_sub_id}) IS NULL)

Target cluster: quickstart

EOF

statement ok
COMMIT