Materialize/test/sqllogictest/transform/scalar_cse.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 Scalar CSE transforms.

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

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

Source materialize.public.x

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT b*b*b, b*b+1 FROM x
----
Explained Query:
  Project (#3, #4) // { arity: 2 }
    Map ((#1{b} * #1{b}), (#2 * #1{b}), (#2 + 1)) // { arity: 5 }
      ReadStorage materialize.public.x // { arity: 2 }

Source materialize.public.x

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
    a::json->'Field1',
    a::json->'Field2',
    a::json->'Field3'
FROM x
----
Explained Query:
  Project (#3..=#5) // { arity: 3 }
    Map (text_to_jsonb(#0{a}), (#2 -> "Field1"), (#2 -> "Field2"), (#2 -> "Field3")) // { arity: 6 }
      ReadStorage materialize.public.x // { arity: 2 }

Source materialize.public.x

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
    a::json->'Field1'->'Foo',
    a::json->'Field1'->'Bar',
    a::json->'Field2'->'Baz',
    a::json->'Field2'->'Quux'->'Zorb'
FROM x
----
Explained Query:
  Project (#4, #5, #7, #8) // { arity: 4 }
    Map (text_to_jsonb(#0{a}), (#2 -> "Field1"), (#3 -> "Foo"), (#3 -> "Bar"), (#2 -> "Field2"), (#6 -> "Baz"), ((#6 -> "Quux") -> "Zorb")) // { arity: 9 }
      ReadStorage materialize.public.x // { arity: 2 }

Source materialize.public.x

Target cluster: quickstart

EOF


# Ensure we don't inline if-guarded expressions
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
    CASE WHEN b = 0 THEN 0 ELSE 1/b END,
    CASE WHEN b != 0 THEN 1/b ELSE 0 END
FROM x
----
Explained Query:
  Project (#2, #3) // { arity: 2 }
    Map (case when (#1{b} = 0) then 0 else (1 / #1{b}) end, case when (#1{b} != 0) then (1 / #1{b}) else 0 end) // { arity: 4 }
      ReadStorage materialize.public.x // { arity: 2 }

Source materialize.public.x

Target cluster: quickstart

EOF



# Ensure we update columns references in if-guarded expressions
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
    CASE WHEN b = 0 THEN 0 ELSE 1/b END,
    CASE WHEN b != 0 THEN 1/b ELSE 0 END
FROM
    (SELECT b/2 as b FROM x)

----
Explained Query:
  Project (#3, #4) // { arity: 2 }
    Map ((#1{b} / 2), case when (#2{b} = 0) then 0 else (1 / #2) end, case when (#2{b} != 0) then (1 / #2) else 0 end) // { arity: 5 }
      ReadStorage materialize.public.x // { arity: 2 }

Source materialize.public.x

Target cluster: quickstart

EOF

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

statement ok
CREATE TABLE t(x int);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from t where 2*x + 2*x > 0
----
Explained Query:
  Project (#0{x})
    Filter ((#1 + #1) > 0)
      Map ((2 * #0{x}))
        ReadStorage materialize.public.t

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from t where 2*x + 2*x + 2*x > 0
----
Explained Query:
  Project (#0{x})
    Filter (((#1 + #1) + #1) > 0)
      Map ((2 * #0{x}))
        ReadStorage materialize.public.t

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from t where 2*x + 5*x + 2*x > 0
----
Explained Query:
  Project (#0{x})
    Filter (((#1 + (5 * #0{x})) + #1) > 0)
      Map ((2 * #0{x}))
        ReadStorage materialize.public.t

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

Target cluster: quickstart

EOF