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

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

query I
select (select sum(1) from (select c) group by c) from (select 1 as c)
----
1

query T
select (select jsonb_agg(1) from (select c) group by c) from (select 1 as c)
----
[1]

statement ok
CREATE TABLE x (f0 int4, f1 string);

statement ok
CREATE TABLE y (f0 int2, f1 string primary key);

# `ReductionPushdown` pushes the Distinct from after the Join into both join inputs.
# Then, `ReduceElision` eliminates the Distinct from the 2nd join input.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, join implementations, keys, humanized expressions) AS VERBOSE TEXT FOR
SELECT DISTINCT *
FROM x, y
WHERE x.f1 = y.f1
----
Explained Query:
  Project (#0{f0}..=#2{f0}, #1{f1}) // { arity: 4, keys: "([0, 1])" }
    Join on=(#1{f1} = #3{f1}) type=differential // { arity: 4, keys: "([0, 1])" }
      implementation
        %1:y[#1]UK » %0[#1]K
      ArrangeBy keys=[[#1{f1}]] // { arity: 2, keys: "([0, 1])" }
        Distinct project=[#0{f0}, #1{f1}] // { arity: 2, keys: "([0, 1])" }
          Filter (#1{f1}) IS NOT NULL // { arity: 2, keys: "()" }
            ReadStorage materialize.public.x // { arity: 2, keys: "()" }
      ArrangeBy keys=[[#1{f1}]] // { arity: 2, keys: "([1])" }
        ReadStorage materialize.public.y // { arity: 2, keys: "([1])" }

Source materialize.public.x
  filter=((#1{f1}) IS NOT NULL)
Source materialize.public.y

Target cluster: quickstart

EOF

## -------------------- Tests for WITH MUTUALLY RECURSIVE --------------------

# `ReductionPushdown` pushes the Distinct from after the Join into both join inputs.
# Then, `ReduceElision` eliminates the Distinct from the 2nd join input.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, join implementations, keys, humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(f0 int4, f1 string, f2 int2, f3 string) AS (
    (SELECT DISTINCT *
     FROM x, y
     WHERE x.f1 = y.f1)
    UNION ALL
    (SELECT *
     FROM c0)
  )
SELECT * FROM c0;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union // { arity: 4, keys: "()" }
        Project (#0{f0}..=#2{f0}, #1{f1}) // { arity: 4, keys: "([0, 1])" }
          Join on=(#1{f1} = #3{f1}) type=differential // { arity: 4, keys: "([0, 1])" }
            implementation
              %1:y[#1]UK » %0[#1]K
            ArrangeBy keys=[[#1{f1}]] // { arity: 2, keys: "([0, 1])" }
              Distinct project=[#0{f0}, #1{f1}] // { arity: 2, keys: "([0, 1])" }
                Filter (#1{f1}) IS NOT NULL // { arity: 2, keys: "()" }
                  ReadStorage materialize.public.x // { arity: 2, keys: "()" }
            ArrangeBy keys=[[#1{f1}]] // { arity: 2, keys: "([1])" }
              ReadStorage materialize.public.y // { arity: 2, keys: "([1])" }
        Get l0 // { arity: 4, keys: "()" }
  Return // { arity: 4, keys: "()" }
    Get l0 // { arity: 4, keys: "()" }

Source materialize.public.x
  filter=((#1{f1}) IS NOT NULL)
Source materialize.public.y

Target cluster: quickstart

EOF

# Similar to the previous test, but
#  - Has UNION instead of UNION ALL. This means that there is a Distinct at the root of l0.
#  - The second input of the UNION has a DISTINCT. This should be eliminated later by `ReduceElision` after we make the
#    unique key inference smarter for `LetRec`.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, join implementations, keys, humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(f0 int4, f1 string, f2 int2, f3 string) AS (
    (SELECT DISTINCT *
     FROM x, y
     WHERE x.f1 = y.f1)
    UNION
    (SELECT DISTINCT *
     FROM c0)
  )
SELECT * FROM c0;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Distinct project=[#0{f0}..=#3{f1}] // { arity: 4, keys: "([0, 1, 2, 3])" }
        Union // { arity: 4, keys: "()" }
          Project (#0{f0}..=#2{f0}, #1{f1}) // { arity: 4, keys: "()" }
            Join on=(#1{f1} = #3{f1}) type=differential // { arity: 4, keys: "()" }
              implementation
                %1:y[#1{f1}]UK » %0:x[#1{f1}]K
              ArrangeBy keys=[[#1{f1}]] // { arity: 2, keys: "()" }
                Filter (#1{f1}) IS NOT NULL // { arity: 2, keys: "()" }
                  ReadStorage materialize.public.x // { arity: 2, keys: "()" }
              ArrangeBy keys=[[#1{f1}]] // { arity: 2, keys: "([1])" }
                ReadStorage materialize.public.y // { arity: 2, keys: "([1])" }
          Get l0 // { arity: 4, keys: "([0, 1, 2, 3])" }
  Return // { arity: 4, keys: "([0, 1, 2, 3])" }
    Get l0 // { arity: 4, keys: "([0, 1, 2, 3])" }

Source materialize.public.x
  filter=((#1{f1}) IS NOT NULL)
Source materialize.public.y

Target cluster: quickstart

EOF