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

# Test the ability to catch plan changes using the `enable eager delta joins`
# config flag in EXPLAIN. This test can be deleted when the feature flag is
# removed.

mode cockroach

# Role-based restrictions
# -----------------------------------------------------------

# Regular users cannot create clusters with FEATURES yet.
statement error db error: ERROR: FEATURES not supported for non\-system users
CREATE CLUSTER FOO SIZE = '1' FEATURES (ENABLE EAGER DELTA JOINS = TRUE);

# Cluster and system config for the test DDL statements below
# -----------------------------------------------------------

simple conn=mz_system,user=mz_system
CREATE CLUSTER c1 SIZE = '1' FEATURES (ENABLE EAGER DELTA JOINS = TRUE);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE CLUSTER c2 SIZE = '1' FEATURES (ENABLE EAGER DELTA JOINS = FALSE);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT ALL ON CLUSTER c1 TO materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT ALL ON CLUSTER c2 TO materialize;
----
COMPLETE 0

# Schema for the test DDL statements below
# ----------------------------------------

statement ok
CREATE TABLE t1 (
  x int,
  y int
);

statement ok
CREATE TABLE t2 (
  x int,
  y int
);

statement ok
CREATE TABLE t3 (
  x int,
  y int
);

# Test materialized views
# -----------------------

# Should be created with the feature flag turned on.
statement ok
CREATE MATERIALIZED VIEW mv1 IN CLUSTER c1 AS
SELECT
  t1.y as f1,
  t2.y as f2,
  t3.y as f3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;

# Should be created with the feature flag turned off.
statement ok
CREATE MATERIALIZED VIEW mv2 IN CLUSTER c2 AS
SELECT
  t1.y as f1,
  t2.y as f2,
  t3.y as f3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;

