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

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 INDEX u_d_idx ON U(d);

# Constant EXCEPT (<outer join> order by ..) will return at least one instance
# of each flavor of MirRelationExpr.

statement ok
CREATE VIEW test1 AS
(SELECT 1 as a, 2 as b, 11 as h, 12 as g) EXCEPT (SELECT t.*, u.c + 1 as g FROM (SELECT a, b, generate_series(a, b) as h FROM t) t LEFT OUTER JOIN u on t.a = u.d
ORDER BY t.b LIMIT 10 OFFSET 1);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM test1
----
Explained Query:
  With
    cte l0 =
      FlatMap generate_series(#0{a}, #1{b}, 1) // { arity: 3 }
        ReadIndex on=t t_a_idx=[*** full scan ***] // { arity: 2 }
    cte l1 =
      ArrangeBy keys=[[#0{a}]] // { arity: 3 }
        Filter (#0{a}) IS NOT NULL // { arity: 3 }
          Get l0 // { arity: 3 }
    cte l2 =
      Project (#0{a}..=#3{c}) // { arity: 4 }
        Join on=(#0{a} = #4{d}) type=differential // { arity: 5 }
          Get l1 // { arity: 3 }
          ArrangeBy keys=[[#1{d}]] // { arity: 2 }
            ReadIndex on=u u_d_idx=[differential join] // { arity: 2 }
  Return // { arity: 4 }
    Threshold // { arity: 4 }
      Union // { arity: 4 }
        Negate // { arity: 4 }
          Distinct project=[#0{a}..=#3] // { arity: 4 }
            TopK order_by=[#1{b} asc nulls_last] limit=10 offset=1 // { arity: 4 }
              Project (#0{a}..=#2, #4) // { arity: 4 }
                Map ((#3{c} + 1)) // { arity: 5 }
                  Union // { arity: 4 }
                    Map (null) // { arity: 4 }
                      Union // { arity: 3 }
                        Negate // { arity: 3 }
                          Project (#0{a}..=#2) // { arity: 3 }
                            Join on=(#0{a} = #3{a}) type=differential // { arity: 4 }
                              Get l1 // { arity: 3 }
                              ArrangeBy keys=[[#0{a}]] // { arity: 1 }
                                Distinct project=[#0{a}] // { arity: 1 }
                                  Project (#0{a}) // { arity: 1 }
                                    Get l2 // { arity: 4 }
                        Get l0 // { arity: 3 }
                    Get l2 // { arity: 4 }
        Constant // { arity: 4 }
          - (1, 2, 11, 12)

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

Target cluster: quickstart

EOF

# a reduce with an aggregation.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, humanized expressions) AS VERBOSE TEXT FOR
SELECT sum(e * f), max(f) FROM v GROUP BY mod(e, 5)
----
Explained Query:
  Project (#1{sum}, #2{max_f}) // { arity: 2 }
    Reduce group_by=[(#0{e} % 5)] aggregates=[sum((#0{e} * #1{f})), max(#1{f})] // { arity: 3 }
      ReadStorage materialize.public.v // { arity: 2 }

Source materialize.public.v

Target cluster: quickstart

EOF

# A let where the value has a different arity from the body

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, humanized expressions) AS VERBOSE TEXT FOR
WITH u AS (select u.c + 1 as g from u)
SELECT u.g as g, w.g as h FROM u, u as w WHERE u.g = w.g
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{g}]] // { arity: 1 }
        Project (#2) // { arity: 1 }
          Filter (#0{c}) IS NOT NULL // { arity: 3 }
            Map ((#0{c} + 1)) // { arity: 3 }
              ReadIndex on=u u_d_idx=[*** full scan ***] // { arity: 2 }
  Return // { arity: 2 }
    Project (#0, #0) // { arity: 2 }
      Join on=(#0{g} = #1{g}) type=differential // { arity: 2 }
        Get l0 // { arity: 1 }
        Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

# a constant error

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM u WHERE (SELECT f FROM v WHERE v.e = u.d) = 1
----
Explained Query:
  With
    cte l0 =
      Project (#0{d}, #2{f}) // { arity: 2 }
        Join on=(#0{d} = #1{e}) type=differential // { arity: 3 }
          ArrangeBy keys=[[#0{d}]] // { arity: 1 }
            Distinct project=[#0{d}] // { arity: 1 }
              Project (#1{d}) // { arity: 1 }
                Filter (#1{d}) IS NOT NULL // { arity: 2 }
                  ReadIndex on=u u_d_idx=[*** full scan ***] // { arity: 2 }
          ArrangeBy keys=[[#0{e}]] // { arity: 2 }
            Filter (#0{e}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.v // { arity: 2 }
  Return // { arity: 2 }
    Project (#0{c}, #1{d}) // { arity: 2 }
      Join on=(#1{d} = #2{d}) type=differential // { arity: 3 }
        ArrangeBy keys=[[#1{d}]] // { arity: 2 }
          ReadIndex on=u u_d_idx=[differential join] // { arity: 2 }
        ArrangeBy keys=[[#0{d}]] // { arity: 1 }
          Union // { arity: 1 }
            Project (#0{d}) // { arity: 1 }
              Filter (#1{f} = 1) // { arity: 2 }
                Get l0 // { arity: 2 }
            Project (#0{d}) // { arity: 1 }
              Filter (#2 = 1) // { arity: 3 }
                FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
                  Reduce group_by=[#0{d}] aggregates=[count(*)] // { arity: 2 }
                    Project (#0{d}) // { arity: 1 }
                      Get l0 // { arity: 2 }

Source materialize.public.v
  filter=((#0{e}) IS NOT NULL)

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

Target cluster: quickstart

EOF