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

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_eager_delta_joins TO false;
----
COMPLETE 0

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
# -----------------------

# A query that will produce different plans depending on the value of the
# `enable eager delta joins` feature flag.
statement ok
CREATE MATERIALIZED VIEW mv AS
SELECT
  t1.y as c1,
  t2.y as c2,
  t3.y as c3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;

# EXPLAIN and EXPLAIN REPLAN should coincide.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
MATERIALIZED VIEW mv;
----
materialize.public.mv:
  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: quickstart

EOF

# EXPLAIN and EXPLAIN REPLAN should coincide.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
REPLAN MATERIALIZED VIEW mv;
----
materialize.public.mv:
  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: quickstart

EOF

# EXPLAIN REPLAN WITH(enable eager delta joins) should differ.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, enable eager delta joins, humanized expressions) AS VERBOSE TEXT FOR
REPLAN MATERIALIZED VIEW mv;
----
materialize.public.mv:
  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: quickstart

EOF

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

# Same as above, but as an indexed view.
statement ok
CREATE VIEW v AS
SELECT
  t1.y as c1,
  t2.y as c2,
  t3.y as c3
FROM
  t1, t2, t3
where
  t1.x = t2.x AND
  t2.y = t3.y;

statement ok
CREATE INDEX v_idx ON v(c1);

# EXPLAIN and EXPLAIN REPLAN should coincide.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
INDEX v_idx;
----
materialize.public.v_idx:
  ArrangeBy keys=[[#0{c1}]]
    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: quickstart

EOF

# EXPLAIN and EXPLAIN REPLAN should coincide.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
REPLAN INDEX v_idx;
----
materialize.public.v_idx:
  ArrangeBy keys=[[#0{c1}]]
    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: quickstart

EOF

# EXPLAIN REPLAN WITH(enable eager delta joins) should differ.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, enable eager delta joins, humanized expressions) AS VERBOSE TEXT FOR
REPLAN INDEX v_idx;
----
materialize.public.v_idx:
  ArrangeBy keys=[[#0{c1}]]
    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: quickstart

EOF