Materialize/test/sqllogictest/transform/aggregation_nullability.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.

mode cockroach

statement ok
CREATE TABLE t1(f1 int, f2 int)

statement ok
CREATE TABLE t2(f1 int, f2 int)

statement ok
INSERT INTO t1 VALUES (1, 2)

# regression test for database-issues#2192: a filter on COUNT aggregation must not trigger the outer to inner join conversion
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having count(t2.f1) >= 0;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          Filter (#0{f1}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 2 }
    Filter (#1{count_f1} >= 0) // { arity: 2 }
      Reduce group_by=[#0{f1}] aggregates=[count(#1{f1})] // { arity: 2 }
        Union // { arity: 2 }
          Map (null) // { arity: 2 }
            Union // { arity: 1 }
              Negate // { arity: 1 }
                Project (#0{f1}) // { arity: 1 }
                  Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
                    implementation
                      %1[#0]UKA » %0:l0[#0{f1}]K
                    Get l0 // { arity: 1 }
                    ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Get l1 // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                ReadStorage materialize.public.t1 // { arity: 2 }
          Project (#0{f1}, #0{f1}) // { arity: 2 }
            Get l1 // { arity: 1 }

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

Target cluster: quickstart

EOF

query II
select t1.f1, count(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having count(t2.f1) >= 0;
----
1  0


# ... but a filter on any other aggregation should convert a left join into an inner join if its parameter comes from the non-preserving side
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having sum(t2.f1) >= 0;
----
Explained Query:
  Filter (#1{sum_f1} >= 0) // { arity: 2 }
    Reduce group_by=[#0{f1}] aggregates=[sum(#0{f1})] // { arity: 2 }
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:t1[#0{f1}]K » %1:t2[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t1 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

query II
select t1.f1, sum(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having sum(t2.f1) >= 0;
----

# multiple aggregations
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f1), sum(t2.f1), max(t2.f1), min(t2.f1), count(t1.f2), sum(t1.f2), min(t1.f2), max(t1.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        Filter (#0{f1}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 9 }
    Reduce group_by=[#0{f1}] aggregates=[count(#2{f1}), sum(#2{f1}), max(#2{f1}), min(#2{f1}), count(#1{f2}), sum(#1{f2}), min(#1{f2}), max(#1{f2})] // { arity: 9 }
      Union // { arity: 3 }
        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}]K
                  Get l0 // { arity: 2 }
                  ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                    Distinct project=[#0{f1}] // { arity: 1 }
                      Project (#0{f1}) // { arity: 1 }
                        Get l1 // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
        Project (#0{f1}, #1{f2}, #0{f1}) // { arity: 3 }
          Get l1 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f1), sum(t2.f1), max(t2.f1), min(t2.f1), count(t1.f2), sum(t1.f2), min(t1.f2), max(t1.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having sum(t1.f2) >= 0;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        Filter (#0{f1}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 9 }
    Filter (#6{sum_f2} >= 0) // { arity: 9 }
      Reduce group_by=[#0{f1}] aggregates=[count(#2{f1}), sum(#2{f1}), max(#2{f1}), min(#2{f1}), count(#1{f2}), sum(#1{f2}), min(#1{f2}), max(#1{f2})] // { arity: 9 }
        Union // { arity: 3 }
          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}]K
                    Get l0 // { arity: 2 }
                    ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Project (#0{f1}) // { arity: 1 }
                          Get l1 // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
          Project (#0{f1}, #1{f2}, #0{f1}) // { arity: 3 }
            Get l1 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f1), sum(t2.f1), max(t2.f1), min(t2.f1), count(t1.f2), sum(t1.f2), min(t1.f2), max(t1.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having sum(t2.f1) >= 0;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        Filter (#0{f1}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 9 }
    Filter (#2{sum_f1} >= 0) // { arity: 9 }
      Reduce group_by=[#0{f1}] aggregates=[count(#2{f1}), sum(#2{f1}), max(#2{f1}), min(#2{f1}), count(#1{f2}), sum(#1{f2}), min(#1{f2}), max(#1{f2})] // { arity: 9 }
        Union // { arity: 3 }
          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}]K
                    Get l0 // { arity: 2 }
                    ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Project (#0{f1}) // { arity: 1 }
                          Get l1 // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
          Project (#0{f1}, #1{f2}, #0{f1}) // { arity: 3 }
            Get l1 // { arity: 2 }

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

Target cluster: quickstart

EOF

# count is never null, predicate removed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t1.f2) from t1 group by t1.f1 having count(t1.f2) is not null;
----
Explained Query:
  Reduce group_by=[#0{f1}] aggregates=[count(#1{f2})] // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t1.f2) from t1 group by t1.f1 having sum(t1.f2) is not null;
----
Explained Query:
  Filter (#1{sum_f2}) IS NOT NULL // { arity: 2 }
    Reduce group_by=[#0{f1}] aggregates=[sum(#1{f2})] // { arity: 2 }
      ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

# outer-to-inner-join conversion allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t2.f1), max(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f1) >= 0;
----
Explained Query:
  Reduce group_by=[#0{f1}] aggregates=[sum(#0{f1}), max(#0{f1})] // { arity: 3 }
    Project (#0{f1}) // { arity: 1 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
        implementation
          %0:t1[#0{f1}]Kif » %1:t2[#0{f1}]Kiif
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1} >= 0) // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1} >= 0) // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1} >= 0))
Source materialize.public.t2
  filter=((#0{f1} >= 0))

Target cluster: quickstart

EOF

# outer-to-inner-join conversion allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t2.f1), max(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f1) >= 0 and sum(t2.f1) >= 0;
----
Explained Query:
  Filter (#1{sum_f1} >= 0) // { arity: 3 }
    Reduce group_by=[#0{f1}] aggregates=[sum(#0{f1}), max(#0{f1})] // { arity: 3 }
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:t1[#0{f1}]Kif » %1:t2[#0{f1}]Kiif
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1} >= 0) // { arity: 2 }
                ReadStorage materialize.public.t1 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1} >= 0) // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1} >= 0))
Source materialize.public.t2
  filter=((#0{f1} >= 0))

Target cluster: quickstart

EOF

# outer-to-inner-join conversion allowed, but we fail to detect this case
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t2.f2), max(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f1) >= 0;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          Filter (#0{f1}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #2{f2}) // { arity: 2 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 3 }
    Filter (#2{max_f1} >= 0) // { arity: 3 }
      Reduce group_by=[#0{f1}] aggregates=[sum(#2{f2}), max(#1{f1})] // { arity: 3 }
        Union // { arity: 3 }
          Map (null, null) // { arity: 3 }
            Union // { arity: 1 }
              Negate // { arity: 1 }
                Project (#0{f1}) // { arity: 1 }
                  Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
                    implementation
                      %1[#0]UKA » %0:l0[#0{f1}]K
                    Get l0 // { arity: 1 }
                    ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Project (#0{f1}) // { arity: 1 }
                          Get l1 // { arity: 2 }
              Project (#0{f1}) // { arity: 1 }
                ReadStorage materialize.public.t1 // { arity: 2 }
          Project (#0{f1}, #0{f1}, #1{f2}) // { arity: 3 }
            Get l1 // { arity: 2 }

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

Target cluster: quickstart

EOF

# outer-to-inner-join conversion allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t2.f1 + t2.f2), max(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f1) >= 0;
----
Explained Query:
  Reduce group_by=[#0{f1}] aggregates=[sum((#0{f1} + #1{f2})), max(#0{f1})] // { arity: 3 }
    Project (#0{f1}, #2{f2}) // { arity: 2 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
        implementation
          %0:t1[#0{f1}]Kif » %1:t2[#0{f1}]Kiif
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1} >= 0) // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Filter (#0{f1} >= 0) // { arity: 2 }
            ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1} >= 0))
Source materialize.public.t2
  filter=((#0{f1} >= 0))

Target cluster: quickstart

EOF

# outer-to-inner-join conversion allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t1.f1 + t2.f1), max(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f1) >= 0;
----
Explained Query:
  Reduce group_by=[#0{f1}] aggregates=[sum((#0{f1} + #0{f1})), max(#0{f1})] // { arity: 3 }
    Project (#0{f1}) // { arity: 1 }
      Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
        implementation
          %0:t1[#0{f1}]Kif » %1:t2[#0{f1}]Kiif
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1} >= 0) // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#0{f1} >= 0) // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1} >= 0))
Source materialize.public.t2
  filter=((#0{f1} >= 0))

Target cluster: quickstart

EOF

# outer-to-inner-join conversion not allowed since that would alter the result of sum(t1.f1)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, sum(t1.f1), max(t2.f1) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f1) >= 0;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          Filter (#0{f1}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}) // { arity: 1 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 3 }
    Filter (#2{max_f1} >= 0) // { arity: 3 }
      Reduce group_by=[#0{f1}] aggregates=[sum(#0{f1}), max(#1{f1})] // { arity: 3 }
        Union // { arity: 2 }
          Map (null) // { arity: 2 }
            Union // { arity: 1 }
              Negate // { arity: 1 }
                Project (#0{f1}) // { arity: 1 }
                  Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
                    implementation
                      %1[#0]UKA » %0:l0[#0{f1}]K
                    Get l0 // { arity: 1 }
                    ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Get l1 // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                ReadStorage materialize.public.t1 // { arity: 2 }
          Project (#0{f1}, #0{f1}) // { arity: 2 }
            Get l1 // { arity: 1 }

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

Target cluster: quickstart

EOF

# outer join to inner join conversion not allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          Filter (#0{f1}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #2{f2}) // { arity: 2 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 2 }
    Reduce group_by=[#0{f1}] aggregates=[count(#1{f2})] // { arity: 2 }
      Union // { arity: 2 }
        Map (null) // { arity: 2 }
          Union // { arity: 1 }
            Negate // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
                  implementation
                    %1[#0]UKA » %0:l0[#0{f1}]K
                  Get l0 // { arity: 1 }
                  ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                    Distinct project=[#0{f1}] // { arity: 1 }
                      Project (#0{f1}) // { arity: 1 }
                        Get l1 // { arity: 2 }
            Project (#0{f1}) // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 2 }
        Get l1 // { arity: 2 }

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

Target cluster: quickstart

EOF

# outer join to inner join conversion not allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f2), max(t2.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          Filter (#0{f1}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #2{f2}) // { arity: 2 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{f1}]K
          Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 3 }
    Reduce group_by=[#0{f1}] aggregates=[count(#1{f2}), max(#1{f2})] // { arity: 3 }
      Union // { arity: 2 }
        Map (null) // { arity: 2 }
          Union // { arity: 1 }
            Negate // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
                  implementation
                    %1[#0]UKA » %0:l0[#0{f1}]K
                  Get l0 // { arity: 1 }
                  ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                    Distinct project=[#0{f1}] // { arity: 1 }
                      Project (#0{f1}) // { arity: 1 }
                        Get l1 // { arity: 2 }
            Project (#0{f1}) // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 2 }
        Get l1 // { arity: 2 }

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

Target cluster: quickstart

EOF

# outer join to inner join conversion allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, count(t2.f2), max(t2.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t2.f2) > 0;
----
Explained Query:
  Filter (#2{max_f2} > 0) // { arity: 3 }
    Reduce group_by=[#0{f1}] aggregates=[count(#1{f2}), max(#1{f2})] // { arity: 3 }
      Project (#0{f1}, #2{f2}) // { arity: 2 }
        Join on=(#0{f1} = #1{f1}) type=differential // { arity: 3 }
          implementation
            %0:t1[#0{f1}]K » %1:t2[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Filter (#0{f1}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t1 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

# outer join to inner join conversion allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, max(t1.f1 + t2.f2), sum(t1.f2 + t2.f2) from t1 LEFT JOIN t2 ON t1.f1 = t2.f1 group by t1.f1 having max(t1.f1 + t2.f2) > 0;
----
Explained Query:
  Filter (#1{max} > 0) // { arity: 3 }
    Reduce group_by=[#0{f1}] aggregates=[max((#0{f1} + #2{f2})), sum((#1{f2} + #2{f2}))] // { arity: 3 }
      Project (#0{f1}, #1{f2}, #3{f2}) // { arity: 3 }
        Join on=(#0{f1} = #2{f1}) type=differential // { arity: 4 }
          implementation
            %0:t1[#0{f1}]K » %1:t2[#0{f1}]K
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

# check that a filter on a non-count aggregation doesn't change the COUNT value
statement ok
drop table t1

statement ok
drop table t2

statement ok
create table t1(f1 integer, f2 integer)

statement ok
create table t2(f1 integer, f2 integer)

statement ok
insert into t1 values (1, 0), (1, 1), (1, 1)

statement ok
insert into t2 values (0, 2)

query III
select t1.f1, count(t1.f2), max(t2.f2) from t1 left join t2 on t1.f2 = t2.f1 group by t1.f1
----
1  3  2

query III
select t1.f1, count(t1.f2), max(t2.f2) from t1 left join t2 on t1.f2 = t2.f1 group by t1.f1 having max(t2.f2) > 0
----
1  3  2

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

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

Target cluster: quickstart

EOF

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

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

Target cluster: quickstart

EOF

# if the count is removed, the outer join can be safely converted into an inner join
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select t1.f1, max(t2.f2) from t1 left join t2 on t1.f2 = t2.f1 group by t1.f1 having max(t2.f2) > 0
----
Explained Query:
  Filter (#1{max_f2} > 0) // { arity: 2 }
    Reduce group_by=[#0{f1}] aggregates=[max(#1{f2})] // { arity: 2 }
      Project (#0{f1}, #3{f2}) // { arity: 2 }
        Join on=(#1{f2} = #2{f1}) type=differential // { arity: 4 }
          implementation
            %0:t1[#1{f2}]K » %1:t2[#0{f1}]K
          ArrangeBy keys=[[#1{f2}]] // { arity: 2 }
            Filter (#1{f2}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t1 // { arity: 2 }
          ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
            Filter (#0{f1}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

# regression test for database-issues#2190
statement ok
drop table t1

statement ok
create table t1(f1 integer)

statement ok
insert into t1 values (0), (1)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM (SELECT 123, COUNT(right_table.f1) AS aggregate FROM t1 AS left_table LEFT JOIN t1 AS right_table ON FALSE GROUP BY 1) AS subquery, t1 AS outer_table WHERE outer_table.f1 = subquery.aggregate;
----
Explained Query:
  Project (#2, #1, #0{f1}) // { arity: 3 }
    Filter (0 = integer_to_bigint(#0{f1})) // { arity: 3 }
      Map (0, 123) // { arity: 3 }
        ReadStorage materialize.public.t1 // { arity: 1 }

Source materialize.public.t1
  filter=((0 = integer_to_bigint(#0{f1})))

Target cluster: quickstart

EOF

query III
SELECT * FROM (SELECT 123, COUNT(right_table.f1) AS aggregate FROM t1 AS left_table LEFT JOIN t1 AS right_table ON FALSE GROUP BY 1) AS subquery, t1 AS outer_table WHERE outer_table.f1 = subquery.aggregate;
----
123  0  0


# non-null requirement on a non-count aggregation coming from a join predicate
statement ok
drop table t1

statement ok
drop table t2

statement ok
create table t1(f1 integer, f2 integer not null)

statement ok
insert into t1 values (null, 0)

statement ok
create table t2(f1 integer, f2 integer)

statement ok
insert into t2 values (null, 0)


statement ok
create table t3(f1 integer, f2 integer)

statement ok
insert into t3 values (null, 0), (null, 0), (1, 1), (6, 6)

query I
SELECT derived.agg1 FROM t1
JOIN (
    SELECT COUNT(*) AS agg1 , MAX(t2.f2) AS agg2
    FROM t2
    RIGHT JOIN t3 ON t3.f2 = 6
) AS derived ON TRUE
WHERE t1.f2 = derived.agg2;
----
4

# the count aggregation prevents the outer-to-inner join conversion
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT derived.agg1 FROM t1
JOIN (
    SELECT COUNT(*) AS agg1 , MAX(t2.f2) AS agg2
    FROM t2
    RIGHT JOIN t3 ON t3.f2 = 6
) AS derived ON TRUE
WHERE t1.f2 = derived.agg2;
----
Explained Query:
  With
    cte l0 =
      CrossJoin type=differential // { arity: 2 }
        implementation
          %1:t3[×]ef » %0:t2[×]ef
        ArrangeBy keys=[[]] // { arity: 1 }
          Project (#1{f2}) // { arity: 1 }
            ReadStorage materialize.public.t2 // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            Filter (#1{f2} = 6) // { arity: 2 }
              ReadStorage materialize.public.t3 // { arity: 2 }
  Return // { arity: 1 }
    Project (#1{count}) // { arity: 1 }
      Join on=(#0{f2} = #2{max_f2}) type=differential // { arity: 3 }
        implementation
          %1[#1{agg2}]UK » %0:t1[#0{f2}]K
        ArrangeBy keys=[[#0{f2}]] // { arity: 1 }
          Project (#1{f2}) // { arity: 1 }
            ReadStorage materialize.public.t1 // { arity: 2 }
        ArrangeBy keys=[[#1{max_f2}]] // { arity: 2 }
          Filter (#1{max_f2}) IS NOT NULL // { arity: 2 }
            Reduce aggregates=[count(*), max(#0{f2})] // { arity: 2 }
              Union // { arity: 1 }
                Project (#0{f2}) // { arity: 1 }
                  Get l0 // { arity: 2 }
                Project (#4) // { arity: 1 }
                  Map (null) // { arity: 5 }
                    Join on=(#0{f1} = #2{f1} AND #1{f2} = #3{f2}) type=differential // { arity: 4 }
                      implementation
                        %0[#0, #1]KK » %1:t3[#0, #1]KK
                      ArrangeBy keys=[[#0{f1}, #1{f2}]] // { arity: 2 }
                        Union // { arity: 2 }
                          Negate // { arity: 2 }
                            Map (6) // { arity: 2 }
                              Distinct project=[#0{f1}] // { arity: 1 }
                                Project (#1{f1}) // { arity: 1 }
                                  Get l0 // { arity: 2 }
                          Distinct project=[#0{f1}, #1{f2}] // { arity: 2 }
                            ReadStorage materialize.public.t3 // { arity: 2 }
                      ArrangeBy keys=[[#0{f1}, #1{f2}]] // { arity: 2 }
                        ReadStorage materialize.public.t3 // { arity: 2 }

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

Target cluster: quickstart

EOF

statement ok
DROP TABLE t1;

statement ok
CREATE TABLE t1 (f1 INTEGER NOT NULL);

statement ok
INSERT INTO t1 VALUES (1);

statement ok
DROP TABLE t2;

statement ok
CREATE TABLE t2 (f1 INTEGER NOT NULL);

statement ok
INSERT INTO t2 VALUES (1);

statement ok
INSERT INTO t2 VALUES (2);

# outer-to-inner join conversion not allowed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT SUM(t1.f1 + t2.f1), SUM(t2.f1 + 0)
FROM t2
LEFT JOIN t1
ON t1.f1 < t2.f1
HAVING SUM(t1.f1 + t2.f1) > 0;
----
Explained Query:
  With
    cte l0 =
      Filter (#1{f1} < #0{f1}) // { arity: 2 }
        CrossJoin type=differential // { arity: 2 }
          implementation
            %0:t2[×] » %1:t1[×]
          ArrangeBy keys=[[]] // { arity: 1 }
            ReadStorage materialize.public.t2 // { arity: 1 }
          ArrangeBy keys=[[]] // { arity: 1 }
            ReadStorage materialize.public.t1 // { arity: 1 }
  Return // { arity: 2 }
    Filter (#0{sum} > 0) // { arity: 2 }
      Reduce aggregates=[sum((#1{f1} + #0{f1})), sum((#0{f1} + 0))] // { arity: 2 }
        Union // { arity: 2 }
          Get l0 // { arity: 2 }
          Project (#0{f1}, #2) // { arity: 2 }
            Map (null) // { arity: 3 }
              Join on=(#0{f1} = #1{f1}) type=differential // { arity: 2 }
                implementation
                  %0[#0]K » %1:t2[#0]K
                ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                  Union // { arity: 1 }
                    Negate // { arity: 1 }
                      Distinct project=[#0{f1}] // { arity: 1 }
                        Project (#0{f1}) // { arity: 1 }
                          Get l0 // { arity: 2 }
                    Distinct project=[#0{f1}] // { arity: 1 }
                      ReadStorage materialize.public.t2 // { arity: 1 }
                ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
                  ReadStorage materialize.public.t2 // { arity: 1 }

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

Target cluster: quickstart

EOF

query II
SELECT SUM(t1.f1 + t2.f1), SUM(t2.f1 + 0)
FROM t2
LEFT JOIN t1
ON t1.f1 < t2.f1
HAVING SUM(t1.f1 + t2.f1) > 0;
----
3  3