Materialize/test/sqllogictest/transform/predicate_reduction.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 simplification of predicates.

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

statement ok
INSERT INTO t1 VALUES (null, null), (0, null), (1, null), (1, 0), (null, 0)

# We can simplify predicates with overlapping predicate subexpressions.
query II
SELECT * FROM t1 WHERE f1 = 0 and (f1 = 0 or f1 = 1)
----
0
NULL

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 = 0 and (f1 = 0 or f1 = 1)
----
Explained Query:
  Filter (#0{f1} = 0) // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 is null and (f1 is null or f1 = 1)
----
Explained Query:
  Filter (#0{f1}) IS NULL // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }

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

Target cluster: quickstart

EOF

mode cockroach

query II rowsort
SELECT * FROM t1 WHERE f1 is null and (f1 is null or f1 = 1)
----
NULL NULL
NULL 0

mode standard

# Test that subexpression matching can detect a `!(predicate)` and then replace
# other instances of `predicate` with `false`.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE f1 is not null and (f1 is null or f1 = 1)
----
Explained Query:
  Filter (#0{f1} = 1) // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1
  filter=((#0{f1} = 1))

Target cluster: quickstart

EOF

mode cockroach

query II rowsort
SELECT * FROM t1 WHERE f1 is not null and (f1 is null or f1 = 1)
----
1 NULL
1 0

mode standard

# A test that simplification works when overlapping subexpressions are nested.

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR SELECT * FROM t1 WHERE (f1 is null)::int - 1 = 0 and ((f1 is null) or ((f1 is null)::int - 1 = 0))
----
Filter (((boolean_to_integer((#0{f1}) IS NULL) - 1) = 0) AND ((#0{f1}) IS NULL OR ((boolean_to_integer((#0{f1}) IS NULL) - 1) = 0))) // { arity: 2 }
  CrossJoin // { arity: 2 }
    Constant // { arity: 0 }
      - ()
    Get materialize.public.t1 // { arity: 2 }

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE ((f1 is null)::int - 1)::string LIKE '1' and ((f1 is null) or not (((f1 is null)::int - 1)::string LIKE '1'))
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

# Ensure that subexpression matching does not break predicate evaluation order
# guarantees for `CASE`

statement ok
CREATE TABLE t2(f1 int not null, f2 int not null)

statement ok
INSERT INTO t2 VALUES (0, -1), (1, 5), (1, -2)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t2 WHERE f1 + f2 > 0 and case when f1 + f2 > 0 then 1/f1 > 0 else false end;
----
Explained Query:
  Project (#0{f1}, #1{f2}) // { arity: 2 }
    Filter #2 AND case when #2 then ((1 / #0{f1}) > 0) else false end // { arity: 3 }
      Map (((#0{f1} + #1{f2}) > 0)) // { arity: 3 }
        ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t2
  filter=(#2 AND case when #2 then ((1 / #0{f1}) > 0) else false end)
  map=(((#0{f1} + #1{f2}) > 0))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t2 WHERE case when f1 + f2 > 0 then 1/f1 > 0 else false end and f1 + f2 > 0;
----
Explained Query:
  Project (#0{f1}, #1{f2}) // { arity: 2 }
    Filter #2 AND case when #2 then ((1 / #0{f1}) > 0) else false end // { arity: 3 }
      Map (((#0{f1} + #1{f2}) > 0)) // { arity: 3 }
        ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t2
  filter=(#2 AND case when #2 then ((1 / #0{f1}) > 0) else false end)
  map=(((#0{f1} + #1{f2}) > 0))

Target cluster: quickstart

EOF

query II
SELECT * FROM t2 WHERE f1 + f2 > 0 and case when f1 + f2 > 0 then 1/f1 > 0 else false end;
----
1
5