# 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. statement ok CREATE TABLE t ( a int, b int ) statement ok CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 statement ok CREATE VIEW v AS SELECT * FROM t WHERE a IS NOT NULL statement ok CREATE INDEX v_a_idx ON v(a) statement ok CREATE MATERIALIZED VIEW mv AS SELECT * FROM t WHERE a IS NOT NULL mode cockroach # EXPLAIN INDEX is not supported for decorrelated plans statement error cannot EXPLAIN DECORRELATED PLAN FOR INDEX EXPLAIN DECORRELATED PLAN AS TEXT FOR INDEX v_a_idx # EXPLAIN CREATE INDEX is not supported for decorrelated plans statement error cannot EXPLAIN DECORRELATED PLAN FOR CREATE INDEX EXPLAIN DECORRELATED PLAN AS TEXT FOR CREATE INDEX v_a_idx ON v(a) # Test constant error. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT 1 / 0 ---- Project (#0) Map ((1 / 0)) Constant - () Target cluster: quickstart EOF # Test constant with two elements. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR (SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4) ---- Union Project (#0, #1) Map (1, 2) Constant - () Project (#0, #1) Map (1, 2) Constant - () Project (#0, #1) Map (3, 4) Constant - () Target cluster: mz_catalog_server EOF # Test basic linear chains. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0 ---- Project (#2, #3) Map (1, (#0{a} + #1{b})) Filter (((#0{a} > 0) AND (#1{b} < 0)) AND ((#0{a} + #1{b}) > 0)) CrossJoin Constant - () Get materialize.public.mv Target cluster: quickstart EOF # Test table functions in the select clause (FlatMap). query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT generate_series(a, b) from t ---- Project (#2) FlatMap generate_series(#0{a}, #1{b}, 1) CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT a FROM t EXCEPT SELECT b FROM mv ---- Threshold Union Distinct project=[#0] Project (#0) CrossJoin Constant - () Get materialize.public.t Negate Distinct project=[#0] Project (#1) CrossJoin Constant - () Get materialize.public.mv Target cluster: quickstart EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT a FROM t EXCEPT ALL SELECT b FROM mv ---- Threshold Union Project (#0) CrossJoin Constant - () Get materialize.public.t Negate Project (#1) CrossJoin Constant - () Get materialize.public.mv Target cluster: quickstart EOF # Test Finish. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 ---- Finish order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 output=[#0, #1] CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test Reduce (global). query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT abs(min(a) - max(a)) FROM t ---- With cte l0 = Reduce aggregates=[min(#0{a}), max(#0{a})] CrossJoin Constant - () Get materialize.public.t Return Project (#2) Map (abs((#0{"?column?"} - #1{"?column?"}))) Union Get l0 CrossJoin Project () CrossJoin Union Negate Distinct project=[] Get l0 Distinct project=[] Constant - () Constant - () Constant - (null, null) Target cluster: quickstart EOF # Test Reduce (local). query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT abs(min(a) - max(a)) FROM t GROUP BY b ---- Project (#3) Map (abs((#1{"?column?"} - #2{"?column?"}))) Reduce group_by=[#2] aggregates=[min(#0{a}), max(#0{a})] Project (#0..=#2) Map (#1{b}) CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test EXISTS subqueries. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT * FROM t WHERE EXISTS(SELECT * FROM mv WHERE t.a < mv.a) AND EXISTS(SELECT * FROM mv WHERE t.b > mv.b) ---- With cte l0 = Filter (true AND true) CrossJoin Constant - () Get materialize.public.t cte l1 = Distinct project=[#0] Get l0 cte l2 = Map (true) Distinct project=[#0] Filter (#0{a} < #1{a}) CrossJoin Get l1 Get materialize.public.mv cte l3 = Project (#0, #1) Filter #2 Project (#0, #1, #3) Join on=(#0 = #2) Get l0 Union Get l2 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l2 Distinct project=[#0] Get l1 Get l1 Constant - (false) cte l4 = Distinct project=[#1] Get l3 cte l5 = Map (true) Distinct project=[#0] Filter (#0{b} > #2{b}) CrossJoin Get l4 Get materialize.public.mv Return Project (#0, #1) Filter #2 Project (#0, #1, #3) Join on=(#1 = #2) Get l3 Union Get l5 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l5 Distinct project=[#0] Get l4 Get l4 Constant - (false) Target cluster: quickstart EOF # Test SELECT subqueries. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT (SELECT v.a FROM v WHERE v.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t ---- With cte l0 = CrossJoin Constant - () Get materialize.public.t cte l1 = Distinct project=[#0, #1] Get l0 cte l2 = Distinct project=[#1] Get l1 cte l3 = Project (#0, #1) TopK group_by=[#0] limit=1 Filter (#2{b} = #0{b}) CrossJoin Get l2 Get materialize.public.v cte l4 = Union Get l3 Project (#0, #2) FlatMap guard_subquery_size(#1) Reduce group_by=[#0] aggregates=[count(*)] Get l3 cte l5 = Distinct project=[#0, #1] Get l0 cte l6 = Distinct project=[#1] Get l5 cte l7 = Project (#0, #1) TopK group_by=[#0] limit=1 Filter (#2{b} = #0{b}) CrossJoin Get l6 Get materialize.public.mv cte l8 = Union Get l7 Project (#0, #2) FlatMap guard_subquery_size(#1) Reduce group_by=[#0] aggregates=[count(*)] Get l7 Return Project (#8, #9) Map (#4, #7) Join on=(#0 = #2 = #5 AND #1 = #3 = #6) Get l0 Project (#0, #1, #3) Join on=(#1 = #2) Get l1 Union Get l4 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l4 Distinct project=[#0] Get l2 Get l2 Constant - (null) Project (#0, #1, #3) Join on=(#1 = #2) Get l5 Union Get l8 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l8 Distinct project=[#0] Get l6 Get l6 Constant - (null) Target cluster: quickstart EOF # Test CrossJoin derived from a comma join without a predicate. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT t1.a, t2.a FROM t as t1, t as t2 ---- Project (#0, #2) Project (#0..=#3) CrossJoin CrossJoin Constant - () Get materialize.public.t CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test CrossJoin derived from an INNER JOIN with a trivial ON clause. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT t1.a, t2.a FROM t as t1 INNER JOIN t as t2 ON true ---- Project (#0, #2) Project (#0..=#3) CrossJoin CrossJoin Constant - () Get materialize.public.t CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test InnerJoin (comma syntax). query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT t1.a, t2.a FROM t as t1, t as t2, t as t3 WHERE t1.b = t2.b AND t2.b = t3.b ---- Project (#0, #2) Filter ((#1{b} = #3{b}) AND (#3{b} = #5{b})) Project (#0..=#5) CrossJoin Project (#0..=#3) CrossJoin CrossJoin Constant - () Get materialize.public.t CrossJoin Constant - () Get materialize.public.t CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test InnerJoin (ON syntax). query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT t1.a, t2.a FROM t as t1 INNER JOIN t as t2 ON t1.b = t2.b INNER JOIN t as t3 ON t2.b = t3.b ---- Project (#0, #2) Filter (#3{b} = #5{b}) Project (#0..=#5) CrossJoin Filter (#1{b} = #3{b}) Project (#0..=#3) CrossJoin CrossJoin Constant - () Get materialize.public.t CrossJoin Constant - () Get materialize.public.t CrossJoin Constant - () Get materialize.public.t Target cluster: quickstart EOF # Test LEFT JOIN and RIGHT JOIN. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT t1.a, t2.a FROM t as t1 LEFT JOIN t as t2 ON t1.b = t2.b RIGHT JOIN t as t3 ON t2.b = t3.b ---- With cte l0 = CrossJoin Constant - () Get materialize.public.t cte l1 = Filter (#1{b} = #3{b}) Project (#0..=#3) CrossJoin Get l0 CrossJoin Constant - () Get materialize.public.t cte l2 = CrossJoin Constant - () Get materialize.public.t cte l3 = Filter (#3{b} = #5{b}) Project (#0..=#5) CrossJoin Union Map (null, null) Union Negate Project (#0, #1) Join on=(#1{b} = #2) Filter (#1{b}) IS NOT NULL Get l0 Distinct project=[#0] Project (#1) Get l1 Get l0 Get l1 Get l2 Return Project (#0, #2) Union Project (#2..=#5, #0, #1) Map (null, null, null, null) Union Negate Project (#0, #1) Join on=(#1{b} = #2) Filter (#1{b}) IS NOT NULL Get l2 Distinct project=[#0] Project (#3) Get l3 Get l2 Get l3 Target cluster: quickstart EOF # Test a single CTE. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR WITH x AS (SELECT t.a * t.b as v from t) SELECT a.v + b.v FROM x as a, x as b ---- With cte l0 = Project (#2) Map ((#0{a} * #1{b})) CrossJoin Constant - () Get materialize.public.t Return Project (#2) Project (#0..=#2) Map ((#0{v} + #1{v})) Project (#0, #1) CrossJoin Get l0 Get l0 Target cluster: quickstart EOF # Test multiple CTEs: a case where we cannot pull the let statement up through # the join because the local l0 is correlated against the lhs of the enclosing join. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT * FROM ( SELECT * FROM t ) as r1 CROSS JOIN LATERAL ( WITH r2 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r2 WHERE r2.m != r1.a ) as r3 CROSS JOIN LATERAL ( WITH r4 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r4 WHERE r4.m != r1.a OR (r4.m IS NOT NULL AND r1.a IS NULL) ) as r5; ---- With cte l0 = CrossJoin Constant - () Get materialize.public.t cte l1 = Distinct project=[#0] Get l0 cte l2 = Reduce group_by=[#0] aggregates=[max((#0{a} * #1{a}))] CrossJoin Get l1 Get materialize.public.t cte l3 = Project (#0, #1, #3) Join on=(#0 = #2) Get l0 Filter (#1{m} != #0{a}) Union Get l2 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l2 Distinct project=[#0] Get l1 Get l1 Constant - (null) cte l4 = Distinct project=[#0] Get l3 cte l5 = Reduce group_by=[#0] aggregates=[max((#0{a} * #1{a}))] CrossJoin Get l4 Get materialize.public.t Return Project (#0..=#2, #4) Join on=(#0 = #3) Get l3 Filter ((#1{m} != #0{a}) OR ((#1{m}) IS NOT NULL AND (#0{a}) IS NULL)) Union Get l5 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l5 Distinct project=[#0] Get l4 Get l4 Constant - (null) Target cluster: quickstart EOF # Test multiple CTEs: a case where we cannot pull the let statement up # through the join because the local l0 is correlated against the lhs of # the enclosing join. query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT * FROM ( SELECT * FROM t ) as r1 CROSS JOIN LATERAL ( WITH r4 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r4 CROSS JOIN LATERAL ( WITH r2 as ( SELECT MAX(r1.a * t.a) AS m FROM t ) SELECT * FROM r2 WHERE r1.a = r4.m AND r2.m > 5 ) as r3 WHERE a != r1.a ) as r5; ---- With cte l0 = CrossJoin Constant - () Get materialize.public.t cte l1 = Distinct project=[#0] Get l0 cte l2 = Reduce group_by=[#0] aggregates=[max((#0{a} * #1{a}))] CrossJoin Get l1 Get materialize.public.t cte l3 = Union Get l2 CrossJoin Project (#0) Join on=(#0 = #1) Union Negate Distinct project=[#0] Get l2 Distinct project=[#0] Get l1 Get l1 Constant - (null) cte l4 = Distinct project=[#1, #0] Get l3 cte l5 = Reduce group_by=[#0, #1] aggregates=[max((#1{a} * #2{a}))] CrossJoin Get l4 Get materialize.public.t Return Project (#0, #1, #3, #4) Join on=(#0 = #2) Get l0 Filter (#0{a} != #0{a}) Project (#0, #1, #4) Join on=(#1 = #2 AND #0 = #3) Get l3 Filter ((#1{a} = #0{m}) AND (#2{m} > 5)) Union Get l5 CrossJoin Project (#0, #1) Join on=(#0 = #2 AND #1 = #3) Union Negate Distinct project=[#0, #1] Get l5 Distinct project=[#0, #1] Get l4 Get l4 Constant - (null) Target cluster: quickstart EOF query T multiline EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT COUNT(*); ---- With cte l0 = Reduce aggregates=[count(*)] Constant - () Return Union Get l0 CrossJoin Project () CrossJoin Union Negate Distinct project=[] Get l0 Distinct project=[] Constant - () Constant - () Constant - (0) Target cluster: quickstart EOF # Test materialize#17348. statement ok CREATE TABLE r(f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT, f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT); query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM r AS r0, r AS r1 WHERE r0.f0=r1.f0 AND r0.f2=r1.f2 AND r0.f3=r1.f3 AND r0.f4=r1.f4 AND r0.f6=r1.f6 AND r0.f8=r1.f8 AND r0.f9=r1.f9 AND r0.f11=r1.f11 AND r0.f12=r1.f12 AND r0.f13=r1.f13 AND r0.f15=r1.f15 AND r0.f16=r1.f16; ---- Explained Query: With cte l0 = ArrangeBy keys=[[#0{f0}, #2{f2}..=#4{f4}, #6{f6}, #8{f8}, #9{f9}, #11{f11}..=#13{f13}, #15{f15}, #16{f16}]] Filter (#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL ReadStorage materialize.public.r Return Project (#0{f0}..=#16{f16}, #0{f0}, #18{f1}, #2{f2}..=#4{f4}, #22{f5}, #6{f6}, #24{f7}, #8{f8}, #9{f9}, #27{f10}, #11{f11}..=#13{f13}, #31{f14}, #15{f15}, #16{f16}) Join on=(#0{f0} = #17{f0} AND #2{f2} = #19{f2} AND #3{f3} = #20{f3} AND #4{f4} = #21{f4} AND #6{f6} = #23{f6} AND #8{f8} = #25{f8} AND #9{f9} = #26{f9} AND #11{f11} = #28{f11} AND #12{f12} = #29{f12} AND #13{f13} = #30{f13} AND #15{f15} = #32{f15} AND #16{f16} = #33{f16}) type=differential Get l0 Get l0 Source materialize.public.r filter=((#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL) Target cluster: quickstart EOF