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

# This file contains tests for the PredicatePushdown transform.

mode cockroach

statement ok
CREATE TABLE x (a int not null, u int, b bool)

statement ok
CREATE TABLE y (a int not null)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT b FROM (SELECT b, not(b) as neg FROM x) WHERE NOT(neg)
----
Explained Query:
  Project (#2{b}) // { arity: 1 }
    Filter #2{b} // { arity: 3 }
      ReadStorage materialize.public.x // { arity: 3 }

Source materialize.public.x
  filter=(#2{b})

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT b FROM (SELECT b, b = false as neg FROM x) WHERE NOT(neg)
----
Explained Query:
  Project (#2{b}) // { arity: 1 }
    Filter (#2{b} != false) // { arity: 3 }
      ReadStorage materialize.public.x // { arity: 3 }

Source materialize.public.x
  filter=((#2{b} != false))

Target cluster: quickstart

EOF

# Inline binary expressions.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
  SELECT a FROM (SELECT a, a = 3 AS cond, u != 2 as cond2 FROM x) WHERE NOT(cond) AND NOT(cond2)
----
Explained Query:
  Project (#0{a}) // { arity: 1 }
    Filter (#1{u} = 2) AND (#0{a} != 3) // { arity: 3 }
      ReadStorage materialize.public.x // { arity: 3 }

Source materialize.public.x
  filter=((#0{a} != 3) AND (#1{u} = 2))

Target cluster: quickstart

EOF

# This test was testing our earlier heuristics for when can push_filters_through_map inline a Map expression.
# (Which was to not inline binary expressions not having a leaf argument.) This has changed with
# https://github.com/MaterializeInc/materialize/pull/22098, so now we inline in this test.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
  SELECT a FROM (SELECT a, (a + 1) = (u + 3) AS cond FROM x) WHERE NOT(cond)
----
Explained Query:
  Project (#0{a}) // { arity: 1 }
    Filter ((#0{a} + 1) != (#1{u} + 3)) // { arity: 3 }
      ReadStorage materialize.public.x // { arity: 3 }

Source materialize.public.x
  filter=(((#0{a} + 1) != (#1{u} + 3)))

Target cluster: quickstart

EOF

# Similar to the above: we push everything here since
# https://github.com/MaterializeInc/materialize/pull/22098

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
  SELECT * FROM
    (SELECT a, b, b+1 as c FROM
      (SELECT a, a+1 as b FROM y))
WHERE b = 3 AND c = 4
----
Explained Query:
  Filter (#1 = 3) AND (4 = (#1 + 1)) // { arity: 3 }
    Map ((#0{a} + 1), 4) // { arity: 3 }
      ReadStorage materialize.public.y // { arity: 1 }

Source materialize.public.y
  filter=((3 = #1) AND (4 = (#1 + 1)))
  map=((#0{a} + 1))

Target cluster: quickstart

EOF

# Regression test to ensure that a filter gets pushed down to all inputs of a union.

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR
SELECT a FROM (SELECT DISTINCT a FROM x UNION ALL SELECT a FROM y) WHERE a = 3
----
Filter (#0{a} = 3) // { arity: 1 }
  Union // { arity: 1 }
    Distinct project=[#0] // { arity: 1 }
      Project (#0) // { arity: 1 }
        CrossJoin // { arity: 3 }
          Constant // { arity: 0 }
            - ()
          Get materialize.public.x // { arity: 3 }
    CrossJoin // { arity: 1 }
      Constant // { arity: 0 }
        - ()
      Get materialize.public.y // { arity: 1 }

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT a FROM (SELECT DISTINCT a FROM x UNION ALL SELECT a FROM y) WHERE a = 3
----
Explained Query:
  Union // { arity: 1 }
    Map (3) // { arity: 1 }
      Distinct project=[] // { arity: 0 }
        Project () // { arity: 0 }
          Filter (#0{a} = 3) // { arity: 3 }
            ReadStorage materialize.public.x // { arity: 3 }
    Filter (#0{a} = 3) // { arity: 1 }
      ReadStorage materialize.public.y // { arity: 1 }

Source materialize.public.x
  filter=((#0{a} = 3))
Source materialize.public.y
  filter=((#0{a} = 3))

Target cluster: quickstart

EOF

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

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

# redundant equivalence is pushed down to all join branches and removed
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1, t2 WHERE t1.f1 = t2.f1 AND t1.f2 = t2.f2 AND t1.f1 + t2.f2 = t2.f1 + t1.f2;
----
Explained Query:
  Project (#0{f1}, #1{f2}, #0{f1}, #1{f2}) // { arity: 4 }
    Join on=(#0{f1} = #2{f1} AND #1{f2} = #3{f2}) type=differential // { arity: 4 }
      implementation
        %0:t1[#0{f1}, #1{f2}]KK » %1:t2[#0{f1}, #1{f2}]KK
      ArrangeBy keys=[[#0{f1}, #1{f2}]] // { arity: 2 }
        Filter (#0{f1}) IS NOT NULL AND (#1{f2}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
      ArrangeBy keys=[[#0{f1}, #1{f2}]] // { arity: 2 }
        Filter (#0{f1}) IS NOT NULL AND (#1{f2}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

# database-issues#2377 avoidable cross joins
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from t1, t2 where t1.f1 = t2.f1 + 1 or (t1.f1 is null and t2.f1 is null);
----
Explained Query:
  Join on=(#0{f1} = (#2{f1} + 1)) type=differential // { arity: 4 }
    implementation
      %0:t1[#0{f1}]K » %1:t2[(#0{f1} + 1)]K
    ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
      ReadStorage materialize.public.t1 // { arity: 2 }
    ArrangeBy keys=[[(#0{f1} + 1)]] // { arity: 2 }
      ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from t1, t2 where t1.f1 = t2.f1 + 1 or (t1.f1 is null and (t2.f1 + 1) is null);
----
Explained Query:
  Join on=(#0{f1} = (#2{f1} + 1)) type=differential // { arity: 4 }
    implementation
      %0:t1[#0{f1}]K » %1:t2[(#0{f1} + 1)]K
    ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
      ReadStorage materialize.public.t1 // { arity: 2 }
    ArrangeBy keys=[[(#0{f1} + 1)]] // { arity: 2 }
      ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from t1, t2 where t2.f1 = t1.f1 + 1 or (t1.f1 is null and (t2.f1 + 1) is null);
----
Explained Query:
  Join on=(#2{f1} = (#0{f1} + 1)) type=differential // { arity: 4 }
    implementation
      %0:t1[(#0{f1} + 1)]K » %1:t2[#0{f1}]K
    ArrangeBy keys=[[(#0{f1} + 1)]] // { arity: 2 }
      ReadStorage materialize.public.t1 // { arity: 2 }
    ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
      ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from t1, t2 where t2.f1 = t1.f1 + 1 or (t1.f1 is null and ((t2.f1 + 1) is null and t1.f1 is null));
----
Explained Query:
  Join on=(#2{f1} = (#0{f1} + 1)) type=differential // { arity: 4 }
    implementation
      %0:t1[(#0{f1} + 1)]K » %1:t2[#0{f1}]K
    ArrangeBy keys=[[(#0{f1} + 1)]] // { arity: 2 }
      ReadStorage materialize.public.t1 // { arity: 2 }
    ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
      ReadStorage materialize.public.t2 // { arity: 2 }

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

Target cluster: quickstart

EOF

# Join-Dependent Predicate Duplication (JoinInputMapper::consequence_for_input)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1, t2 WHERE t1.f1 = t2.f1 AND ((t1.f2 = 3 AND t2.f2 = 4) OR (t1.f2 = 5 AND t2.f2 = 6));
----
Explained Query:
  Project (#0{f1}, #1{f2}, #0{f1}, #3{f2}) // { arity: 4 }
    Filter (((#1{f2} = 3) AND (#3{f2} = 4)) OR ((#1{f2} = 5) AND (#3{f2} = 6))) // { arity: 4 }
      Join on=(#0{f1} = #2{f1}) type=differential // { arity: 4 }
        implementation
          %0:t1[#0{f1}]Kef » %1:t2[#0{f1}]Kef
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Filter (#0{f1}) IS NOT NULL AND ((#1{f2} = 3) OR (#1{f2} = 5)) // { arity: 2 }
            ReadStorage materialize.public.t1 // { arity: 2 }
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Filter (#0{f1}) IS NOT NULL AND ((#1{f2} = 4) OR (#1{f2} = 6)) // { arity: 2 }
            ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL AND ((#1{f2} = 3) OR (#1{f2} = 5)))
Source materialize.public.t2
  filter=((#0{f1}) IS NOT NULL AND ((#1{f2} = 4) OR (#1{f2} = 6)))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t1, t2 WHERE t1.f1 = t2.f1 AND ((t1.f2 = 3 AND t2.f2 = 4) OR t1.f2 = 5);
----
Explained Query:
  Project (#0{f1}, #1{f2}, #0{f1}, #3{f2}) // { arity: 4 }
    Filter ((#1{f2} = 5) OR ((#1{f2} = 3) AND (#3{f2} = 4))) // { arity: 4 }
      Join on=(#0{f1} = #2{f1}) type=differential // { arity: 4 }
        implementation
          %0:t1[#0{f1}]Kef » %1:t2[#0{f1}]Kef
        ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
          Filter (#0{f1}) IS NOT NULL AND ((#1{f2} = 3) OR (#1{f2} = 5)) // { 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 AND ((#1{f2} = 3) OR (#1{f2} = 5)))
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 * FROM t1, t2
WHERE t1.f2 = 27 OR (t1.f2 <= 1995 AND t1.f1 = t2.f1);
----
Explained Query:
  Filter ((#1{f2} = 27) OR ((#0{f1} = #2{f1}) AND (#1{f2} <= 1995))) // { arity: 4 }
    CrossJoin type=differential // { arity: 4 }
      implementation
        %0:t1[×]eiif » %1:t2[×]eiif
      ArrangeBy keys=[[]] // { arity: 2 }
        Filter ((#1{f2} = 27) OR (#1{f2} <= 1995)) // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
      ArrangeBy keys=[[]] // { arity: 2 }
        ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
  filter=(((#1{f2} = 27) OR (#1{f2} <= 1995)))
Source materialize.public.t2

Target cluster: quickstart

EOF

# Delta join -- In this case, `JoinImplementation` lifts the newly created predicates to after the join at the end of
# MIR. However, MIR -> LIR lowering will push these predicates to their correct place. We can check this in the physical
# plan, i.e., that they are in the `initial_closure`.

statement ok
CREATE INDEX t1_f1_ind on t1(f1)

statement ok
CREATE INDEX t2_f1_ind on t2(f1)

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM t1, t2 WHERE t1.f1 = t2.f1 AND ((t1.f2 = 3 AND t2.f2 = 4) OR (t1.f2 = 5 AND t2.f2 = 6));
----
Explained Query:
  Join::Linear
    final_closure
      project=(#0, #1, #0, #2)
    linear_stage[0]
      closure
        filter=((#0{f1}) IS NOT NULL AND (#3 OR #4) AND (#5 OR #6) AND ((#3 AND #5) OR (#4 AND #6)))
        map=((#1{f2} = 3), (#1{f2} = 5), (#2{f2} = 4), (#2{f2} = 6))
      lookup={ relation=1, key=[#0{f1}] }
      stream={ key=[#0{f1}], thinning=(#1) }
    source={ relation=0, key=[#0{f1}] }
    Get::PassArrangements materialize.public.t1
      raw=false
      arrangements[0]={ key=[#0{f1}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]
    Get::PassArrangements materialize.public.t2
      raw=false
      arrangements[0]={ key=[#0{f1}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

Used Indexes:
  - materialize.public.t1_f1_ind (differential join)
  - materialize.public.t2_f1_ind (differential join)

Target cluster: quickstart

EOF

# Regression tests for https://github.com/MaterializeInc/database-issues/issues/4659

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

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

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *
FROM tt2 ,
(
    SELECT AVG(1) AS f1 , COUNT (1) AS f2
    FROM tt2
    WHERE f2 IS NULL
) AS a2
WHERE a2.f1 = 1
OR a2.f1 + 4 = 8
AND tt2.f2 = 1;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
        s.name, r.name
FROM
        mz_schemas s,
        mz_relations r
WHERE
        r.schema_id = s.id AND (r.type = 'materialized-view' OR (r.type = 'view' AND s.name != 'doesntmatter'))
----
Explained Query:
  Project (#1{name}, #3{name}) // { arity: 2 }
    Filter ((#4{type} = "materialized-view") OR ((#4{type} = "view") AND (#1{name} != "doesntmatter"))) // { arity: 5 }
      Join on=(#0{id} = #2{schema_id}) type=differential // { arity: 5 }
        implementation
          %0:mz_schemas[#0{id}]UK » %1[#0{schema_id}]Kef
        ArrangeBy keys=[[#0{id}]] // { arity: 2 }
          Project (#0{id}, #3{name}) // { arity: 2 }
            ReadIndex on=mz_schemas mz_schemas_ind=[*** full scan ***] // { arity: 6 }
        ArrangeBy keys=[[#0{schema_id}]] // { arity: 3 }
          Union // { arity: 3 }
            Project (#2{schema_id}, #3{name}, #9) // { arity: 3 }
              Map ("view") // { arity: 10 }
                ReadIndex on=mz_views mz_views_ind=[*** full scan ***] // { arity: 9 }
            Project (#2{schema_id}, #3{name}, #10) // { arity: 3 }
              Map ("materialized-view") // { arity: 11 }
                ReadIndex on=mz_materialized_views mz_materialized_views_ind=[*** full scan ***] // { arity: 10 }

Used Indexes:
  - mz_catalog.mz_materialized_views_ind (*** full scan ***)
  - mz_catalog.mz_schemas_ind (*** full scan ***)
  - mz_catalog.mz_views_ind (*** full scan ***)

Target cluster: mz_catalog_server

EOF

# Regression tests for database-issues#5089
# Push predicates through Threshold and TopK

# Test Threshold.
query T multiline
EXPLAIN OPTIMIZED PLAN AS TEXT FOR
WITH cte(a) as (SELECT a FROM x EXCEPT ALL SELECT a FROM y)
SELECT a * 5 FROM cte WHERE a = 5
----
Explained Query:
  Project (#1)
    Map (25)
      Threshold
        Union
          Project (#0)
            Filter (#0{a} = 5)
              ReadStorage materialize.public.x
          Negate
            Filter (#0{a} = 5)
              ReadStorage materialize.public.y

Source materialize.public.x
  filter=((#0{a} = 5))
Source materialize.public.y
  filter=((#0{a} = 5))

Target cluster: quickstart

EOF

# Test TopK.
query T multiline
EXPLAIN OPTIMIZED PLAN AS TEXT FOR
SELECT a FROM y as y, LATERAL(SELECT u FROM x WHERE y.a = x.a LIMIT 5) WHERE a IN (41, 42);
----
Explained Query:
  With
    cte l0 =
      Filter ((#0{a} = 41) OR (#0{a} = 42))
        ReadStorage materialize.public.y
  Return
    Project (#0)
      Join on=(#0 = #1) type=differential
        ArrangeBy keys=[[#0]]
          Get l0
        ArrangeBy keys=[[#0]]
          TopK group_by=[#0] limit=5
            Project (#0)
              Join on=(#0{a} = #1{a}) type=differential
                ArrangeBy keys=[[#0{a}]]
                  Distinct project=[#0]
                    Get l0
                ArrangeBy keys=[[#0{a}]]
                  Project (#0)
                    Filter ((#0{a} = 41) OR (#0{a} = 42))
                      ReadStorage materialize.public.x

Source materialize.public.x
  filter=(((#0{a} = 41) OR (#0{a} = 42)))
Source materialize.public.y
  filter=(((#0{a} = 41) OR (#0{a} = 42)))

Target cluster: quickstart

EOF

# One more for https://github.com/MaterializeInc/database-issues/issues/4659
# https://github.com/MaterializeInc/materialize/pull/16147#issuecomment-1322042176

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false;
----
COMPLETE 0

statement ok
DROP SCHEMA public CASCADE ;

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET enable_rbac_checks;
----
COMPLETE 0

statement ok
CREATE SCHEMA public ;

statement ok
CREATE TABLE table_f2_f3 ( f2 INTEGER , f3 INTEGER  ) ;

statement ok
CREATE TABLE table_f3_f4_f5 ( f3 INTEGER , f4 INTEGER , f5 INTEGER  ) ;

statement ok
CREATE TABLE table_f4_f5_f6 ( f4 INTEGER , f5 INTEGER , f6 INTEGER  ) ;

statement ok
INSERT INTO table_f2_f3 VALUES ( 0 , 0 ), ( 0 , 1 ), ( NULL , 0 );

statement ok
INSERT INTO table_f3_f4_f5 VALUES ( 0 , 1 , NULL ), ( 1 , 1 , 0 ), ( 1 , NULL , 0 );

statement ok
INSERT INTO table_f4_f5_f6 VALUES ( 0 , 0 , 1 ), ( 1 , 0 , 0 ), ( NULL , 0 , 1 ), ( NULL , 1 , 0 ), ( NULL , 1 , 0 );

query IIIIII rowsort
SELECT * FROM table_f2_f3  JOIN ( table_f3_f4_f5  JOIN table_f4_f5_f6  USING ( f5  )  )  USING ( f3  )
 WHERE f6  IS  NULL  OR f3  >= f6  AND f6  <=  10000   ;
----
1  0  0  1  0  1
1  0  0  1  1  0
1  0  0  1  NULL  1
1  0  0  NULL  0  1
1  0  0  NULL  1  0
1  0  0  NULL  NULL  1

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

statement ok
CREATE TABLE init(n int, m int, s string);

statement ok
INSERT INTO init VALUES (1, 3, 'aaa'), (2, 4, 'bbb');

# Across Let bindings, and within a Let binding.
# The `WHERE n<5` should be pushed into l0.
# Furthermore, within l0 it should be pushed down on top of the Gets. The Source should also have it.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0 WHERE n<5
  ),
  c2(n int) AS (
    (SELECT * FROM c0 WHERE n<5)
    UNION ALL
    (SELECT * FROM c1)
    UNION ALL
    (SELECT * FROM c1)
  )
SELECT * FROM c2;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          Filter (#0{n} < 5)
            ReadStorage materialize.public.init
        Filter (#0{n} < 5)
          Get l2
    cte l1 =
      Project (#1)
        Map ((#0{n} + #0{n}))
          Get l0
    cte l2 =
      Union
        Get l0
        Get l1
        Get l1
  Return
    Get l2

Source materialize.public.init
  filter=((#0{n} < 5))

Target cluster: quickstart

EOF

# Here, a pushdown should NOT happen, because not every use of the Get has the predicate.
# That is, the `< 5` predicate should stay inside the `cte l1 =`, and shouldn't appear anywhere else.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0 WHERE n<5
  ),
  c2(n int) AS (
    (SELECT * FROM c0)
    UNION ALL
    (SELECT * FROM c1)
    UNION ALL
    (SELECT * FROM c1)
  )
SELECT * FROM c2;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          ReadStorage materialize.public.init
        Get l2
    cte l1 =
      Project (#1)
        Filter (#0{n} < 5)
          Map ((#0{n} + #0{n}))
            Get l0
    cte l2 =
      Union
        Get l0
        Get l1
        Get l1
  Return
    Get l2

Source materialize.public.init

Target cluster: quickstart

EOF

# Same as the previous query, but the predicate should stay inside the `cte l2 =`
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0
  ),
  c2(n int) AS (
    (SELECT * FROM c0 WHERE n<5)
    UNION ALL
    (SELECT * FROM c1)
    UNION ALL
    (SELECT * FROM c1)
  )
SELECT * FROM c2;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          ReadStorage materialize.public.init
        Get l2
    cte l1 =
      Project (#1)
        Map ((#0{n} + #0{n}))
          Get l0
    cte l2 =
      Union
        Filter (#0{n} < 5)
          Get l0
        Get l1
        Get l1
  Return
    Get l2

Source materialize.public.init

Target cluster: quickstart

EOF

# Similar to the previous two queries, but here one of the uses of `l0` is in the body, so this would catch the error
# case of forgetting to call PredicatePushdown's `action` on the `body` of the `LetRec`, whose role here is to make the
# intersection in `get_predicates` empty.
# The predicate should NOT be pushed into the `cte l0 =`, and should stay in both the `cte l2 =` and the `cte l1 =`.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0 WHERE n<5
  ),
  c2(n int) AS (
    (SELECT * FROM c0 WHERE n<5)
    UNION ALL
    (SELECT * FROM c1)
    UNION ALL
    (SELECT * FROM c1)
  )
SELECT * FROM ((SELECT * FROM c2) UNION (SELECT * FROM c0));
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          ReadStorage materialize.public.init
        Get l2
    cte l1 =
      Project (#1)
        Filter (#0{n} < 5)
          Map ((#0{n} + #0{n}))
            Get l0
    cte l2 =
      Union
        Filter (#0{n} < 5)
          Get l0
        Get l1
        Get l1
  Return
    Distinct project=[#0{n}]
      Union
        Get l2
        Get l0

Source materialize.public.init

Target cluster: quickstart

EOF

# For now, we can’t push from the body into such a Let binding that is being referenced across
# iterations (l2).
# That is, the `> 7` predicate should stay inside the `cte l0 =` and the body.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2 WHERE n>7)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0
  ),
  c2(n int) AS (
    (SELECT * FROM c0)
    UNION ALL
    (SELECT * FROM c1)
    UNION ALL
    (SELECT * FROM c1)
  )
SELECT * FROM c2 WHERE n>7;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          ReadStorage materialize.public.init
        Filter (#0{n} > 7)
          Get l2
    cte l1 =
      Project (#1)
        Map ((#0{n} + #0{n}))
          Get l0
    cte l2 =
      Union
        Get l0
        Get l1
        Get l1
  Return
    Filter (#0{n} > 7)
      Get l2

Source materialize.public.init

Target cluster: quickstart

EOF

# We can push down from the body into such a Let binding that is NOT being referenced across
# iterations (l1).
# The `> 7` should end up inside the `cte l1 =`, and should disappear from everywhere else.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0
  ),
  c2(n int) AS (
    (SELECT * FROM c0)
    UNION ALL
    (SELECT * FROM c1 WHERE n>7)
    UNION ALL
    (SELECT * FROM c1 WHERE n>7)
  )
SELECT * FROM c1 WHERE n>7;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          ReadStorage materialize.public.init
        Get l2
    cte l1 =
      Project (#1)
        Filter (#1 > 7)
          Map ((#0{n} + #0{n}))
            Get l0
    cte l2 =
      Union
        Get l0
        Get l1
        Get l1
  Return
    Project (#1)
      Filter (#1 > 7)
        Map ((#0{n} + #0{n}))
          Get l0

Source materialize.public.init

Target cluster: quickstart

EOF

# Even though the only usage of `l2` has a predicate, we don't push that predicate into `l2`,
# because `l2` is being referenced across iterations.
# That is, the `< 3` predicate should stay inside the `cte l0 =`.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(n int) AS (
    (SELECT n FROM init)
    UNION ALL
    (SELECT * FROM c2 WHERE n<3)
  ),
  c1(n int) AS (
    SELECT n+n FROM c0
  ),
  c2(n int) AS (
    (SELECT * FROM c0)
    UNION ALL
    (SELECT * FROM c1)
    UNION ALL
    (SELECT * FROM c1)
  )
SELECT * FROM c1;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Project (#0{n})
          ReadStorage materialize.public.init
        Filter (#0{n} < 3)
          Get l2
    cte l1 =
      Project (#1)
        Map ((#0{n} + #0{n}))
          Get l0
    cte l2 =
      Union
        Get l0
        Get l1
        Get l1
  Return
    Project (#1)
      Map ((#0{n} + #0{n}))
        Get l0

Source materialize.public.init

Target cluster: quickstart

EOF

# Regression test for https://github.com/MaterializeInc/database-issues/issues/5691
statement ok
with
  v1 as (
    WITH
            creates AS
            (
                SELECT
                    details ->> 'logical_size' AS size,
                    details ->> 'replica_id' AS replica_id,
                    occurred_at
                FROM mz_catalog.mz_audit_events
                WHERE
                    object_type = 'cluster-replica' AND event_type = 'create'
            )
        SELECT
            mz_unsafe.mz_error_if_null(
                    mz_cluster_replica_sizes.credits_per_hour, 'Replica of unknown size'
                )
                AS credits_per_hour
        FROM
            creates
                LEFT JOIN
                    mz_catalog.mz_cluster_replica_sizes
                    ON mz_cluster_replica_sizes.size = creates.size
  )
select * from v1
WHERE credits_per_hour > credits_per_hour;

# Regression test for https://github.com/MaterializeInc/database-issues/issues/6049

statement ok
CREATE ROLE r2;

statement ok
SELECT
    member.name AS grantee,
    role.name AS role_name
FROM mz_role_members membership
JOIN mz_roles role ON membership.role_id = role.id
JOIN mz_roles member ON membership.member = member.id
WHERE pg_has_role('r2', member.oid, 'USAGE');

query error db error: ERROR: role "r555" does not exist
SELECT
    member.name AS grantee,
    role.name AS role_name
FROM mz_role_members membership
JOIN mz_roles role ON membership.role_id = role.id
JOIN mz_roles member ON membership.member = member.id
WHERE pg_has_role('r555', member.oid, 'USAGE');

query error db error: ERROR: role "aaa" does not exist
select * from (select 'aaa' as aaa) where pg_has_role('r2', aaa, 'USAGE');

# Test when `push_filters_through_map` needs to do several inlining steps on top of each other

statement ok
CREATE TABLE t(x int, y int);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM (
  SELECT x, y, x*y as z1, x*y + 1 AS z2
  FROM t
)
WHERE z2 > 5;
----
Explained Query:
  Filter (#3 > 5)
    Map ((#0{x} * #1{y}), (#2 + 1))
      ReadStorage materialize.public.t

Source materialize.public.t
  filter=((((#0{x} * #1{y}) + 1) > 5))

Target cluster: quickstart

EOF

statement ok
DROP ROLE r2;

# Regression test for https://github.com/MaterializeInc/database-issues/issues/6714
# The transitive closure computation in `inline_if_not_too_big` has to do more than one step here. For this, later map
# expressions should mention only the previous map expression, but not earlier ones. Also, there has to be at least
# 3 map expressions, so that adding the support of the last one doesn't immediately cover all of the map expressions.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM (
  SELECT z2 * 4 AS z3
  FROM (
    SELECT z1 + 1 AS z2
    FROM (
      SELECT x, y, x*y AS z1
      FROM t
    )
  )
)
WHERE z3 > 5;
----
Explained Query:
  Project (#2)
    Filter (#2 > 5)
      Map ((((#0{x} * #1{y}) + 1) * 4))
        ReadStorage materialize.public.t

Source materialize.public.t
  filter=(((((#0{x} * #1{y}) + 1) * 4) > 5))

Target cluster: quickstart

EOF

# Test when `push_filters_through_map` runs into the inlining limit: The source shouldn't have the filter pushed down
# into it.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM (
  SELECT * FROM (
    SELECT *, x + y + z1 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 AS r1
    FROM (
      SELECT x, y, x*y as z1, x*y + 1 AS z2
      FROM t
    )
  )
)
WHERE r1 * r1 * r1 * r1 * r1 * r1 * r1 * r1 * r1 * r1 * r1 * r1 * r1 > 5;
----
Explained Query:
  Filter (((((((((((((#4{r1} * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) * #4{r1}) > 5)
    Map ((#0{x} * #1{y}), (#2{"?column?"} + 1), ((((((((((((((((((((((((((#0{x} + #1{y}) + #2{z1}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}) + #3{z2}))
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

# Similar test as above, but a bit smaller, so we are below the inlining limit. The source should get the pushdown.
# Also adds more predicates to test a bit more scenarios.
# Also tests that MFP CSE happens for the MFP that is in the source.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM (
  SELECT * FROM (
    SELECT *, x + y + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z2 + z1 AS r1
    FROM (
      SELECT x, y, x*y as z1, x*y + 1 AS z2
      FROM t
    )
  )
)
WHERE r1 * r1 * r1 * r1 > 5 AND r1 * x * z2 * r1 < z2 * z1 + y AND x < y AND z2 > z1 AND z1 > z2 + r1 + r1;
----
Explained Query:
  Filter (#0{x} < #1{y}) AND (#3 > #2) AND (#2 > ((#3 + #4) + #4)) AND ((((#4 * #4) * #4) * #4) > 5) AND ((((#4 * #0{x}) * #3) * #4) < ((#3 * #2) + #1{y}))
    Map ((#0{x} * #1{y}), (#2 + 1), ((((((((((((((((((((((((((#0{x} + #1{y}) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #2))
      ReadStorage materialize.public.t

Source materialize.public.t
  filter=((#0{x} < #1{y}) AND (#3 > #2) AND ((((#4 * #0{x}) * #3) * #4) < ((#3 * #2) + #1{y})) AND (#2 > ((#3 + #4) + #4)) AND ((((#4 * #4) * #4) * #4) > 5))
  map=((#0{x} * #1{y}), (#2 + 1), ((((((((((((((((((((((((((#0{x} + #1{y}) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #3) + #2))

Target cluster: quickstart

EOF