Materialize/test/sqllogictest/transform/join_fusion.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.
#
# 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 integer, f2 integer)

statement ok
INSERT INTO t1 VALUES (1, 1), (2, 3), (4, 5);

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

statement ok
INSERT INTO t2 VALUES (2, 3), (5, 5);

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

statement ok
INSERT INTO t3 VALUES (2, 3), (5, 5);

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

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

Target cluster: quickstart

EOF

query IIIIII
SELECT * FROM t1 INNER JOIN t2 ON t2.f2 = t1.f2 INNER JOIN t3 ON t1.f1 = t3.f1 WHERE t1.f1 <= t2.f1 AND t3.f1 > 0;
----
2  3  2  3  2  3

#
# Additional queries that came out of the randomized testing of materialize#6936
#

#
# Randomized queries against a TPC-like schema
#

statement ok
CREATE TABLE customer (c_custkey integer, c_nationkey integer NOT NULL, c_acctbal decimal(15, 2) NOT NULL);

statement ok
CREATE INDEX pk_customer_custkey ON customer (c_custkey);

statement ok
CREATE INDEX fk_customer_nationkey ON customer (c_nationkey ASC);

statement ok
CREATE TABLE orders (o_orderkey integer, o_custkey integer NOT NULL, o_totalprice decimal(15, 2) NOT NULL, o_orderdate DATE NOT NULL);

statement ok
CREATE INDEX pk_orders_orderkey ON orders (o_orderkey);

statement ok
CREATE INDEX fk_orders_custkey ON orders (o_custkey ASC);

statement ok
CREATE TABLE lineitem (l_orderkey integer NOT NULL, l_partkey integer NOT NULL, l_suppkey integer NOT NULL, l_linenumber integer NOT NULL, l_extendedprice decimal(15, 2) NOT NULL, l_shipdate date NOT NULL, l_commitdate date NOT NULL, l_receiptdate date NOT NULL);

statement ok
CREATE INDEX pk_lineitem_orderkey_linenumber ON lineitem (l_orderkey, l_linenumber);

statement ok
CREATE INDEX fk_lineitem_orderkey ON lineitem (l_orderkey ASC);

statement ok
CREATE INDEX fk_lineitem_partkey ON lineitem (l_partkey ASC);

statement ok
CREATE INDEX fk_lineitem_suppkey ON lineitem (l_suppkey ASC);

statement ok
CREATE INDEX fk_lineitem_partsuppkey ON lineitem (l_partkey ASC, l_suppkey ASC);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM lineitem
  JOIN orders ON ( l_orderkey = o_orderkey )
  JOIN customer ON ( c_acctbal >= o_totalprice )
  WHERE l_shipDATE <> o_orderdate
  AND o_orderdate = l_shipDATE - INTERVAL ' 9 MONTHS ';
