Materialize/test/sqllogictest/scalar_subqueries_select_list.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 that multiple scalar subqueries are properly flattened by materialize#8582 whenever possible.
#
# For eligible queries:
# - a (number_of_subqueries + 1)-way join is expected at the bottom of the plan
# - any comonality of the subqueries will be detected by the CSE transform
# - the operators that check for more than 1 row returned from the subquery will appear only once
#

mode cockroach

statement ok
CREATE TABLE t1 (
  f1 INTEGER
)

statement ok
INSERT INTO t1 VALUES (1), (2), (3)

statement ok
CREATE TABLE t2 (
  f1 INTEGER
)

statement ok
INSERT INTO t2 VALUES (1), (2), (3)

statement ok
CREATE TABLE t3 (
  f1 INTEGER
)

statement ok
INSERT INTO t3 VALUES (1), (2), (3)

#
# Uncorrelated
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT * FROM t1), (SELECT * FROM t1) FROM t2
----
Explained Query:
  With
    cte l0 =
      Union // { arity: 1 }
        ReadStorage materialize.public.t1 // { arity: 1 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                ReadStorage materialize.public.t1 // { arity: 1 }
  Return // { arity: 2 }
    Project (#0{f1}, #0{f1}) // { arity: 2 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0:t2[×] » %1[×]
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            ReadStorage materialize.public.t2 // { arity: 1 }
        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 }
                  - ()

Source materialize.public.t1
Source materialize.public.t2

Target cluster: quickstart

EOF

