# 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 Common subexpression elimination for Relations.
# PR https://github.com/MaterializeInc/materialize/pull/7715
#

statement ok
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);

statement ok
CREATE INDEX i1 ON t1 (f1);

statement ok
CREATE TABLE t2 (f1 INTEGER, f2 INTEGER);

##
## Tests around the placement of CSEs in the top-level query itself
##

# no CSE detection here, no predicates in query
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1 , t1 AS a2;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:l0[×] » %1:l0[×]
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# Inner joins
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1 , t1 AS a2, t1 AS a3;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 6 }
    CrossJoin type=delta // { arity: 6 }
      implementation
        %0:l0 » %1:l0[×] » %2:l0[×]
        %1:l0 » %0:l0[×] » %2:l0[×]
        %2:l0 » %0:l0[×] » %1:l0[×]
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1 , t1 AS a2 WHERE a1.f1 = 1 AND a2.f1 = 1;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:l0[×]e » %1:l0[×]e
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1 , t1 AS a2, t1 AS a3 WHERE a1.f1 = 1 AND a2.f1 = 1 AND a3.f1 = 1;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 6 }
    CrossJoin type=delta // { arity: 6 }
      implementation
        %0:l0 » %1:l0[×]e » %2:l0[×]e
        %1:l0 » %0:l0[×]e » %2:l0[×]e
        %2:l0 » %0:l0[×]e » %1:l0[×]e
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# Outer join
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1) WHERE a1.f1 = 1 AND a2.f1 = 1;
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 3 }
    CrossJoin type=differential // { arity: 3 }
      implementation
        %0:l0[×]e » %1:l0[×]e
      ArrangeBy keys=[[]] // { arity: 2 }
        Get l0 // { arity: 2 }
      ArrangeBy keys=[[]] // { arity: 1 }
        Project (#1{f2}) // { arity: 1 }
          Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# Subqueries
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1) AND f2 = (SELECT f1 FROM t1);
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Union // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
          Project (#1) // { arity: 1 }
            FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
              Reduce aggregates=[count(*)] // { arity: 1 }
                Project () // { arity: 0 }
                  ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 2 }
    Project (#0{f1}, #1{f2}) // { arity: 2 }
      Join on=(#0{f1} = #2{f1} AND #1{f2} = #3{f1}) type=delta // { arity: 4 }
        implementation
          %0:t1 » %1:l0[#0]K » %2:l0[#0]K
          %1:l0 » %0:t1[#0{f1}]KA » %2:l0[#0]K
          %2:l0 » %0:t1[#1{f2}]K » %1:l0[#0]K
        ArrangeBy keys=[[#0{f1}], [#1{f2}]] // { arity: 2 }
          Filter (#0{f1}) IS NOT NULL AND (#1{f2}) IS NOT NULL // { arity: 2 }
            ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        Get l0 // { arity: 1 }
        Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 WHERE f1 = 1) AND f2 = (SELECT f1 FROM t1 WHERE f1 = 1);
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l1 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Union // { arity: 1 }
          Get l0 // { arity: 1 }
          Project (#1) // { arity: 1 }
            FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
              Reduce aggregates=[count(*)] // { arity: 1 }
                Project () // { arity: 0 }
                  Get l0 // { arity: 1 }
  Return // { arity: 2 }
    Project (#0{f1}, #1{f2}) // { arity: 2 }
      Join on=(#0{f1} = #2{f1} AND #1{f2} = #3{f1}) type=delta // { arity: 4 }
        implementation
          %0:t1 » %1:l1[#0]K » %2:l1[#0]K
          %1:l1 » %0:t1[#0{f1}]KA » %2:l1[#0]K
          %2:l1 » %0:t1[#1{f2}]K » %1:l1[#0]K
        ArrangeBy keys=[[#0{f1}], [#1{f2}]] // { arity: 2 }
          Filter (#0{f1}) IS NOT NULL AND (#1{f2}) IS NOT NULL // { arity: 2 }
            ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        Get l1 // { arity: 1 }
        Get l1 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1) AND EXISTS (SELECT f1 FROM t1);
----
Explained Query:
  Project (#0{f1}, #1{f2}) // { arity: 2 }
    Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
      implementation
        %0:t1[#0{f1}]KA » %1[#0]K
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[differential join] // { arity: 2 }
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Union // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
          Project (#1) // { arity: 1 }
            FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
              Reduce aggregates=[count(*)] // { arity: 1 }
                Project () // { arity: 0 }
                  ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1) OR f2 = (SELECT f1 FROM t1);
----
Explained Query:
  With
    cte l0 =
      Union // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
    cte l1 =
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Get l0 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Distinct project=[] // { arity: 0 }
                  Project () // { arity: 0 }
                    Get l0 // { arity: 1 }
              Constant // { arity: 0 }
                - ()
  Return // { arity: 2 }
    Project (#0{f1}, #1{f2}) // { arity: 2 }
      Filter ((#0{f1} = #2{f1}) OR (#1{f2} = #3{f1})) // { arity: 4 }
        CrossJoin type=delta // { arity: 4 }
          implementation
            %0:t1 » %1:l1[×] » %2:l1[×]
            %1:l1 » %0:t1[×] » %2:l1[×]
            %2:l1 » %0:t1[×] » %1:l1[×]
          ArrangeBy keys=[[]] // { arity: 2 }
            ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
          Get l1 // { arity: 1 }
          Get l1 // { arity: 1 }

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

Target cluster: quickstart

EOF

#
# CSEs in derived tables
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *
FROM (SELECT * FROM t1 WHERE f1 = 1) AS a1
JOIN (SELECT * FROM t1 WHERE f1 = 1) AS a2
ON TRUE
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:l0[×]e » %1:l0[×]e
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *
FROM (SELECT * FROM t1 WHERE f1 = 1) AS a1
, (SELECT * FROM t1 WHERE f1 = 1) AS a2
WHERE a1.f2 = 2
AND a2.f2 = 2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          Filter (#1{f2} = 2) // { arity: 3 }
            ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:l0[×]ef » %1:l0[×]ef
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

# No CSE with only partially-overlapping predicates
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *
FROM (SELECT * FROM t1 WHERE f1 = 1) AS a1
, (SELECT * FROM t1 WHERE f1 = 1) AS a2
WHERE a1.f2 = 2
AND a2.f2 = 3
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:l0[×]ef » %1:l0[×]ef
      ArrangeBy keys=[[]] // { arity: 2 }
        Filter (#1{f2} = 2) // { arity: 2 }
          Get l0 // { arity: 2 }
      ArrangeBy keys=[[]] // { arity: 2 }
        Filter (#1{f2} = 3) // { arity: 2 }
          Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# CSEs in UNION branches
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = 1 UNION ALL SELECT * FROM t1 WHERE f1 = 1 UNION ALL SELECT * FROM t1 WHERE f1 = 1;
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = 1 UNION ALL SELECT * FROM t1 WHERE f1 = 1 UNION SELECT * FROM t1 WHERE f1 = 1;
----
Explained Query:
  With
    cte l0 =
      Project (#1{f2}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Project (#1, #0{f2}) // { arity: 2 }
      Map (1) // { arity: 2 }
        Distinct project=[#0{f2}] // { arity: 1 }
          Union // { arity: 1 }
            Get l0 // { arity: 1 }
            Get l0 // { arity: 1 }
            Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

#
# CSEs in the SELECT list
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT f1 FROM t1 WHERE f1 = 1) , (SELECT f1 FROM t1 WHERE f1 = 1) FROM t1;
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l1 =
      Union // { arity: 1 }
        Get l0 // { arity: 1 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                Get l0 // { arity: 1 }
  Return // { arity: 2 }
    Project (#0{f1}, #0{f1}) // { arity: 2 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:t1[×] » %1[×]
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Union // { arity: 1 }
            Get l1 // { arity: 1 }
            Map (null) // { arity: 1 }
              Union // { arity: 0 }
                Negate // { arity: 0 }
                  Distinct project=[] // { arity: 0 }
                    Project () // { arity: 0 }
                      Get l1 // { arity: 1 }
                Constant // { arity: 0 }
                  - ()

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT MIN((SELECT f1 FROM t1 WHERE f1 = 1)), MAX((SELECT f1 FROM t1 WHERE f1 = 1)) FROM t1;
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l1 =
      Union // { arity: 1 }
        Get l0 // { arity: 1 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                Get l0 // { arity: 1 }
    cte l2 =
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Get l1 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Distinct project=[] // { arity: 0 }
                  Project () // { arity: 0 }
                    Get l1 // { arity: 1 }
              Constant // { arity: 0 }
                - ()
    cte l3 =
      Reduce aggregates=[min(#0{f1}), max(#1{f1})] // { arity: 2 }
        CrossJoin type=delta // { arity: 2 }
          implementation
            %0:t1 » %1:l2[×] » %2:l2[×]
            %1:l2 » %0:t1[×] » %2:l2[×]
            %2:l2 » %0:t1[×] » %1:l2[×]
          ArrangeBy keys=[[]] // { arity: 0 }
            Project () // { arity: 0 }
              ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
          Get l2 // { arity: 1 }
          Get l2 // { arity: 1 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l3 // { arity: 2 }
      Map (null, null) // { arity: 2 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l3 // { arity: 2 }
          Constant // { arity: 0 }
            - ()

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

Target cluster: quickstart

EOF

#
# CSEs at two distinct positions within the query
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT f1 FROM t1 WHERE f1 = 1) FROM t1 WHERE EXISTS (SELECT f1 FROM t1 WHERE f1 = 1);
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l1 =
      Project () // { arity: 0 }
        Get l0 // { arity: 1 }
    cte l2 =
      Union // { arity: 1 }
        Get l0 // { arity: 1 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Get l1 // { arity: 0 }
  Return // { arity: 1 }
    CrossJoin type=delta // { arity: 1 }
      implementation
        %0:t1 » %1[×]UA » %2[×]
        %1 » %0:t1[×] » %2[×]
        %2 » %1[×]UA » %0:t1[×]
      ArrangeBy keys=[[]] // { arity: 0 }
        Project () // { arity: 0 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
      ArrangeBy keys=[[]] // { arity: 0 }
        Distinct project=[] // { arity: 0 }
          Get l1 // { arity: 0 }
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Get l2 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Distinct project=[] // { arity: 0 }
                  Project () // { arity: 0 }
                    Get l2 // { arity: 1 }
              Constant // { arity: 0 }
                - ()

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT f1 FROM t1 WHERE f1 = 1) FROM t1
UNION ALL
SELECT f1 FROM t1 WHERE f1 = 1
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l1 =
      Union // { arity: 1 }
        Get l0 // { arity: 1 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                Get l0 // { arity: 1 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:t1[×] » %1[×]
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Union // { arity: 1 }
            Get l1 // { arity: 1 }
            Map (null) // { arity: 1 }
              Union // { arity: 0 }
                Negate // { arity: 0 }
                  Distinct project=[] // { arity: 0 }
                    Project () // { arity: 0 }
                      Get l1 // { arity: 1 }
                Constant // { arity: 0 }
                  - ()
      Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

##
## Tests around the contents of the CSE itself
##

#
# CSE containing a join
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 AS a1, t1 AS a2
UNION ALL
SELECT * FROM t1 AS a1, t1 AS a2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 2 }
        ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
    cte l1 =
      CrossJoin type=differential // { arity: 4 }
        implementation
          %0:l0[×] » %1:l0[×]
        Get l0 // { arity: 2 }
        Get l0 // { arity: 2 }
  Return // { arity: 4 }
    Union // { arity: 4 }
      Get l1 // { arity: 4 }
      Get l1 // { arity: 4 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)
UNION ALL
SELECT * FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[differential join] // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #1{f2}, #3{f2}) // { arity: 3 }
        Filter (#0{f1}) IS NOT NULL // { arity: 4 }
          Join on=(#0{f1} = #2{f1}) type=differential // { arity: 4 }
            implementation
              %0:l0[#0{f1}]KA » %1:l0[#0{f1}]KA
            Get l0 // { arity: 2 }
            Get l0 // { arity: 2 }
    cte l2 =
      Map (null) // { arity: 3 }
        Union // { arity: 2 }
          Negate // { arity: 2 }
            Project (#0{f1}, #1{f2}) // { arity: 2 }
              Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
                implementation
                  %1[#0]UKA » %0:l0[#0{f1}]KA
                Get l0 // { arity: 2 }
                ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l1 // { arity: 3 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 3 }
    Union // { arity: 3 }
      Get l2 // { arity: 3 }
      Get l1 // { arity: 3 }
      Get l2 // { arity: 3 }
      Get l1 // { arity: 3 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM (SELECT a2.f1 AS f1 FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)) WHERE f1 = 1
UNION ALL
SELECT * FROM (SELECT a2.f1 AS f1 FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)) WHERE f1 = 2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[lookup] // { arity: 2 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l2 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(2)] // { arity: 3 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:l1[×]e » %1:l1[×]e
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            Get l1 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Get l1 // { arity: 1 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:l2[×]e » %1:l2[×]e
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            Get l2 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Get l2 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM
(SELECT a2.f1 AS f1 FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)) AS s1,
(SELECT a2.f1 AS f1 FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)) AS s2
WHERE s1.f1 = 1 AND s2.f1 = 1
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l1 =
      ArrangeBy keys=[[]] // { arity: 0 }
        Project () // { arity: 0 }
          Get l0 // { arity: 1 }
    cte l2 =
      ArrangeBy keys=[[]] // { arity: 1 }
        Get l0 // { arity: 1 }
  Return // { arity: 2 }
    CrossJoin type=delta // { arity: 2 }
      implementation
        %0:l1 » %1:l2[×]e » %2:l1[×]e » %3:l2[×]e
        %1:l2 » %0:l1[×]e » %2:l1[×]e » %3:l2[×]e
        %2:l1 » %0:l1[×]e » %1:l2[×]e » %3:l2[×]e
        %3:l2 » %0:l1[×]e » %1:l2[×]e » %2:l1[×]e
      Get l1 // { arity: 0 }
      Get l2 // { arity: 1 }
      Get l1 // { arity: 0 }
      Get l2 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM
(SELECT a2.f1 AS f1 FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)) AS s1,
(SELECT a2.f1 AS f1 FROM t1 AS a1 LEFT JOIN t1 AS a2 USING (f1)) AS s2
WHERE s1.f1 = 1 AND s2.f1 = 2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[lookup] // { arity: 2 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l2 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(2)] // { arity: 3 }
  Return // { arity: 2 }
    CrossJoin type=delta // { arity: 2 }
      implementation
        %0:l1 » %1:l1[×]e » %2:l2[×]e » %3:l2[×]e
        %1:l1 » %0:l1[×]e » %2:l2[×]e » %3:l2[×]e
        %2:l2 » %0:l1[×]e » %1:l1[×]e » %3:l2[×]e
        %3:l2 » %0:l1[×]e » %1:l1[×]e » %2:l2[×]e
      ArrangeBy keys=[[]] // { arity: 0 }
        Project () // { arity: 0 }
          Get l1 // { arity: 1 }
      ArrangeBy keys=[[]] // { arity: 1 }
        Get l1 // { arity: 1 }
      ArrangeBy keys=[[]] // { arity: 0 }
        Project () // { arity: 0 }
          Get l2 // { arity: 1 }
      ArrangeBy keys=[[]] // { arity: 1 }
        Get l2 // { arity: 1 }

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

Target cluster: quickstart

EOF

#
# CSE containing a conjunction (AND)
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 WHERE f1 = 1 AND f2 = 2
UNION ALL
SELECT * FROM t1 WHERE f1 = 1 AND f2 = 2
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        Filter (#1{f2} = 2) // { arity: 3 }
          ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# CSE containing a disjunction (OR)
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 WHERE f1 = 1 OR f1 = 2
UNION ALL
SELECT * FROM t1 WHERE f1 = 1 OR f1 = 2
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup values=[(1); (2)]] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# CSE containing a subquery
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1)
UNION ALL
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1)
----
Explained Query:
  With
    cte l0 =
      CrossJoin type=differential // { arity: 2 }
        implementation
          %1[×]UA » %0:t1[×]
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 0 }
          Distinct project=[] // { arity: 0 }
            Project () // { arity: 0 }
              ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 WHERE f1 = 1)
UNION ALL
SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 WHERE f1 = 1)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[differential join, lookup] // { arity: 2 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l2 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]KA » %1[#0]K
          Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Union // { arity: 1 }
              Get l1 // { arity: 1 }
              Project (#1) // { arity: 1 }
                FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
                  Reduce aggregates=[count(*)] // { arity: 1 }
                    Project () // { arity: 0 }
                      Get l1 // { arity: 1 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l2 // { arity: 2 }
      Get l2 // { arity: 2 }

Used Indexes:
  - materialize.public.i1 (differential join, lookup)

Target cluster: quickstart

EOF

#
# CSE containing a derived table
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT f1 + 1 FROM (SELECT f1 + 2 AS f1 FROM t1)
UNION ALL
SELECT f1 + 1 FROM (SELECT f1 + 2 AS f1 FROM t1)
----
Explained Query:
  With
    cte l0 =
      Project (#2) // { arity: 1 }
        Map (((#0{f1} + 2) + 1)) // { arity: 3 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

#
# CSEs containing an aggregate
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT MIN(f1) FROM t1
UNION ALL
SELECT MIN(f1) FROM t1;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{f1})] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
    cte l1 =
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Get l1 // { arity: 1 }
      Get l0 // { arity: 1 }
      Get l1 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT DISTINCT f1 FROM t1
UNION ALL
SELECT DISTINCT f1 FROM t1
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT f1, COUNT(*) FROM t1 GROUP BY f1
UNION ALL
SELECT f1, COUNT(*) FROM t1 GROUP BY f1
----
Explained Query:
  With
    cte l0 =
      Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
        Project (#0{f1}) // { arity: 1 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

#
# CSEs containing an expression / function

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT f1 + f1 + f1 + f1 FROM t1
UNION ALL
SELECT f1 + f1 + f1 + f1 FROM t1
----
Explained Query:
  With
    cte l0 =
      Project (#2) // { arity: 1 }
        Map ((((#0{f1} + #0{f1}) + #0{f1}) + #0{f1})) // { arity: 3 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT ABS(f1) FROM t1
UNION ALL
SELECT ABS(f1) FROM t1
----
Explained Query:
  With
    cte l0 =
      Project (#2) // { arity: 1 }
        Map (abs(#0{f1})) // { arity: 3 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Get l0 // { arity: 1 }

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

Target cluster: quickstart

EOF

##
## Nested CSEs
##

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(SELECT * FROM t1 WHERE f1 = 1 UNION ALL SELECT * FROM t1 WHERE f1 = 1)
UNION ALL
(SELECT * FROM t1 WHERE f1 = 1 UNION ALL SELECT * FROM t1 WHERE f1 = 1)
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1))
UNION ALL
(SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1))
----
Explained Query:
  With
    cte l0 =
      CrossJoin type=differential // { arity: 2 }
        implementation
          %1[×]UA » %0:t1[×]
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 0 }
          Distinct project=[] // { arity: 0 }
            Project () // { arity: 0 }
              ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l0 // { arity: 2 }
      Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

##
## Deeper-placed CSEs (where a higher-level construct is not a CSE but a lower-level one is)
##

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(SELECT f1 FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1))
UNION ALL
(SELECT f2 FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1))
----
Explained Query:
  With
    cte l0 =
      CrossJoin type=differential // { arity: 2 }
        implementation
          %1[×]UA » %0:t1[×]
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 0 }
          Distinct project=[] // { arity: 0 }
            Project () // { arity: 0 }
              ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Project (#0{f1}) // { arity: 1 }
        Get l0 // { arity: 2 }
      Project (#1{f2}) // { arity: 1 }
        Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1))
UNION ALL
(SELECT * FROM t2 WHERE EXISTS (SELECT * FROM t1 WHERE f1 = 1))
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { arity: 0 }
        Distinct project=[] // { arity: 0 }
          Project () // { arity: 0 }
            ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      CrossJoin type=differential // { arity: 2 }
        implementation
          %1:l0[×]UA » %0:t1[×]
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        Get l0 // { arity: 0 }
      CrossJoin type=differential // { arity: 2 }
        implementation
          %1:l0[×]UA » %0:t2[×]
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadStorage materialize.public.t2 // { arity: 2 }
        Get l0 // { arity: 0 }

Source materialize.public.t2

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM
(SELECT f1 FROM t2 UNION ALL SELECT f1 FROM t1 WHERE f1 = 1) ,
(SELECT f2 FROM t2 UNION ALL SELECT f1 FROM t1 WHERE f1 = 1)
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 2 }
    CrossJoin type=differential // { arity: 2 }
      implementation
        %0[×] » %1[×]
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            ReadStorage materialize.public.t2 // { arity: 2 }
          Get l0 // { arity: 1 }
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Project (#1{f2}) // { arity: 1 }
            ReadStorage materialize.public.t2 // { arity: 2 }
          Get l0 // { arity: 1 }

Source materialize.public.t2

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

Target cluster: quickstart

EOF

# Same predicate, different projections
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT f1 FROM t1 WHERE f1 = 1
UNION ALL
SELECT f2 FROM t1 WHERE f1 = 1
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Project (#0{f1}) // { arity: 1 }
        Get l0 // { arity: 2 }
      Project (#1{f2}) // { arity: 1 }
        Get l0 // { arity: 2 }

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

Target cluster: quickstart

EOF

##
## Negative cases - CSEs should not be identified where they do not exist
##

#
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1, t1 AS a2 WHERE a1.f1 = 1 AND a2.f1 = 2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[lookup] // { arity: 2 }
  Return // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:t1[×]e » %1:t1[×]e
      ArrangeBy keys=[[]] // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
      ArrangeBy keys=[[]] // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          ReadIndex on=materialize.public.t1 i1=[lookup value=(2)] // { arity: 3 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM (SELECT a2.f1 AS f1 FROM t1 AS a1 JOIN t1 AS a2 USING (f1)) WHERE f1 = 1
UNION ALL
SELECT * FROM (SELECT a2.f1 AS f1 FROM t1 AS a1 JOIN t1 AS a2 USING (f1)) WHERE f1 = 2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[lookup] // { arity: 2 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
    cte l2 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(2)] // { arity: 3 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:l1[×]e » %1:l1[×]e
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            Get l1 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Get l1 // { arity: 1 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:l2[×]e » %1:l2[×]e
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            Get l2 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Get l2 // { arity: 1 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1 WHERE f1 = 1
UNION ALL
SELECT * FROM t1 WHERE f1 = 2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        ReadIndex on=t1 i1=[lookup] // { arity: 2 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(1)] // { arity: 3 }
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 i1=[lookup value=(2)] // { arity: 3 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT MIN(f1) FROM t1
UNION ALL
SELECT MAX(f1) FROM t1
----
Explained Query:
  With
    cte l0 =
      Project (#0{f1}) // { arity: 1 }
        ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
    cte l1 =
      Reduce aggregates=[min(#0{f1})] // { arity: 1 }
        Get l0 // { arity: 1 }
    cte l2 =
      Reduce aggregates=[max(#0{f1})] // { arity: 1 }
        Get l0 // { arity: 1 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l1 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l1 // { arity: 1 }
          Constant // { arity: 0 }
            - ()
      Get l2 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l2 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT MIN(f1) FROM t1
UNION ALL
SELECT MIN(f2) FROM t1
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{f1})] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
    cte l1 =
      Reduce aggregates=[min(#0{f2})] // { arity: 1 }
        Project (#1{f2}) // { arity: 1 }
          ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()
      Get l1 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l1 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

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

Target cluster: quickstart

EOF


# WITH MUTUALLY RECURSIVE support
# -------------------------------

# Basic support for recursive queries.
# With materialize#27389 this stopped testing what it says it tests; see issue database-issues#8294.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(f1 INTEGER, f2 INTEGER) AS (
    SELECT f1, f2 FROM t1
    UNION ALL
    SELECT f1, f2 FROM t1
  ),
  c1(f1 INTEGER, f2 INTEGER) AS (
    SELECT * FROM c0 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c1 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c1 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c2 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c2 WHERE f2 > 7
  ),
  c2(f1 INTEGER, f2 INTEGER) AS (
    SELECT * FROM c0 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c1 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c1 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c2 WHERE f2 > 7
    UNION ALL
    SELECT * FROM c2 WHERE f2 > 7
  )
SELECT * FROM c0 WHERE f1 > 7
UNION ALL
SELECT * FROM c1 WHERE f1 > 7
UNION ALL
SELECT * FROM c2 WHERE f1 > 7
----
Explained Query:
  With
    cte l0 =
      Union // { arity: 2 }
        ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
        ReadIndex on=t1 i1=[*** full scan ***] // { arity: 2 }
    cte l1 =
      Filter (#1{f2} > 7) // { arity: 2 }
        Get l0 // { arity: 2 }
  Return // { arity: 2 }
    With Mutually Recursive
      cte l2 =
        Union // { arity: 2 }
          Get l1 // { arity: 2 }
          Get l2 // { arity: 2 }
          Get l2 // { arity: 2 }
          Get l3 // { arity: 2 }
          Get l3 // { arity: 2 }
      cte l3 =
        Union // { arity: 2 }
          Get l1 // { arity: 2 }
          Get l2 // { arity: 2 }
          Get l2 // { arity: 2 }
          Get l3 // { arity: 2 }
          Get l3 // { arity: 2 }
    Return // { arity: 2 }
      Union // { arity: 2 }
        Filter (#0{f1} > 7) // { arity: 2 }
          Get l0 // { arity: 2 }
        Filter (#0{f1} > 7) // { arity: 2 }
          Get l2 // { arity: 2 }
        Filter (#0{f1} > 7) // { arity: 2 }
          Get l3 // { arity: 2 }

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

Target cluster: quickstart

EOF

# Correlated WMR block.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT bound, (
    WITH MUTUALLY RECURSIVE
        numbers (n int) as (
            VALUES (1)
            UNION ALL
            (
                WITH rebound AS (SELECT * FROM numbers)
                SELECT distinct t1.n + t2.n AS n
                FROM rebound AS t1, rebound AS t2
                WHERE t1.n <= bound AND t2.n <= bound
            )
        )
    SELECT count(*) FROM numbers
)
FROM (
    SELECT generate_series AS bound FROM generate_series(1, 10)
);
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      ArrangeBy keys=[[#0]] // { arity: 2 }
        Filter (#1{n} <= #0{bound}) // { arity: 2 }
          Get l1 // { arity: 2 }
    cte l1 =
      Union // { arity: 2 }
        Distinct project=[#0, (#1{n} + #2{n})] monotonic // { arity: 2 }
          Project (#0, #1, #3) // { arity: 3 }
            Join on=(#0 = #2) type=differential // { arity: 4 }
              implementation
                %0:l0[#0]Kf » %1:l0[#0]Kf
              Get l0 // { arity: 2 }
              Get l0 // { arity: 2 }
        Constant // { arity: 2 }
          - (1, 1)
          - (2, 1)
          - (3, 1)
          - (4, 1)
          - (5, 1)
          - (6, 1)
          - (7, 1)
          - (8, 1)
          - (9, 1)
          - (10, 1)
  Return // { arity: 2 }
    With
      cte l2 =
        Reduce group_by=[#0] aggregates=[count(*)] monotonic // { arity: 2 }
          Project (#0) // { arity: 1 }
            Get l1 // { arity: 2 }
      cte l3 =
        ArrangeBy keys=[[#0]] // { arity: 1 }
          Constant // { arity: 1 }
            - (1)
            - (2)
            - (3)
            - (4)
            - (5)
            - (6)
            - (7)
            - (8)
            - (9)
            - (10)
      cte l4 =
        Union // { arity: 2 }
          Get l2 // { arity: 2 }
          Project (#0, #2) // { arity: 2 }
            Map (0) // { arity: 3 }
              Join on=(#0 = #1) type=differential // { arity: 2 }
                implementation
                  %1:l3[#0]UK » %0[#0]K
                ArrangeBy keys=[[#0]] // { arity: 1 }
                  Union // { arity: 1 }
                    Negate // { arity: 1 }
                      Project (#0) // { arity: 1 }
                        Get l2 // { arity: 2 }
                    Constant // { arity: 1 }
                      - (1)
                      - (2)
                      - (3)
                      - (4)
                      - (5)
                      - (6)
                      - (7)
                      - (8)
                      - (9)
                      - (10)
                Get l3 // { arity: 1 }
      cte l5 =
        Union // { arity: 2 }
          Get l4 // { arity: 2 }
          Project (#0, #2) // { arity: 2 }
            FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
              Reduce group_by=[#0] aggregates=[count(*)] // { arity: 2 }
                Project (#0) // { arity: 1 }
                  Get l4 // { arity: 2 }
    Return // { arity: 2 }
      Project (#0, #3{count}) // { arity: 2 }
        Join on=(#0 = #1 = #2) type=delta // { arity: 4 }
          implementation
            %0 » %1:l3[#0]UK » %2[#0]K
            %1:l3 » %0[#0]UK » %2[#0]K
            %2 » %0[#0]UK » %1:l3[#0]UK
          ArrangeBy keys=[[#0]] // { arity: 1 }
            Constant // { arity: 1 }
              - (1)
              - (2)
              - (3)
              - (4)
              - (5)
              - (6)
              - (7)
              - (8)
              - (9)
              - (10)
          Get l3 // { arity: 1 }
          ArrangeBy keys=[[#0]] // { arity: 2 }
            Union // { arity: 2 }
              Get l5 // { arity: 2 }
              Project (#0, #2) // { arity: 2 }
                Map (null) // { arity: 3 }
                  Join on=(#0 = #1) type=differential // { arity: 2 }
                    implementation
                      %1:l3[#0]UK » %0[#0]K
                    ArrangeBy keys=[[#0]] // { arity: 1 }
                      Union // { arity: 1 }
                        Negate // { arity: 1 }
                          Distinct project=[#0] // { arity: 1 }
                            Project (#0) // { arity: 1 }
                              Get l5 // { arity: 2 }
                        Constant // { arity: 1 }
                          - (1)
                          - (2)
                          - (3)
                          - (4)
                          - (5)
                          - (6)
                          - (7)
                          - (8)
                          - (9)
                          - (10)
                    Get l3 // { arity: 1 }

Target cluster: quickstart

EOF