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

mode cockroach

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

statement ok
CREATE TABLE t2 (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 ( SELECT FROM t2 a1 RIGHT JOIN t2 ON a1.f1 IS NULL WHERE TRUE AND a1.f1 = a1.f2 )
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

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

Source materialize.public.t1
  filter=((#0{f1}) IS NOT NULL)
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 WHERE f2 IN ( SELECT agg1 FROM ( SELECT COUNT ( TRUE ) agg1 FROM t2 a1 JOIN ( SELECT a2.f2 FROM t1 LEFT JOIN t1 a2 ON TRUE ) a2 ON TRUE WHERE  a2.f2 IS NOT NULL AND a2.f2 > a1.f2 ) )
----
Explained Query:
  With
    cte l0 =
      Project (#1{f2}) // { arity: 1 }
        ReadStorage materialize.public.t1 // { arity: 2 }
    cte l1 =
      Distinct project=[#0{f2}] // { arity: 1 }
        Get l0 // { arity: 1 }
    cte l2 =
      Reduce group_by=[#0{f2}] aggregates=[count(*)] // { arity: 2 }
        Project (#0{f2}) // { arity: 1 }
          Filter (#2{f2} > #1{f2}) // { arity: 3 }
            CrossJoin type=delta // { arity: 3 }
              implementation
                %0:l1 » %1:t2[×] » %2:t1[×] » %3:l0[×]
                %1:t2 » %0:l1[×] » %2:t1[×] » %3:l0[×]
                %2:t1 » %0:l1[×] » %1:t2[×] » %3:l0[×]
                %3:l0 » %0:l1[×] » %1:t2[×] » %2:t1[×]
              ArrangeBy keys=[[]] // { arity: 1 }
                Get l1 // { arity: 1 }
              ArrangeBy keys=[[]] // { arity: 1 }
                Project (#1{f2}) // { arity: 1 }
                  ReadStorage materialize.public.t2 // { arity: 2 }
              ArrangeBy keys=[[]] // { arity: 0 }
                Project () // { arity: 0 }
                  ReadStorage materialize.public.t1 // { arity: 2 }
              ArrangeBy keys=[[]] // { arity: 1 }
                Get l0 // { arity: 1 }
  Return // { arity: 0 }
    Project () // { arity: 0 }
      Join on=(#0{f2} = #1{f2}) type=differential // { arity: 2 }
        implementation
          %1[#0]UKA » %0:l0[#0]K
        ArrangeBy keys=[[#0{f2}]] // { arity: 1 }
          Get l0 // { arity: 1 }
        ArrangeBy keys=[[#0{f2}]] // { arity: 1 }
          Distinct project=[#0{f2}] // { arity: 1 }
            Union // { arity: 1 }
              Project (#0{f2}) // { arity: 1 }
                Filter (#0{f2} = bigint_to_double(#1{count})) // { arity: 2 }
                  Get l2 // { arity: 2 }
              Negate // { arity: 1 }
                Project (#0{f2}) // { arity: 1 }
                  Filter (#0{f2} = 0) // { arity: 2 }
                    Get l2 // { arity: 2 }
              Filter (#0{f2} = 0) // { arity: 1 }
                Get l1 // { arity: 1 }

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

Target cluster: quickstart

EOF