#
# Correlated, identical subqueries
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1) , (SELECT * FROM t1 WHERE t1.f1 = t2.f1) FROM t2
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t2 // { arity: 1 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]UKA » %1:t1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 1 }
    cte l2 =
      Union // { arity: 2 }
        Project (#0{f1}, #0{f1}) // { arity: 2 }
          Get l1 // { arity: 1 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Get l1 // { arity: 1 }
  Return // { arity: 2 }
    Project (#2{f1}, #2{f1}) // { arity: 2 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
        implementation
          %0:t2[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l2 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l2 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2

Target cluster: quickstart

EOF

query II rowsort
SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1) , (SELECT * FROM t1 WHERE t1.f1 = t2.f1) FROM t2
----
1 1
2 2
3 3

query I
SELECT COUNT(*) FROM (SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1) , (SELECT * FROM t1 WHERE t1.f1 = t2.f1) FROM t2)
----
3

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 1 UNION ALL SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 2) , (SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 1 UNION ALL SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 2) FROM t2
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t2 // { arity: 1 }
    cte l1 =
      Filter (#0{f1}) IS NOT NULL // { arity: 1 }
        Get l0 // { arity: 1 }
    cte l2 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Filter (#0{f1}) IS NOT NULL // { arity: 1 }
          ReadStorage materialize.public.t1 // { arity: 1 }
    cte l3 =
      Union // { arity: 2 }
        Join on=(#1{f1} = (#0{f1} + 1)) type=differential // { arity: 2 }
          implementation
            %0:l1[(#0{f1} + 1)]K » %1:l2[#0{f1}]K
          ArrangeBy keys=[[(#0{f1} + 1)]] // { arity: 1 }
            Get l1 // { arity: 1 }
          Get l2 // { arity: 1 }
        Join on=(#1{f1} = (#0{f1} + 2)) type=differential // { arity: 2 }
          implementation
            %0:l1[(#0{f1} + 2)]K » %1:l2[#0{f1}]K
          ArrangeBy keys=[[(#0{f1} + 2)]] // { arity: 1 }
            Get l1 // { arity: 1 }
          Get l2 // { arity: 1 }
    cte l4 =
      Union // { arity: 2 }
        Get l3 // { arity: 2 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Project (#0{f1}) // { arity: 1 }
                Get l3 // { arity: 2 }
  Return // { arity: 2 }
    Project (#2{f1}, #2{f1}) // { arity: 2 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
        implementation
          %0:t2[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l4 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l4 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2

Target cluster: quickstart

EOF

#
# Correlated, with distinct WHERE clauses
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 1) , (SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 2) FROM t2
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t2 // { arity: 1 }
    cte l1 =
      Filter (#0{f1}) IS NOT NULL // { arity: 1 }
        Get l0 // { arity: 1 }
    cte l2 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Filter (#0{f1}) IS NOT NULL // { arity: 1 }
          ReadStorage materialize.public.t1 // { arity: 1 }
    cte l3 =
      Join on=(#1{f1} = (#0{f1} + 1)) type=differential // { arity: 2 }
        implementation
          %0:l1[(#0{f1} + 1)]K » %1:l2[#0{f1}]K
        ArrangeBy keys=[[(#0{f1} + 1)]] // { arity: 1 }
          Get l1 // { arity: 1 }
        Get l2 // { arity: 1 }
    cte l4 =
      Union // { arity: 2 }
        Get l3 // { arity: 2 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Project (#0{f1}) // { arity: 1 }
                Get l3 // { arity: 2 }
    cte l5 =
      Join on=(#1{f1} = (#0{f1} + 2)) type=differential // { arity: 2 }
        implementation
          %0:l1[(#0{f1} + 2)]K » %1:l2[#0{f1}]K
        ArrangeBy keys=[[(#0{f1} + 2)]] // { arity: 1 }
          Get l1 // { arity: 1 }
        Get l2 // { arity: 1 }
    cte l6 =
      Union // { arity: 2 }
        Get l5 // { arity: 2 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Project (#0{f1}) // { arity: 1 }
                Get l5 // { arity: 2 }
  Return // { arity: 2 }
    Project (#2{f1}, #4{f1}) // { arity: 2 }
      Join on=(#0{f1} = #1{f1} = #3{f1}) type=delta // { arity: 5 }
        implementation
          %0:t2 » %1[#0]K » %2[#0]K
          %1 » %0:t2[#0]K » %2[#0]K
          %2 » %0:t2[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l4 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l4 // { arity: 2 }
                Get l0 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l6 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l6 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2

Target cluster: quickstart

EOF

query II rowsort
SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 1) , (SELECT * FROM t1 WHERE t1.f1 = t2.f1 + 2) FROM t2
----
NULL NULL
3 NULL
2 3

#
# Correlated, with distinct SELECT lists
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT f1 + 1 FROM t1 WHERE t1.f1 = t2.f1) , (SELECT f1 + 2 FROM t1 WHERE t1.f1 = t2.f1) FROM t2
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t2 // { arity: 1 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]UKA » %1:t1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 1 }
    cte l2 =
      Project (#0{f1}, #2) // { arity: 2 }
        FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
          Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
            Get l1 // { arity: 1 }
    cte l3 =
      Union // { arity: 2 }
        Map ((#0{f1} + 1)) // { arity: 2 }
          Get l1 // { arity: 1 }
        Get l2 // { arity: 2 }
    cte l4 =
      Union // { arity: 2 }
        Map ((#0{f1} + 2)) // { arity: 2 }
          Get l1 // { arity: 1 }
        Get l2 // { arity: 2 }
  Return // { arity: 2 }
    Project (#2, #4) // { arity: 2 }
      Join on=(#0{f1} = #1{f1} = #3{f1}) type=delta // { arity: 5 }
        implementation
          %0:t2 » %1[#0]K » %2[#0]K
          %1 » %0:t2[#0]K » %2[#0]K
          %2 » %0:t2[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l3 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l3 // { arity: 2 }
                Get l0 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l4 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l4 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2

Target cluster: quickstart

EOF

query II
SELECT (SELECT f1 + 1 FROM t1 WHERE t1.f1 = t2.f1) , (SELECT f1 + 2 FROM t1 WHERE t1.f1 = t2.f1) FROM t2
----
2 3
3 4
4 5

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT MIN(f1) FROM t1 WHERE t1.f1 = t2.f1) , (SELECT MAX(f1) FROM t1 WHERE t1.f1 = t2.f1) FROM t2
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t2 // { arity: 1 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]UKA » %1:t1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 1 }
    cte l2 =
      Reduce group_by=[#0{f1}] aggregates=[min(#0{f1})] // { arity: 2 }
        Get l1 // { arity: 1 }
    cte l3 =
      Union // { arity: 2 }
        Get l2 // { arity: 2 }
        Map (null) // { arity: 2 }
          Union // { arity: 1 }
            Negate // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                Get l2 // { arity: 2 }
            Get l0 // { arity: 1 }
    cte l4 =
      Reduce group_by=[#0{f1}] aggregates=[max(#0{f1})] // { arity: 2 }
        Get l1 // { arity: 1 }
    cte l5 =
      Union // { arity: 2 }
        Get l4 // { arity: 2 }
        Map (null) // { arity: 2 }
          Union // { arity: 1 }
            Negate // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                Get l4 // { arity: 2 }
            Get l0 // { arity: 1 }
  Return // { arity: 2 }
    Project (#2{min_f1}, #4{max_f1}) // { arity: 2 }
      Join on=(#0{f1} = #1{f1} = #3{f1}) type=delta // { arity: 5 }
        implementation
          %0:t2 » %1[#0]UK » %2[#0]UK
          %1 » %2[#0]UK » %0:t2[#0]K
          %2 » %1[#0]UK » %0:t2[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l3 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Project (#0{f1}) // { arity: 1 }
                    Get l3 // { arity: 2 }
                Get l0 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l5 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Project (#0{f1}) // { arity: 1 }
                    Get l5 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2

Target cluster: quickstart

EOF

query II
SELECT (SELECT MIN(f1) FROM t1 WHERE t1.f1 = t2.f1) , (SELECT MAX(f1) FROM t1 WHERE t1.f1 = t2.f1) FROM t2
----
1 1
2 2
3 3

#
# Nested
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1) FROM t2 WHERE t2.f1 = t3.f1) FROM t3
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t3 // { arity: 1 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]UKA » %1:t2[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t2 // { arity: 1 }
    cte l2 =
      Distinct project=[#0{f1}] // { arity: 1 }
        Get l1 // { arity: 1 }
    cte l3 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l2[#0{f1}]UKA » %1:t1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l2 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 1 }
    cte l4 =
      Union // { arity: 2 }
        Project (#0{f1}, #0{f1}) // { arity: 2 }
          Get l3 // { arity: 1 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Get l3 // { arity: 1 }
    cte l5 =
      Project (#0{f1}, #2{f1}) // { arity: 2 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
          implementation
            %0:l1[#0]K » %1[#0]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l1 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Union // { arity: 2 }
              Get l4 // { arity: 2 }
              Map (null) // { arity: 2 }
                Union // { arity: 1 }
                  Negate // { arity: 1 }
                    Distinct project=[#0{f1}] // { arity: 1 }
                      Project (#0{f1}) // { arity: 1 }
                        Get l4 // { arity: 2 }
                  Get l2 // { arity: 1 }
    cte l6 =
      Union // { arity: 2 }
        Get l5 // { arity: 2 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Project (#0{f1}) // { arity: 1 }
                Get l5 // { arity: 2 }
  Return // { arity: 1 }
    Project (#2{f1}) // { arity: 1 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
        implementation
          %0:t3[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t3 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l6 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l6 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t3

Target cluster: quickstart

EOF

query I rowsort
SELECT (SELECT (SELECT * FROM t1 WHERE t1.f1 = t2.f1) FROM t2 WHERE t2.f1 = t3.f1) FROM t3
----
1
2
3

#
# Inside an aggregate
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT MIN((SELECT f1 FROM t1 WHERE t1.f1 = t2.f1)), MAX((SELECT f1 FROM t1 WHERE t1.f1 = t2.f1)) FROM t2;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t2 // { arity: 1 }
    cte l1 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Filter (#0{f1}) IS NOT NULL // { arity: 1 }
          ReadStorage materialize.public.t1 // { arity: 1 }
    cte l2 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]UKA » %1:l1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          Get l1 // { arity: 1 }
    cte l3 =
      Union // { arity: 2 }
        Project (#0{f1}, #0{f1}) // { arity: 2 }
          Get l2 // { arity: 1 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Get l2 // { arity: 1 }
    cte l4 =
      Project (#0{f1}, #2{f1}) // { arity: 2 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
          implementation
            %0:t2[#0]K » %1[#0]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            ReadStorage materialize.public.t2 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Union // { arity: 2 }
              Get l3 // { arity: 2 }
              Map (null) // { arity: 2 }
                Union // { arity: 1 }
                  Negate // { arity: 1 }
                    Distinct project=[#0{f1}] // { arity: 1 }
                      Project (#0{f1}) // { arity: 1 }
                        Get l3 // { arity: 2 }
                  Get l0 // { arity: 1 }
    cte l5 =
      Distinct project=[#0{f1}] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          Get l4 // { arity: 2 }
    cte l6 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l5[#0{f1}]UKA » %1:l1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l5 // { arity: 1 }
          Get l1 // { arity: 1 }
    cte l7 =
      Union // { arity: 2 }
        Project (#0{f1}, #0{f1}) // { arity: 2 }
          Get l6 // { arity: 1 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Get l6 // { arity: 1 }
    cte l8 =
      Reduce aggregates=[min(#0{f1}), max(#1{f1})] // { arity: 2 }
        Project (#1{f1}, #3{f1}) // { arity: 2 }
          Join on=(#0{f1} = #2{f1}) type=differential // { arity: 4 }
            implementation
              %0:l4[#0]K » %1[#0]K
            ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
              Get l4 // { arity: 2 }
            ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
              Union // { arity: 2 }
                Get l7 // { arity: 2 }
                Map (null) // { arity: 2 }
                  Union // { arity: 1 }
                    Negate // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Project (#0{f1}) // { arity: 1 }
                          Get l7 // { arity: 2 }
                    Get l5 // { arity: 1 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Get l8 // { arity: 2 }
      Map (null, null) // { arity: 2 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l8 // { arity: 2 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2

Target cluster: quickstart

EOF


query II
SELECT MIN((SELECT f1 FROM t1 WHERE t1.f1 = t2.f1)), MAX((SELECT f1 FROM t1 WHERE t1.f1 = t2.f1)) FROM t2;
----
1 3

#
# Join on the inside
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
	(SELECT t1.f1 FROM t1, t2 WHERE t1.f1 = t3.f1 AND t2.f1 = t3.f1),
	(SELECT t1.f1 FROM t1, t2 WHERE t1.f1 = t3.f1 AND t2.f1 = t3.f1)
FROM t3
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        ReadStorage materialize.public.t3 // { arity: 1 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1} = #2{f1}) type=delta // { arity: 3 }
          implementation
            %0:l0 » %1:t1[#0{f1}]K » %2:t2[#0{f1}]K
            %1:t1 » %0:l0[#0]UKA » %2:t2[#0{f1}]K
            %2:t2 » %0:l0[#0]UKA » %1:t1[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t2 // { arity: 1 }
    cte l2 =
      Union // { arity: 2 }
        Project (#0{f1}, #0{f1}) // { arity: 2 }
          Get l1 // { arity: 1 }
        Project (#0{f1}, #2) // { arity: 2 }
          FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
            Reduce group_by=[#0{f1}] aggregates=[count(*)] // { arity: 2 }
              Get l1 // { arity: 1 }
  Return // { arity: 2 }
    Project (#2{f1}, #2{f1}) // { arity: 2 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
        implementation
          %0:t3[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          ReadStorage materialize.public.t3 // { arity: 1 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Union // { arity: 2 }
            Get l2 // { arity: 2 }
            Map (null) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Distinct project=[#0{f1}] // { arity: 1 }
                    Project (#0{f1}) // { arity: 1 }
                      Get l2 // { arity: 2 }
                Get l0 // { arity: 1 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t3

Target cluster: quickstart

EOF

query II
SELECT
	(SELECT t1.f1 FROM t1, t2 WHERE t1.f1 = t3.f1 AND t2.f1 = t3.f1),
	(SELECT t1.f1 FROM t1, t2 WHERE t1.f1 = t3.f1 AND t2.f1 = t3.f1)
FROM t3
----
1  1
2  2
3  3


#
# Join on the outside
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
	(SELECT * FROM t1 WHERE t1.f1 = t2.f1 AND t1.f1 = t3.f1),
	(SELECT * FROM t1 WHERE t1.f1 = t2.f1 AND t1.f1 = t3.f1)
FROM t2, t3
----
Explained Query:
  With
    cte l0 =
      CrossJoin type=differential // { arity: 2 }
        implementation
          %0:t2[×] » %1:t3[×]
        ArrangeBy keys=[[]] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          ReadStorage materialize.public.t3 // { arity: 1 }
    cte l1 =
      Distinct project=[#0{f1}, #1{f1}] // { arity: 2 }
        Get l0 // { arity: 2 }
    cte l2 =
      Project (#0{f1}, #1{f1}) // { arity: 2 }
        Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
          implementation
            %0:l1[#0{f1}]UKf » %1:t1[#0{f1}]Kf
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1} = #1{f1}) // { arity: 2 }
              Get l1 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Filter (#0{f1}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 1 }
    cte l3 =
      Union // { arity: 3 }
        Project (#0{f1}, #1{f1}, #0{f1}) // { arity: 3 }
          Get l2 // { arity: 2 }
        Project (#0{f1}, #1{f1}, #3) // { arity: 3 }
          FlatMap guard_subquery_size(#2{count}) // { arity: 4 }
            Reduce group_by=[#0{f1}, #1{f1}] aggregates=[count(*)] // { arity: 3 }
              Get l2 // { arity: 2 }
  Return // { arity: 2 }
    Project (#4{f1}, #4{f1}) // { arity: 2 }
      Join on=(#0{f1} = #2{f1} AND #1{f1} = #3{f1}) type=differential // { arity: 5 }
        implementation
          %0:l0[#0, #1]KK » %1[#0, #1]KK
        ArrangeBy keys=[[#0{f1}, #1{f1}]] // { arity: 2 }
          Get l0 // { arity: 2 }
        ArrangeBy keys=[[#0{f1}, #1{f1}]] // { arity: 3 }
          Union // { arity: 3 }
            Get l3 // { arity: 3 }
            Map (null) // { arity: 3 }
              Union // { arity: 2 }
                Negate // { arity: 2 }
                  Distinct project=[#0{f1}, #1{f1}] // { arity: 2 }
                    Project (#0{f1}, #1{f1}) // { arity: 2 }
                      Get l3 // { arity: 3 }
                Get l1 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
Source materialize.public.t2
Source materialize.public.t3

Target cluster: quickstart

EOF

query II rowsort
SELECT
	(SELECT * FROM t1 WHERE t1.f1 = t2.f1 AND t1.f1 = t3.f1),
	(SELECT * FROM t1 WHERE t1.f1 = t2.f1 AND t1.f1 = t3.f1)
FROM t2, t3
----
NULL  NULL
NULL  NULL
NULL  NULL
NULL  NULL
NULL  NULL
NULL  NULL
1  1
2  2
3  3

query T multiline
EXPLAIN RAW PLAN WITH(types) FOR SELECT CASE (SELECT 1) WHEN 1 THEN 0 ELSE 2 END, 'TEXT';
----
With
  cte [l1 as subquery-1] =
    Map (1)
      Constant
        - ()
Return
  Map (case when (select(Get l1) = 1) then 0 else 2 end, "TEXT")
    Constant
      - ()

Target cluster: quickstart

EOF

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity, types) FOR SELECT CASE (SELECT 1) WHEN 1 THEN 0 ELSE 2 END, 'TEXT';
----
With
  cte l0 =
    Project (#0) // { arity: 1, types: "(integer)" }
      Map (1) // { arity: 1, types: "(integer)" }
        Constant // { arity: 0, types: "()" }
          - ()
  cte l1 =
    Union // { arity: 1, types: "(integer)" }
      Get l0 // { arity: 1, types: "(integer)" }
      Project (#1) // { arity: 1, types: "(integer)" }
        FlatMap guard_subquery_size(#0) // { arity: 2, types: "(bigint, integer)" }
          Reduce aggregates=[count(*)] // { arity: 1, types: "(bigint)" }
            Get l0 // { arity: 1, types: "(integer)" }
Return // { arity: 2, types: "(integer, text)" }
  Project (#1, #2) // { arity: 2, types: "(integer, text)" }
    Map (case when (#0 = 1) then 0 else 2 end, "TEXT") // { arity: 3, types: "(integer?, integer, text)" }
      Project (#0) // { arity: 1, types: "(integer?)" }
        CrossJoin // { arity: 1, types: "(integer?)" }
          Constant // { arity: 0, types: "()" }
            - ()
          Union // { arity: 1, types: "(integer?)" }
            Get l1 // { arity: 1, types: "(integer)" }
            CrossJoin // { arity: 1, types: "(integer?)" }
              Project () // { arity: 0, types: "()" }
                CrossJoin // { arity: 0, types: "()" }
                  Union // { arity: 0, types: "()" }
                    Negate // { arity: 0, types: "()" }
                      Distinct project=[] // { arity: 0, types: "()" }
                        Get l1 // { arity: 1, types: "(integer)" }
                    Distinct project=[] // { arity: 0, types: "()" }
                      Constant // { arity: 0, types: "()" }
                        - ()
                  Constant // { arity: 0, types: "()" }
                    - ()
              Constant // { arity: 1, types: "(integer?)" }
                - (null)

Target cluster: quickstart

EOF

query IT
SELECT CASE (SELECT 1) WHEN 1 THEN 0 ELSE 2 END, 'TEXT';
----
0  TEXT