# EXPLAIN mv1 in c1 (should be running with the feature flag turned on).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
MATERIALIZED VIEW mv1;
----
materialize.public.mv1:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=delta
      implementation
        %0:t1 » %1:t2[#0{x}]K » %2:t3[#0{y}]K
        %1:t2 » %0:t1[#0{x}]K » %2:t3[#0{y}]K
        %2:t3 » %1:t2[#1{y}]K » %0:t1[#0{x}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}], [#1{y}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# EXPLAIN mv2 in c2 (should be running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
MATERIALIZED VIEW mv2;
----
materialize.public.mv2:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      implementation
        %0:t1[#0{x}]K » %1:t2[#0{x}]K » %2:t3[#0{y}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c2

EOF

# EXPLAIN REPLAN mv1 in c1 (should be running with the feature flag turned on).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
REPLAN MATERIALIZED VIEW mv1;
----
materialize.public.mv1:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=delta
      implementation
        %0:t1 » %1:t2[#0{x}]K » %2:t3[#0{y}]K
        %1:t2 » %0:t1[#0{x}]K » %2:t3[#0{y}]K
        %2:t3 » %1:t2[#1{y}]K » %0:t1[#0{x}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}], [#1{y}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# EXPLAIN REPLAN mv1 in c1 with an explain-level feature override (should be
# running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS, ENABLE EAGER DELTA JOINS = FALSE) AS VERBOSE TEXT FOR
REPLAN MATERIALIZED VIEW mv1;
----
materialize.public.mv1:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      implementation
        %0:t1[#0{x}]K » %1:t2[#0{x}]K » %2:t3[#0{y}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# EXPLAIN CREATE in c1 with an explain-level feature override (should be
# running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS, ENABLE EAGER DELTA JOINS = FALSE) AS VERBOSE TEXT FOR
CREATE MATERIALIZED VIEW mv1 IN CLUSTER c1 AS
SELECT
  t1.y as f1,
  t2.y as f2,
  t3.y as f3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;
----
materialize.public.mv1:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      implementation
        %0:t1[#0{x}]K » %1:t2[#0{x}]K » %2:t3[#0{y}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# Test indexed views
# ------------------

# Same as the mv1 / mv2 definitions above.
statement ok
CREATE VIEW v AS
SELECT
  t1.y as f1,
  t2.y as f2,
  t3.y as f3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;

statement ok
CREATE INDEX v_idx_in_c1 IN CLUSTER c1 ON v(f1);

statement ok
CREATE INDEX v_idx_in_c2 IN CLUSTER c2 ON v(f1);

# EXPLAIN v in c2 (should be running with the feature flag turned on).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
INDEX v_idx_in_c1;
----
materialize.public.v_idx_in_c1:
  ArrangeBy keys=[[#0{f1}]]
    ReadGlobalFromSameDataflow materialize.public.v

materialize.public.v:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=delta
      implementation
        %0:t1 » %1:t2[#0{x}]K » %2:t3[#0{y}]K
        %1:t2 » %0:t1[#0{x}]K » %2:t3[#0{y}]K
        %2:t3 » %1:t2[#1{y}]K » %0:t1[#0{x}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}], [#1{y}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# EXPLAIN v in c2 (should be running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
INDEX v_idx_in_c2;
----
materialize.public.v_idx_in_c2:
  ArrangeBy keys=[[#0{f1}]]
    ReadGlobalFromSameDataflow materialize.public.v

materialize.public.v:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      implementation
        %0:t1[#0{x}]K » %1:t2[#0{x}]K » %2:t3[#0{y}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c2

EOF

# EXPLAIN REPLAN v in c1 (should be running with the feature flag turned on).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
REPLAN INDEX v_idx_in_c1;
----
materialize.public.v_idx_in_c1:
  ArrangeBy keys=[[#0{f1}]]
    ReadGlobalFromSameDataflow materialize.public.v

materialize.public.v:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=delta
      implementation
        %0:t1 » %1:t2[#0{x}]K » %2:t3[#0{y}]K
        %1:t2 » %0:t1[#0{x}]K » %2:t3[#0{y}]K
        %2:t3 » %1:t2[#1{y}]K » %0:t1[#0{x}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}], [#1{y}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# EXPLAIN REPLAN v in c1 with an explain-level feature override (should be
# running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS, ENABLE EAGER DELTA JOINS = FALSE) AS VERBOSE TEXT FOR
REPLAN INDEX v_idx_in_c1;
----
materialize.public.v_idx_in_c1:
  ArrangeBy keys=[[#0{f1}]]
    ReadGlobalFromSameDataflow materialize.public.v

materialize.public.v:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      implementation
        %0:t1[#0{x}]K » %1:t2[#0{x}]K » %2:t3[#0{y}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# Delete the existing index in order to get the expected output in the next
# test.
statement ok
DROP INDEX v_idx_in_c1;

# EXPLAIN CREATE in c1 with an explain-level feature override (should be
# running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(JOIN IMPLEMENTATIONS, HUMANIZED EXPRESSIONS, ENABLE EAGER DELTA JOINS = FALSE) AS VERBOSE TEXT FOR
CREATE INDEX v_idx_in_c1 IN CLUSTER c1 ON v(f1);
----
materialize.public.v_idx_in_c1:
  ArrangeBy keys=[[#0{f1}]]
    ReadGlobalFromSameDataflow materialize.public.v

materialize.public.v:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      implementation
        %0:t1[#0{x}]K » %1:t2[#0{x}]K » %2:t3[#0{y}]K
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

# Test peeks
# ----------

statement ok
SET cluster = c1;

# EXPLAIN in c1 (should be running with the feature flag turned on).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  t1.y as f1,
  t2.y as f2,
  t3.y as f3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;
----
Explained Query:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=delta
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}], [#1{y}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c1

EOF

statement ok
SET cluster = c2;

# EXPLAIN in c2 (should be running with the feature flag turned off).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
  t1.y as f1,
  t2.y as f2,
  t3.y as f3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;
----
Explained Query:
  Project (#1{y}, #3{y}, #3{y})
    Join on=(#0{x} = #2{x} AND #3{y} = #4{y}) type=differential
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL
          ReadStorage materialize.public.t1
      ArrangeBy keys=[[#0{x}]]
        Filter (#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL
          ReadStorage materialize.public.t2
      ArrangeBy keys=[[#0{y}]]
        Project (#1{y})
          Filter (#1{y}) IS NOT NULL
            ReadStorage materialize.public.t3

Source materialize.public.t1
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{x}) IS NOT NULL AND (#1{y}) IS NOT NULL)
Source materialize.public.t3
  filter=((#1{y}) IS NOT NULL)

Target cluster: c2

EOF