Materialize/test/sqllogictest/transform/redundant_join.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 t1(f1 INT, f2 INT);

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

statement ok
CREATE VIEW v1 AS SELECT t1 from t1;

statement ok
CREATE DEFAULT INDEX ON v1

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1, (SELECT DISTINCT f1 % 2 AS F FROM t1) T WHERE t1.f1 % 2 = t.f;
----
Explained Query:
  Filter (#0{f1}) IS NOT NULL // { arity: 3 }
    Map ((#0{f1} % 2)) // { arity: 3 }
      ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1
  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 * FROM v1, (SELECT DISTINCT (v1.t1).f1 as f1 FROM v1) Y WHERE (v1.t1).f1 = y.f1;
----
Explained Query (fast path):
  Filter (#1) IS NOT NULL
    Map (record_get[0](#0{t1}))
      ReadIndex on=materialize.public.v1 v1_primary_idx=[*** full scan ***]

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

Target cluster: quickstart

EOF

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

# Wrapping the first example from this file in a WMR works, but only if we can
# eliminate the `<expr> IS NOT NULL` predicates that are added when pushing the
# join condition through the CrossJoin.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(f1 INT, f2 INT, f INT) AS (
    SELECT * FROM c0
    UNION
    SELECT * FROM t2, (SELECT DISTINCT f1 % 2 AS f FROM t2) t0 WHERE t2.f1 % 2 = t0.f
  )
SELECT * FROM c0;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Distinct project=[#0{f1}..=#2] // { arity: 3 }
        Union // { arity: 3 }
          Get l0 // { arity: 3 }
          Map ((#0{f1} % 2)) // { arity: 3 }
            ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 3 }
    Get l0 // { arity: 3 }

Source materialize.public.t2

Target cluster: quickstart

EOF

# Same query, but selecting from t1 instead of t2. The added `- IS NOT NULL`
# filters prevent redundant join elimination at first. To be able to eliminate
# the join we need to factor out the filter on top of t1 behind a common
# binding (fixed by database-issues#5342).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(f1 INT, f2 INT, f INT) AS (
    SELECT * FROM c0
    UNION
    SELECT * FROM t1, (SELECT DISTINCT f1 % 2 AS f FROM t1) t0 WHERE t1.f1 % 2 = t0.f
  )
SELECT * FROM c0;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Distinct project=[#0{f1}..=#2] // { arity: 3 }
        Union // { arity: 3 }
          Get l0 // { arity: 3 }
          Filter (#0{f1}) IS NOT NULL // { arity: 3 }
            Map ((#0{f1} % 2)) // { arity: 3 }
              ReadStorage materialize.public.t1 // { arity: 2 }
  Return // { arity: 3 }
    Get l0 // { arity: 3 }

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

Target cluster: quickstart

EOF

# Another case that does not work at the moment because of the naive ProvInfo
# initialization for WMR bindings.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH
  t0 AS (
    SELECT DISTINCT f1 % 2 AS f, 42 as c FROM t2
  )
SELECT * FROM (
  WITH MUTUALLY RECURSIVE
    c0(f INT) AS (
      SELECT f FROM t0
    ),
    c1(f1 INT, f2 INT, f INT) AS (
      SELECT * FROM c1
      UNION
      SELECT f, f, f from c0
      UNION
      SELECT * FROM t2, c0 WHERE t2.f1 % 2 = c0.f
    )
  SELECT f FROM c1 UNION ALL SELECT c FROM t0
);
----
Explained Query:
  With
    cte l0 =
      Distinct project=[(#0{f1} % 2)] // { arity: 1 }
        Project (#0{f1}) // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 1 }
    With Mutually Recursive
      cte l1 =
        Distinct project=[#0{f1}..=#2] // { arity: 3 }
          Union // { arity: 3 }
            Get l1 // { arity: 3 }
            Project (#0, #0, #0) // { arity: 3 }
              Get l0 // { arity: 1 }
            Map ((#0{f1} % 2)) // { arity: 3 }
              ReadStorage materialize.public.t2 // { arity: 2 }
    Return // { arity: 1 }
      Union // { arity: 1 }
        Project (#2) // { arity: 1 }
          Get l1 // { arity: 3 }
        Project (#1) // { arity: 1 }
          Map (42) // { arity: 2 }
            Get l0 // { arity: 1 }

Source materialize.public.t2

Target cluster: quickstart

EOF