# 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=] // { arity: 5 } ArrangeBy keys=[[]] // { arity: 0 } Project () // { arity: 0 } ReadIndex on=materialize.public.customer pk_customer_custkey=[lookup values=] // { 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=] // { 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=] // { 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 Target cluster: quickstart EOF