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

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 <empty>

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