----
Explained Query:
  Project (#0{l_orderkey}..=#7{l_receiptdate}, #0{l_orderkey}, #9{o_custkey}..=#14{c_acctbal}) // { arity: 15 }
    Filter (#5{l_shipdate} != #11{o_orderdate}) AND (#14{c_acctbal} >= #10{o_totalprice}) // { arity: 15 }
      Join on=(#0{l_orderkey} = #8{o_orderkey} AND date_to_timestamp(#11{o_orderdate}) = (#5{l_shipdate} - 9 months)) type=delta // { arity: 15 }
        implementation
          %0:lineitem » %1:orders[#0{o_orderkey}, date_to_timestamp(#3{o_orderdate})]KK » %2:customer[×]
          %1:orders » %0:lineitem[#0{l_orderkey}, (#5{l_shipdate} - 9 months)]KK » %2:customer[×]
          %2:customer » %0:lineitem[×] » %1:orders[#0{o_orderkey}, date_to_timestamp(#3{o_orderdate})]KK
        ArrangeBy keys=[[], [#0{l_orderkey}, (#5{l_shipdate} - 9 months)]] // { arity: 8 }
          ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 8 }
        ArrangeBy keys=[[#0{o_orderkey}, date_to_timestamp(#3{o_orderdate})]] // { arity: 4 }
          Filter (#0{o_orderkey}) IS NOT NULL // { arity: 4 }
            ReadIndex on=orders pk_orders_orderkey=[*** full scan ***] // { arity: 4 }
        ArrangeBy keys=[[]] // { arity: 3 }
          ReadIndex on=customer pk_customer_custkey=[*** full scan ***] // { arity: 3 }

Used Indexes:
  - materialize.public.pk_customer_custkey (*** full scan ***)
  - materialize.public.pk_orders_orderkey (*** full scan ***)
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT  MIN( o_orderkey  )
  FROM lineitem  JOIN orders  ON ( l_extendedprice  = o_totalprice  )
  WHERE l_commitDATE  = '1997-01-25'
  AND o_orderkey  BETWEEN  38  AND  195
  AND o_orderdate  = l_commitDATE  + ' 7 MONTHS '
  AND o_orderkey  = (  SELECT l_orderkey  FROM lineitem  WHERE l_orderkey  =  38  )
----
Explained Query:
  With
    cte l0 =
      Project (#0{l_orderkey}) // { arity: 1 }
        ReadIndex on=materialize.public.lineitem fk_lineitem_orderkey=[lookup value=(38)] // { arity: 9 }
    cte l1 =
      Reduce aggregates=[min(#0{o_orderkey})] // { arity: 1 }
        Project (#1{o_orderkey}) // { arity: 1 }
          Join on=(#0{l_extendedprice} = #2{o_totalprice} AND #1{o_orderkey} = #3{l_orderkey}) type=delta // { arity: 4 }
            implementation
              %0:lineitem » %1:orders[#1{o_totalprice}]Keiif » %2[#0]K
              %1:orders » %0:lineitem[#0{l_extendedprice}]Kef » %2[#0]K
              %2 » %1:orders[#0{o_orderkey}]KAeiif » %0:lineitem[#0{l_extendedprice}]Kef
            ArrangeBy keys=[[#0{l_extendedprice}]] // { arity: 1 }
              Project (#4{l_extendedprice}) // { arity: 1 }
                Filter (#6{l_commitdate} = 1997-01-25) // { arity: 8 }
                  ReadIndex on=lineitem fk_lineitem_orderkey=[*** full scan ***] // { arity: 8 }
            ArrangeBy keys=[[#0{o_orderkey}], [#1{o_totalprice}]] // { arity: 2 }
              Project (#0{o_orderkey}, #2{o_totalprice}) // { arity: 2 }
                Filter (#0{o_orderkey} <= 195) AND (#0{o_orderkey} >= 38) AND (1997-08-25 00:00:00 = date_to_timestamp(#3{o_orderdate})) // { arity: 4 }
                  ReadIndex on=orders pk_orders_orderkey=[*** full scan ***] // { arity: 4 }
            ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 1 }
              Union // { arity: 1 }
                Get l0 // { arity: 1 }
                Project (#1) // { arity: 1 }
                  Filter (#1 <= 195) AND (#1 >= 38) // { arity: 2 }
                    FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
                      Reduce aggregates=[count(*)] // { arity: 1 }
                        Project () // { arity: 0 }
                          Get l0 // { arity: 1 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l1 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l1 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Used Indexes:
  - materialize.public.pk_orders_orderkey (*** full scan ***)
  - materialize.public.fk_lineitem_orderkey (*** full scan ***, lookup)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT l_partkey AS col24843 , l_orderkey AS col24844 , l_partkey AS col24845
  FROM lineitem JOIN orders ON ( l_commitDATE = o_orderdate )
  JOIN customer ON ( o_custkey = c_custkey )
  WHERE l_extendedprice = o_totalprice
  AND c_custkey = 134
  AND l_extendedprice = MOD (o_totalprice , 5 ) ;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0]] // { arity: 1 }
        Constant // { arity: 1 }
          - (134)
  Return // { arity: 3 }
    Project (#1{l_partkey}, #0{l_orderkey}, #1{l_partkey}) // { arity: 3 }
      Join on=(#2{l_extendedprice} = #4{o_totalprice} AND #3{l_commitdate} = #5{o_orderdate}) type=delta // { arity: 6 }
        implementation
          %0:lineitem » %1:orders[#0{o_totalprice}, #1{o_orderdate}]KKef » %2:customer[×]e
          %1:orders » %0:lineitem[#2{l_extendedprice}, #3{l_commitdate}]KKf » %2:customer[×]e
          %2:customer » %1:orders[×]ef » %0:lineitem[#2{l_extendedprice}, #3{l_commitdate}]KKf
        ArrangeBy keys=[[#2{l_extendedprice}, #3{l_commitdate}]] // { arity: 4 }
          Project (#0{l_orderkey}, #1{l_partkey}, #4{l_extendedprice}, #6{l_commitdate}) // { arity: 4 }
            Filter (#4{l_extendedprice} = (#4{l_extendedprice} % 5)) // { arity: 8 }
              ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 8 }
        ArrangeBy keys=[[], [#0{o_totalprice}, #1{o_orderdate}]] // { arity: 2 }
          Project (#2{o_totalprice}, #3{o_orderdate}) // { arity: 2 }
            Filter (#2{o_totalprice} = (#2{o_totalprice} % 5)) // { arity: 5 }
              ReadIndex on=materialize.public.orders fk_orders_custkey=[lookup values=<Get l0>] // { arity: 5 }
        ArrangeBy keys=[[]] // { arity: 0 }
          Project () // { arity: 0 }
            ReadIndex on=materialize.public.customer pk_customer_custkey=[lookup values=<Get l0>] // { arity: 4 }

Used Indexes:
  - materialize.public.pk_customer_custkey (lookup)
  - materialize.public.fk_orders_custkey (lookup)
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *
  FROM lineitem JOIN orders ON ( l_extendedprice = o_totalprice )
  JOIN customer ON ( o_custkey = c_custkey )
  WHERE o_custkey = 229
  AND l_receiptDATE = o_orderdate + INTERVAL ' 6 DAYS '
  AND l_shipDATE = o_orderdate;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0]] // { arity: 1 }
        Constant // { arity: 1 }
          - (229)
  Return // { arity: 15 }
    Project (#0{l_orderkey}..=#9{o_custkey}, #4{l_extendedprice}, #5{l_shipdate}, #12{c_custkey}..=#14{c_acctbal}) // { arity: 15 }
      Join on=(#4{l_extendedprice} = #10{o_totalprice} AND #5{l_shipdate} = #11{o_orderdate}) type=delta // { arity: 15 }
        implementation
          %0:lineitem » %1:orders[#2{o_totalprice}, #3{o_orderdate}]KKe » %2:customer[×]e
          %1:orders » %0:lineitem[#4{l_extendedprice}, #5{l_shipdate}]KKf » %2:customer[×]e
          %2:customer » %1:orders[×]e » %0:lineitem[#4{l_extendedprice}, #5{l_shipdate}]KKf
        ArrangeBy keys=[[#4{l_extendedprice}, #5{l_shipdate}]] // { arity: 8 }
          Filter (date_to_timestamp(#7{l_receiptdate}) = (#5{l_shipdate} + 6 days)) // { arity: 8 }
            ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 8 }
        ArrangeBy keys=[[], [#2{o_totalprice}, #3{o_orderdate}]] // { arity: 4 }
          Project (#0{o_orderkey}..=#3{o_orderdate}) // { arity: 4 }
            ReadIndex on=materialize.public.orders fk_orders_custkey=[lookup values=<Get l0>] // { arity: 5 }
        ArrangeBy keys=[[]] // { arity: 3 }
          Project (#0{c_custkey}..=#2{c_acctbal}) // { arity: 3 }
            ReadIndex on=materialize.public.customer pk_customer_custkey=[lookup values=<Get l0>] // { arity: 4 }

Used Indexes:
  - materialize.public.pk_customer_custkey (lookup)
  - materialize.public.fk_orders_custkey (lookup)
  - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***)

Target cluster: quickstart

EOF

#
# Randomized queries against the "simple" schema
#

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

Target cluster: quickstart

EOF