# 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. simple conn=mz_system,user=mz_system ALTER SYSTEM SET unsafe_enable_table_keys = true ---- COMPLETE 0 mode cockroach statement ok CREATE TABLE t1 (key integer PRIMARY KEY, nokey integer) statement ok INSERT INTO t1 VALUES (1, 1), (2, 3), (4, 5); statement ok CREATE TABLE t2 (key integer PRIMARY KEY, nokey 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 (4, 5), (5, 5), (5, 5), (null, null) query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 ---- Explained Query: ReadStorage materialize.public.t1 // { arity: 2 } Source materialize.public.t1 Target cluster: quickstart EOF query II SELECT * FROM t1 ---- 1 1 2 3 4 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a1.* FROM t1 AS a1 LEFT JOIN t1 AS a2 ON (a1.key = a2.key) ---- Explained Query: ReadStorage materialize.public.t1 // { arity: 2 } Source materialize.public.t1 Target cluster: quickstart EOF query II SELECT a1.* FROM t1 AS a1 LEFT JOIN t1 AS a2 ON (a1.key = a2.key) ---- 1 1 2 3 4 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 AS a1 LEFT JOIN t1 AS a2 ON (a1.key = a2.key) WHERE a1.nokey = 1 ---- Explained Query: Project (#0{key}, #1{nokey}, #0{key}, #1{nokey}) // { arity: 4 } Filter (#1{nokey} = 1) // { arity: 2 } ReadStorage materialize.public.t1 // { arity: 2 } Source materialize.public.t1 filter=((#1{nokey} = 1)) Target cluster: quickstart EOF query IIII SELECT * FROM t1 AS a1 LEFT JOIN t1 AS a2 ON (a1.key = a2.key) WHERE a1.nokey = 1 ---- 1 1 1 1 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 ---- Explained Query (fast path): Constant Target cluster: quickstart EOF query II SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 ---- query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 UNION ALL SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 ---- Explained Query: ReadStorage materialize.public.t1 // { arity: 2 } Source materialize.public.t1 Target cluster: quickstart EOF query II SELECT * FROM t1 UNION ALL SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 ---- 1 1 2 3 4 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 UNION ALL SELECT * FROM t1 ---- Explained Query: ReadStorage materialize.public.t1 // { arity: 2 } Source materialize.public.t1 Target cluster: quickstart EOF query II SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 UNION ALL SELECT * FROM t1 ---- 1 1 2 3 4 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 UNION ALL SELECT * FROM t2 EXCEPT ALL SELECT * FROM t1 ---- Explained Query: ReadStorage materialize.public.t2 // { arity: 2 } Source materialize.public.t2 Target cluster: quickstart EOF query II SELECT * FROM t1 UNION ALL SELECT * FROM t2 EXCEPT ALL SELECT * FROM t1 ---- 2 3 5 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t2 UNION ALL SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 ---- Explained Query: ReadStorage materialize.public.t2 // { arity: 2 } Source materialize.public.t2 Target cluster: quickstart EOF query II SELECT * FROM t2 UNION ALL SELECT * FROM t1 EXCEPT ALL SELECT * FROM t1 ---- 2 3 5 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t2 EXCEPT ALL SELECT * FROM t1 UNION ALL SELECT * FROM t1 ---- Explained Query: Union // { arity: 2 } Threshold // { arity: 2 } Union // { arity: 2 } ReadStorage materialize.public.t2 // { arity: 2 } Negate // { arity: 2 } ReadStorage materialize.public.t1 // { arity: 2 } ReadStorage materialize.public.t1 // { arity: 2 } Source materialize.public.t1 Source materialize.public.t2 Target cluster: quickstart EOF query II SELECT * FROM t2 EXCEPT ALL SELECT * FROM t1 UNION ALL SELECT * FROM t1 ---- 1 1 2 3 4 5 5 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR WITH t3_with_key AS (select f1 as key, sum(f2) as nokey from t3 group by f1) SELECT a1.* FROM t3_with_key AS a1 LEFT JOIN t3_with_key AS a2 ON (a1.key = a2.key) ---- Explained Query: Reduce group_by=[#0{f1}] aggregates=[sum(#1{f2})] // { arity: 2 } ReadStorage materialize.public.t3 // { arity: 2 } Source materialize.public.t3 Target cluster: quickstart EOF query II rowsort WITH t3_with_key AS (select f1 as key, sum(f2) as nokey from t3 group by f1) SELECT a1.* FROM t3_with_key AS a1 LEFT JOIN t3_with_key AS a2 ON (a1.key = a2.key) ---- NULL NULL 4 5 5 10 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR WITH t3_with_key AS (select f1 as key, sum(f2) as nokey from t3 group by f1) SELECT a1.* FROM t3 AS a1 LEFT JOIN t3_with_key AS a2 ON (a1.f1 = a2.key); ---- Explained Query: ReadStorage materialize.public.t3 // { arity: 2 } Source materialize.public.t3 Target cluster: quickstart EOF query II rowsort WITH t3_with_key AS (select f1 as key, sum(f2) as nokey from t3 group by f1) SELECT a1.* FROM t3 AS a1 LEFT JOIN t3_with_key AS a2 ON (a1.f1 = a2.key) ---- NULL NULL 4 5 5 5 5 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR WITH t3_with_key AS (select f1 as key, sum(f2) as nokey from t3 group by f1) SELECT a1.* FROM t3 AS a1 LEFT JOIN t3_with_key AS a2 ON (a1.f1 = a2.key or (a1.f1 is null and a2.key is null)); ---- Explained Query: ReadStorage materialize.public.t3 // { arity: 2 } Source materialize.public.t3 Target cluster: quickstart EOF query II rowsort WITH t3_with_key AS (select f1 as key, sum(f2) as nokey from t3 group by f1) SELECT a1.* FROM t3 AS a1 LEFT JOIN t3_with_key AS a2 ON (a1.f1 = a2.key or (a1.f1 is null and a2.key is null)); ---- NULL NULL 4 5 5 5 5 5 statement ok CREATE TABLE init(n int, m int, s string); # Union branch cancellation should happen inside WMR. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR WITH MUTUALLY RECURSIVE c0(n int) AS ( (SELECT n FROM init) UNION ALL (SELECT * FROM c2) ), c1(n int) AS ( (SELECT n+n FROM c0) UNION ALL ((SELECT n+3 FROM c0) EXCEPT ALL (SELECT n+3 FROM c0)) ), c2(n int) AS ( (SELECT * FROM c0) UNION ALL (SELECT * FROM c1) UNION ALL (SELECT * FROM c1) ) SELECT * FROM c2; ---- Explained Query: With Mutually Recursive cte l0 = Union Project (#0{n}) ReadStorage materialize.public.init Get l2 cte l1 = Project (#1) Map ((#0{n} + #0{n})) Get l0 cte l2 = Union Get l0 Get l1 Get l1 Return Get l2 Source materialize.public.init Target cluster: quickstart EOF