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

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

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

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

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

Target cluster: quickstart

EOF

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

# `ReductionPushdown` pushes the Distinct from after the Join into both join inputs.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) 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 }
        Project (#0{f0}, #1{f1}, #3{f0}, #1{f1}) // { arity: 4 }
          Join on=(#1{f1} = #2{f1}) type=differential // { arity: 4 }
            implementation
              %0[#1]K » %1[#0]K
            ArrangeBy keys=[[#1{f1}]] // { arity: 2 }
              Distinct project=[#0{f0}, #1{f1}] // { arity: 2 }
                Filter (#1{f1}) IS NOT NULL // { arity: 2 }
                  ReadStorage materialize.public.x // { arity: 2 }
            ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
              Distinct project=[#1{f1}, #0{f0}] // { arity: 2 }
                Filter (#1{f1}) IS NOT NULL // { arity: 2 }
                  ReadStorage materialize.public.y // { arity: 2 }
        Get l0 // { arity: 4 }
  Return // { arity: 4 }
    Get l0 // { arity: 4 }

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

Target cluster: quickstart

EOF

## Regression test for https://github.com/MaterializeInc/database-issues/issues/8146
statement ok
CREATE TABLE t1 (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION NOT NULL);

statement ok
CREATE TABLE t2 (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION NOT NULL);

statement ok
CREATE TABLE t3 (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION NOT NULL);

statement ok
CREATE MATERIALIZED VIEW pk1 AS SELECT DISTINCT ON (f1) f1 , f2 FROM t1 WHERE f1 IS NOT NULL AND f2 IS NOT NULL;

statement ok
CREATE MATERIALIZED VIEW pk2 AS SELECT DISTINCT ON (f1) f1 , f2 FROM t2 WHERE f1 IS NOT NULL AND f2 IS NOT NULL;

statement ok
CREATE MATERIALIZED VIEW pk3 AS SELECT DISTINCT ON (f1) f1 , f2 FROM t3 WHERE f1 IS NOT NULL AND f2 IS NOT NULL;

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT FROM(SELECT) a JOIN(t2 JOIN pk1 ON NULL) ON(NULL) UNION SELECT FROM(SELECT AVG(a.f2) f2 FROM t2 a RIGHT JOIN t2 USING(f1) WHERE a.f2 IS NULL) a WHERE NULLIF(a.f2, 1) NOT IN(SELECT c FROM(SELECT 0 c FROM pk1 JOIN(SELECT f2 FROM pk1) b ON NULLIF(b.f2, b.f2) < b.f2) d);
----
Explained Query:
  Distinct project=[]
    Union
      Negate
        Distinct project=[]
          Project ()
            Filter (case when (#1{f2} = #1{f2}) then null else #1{f2} end < #1{f2})
              ReadStorage materialize.public.pk1
      Constant
        - ()

Source materialize.public.t2
Source materialize.public.pk1
  filter=((case when (#1{f2} = #1{f2}) then null else #1{f2} end < #1{f2}))

Target cluster: quickstart

EOF

## Regression test for https://github.com/MaterializeInc/database-issues/issues/9013
query RRR
SELECT
    (a1.f1) AS c1,
    (a2.f1) AS c2,
    (a1.f2) AS c3
FROM (
    SELECT
        a1.f2 AS f1,
        a1.f2 + a1.f1 AS f2
    FROM pk1 AS a1
    ORDER BY 1, 2
    LIMIT 1 OFFSET 1
) AS a1
LEFT JOIN (
    SELECT a1.f2 AS f1
    FROM t2 AS a1
    ORDER BY 1
    LIMIT 1 OFFSET 7
) AS a2
ON (a1.f2 != 4)
WHERE
    a2.f1 IS NULL
    AND a1.f1 + a1.f2 = NULLIF(a1.f1, a2.f1)
GROUP BY 1, 2, 3;
----

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (arity, humanized expressions) AS VERBOSE TEXT FOR
SELECT
    (a1.f1) AS c1,
    (a2.f1) AS c2,
    (a1.f2) AS c3
FROM (
    SELECT
        a1.f2 AS f1,
        a1.f2 + a1.f1 AS f2
    FROM pk1 AS a1
    ORDER BY 1, 2
    LIMIT 1 OFFSET 1
) AS a1
LEFT JOIN (
    SELECT a1.f2 AS f1
    FROM t2 AS a1
    ORDER BY 1
    LIMIT 1 OFFSET 7
) AS a2
ON (a1.f2 != 4)
WHERE
    a2.f1 IS NULL
    AND a1.f1 + a1.f2 = NULLIF(a1.f1, a2.f1)
GROUP BY 1, 2, 3;
----
Explained Query:
  With
    cte l0 =
      Filter (#0{f2} = (#0{f2} + #1{f2})) // { arity: 2 }
        TopK order_by=[#0{f2} asc nulls_last, #1 asc nulls_last] limit=1 offset=1 // { arity: 2 }
          Project (#1{f2}, #2) // { arity: 2 }
            Map ((#1{f2} + #0{f1})) // { arity: 3 }
              ReadStorage materialize.public.pk1 // { arity: 2 }
  Return // { arity: 3 }
    Project (#0{f2}, #3, #2) // { arity: 3 }
      Map (null) // { arity: 4 }
        Distinct project=[#0{f2}, (#0{f2} + #1{f2}), #1] // { arity: 3 }
          Union // { arity: 2 }
            Negate // { arity: 2 }
              CrossJoin type=differential // { arity: 2 }
                ArrangeBy keys=[[]] // { arity: 2 }
                  Filter (#1{f2} != 4) // { arity: 2 }
                    Get l0 // { arity: 2 }
                ArrangeBy keys=[[]] // { arity: 0 }
                  Project () // { arity: 0 }
                    TopK order_by=[#0{f2} asc nulls_last] limit=1 offset=7 // { arity: 1 }
                      Project (#1{f2}) // { arity: 1 }
                        ReadStorage materialize.public.t2 // { arity: 2 }
            Get l0 // { arity: 2 }

Source materialize.public.t2
Source materialize.public.pk1

Target cluster: quickstart

EOF