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

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
CREATE TABLE t (
  a int,
  b int
);

statement ok
CREATE TABLE u (
  c int,
  d int
);

statement ok
CREATE TABLE v (
  e int,
  f int
);

statement ok
CREATE INDEX t_a_idx ON t(a);

statement ok
CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5;

statement ok
CREATE VIEW iv AS
SELECT * FROM t WHERE a IS NOT NULL;

statement ok
CREATE INDEX iv_a_idx ON iv(a);

statement ok
CREATE INDEX iv_b_idx ON iv(b);

# This is an identical index to the above (on the same object, on the same key)
statement ok
CREATE INDEX iv_b_idx_2 ON iv(b);

statement ok
CREATE MATERIALIZED VIEW mv AS
SELECT * FROM t WHERE a IS NOT NULL;

mode cockroach

# Test constant error.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT 1 / 0
----
Explained Query (fast path):
  Error █

Target cluster: quickstart

EOF

# Test constant with two elements.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
(SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4)
----
Explained Query (fast path):
  Constant
    - ((█, █) x 2)
    - (█, █)

Target cluster: mz_catalog_server

EOF

# Test catalog queries (index found based on cluster auto-routing).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM mz_internal.mz_source_status_history
----
Explained Query (fast path):
  Project (#1{occurred_at}, #0{source_id}, #2{status}..=#5{replica_id})
    ReadIndex on=mz_internal.mz_source_status_history mz_source_status_history_ind=[*** full scan ***]

Used Indexes:
  - mz_internal.mz_source_status_history_ind (*** full scan ***)

Target cluster: mz_catalog_server

EOF

# Test basic linear chains (fast path).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT 1, a + b as c FROM t WHERE a = 5 and b < 0 and a + b > 0
----
Explained Query (fast path):
  Project (#4, #3)
    Filter (#1{b} < █) AND ((#0{a} + #1{b}) > █)
      Map ((█ + #1{b}), █)
        ReadIndex on=materialize.public.t t_a_idx=[lookup value=(█)]

Used Indexes:
  - materialize.public.t_a_idx (lookup)

Target cluster: quickstart

EOF

# Test basic linear chains (slow path).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0
----
Explained Query:
  Project (#3, #2)
    Filter (#1{b} < █) AND (#0{a} > █) AND (#2 > █)
      Map ((#0{a} + #1{b}), █)
        ReadStorage materialize.public.mv

Source materialize.public.mv
  filter=((#0{a} > █) AND (#1{b} < █) AND ((#0{a} + #1{b}) > █))

Target cluster: quickstart

EOF

# Test table functions in the select clause (FlatMap).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT generate_series(a, b) from t
----
Explained Query:
  Project (#2)
    FlatMap generate_series(#0{a}, #1{b}, █)
      ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test TopK.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT * FROM ov
----
Explained Query:
  TopK order_by=[#1{b} asc nulls_last, #0{a} desc nulls_first] limit=█
    ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Finish.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT * FROM t ORDER BY b asc, a desc LIMIT 5
----
Explained Query (fast path):
  Finish order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 output=[#0, #1]
    ReadIndex on=materialize.public.t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Reduce (global).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT abs(min(a) - max(a)) FROM t
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{a}), max(#0{a})]
        Project (#0{a})
          ReadIndex on=t t_a_idx=[*** full scan ***]
  Return
    Project (#2)
      Map (abs((#0{min_a} - #1{max_a})))
        Union
          Get l0
          Map (█, █)
            Union
              Negate
                Project ()
                  Get l0
              Constant
                - ()

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Reduce (local).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT abs(min(a) - max(a)) FROM t GROUP BY b
----
Explained Query:
  Project (#3)
    Map (abs((#1{min_a} - #2{max_a})))
      Reduce group_by=[#1{b}] aggregates=[min(#0{a}), max(#0{a})]
        ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test EXISTS subqueries.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT * FROM t WHERE EXISTS(SELECT * FROM mv WHERE t.a < mv.a) AND EXISTS(SELECT * FROM mv WHERE t.b > mv.b)
----
Explained Query:
  With
    cte l0 =
      Project (#0{a}, #1{b})
        Join on=(#0{a} = #2{a}) type=differential
          ArrangeBy keys=[[#0{a}]]
            ReadIndex on=t t_a_idx=[differential join]
          ArrangeBy keys=[[#0{a}]]
            Distinct project=[#0{a}]
              Project (#0{a})
                Filter (#0{a} < #1{a})
                  CrossJoin type=differential
                    ArrangeBy keys=[[]]
                      Distinct project=[#0{a}]
                        Project (#0{a})
                          ReadIndex on=t t_a_idx=[*** full scan ***]
                    ArrangeBy keys=[[]]
                      Project (#0{a})
                        ReadStorage materialize.public.mv
  Return
    Project (#0{a}, #1{b})
      Join on=(#1{b} = #2{b}) type=differential
        ArrangeBy keys=[[#1{b}]]
          Get l0
        ArrangeBy keys=[[#0{b}]]
          Distinct project=[#0{b}]
            Project (#0{b})
              Filter (#0{b} > #1{b})
                CrossJoin type=differential
                  ArrangeBy keys=[[]]
                    Distinct project=[#0{b}]
                      Project (#1{b})
                        Get l0
                  ArrangeBy keys=[[]]
                    Project (#1{b})
                      ReadStorage materialize.public.mv

Source materialize.public.mv

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***, differential join)

Target cluster: quickstart

EOF

# Test SELECT subqueries.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT (SELECT iv.a FROM iv WHERE iv.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t
----
Explained Query:
  With
    cte l0 =
      Project (#1{b})
        ReadIndex on=t t_a_idx=[*** full scan ***]
    cte l1 =
      Distinct project=[#0{b}]
        Get l0
    cte l2 =
      ArrangeBy keys=[[#0{b}]]
        Get l1
    cte l3 =
      TopK group_by=[#0{b}] limit=█
        Project (#0{b}, #1{a})
          Filter (#0{b}) IS NOT NULL
            Join on=(#0{b} = #2{b}) type=differential
              Get l2
              ArrangeBy keys=[[#1{b}]]
                ReadIndex on=iv iv_b_idx=[differential join]
    cte l4 =
      TopK group_by=[#0{b}] limit=█
        Project (#0{b}, #1{a})
          Join on=(#0{b} = #2{b}) type=differential
            Get l2
            ArrangeBy keys=[[#1{b}]]
              Filter (#1{b}) IS NOT NULL
                ReadStorage materialize.public.mv
  Return
    Project (#2{a}, #4{a})
      Join on=(#0{b} = #1{b} = #3{b}) type=delta
        ArrangeBy keys=[[#0{b}]]
          Get l0
        ArrangeBy keys=[[#0{b}]]
          Union
            Get l3
            Map (█)
              Union
                Negate
                  Project (#0{b})
                    Get l3
                Get l1
        ArrangeBy keys=[[#0{b}]]
          Union
            Get l4
            Map (█)
              Union
                Negate
                  Project (#0{b})
                    Get l4
                Get l1

Source materialize.public.mv
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)
  - materialize.public.iv_b_idx (differential join)

Target cluster: quickstart

EOF

# Test redaction with the equivalences analysis.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted, equivalences) AS VERBOSE TEXT FOR
SELECT * FROM t WHERE EXISTS(SELECT * FROM mv WHERE t.a < mv.a) AND EXISTS(SELECT * FROM mv WHERE t.b > mv.b)
----
Explained Query:
  With
    cte l0 =
      Project (#0{a}, #1{b}) // { equivs: "[[█, (#0{a}) IS NULL]]" }
        Join on=(#0{a} = #2{a}) type=differential // { equivs: "[[#0{a}, #2{a}], [█, (#0{a}) IS NULL]]" }
          ArrangeBy keys=[[#0{a}]] // { equivs: "[]" }
            ReadIndex on=t t_a_idx=[differential join] // { equivs: "[]" }
          ArrangeBy keys=[[#0{a}]] // { equivs: "[[█, (#0{a}) IS NULL]]" }
            Distinct project=[#0{a}] // { equivs: "[[█, (#0{a}) IS NULL]]" }
              Project (#0{a}) // { equivs: "[[█, (#0{a}) IS NULL]]" }
                Filter (#0{a} < #1{a}) // { equivs: "[[█, (#0{a}) IS NULL, (#1{a}) IS NULL], [█, (#0{a} < #1{a})]]" }
                  CrossJoin type=differential // { equivs: "[[█, (#1{a}) IS NULL]]" }
                    ArrangeBy keys=[[]] // { equivs: "[]" }
                      Distinct project=[#0{a}] // { equivs: "[]" }
                        Project (#0{a}) // { equivs: "[]" }
                          ReadIndex on=t t_a_idx=[*** full scan ***] // { equivs: "[]" }
                    ArrangeBy keys=[[]] // { equivs: "[[█, (#0{a}) IS NULL]]" }
                      Project (#0{a}) // { equivs: "[[█, (#0{a}) IS NULL]]" }
                        ReadStorage materialize.public.mv // { equivs: "[[█, (#0{a}) IS NULL]]" }
  Return // { equivs: "[[█, (#0{a}) IS NULL, (#1{b}) IS NULL]]" }
    Project (#0{a}, #1{b}) // { equivs: "[[█, (#0{a}) IS NULL, (#1{b}) IS NULL]]" }
      Join on=(#1{b} = #2{b}) type=differential // { equivs: "[[#1{b}, #2{b}], [█, (#0{a}) IS NULL, (#1{b}) IS NULL]]" }
        ArrangeBy keys=[[#1{b}]] // { equivs: "[[█, (#0{a}) IS NULL]]" }
          Get l0 // { equivs: "[[█, (#0{a}) IS NULL]]" }
        ArrangeBy keys=[[#0{b}]] // { equivs: "[[█, (#0{b}) IS NULL]]" }
          Distinct project=[#0{b}] // { equivs: "[[█, (#0{b}) IS NULL]]" }
            Project (#0{b}) // { equivs: "[[█, (#0{b}) IS NULL]]" }
              Filter (#0{b} > #1{b}) // { equivs: "[[█, (#0{b}) IS NULL, (#1{b}) IS NULL], [█, (#0{b} > #1{b})]]" }
                CrossJoin type=differential // { equivs: "[]" }
                  ArrangeBy keys=[[]] // { equivs: "[]" }
                    Distinct project=[#0{b}] // { equivs: "[]" }
                      Project (#1{b}) // { equivs: "[]" }
                        Get l0 // { equivs: "[[█, (#0{a}) IS NULL]]" }
                  ArrangeBy keys=[[]] // { equivs: "[]" }
                    Project (#1{b}) // { equivs: "[]" }
                      ReadStorage materialize.public.mv // { equivs: "[[█, (#0{a}) IS NULL]]" }

Source materialize.public.mv

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***, differential join)

Target cluster: quickstart

EOF

# Test outer joins (ON syntax).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT t1.a, t2.a
FROM t as t1
LEFT JOIN t as t2 ON t1.b = t2.b
RIGHT JOIN t as t3 ON t2.b = t3.b
----
Explained Query:
  With
    cte l0 =
      Filter (#1{b}) IS NOT NULL
        ReadIndex on=t t_a_idx=[*** full scan ***]
    cte l1 =
      ArrangeBy keys=[[#1{b}]]
        Get l0
    cte l2 =
      ArrangeBy keys=[[#0{b}]]
        Project (#1{b})
          Get l0
    cte l3 =
      Project (#0{a}..=#2{a})
        Join on=(#1{b} = #3{b} = #4{b}) type=delta
          Get l1
          Get l1
          Get l2
  Return
    Union
      Map (█, █)
        Union
          Negate
            Project ()
              Join on=(#0{b} = #1{b}) type=differential
                Get l2
                ArrangeBy keys=[[#0{b}]]
                  Distinct project=[#0{b}]
                    Project (#1{b})
                      Get l3
          Project ()
            ReadIndex on=t t_a_idx=[*** full scan ***]
      Project (#0{a}, #2{a})
        Get l3

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test an IndexedFilter join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT a, max(b)
FROM t
WHERE a = 0
GROUP BY a
----
Explained Query:
  Project (#1, #0{max_b})
    Map (█)
      Reduce aggregates=[max(#0{b})]
        Project (#1{b})
          ReadIndex on=materialize.public.t t_a_idx=[lookup value=(█)]

Used Indexes:
  - materialize.public.t_a_idx (lookup)

Target cluster: quickstart

EOF

# Create index for IndexedFilter test

statement ok
CREATE INDEX t_a_b_idx ON T(a,b)

# Test an IndexedFilter join WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
SELECT a, max(b)
FROM t
WHERE (a = 0 AND b = 1) OR (a = 3 AND b = 4) OR (a = 7 AND b = 8)
GROUP BY a
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[max(#1{b})]
    Project (#0{a}, #1{b})
      ReadIndex on=materialize.public.t t_a_b_idx=[lookup values=[(0, 1); (3, 4); (7, 8)]]

Used Indexes:
  - materialize.public.t_a_b_idx (lookup)

Target cluster: quickstart

EOF

# Test an IndexedFilter join on fast path WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t
WHERE (a = 0 AND b = 1) OR (a = 3 AND b = 4) OR (a = 7 AND b = 8)
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    ReadIndex on=materialize.public.t t_a_b_idx=[lookup values=[(0, 1); (3, 4); (7, 8)]]

Used Indexes:
  - materialize.public.t_a_b_idx (lookup)

Target cluster: quickstart

EOF

# Test materialize#17348.

statement ok
CREATE TABLE r(f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT, f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT *
FROM r AS r0, r AS r1
WHERE
  r0.f0=r1.f0 AND
  r0.f2=r1.f2 AND
  r0.f3=r1.f3 AND
  r0.f4=r1.f4 AND
  r0.f6=r1.f6 AND
  r0.f8=r1.f8 AND
  r0.f9=r1.f9 AND
  r0.f11=r1.f11 AND
  r0.f12=r1.f12 AND
  r0.f13=r1.f13 AND
  r0.f15=r1.f15 AND
  r0.f16=r1.f16;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f0}, #2{f2}..=#4{f4}, #6{f6}, #8{f8}, #9{f9}, #11{f11}..=#13{f13}, #15{f15}, #16{f16}]]
        Filter (#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL
          ReadStorage materialize.public.r
  Return
    Project (#0{f0}..=#16{f16}, #0{f0}, #18{f1}, #2{f2}..=#4{f4}, #22{f5}, #6{f6}, #24{f7}, #8{f8}, #9{f9}, #27{f10}, #11{f11}..=#13{f13}, #31{f14}, #15{f15}, #16{f16})
      Join on=(#0{f0} = #17{f0} AND #2{f2} = #19{f2} AND #3{f3} = #20{f3} AND #4{f4} = #21{f4} AND #6{f6} = #23{f6} AND #8{f8} = #25{f8} AND #9{f9} = #26{f9} AND #11{f11} = #28{f11} AND #12{f12} = #29{f12} AND #13{f13} = #30{f13} AND #15{f15} = #32{f15} AND #16{f16} = #33{f16}) type=differential
        Get l0
        Get l0

Source materialize.public.r
  filter=((#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL)

Target cluster: quickstart

EOF

## linear chains is currently disabled for WMR.
statement error not supported
EXPLAIN OPTIMIZED PLAN WITH(linear chains) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
    foo (a int, b int) AS (SELECT 1, 2 UNION SELECT a, 7 FROM bar),
    bar (a int) as (SELECT a FROM foo)
SELECT * FROM bar;

# Regression test for database-issues#5674: support mz_now() on select from indexed table
# ---

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

statement ok
DROP SCHEMA IF EXISTS public CASCADE;

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

statement ok
CREATE SCHEMA public;

statement ok
CREATE TABLE t(a TIMESTAMP);

statement ok
CREATE DEFAULT INDEX ON t;

# EXPLAIN output is time-dependent, so we don't want show the output here, just
# assert that the query doesn't fail.
statement ok
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t WHERE a < mz_now();

# Regression test for materialize#19177
# ---

statement ok
DROP SCHEMA IF EXISTS public CASCADE;

statement ok
CREATE SCHEMA public;

statement ok
CREATE TABLE t1(x text);

statement ok
CREATE TABLE t2(x text);

statement ok
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t1, t2 WHERE t1.x || mz_internal.mz_session_id()  = t2.x || mz_internal.mz_session_id();

# Regression test for the join visitation part of materialize#19177
statement ok
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t1, t2 WHERE t1.x || mz_now()  = t2.x || mz_now();

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT lag(x, 3, 'default') IGNORE NULLS OVER (ORDER BY x || x)
FROM t1;
----
Explained Query:
  Project (#2)
    Map (record_get[0](#1))
      FlatMap unnest_list(#0{lag})
        Reduce aggregates=[lag[ignore_nulls=true, order_by=[#0{x} asc nulls_last]](row(row(row(#0{x}), row(#0{x}, █, █)), (#0{x} || #0{x})))]
          ReadStorage materialize.public.t1

Source materialize.public.t1

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT first_value(x) OVER (ORDER BY x || x ROWS BETWEEN 5 preceding AND CURRENT ROW)
FROM t1;
----
Explained Query:
  Project (#2)
    Map (record_get[0](#1))
      FlatMap unnest_list(#0{first_value})
        Reduce aggregates=[first_value[order_by=[#0{x} asc nulls_last] rows between 5 preceding and current row](row(row(row(#0{x}), #0{x}), (#0{x} || #0{x})))]
          ReadStorage materialize.public.t1

Source materialize.public.t1

Target cluster: quickstart

EOF

## "Used indexes" tests

statement ok
CREATE TABLE t (
  a int,
  b int
);

statement ok
CREATE TABLE u (
  c int,
  d int
);

# If two indexes exist on the same table, then "Used indexes" should print the one that we are actually going to use

statement ok
CREATE INDEX u_c ON u(c);

statement ok
CREATE INDEX u_d ON u(d);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT *
FROM t, u
WHERE t.b = u.c;
----
Explained Query:
  Project (#0{a}, #1{b}, #1{b}, #3{d})
    Join on=(#1{b} = #2{c}) type=differential
      ArrangeBy keys=[[#1{b}]]
        Filter (#1{b}) IS NOT NULL
          ReadStorage materialize.public.t
      ArrangeBy keys=[[#0{c}]]
        ReadIndex on=u u_c=[differential join]

Source materialize.public.t
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.u_c (differential join)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT *
FROM t, u
WHERE t.b = u.d;
----
Explained Query:
  Project (#0{a}..=#2{c}, #1{b})
    Join on=(#1{b} = #3{d}) type=differential
      ArrangeBy keys=[[#1{b}]]
        Filter (#1{b}) IS NOT NULL
          ReadStorage materialize.public.t
      ArrangeBy keys=[[#1{d}]]
        ReadIndex on=u u_d=[differential join]

Source materialize.public.t
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.u_d (differential join)

Target cluster: quickstart

EOF

statement ok
DROP INDEX u_c;

# Let's test the weird situation that two identical indexes exist.

statement ok
CREATE INDEX t_a_idx_1 ON t(a);

statement ok
CREATE INDEX t_a_idx_2 ON t(a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT *
FROM t, u
WHERE t.a = u.c
----
Explained Query:
  Project (#0{a}, #1{b}, #0{a}, #3{d})
    Join on=(#0{a} = #2{c}) type=differential
      ArrangeBy keys=[[#0{a}]]
        ReadIndex on=t t_a_idx_1=[differential join]
      ArrangeBy keys=[[#0{c}]]
        Filter (#0{c}) IS NOT NULL
          ReadIndex on=u u_d=[*** full scan ***]

Used Indexes:
  - materialize.public.u_d (*** full scan ***)
  - materialize.public.t_a_idx_1 (differential join)

Target cluster: quickstart

EOF

# An index is used two times by the same (self) join. We should show a 1st input and a non-1st input usage.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT *
FROM t AS t1, t AS t2, t AS t3
WHERE t1.a = t2.a AND t2.a = t3.a;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{a}]]
        ReadIndex on=t t_a_idx_1=[delta join lookup, delta join 1st input (full scan)]
  Return
    Project (#0{a}, #1{b}, #0{a}, #3{b}, #0{a}, #5{b})
      Filter (#0{a}) IS NOT NULL
        Join on=(#0{a} = #2{a} = #4{a}) type=delta
          Get l0
          Get l0
          Get l0

Used Indexes:
  - materialize.public.t_a_idx_1 (delta join lookup, delta join 1st input (full scan))

Target cluster: quickstart

EOF

# An index is used in both a join and a full scan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t AS t1, t AS t2
 WHERE t1.a = t2.a)
UNION
(SELECT *
 FROM t
 WHERE b > 5)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{a}]]
        ReadIndex on=t t_a_idx_1=[differential join]
  Return
    Distinct project=[#0{a}, #1{b}]
      Union
        Project (#4, #5)
          Filter (#0{a}) IS NOT NULL
            Map ((#0{a} + #0{a}), (#1{b} + #3{b}))
              Join on=(#0{a} = #2{a}) type=differential
                Get l0
                Get l0
        Filter (#1{b} > █)
          ReadIndex on=t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***, differential join)

Target cluster: quickstart

EOF

# An index exists that can't be used for the join because of having the wrong key.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t AS t1, t AS t2
 WHERE t1.b = t2.b)
UNION
(SELECT *
 FROM t
 WHERE b > 5)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]]
        Filter (#1{b}) IS NOT NULL
          ReadIndex on=t t_a_idx_1=[*** full scan ***]
  Return
    Distinct project=[#0{a}, #1{b}]
      Union
        Project (#4, #5)
          Map ((#0{a} + #2{a}), (#1{b} + #1{b}))
            Join on=(#1{b} = #3{b}) type=differential
              Get l0
              Get l0
        Filter (#1{b} > █)
          ReadIndex on=t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***)

Target cluster: quickstart

EOF

# Similar to the previous test, but exercises the full scan code inside the context loop of the Get case in
# `collect_index_reqs_inner`, where we don't have an index for the requested key.

statement ok
CREATE TABLE t_non_null (
  a int NOT NULL,
  b int NOT NULL
);

statement ok
CREATE INDEX t_non_null_a_idx ON t_non_null(a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t_non_null AS t1, t_non_null AS t2
 WHERE t1.b = t2.b)
UNION
(SELECT *
 FROM t_non_null
 WHERE b > 5)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]]
        ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]
  Return
    Distinct project=[#0{a}, #1{b}]
      Union
        Project (#4, #5)
          Map ((#0{a} + #2{a}), (#1{b} + #1{b}))
            Join on=(#1{b} = #3{b}) type=differential
              Get l0
              Get l0
        Filter (#1{b} > █)
          ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_non_null_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# This has 1 more full scan than the previous test, because the join needs 2 different arrangements.
# (But we print only one full scan due to deduplication.)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t_non_null AS t1, t_non_null AS t2
 WHERE t1.b = t2.b + 1)
UNION
(SELECT *
 FROM t_non_null
 WHERE b > 5)
----
Explained Query:
  Distinct project=[#0{a}, #1{b}]
    Union
      Project (#4, #5)
        Map ((#0{a} + #2{a}), (#1{b} + #3{b}))
          Join on=(#1{b} = (#3{b} + █)) type=differential
            ArrangeBy keys=[[#1{b}]]
              ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]
            ArrangeBy keys=[[(#1{b} + █)]]
              ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]
      Filter (#1{b} > █)
        ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_non_null_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# An index is used in both a lookup and a full scan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT * FROM t
UNION
SELECT * FROM t WHERE a = 5;
----
Explained Query:
  Distinct project=[#0{a}, #1{b}]
    Union
      ReadIndex on=t t_a_idx_2=[*** full scan ***]
      Project (#0{a}, #1{b})
        ReadIndex on=materialize.public.t t_a_idx_2=[lookup value=(█)]

Used Indexes:
  - materialize.public.t_a_idx_2 (*** full scan ***, lookup)

Target cluster: quickstart

EOF

# Several lookups using different indexes

statement ok
CREATE INDEX t_b_idx ON t(b);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT * FROM t
UNION ALL
SELECT * FROM t WHERE b = 7
UNION ALL
SELECT * FROM t WHERE a = 5
UNION ALL
SELECT * FROM u WHERE c = 3
UNION ALL
SELECT * FROM u WHERE d = 1;
----
Explained Query:
  Union
    ReadIndex on=t t_b_idx=[*** full scan ***]
    Project (#0{a}, #1{b})
      ReadIndex on=materialize.public.t t_b_idx=[lookup value=(█)]
    Project (#0{a}, #1{b})
      ReadIndex on=materialize.public.t t_a_idx_2=[lookup value=(█)]
    Filter (#0{c} = █)
      ReadIndex on=u u_d=[*** full scan ***]
    Project (#0{c}, #1{d})
      ReadIndex on=materialize.public.u u_d=[lookup value=(█)]

Used Indexes:
  - materialize.public.u_d (*** full scan ***, lookup)
  - materialize.public.t_a_idx_2 (lookup)
  - materialize.public.t_b_idx (*** full scan ***, lookup)

Target cluster: quickstart

EOF

# Fast path with a LIMIT and no ORDER BY. This is not a full scan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT a+b as x
FROM t
WHERE a < 7
LIMIT 3;
----
Explained Query (fast path):
  Finish limit=3 output=[#0]
    Project (#2)
      Filter (#0{a} < █)
        Map ((#0{a} + #1{b}))
          ReadIndex on=materialize.public.t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (fast path limit)

Target cluster: quickstart

EOF

# Same query without a LIMIT, so full scan
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT a+b as x
FROM t
WHERE a < 7;
----
Explained Query (fast path):
  Project (#2)
    Filter (#0{a} < █)
      Map ((#0{a} + #1{b}))
        ReadIndex on=materialize.public.t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***)

Target cluster: quickstart

EOF

# Same query with a LIMIT + ORDER BY, so full scan
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, redacted) AS VERBOSE TEXT FOR
SELECT a+b as x
FROM t
WHERE a < 7
ORDER BY x
LIMIT 3;
----
Explained Query (fast path):
  Finish order_by=[#0 asc nulls_last] limit=3 output=[#0]
    Project (#2)
      Filter (#0{a} < █)
        Map ((#0{a} + #1{b}))
          ReadIndex on=materialize.public.t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***)

Target cluster: quickstart

EOF