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

#
# Test Common subexpression elimination for Relations.
# PR https://github.com/MaterializeInc/materialize/pull/7715
#

statement ok
CREATE SOURCE counter FROM LOAD GENERATOR COUNTER;

# Properly TopK
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (SELECT counter FROM counter limit 1);
----
Explained Query:
  TopK limit=1 monotonic
    ReadStorage materialize.public.counter

Source materialize.public.counter

Target cluster: quickstart

EOF

# Infer monotonic Reduce operator
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT counter % 3, MAX(counter) as sum FROM counter GROUP BY counter % 3;
----
Explained Query:
  Reduce group_by=[(#0{counter} % 3)] aggregates=[max(#0{counter})] monotonic
    ReadStorage materialize.public.counter

Source materialize.public.counter

Target cluster: quickstart

EOF

# _No_ propagation of monotonicity through materialized views

statement ok
CREATE MATERIALIZED VIEW v1 AS SELECT DISTINCT counter % 3 as f1 FROM counter GROUP BY counter % 3;

statement ok
CREATE MATERIALIZED VIEW v2 AS SELECT f1 as counter FROM v1 WHERE f1 % 7 = 0;

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM v1 CROSS JOIN LATERAL (SELECT * FROM v2 WHERE counter < f1 ORDER BY counter DESC LIMIT 3);
----
Explained Query:
  TopK group_by=[#0{f1}] order_by=[#1{counter} desc nulls_first] limit=3
    Filter (#1{counter} < #0{f1})
      CrossJoin type=differential
        ArrangeBy keys=[[]]
          ReadStorage materialize.public.v1
        ArrangeBy keys=[[]]
          ReadStorage materialize.public.v2

Source materialize.public.v1
Source materialize.public.v2

Target cluster: quickstart

EOF

# Propagating monotonicity analysis thorugh recursive queries

# Positive example: both c1 and c2 and consequently the body of the
# WMR are monotonic.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(x INT, y INT) AS (
    SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6))
  ),
  c1(x INT, y INT) AS (
    SELECT * FROM c0
    UNION ALL
    SELECT DISTINCT y, y-1 FROM c2 WHERE x < 1
  ),
  c2(x INT, y INT) AS (
    SELECT * FROM c0
    UNION ALL
    SELECT DISTINCT x, x+1 FROM c1 WHERE x >= 1
  )
SELECT x, MAX(y) FROM (SELECT * FROM c1 UNION SELECT * FROM c2) GROUP BY x
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Distinct project=[#0, (#0{y} - 1)] monotonic
          Project (#1)
            Filter (#0{x} < 1)
              Get l1
        Constant
          - (1, 2)
          - (3, 4)
          - (5, 6)
    cte l1 =
      Union
        Distinct project=[#0, (#0{x} + 1)] monotonic
          Project (#0)
            Filter (#0{x} >= 1)
              Get l0
        Constant
          - (1, 2)
          - (3, 4)
          - (5, 6)
  Return
    Reduce group_by=[#0] aggregates=[max(#1{y})] monotonic
      Distinct project=[#0, #1] monotonic
        Union
          Get l0
          Get l1

Target cluster: quickstart

EOF

# Negative example: c2 (and consequently c2 and the WMR block body) are not
# monotonic because c2 has a LIMIT clause.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(x INT, y INT) AS (
    SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6))
  ),
  c1(x INT, y INT) AS (
    SELECT * FROM c0
    UNION ALL
    SELECT DISTINCT y, y-1 FROM c2 WHERE x < 1
  ),
  c2(x INT, y INT) AS (
    SELECT * FROM c0
    UNION ALL
    SELECT DISTINCT x, x+1 FROM c1 WHERE x >= 1 LIMIT 2
  )
SELECT x, MAX(y) FROM (SELECT * FROM c1 UNION SELECT * FROM c2) GROUP BY x
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Union
        Distinct project=[#0, (#0{y} - 1)]
          Project (#1)
            Filter (#0{x} < 1)
              Get l1
        Constant
          - (1, 2)
          - (3, 4)
          - (5, 6)
    cte l1 =
      TopK limit=2
        Union
          Distinct project=[#0, (#0{x} + 1)]
            Project (#0)
              Filter (#0{x} >= 1)
                Get l0
          Constant
            - (1, 2)
            - (3, 4)
            - (5, 6)
  Return
    Reduce group_by=[#0] aggregates=[max(#1{y})]
      Distinct project=[#0, #1]
        Union
          Get l0
          Get l1

Target cluster: quickstart

EOF