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

# Regression test for https://github.com/MaterializeInc/materialize/pulls/16036
mode cockroach

statement ok
CREATE TABLE t1 (v1 TEXT, k1 INTEGER, k2 INTEGER);

statement ok
CREATE TABLE t2 (v2 TEXT, k1 INTEGER);

statement ok
CREATE TABLE t3 (v3 TEXT, k2 INTEGER);

statement ok
CREATE INDEX i1 ON t1 (k1);

statement ok
CREATE INDEX i2 ON t2 (k1);

statement ok
CREATE INDEX i3 ON t3 (k2);

statement ok
CREATE VIEW test AS SELECT v1, v2, v3 FROM t1, t2, t3 where t1.k1 = t2.k1 and t1.k2 = t3.k2;

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT * FROM test;
----
Explained Query:
  Join::Delta
    plan_path[0]
      delta_stage[1]
        closure
          project=(#1..=#3)
        lookup={ relation=2, key=[#1{k2}] }
        stream={ key=[#1{k2}], thinning=(#0, #2) }
      delta_stage[0]
        closure
          project=(#1..=#3)
        lookup={ relation=1, key=[#1{k1}] }
        stream={ key=[#1{k1}], thinning=(#0, #2) }
      initial_closure
        project=(#1, #0, #2)
      source={ relation=0, key=[#1{k1}] }
    plan_path[1]
      delta_stage[1]
        closure
          project=(#1..=#3)
        lookup={ relation=2, key=[#1{k2}] }
        stream={ key=[#1{k2}], thinning=(#0, #2) }
      delta_stage[0]
        closure
          project=(#2, #3, #1)
        lookup={ relation=0, key=[#1{k1}] }
        stream={ key=[#1{k1}], thinning=(#0) }
      initial_closure
        project=(#1, #0)
      source={ relation=1, key=[#1{k1}] }
    plan_path[2]
      delta_stage[1]
        closure
          project=(#1, #3, #2)
        lookup={ relation=1, key=[#1{k1}] }
        stream={ key=[#1{k1}], thinning=(#0, #2) }
      delta_stage[0]
        closure
          project=(#2, #3, #1)
        lookup={ relation=0, key=[#2{k2}] }
        stream={ key=[#1{k2}], thinning=(#0) }
      initial_closure
        project=(#1, #0)
      source={ relation=2, key=[#1{k2}] }
    ArrangeBy
      raw=true
      arrangements[0]={ key=[#1{k1}], permutation={#0: #1, #1: #0}, thinning=(#0, #2) }
      arrangements[1]={ key=[#2{k2}], permutation={#0: #1, #1: #2, #2: #0}, thinning=(#0, #1) }
      types=[text?, integer, integer]
      Get::Arrangement materialize.public.t1
        project=(#1, #0, #2)
        filter=((#0{k1}) IS NOT NULL AND (#2{k2}) IS NOT NULL)
        key=#1{k1}
        raw=false
        arrangements[0]={ key=[#1{k1}], permutation={#0: #1, #1: #0}, thinning=(#0, #2) }
        types=[text?, integer?, integer?]
    Get::PassArrangements materialize.public.t2
      raw=false
      arrangements[0]={ key=[#1{k1}], permutation={#0: #1, #1: #0}, thinning=(#0) }
      types=[text?, integer?]
    Get::PassArrangements materialize.public.t3
      raw=false
      arrangements[0]={ key=[#1{k2}], permutation={#0: #1, #1: #0}, thinning=(#0) }
      types=[text?, integer?]

Used Indexes:
  - materialize.public.i1 (*** full scan ***)
  - materialize.public.i2 (delta join lookup)
  - materialize.public.i3 (delta join lookup)

Target cluster: quickstart

EOF