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

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

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 (2, 3), (5, 5), (5, 5), (6, 1)

# Test that nested unions are fused into a single Union operator
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR (SELECT * FROM t1 UNION ALL SELECT * FROM t1) UNION ALL (SELECT * FROM t2 UNION ALL SELECT * FROM t2);
----
Explained Query:
  Union // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }
    ReadStorage materialize.public.t2 // { arity: 2 }
    ReadStorage materialize.public.t2 // { arity: 2 }

Source materialize.public.t1
Source materialize.public.t2

Target cluster: quickstart

EOF

query II
(SELECT * FROM t1 UNION ALL SELECT * FROM t1) UNION ALL (SELECT * FROM t2 UNION ALL SELECT * FROM t2);
----
1  1
1  1
2  3
2  3
2  3
2  3
4  5
4  5
5  5
5  5

# Test that nested negated unions are merged into the parent Union operator by pushing the Negate into their branches
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 t2 UNION ALL SELECT * FROM t2);
----
Explained Query:
  With
    cte l0 =
      Negate // { arity: 2 }
        ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 2 }
    Threshold // { arity: 2 }
      Union // { arity: 2 }
        ReadStorage materialize.public.t1 // { arity: 2 }
        ReadStorage materialize.public.t1 // { arity: 2 }
        Get l0 // { arity: 2 }
        Get l0 // { arity: 2 }

Source materialize.public.t1
Source materialize.public.t2

Target cluster: quickstart

EOF

query II
(SELECT * FROM t1 UNION ALL SELECT * FROM t1) EXCEPT ALL (SELECT * FROM t2 UNION ALL SELECT * FROM t2);
----
1  1
1  1
4  5
4  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 INTERSECT ALL SELECT * FROM t3;
----
Explained Query:
  Threshold // { arity: 2 }
    Union // { arity: 2 }
      ReadStorage materialize.public.t2 // { arity: 2 }
      Negate // { arity: 2 }
        ReadStorage materialize.public.t1 // { arity: 2 }
      Threshold // { arity: 2 }
        Union // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
          Negate // { arity: 2 }
            ReadStorage materialize.public.t3 // { arity: 2 }

Source materialize.public.t1
Source materialize.public.t2
Source materialize.public.t3

Target cluster: quickstart

EOF

query II
SELECT * FROM t2  EXCEPT ALL SELECT * FROM t1 INTERSECT ALL SELECT * FROM t3;
----
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 INTERSECT ALL SELECT f1, null::int FROM t3);
----
Explained Query:
  Threshold // { arity: 2 }
    Union // { arity: 2 }
      ReadStorage materialize.public.t2 // { arity: 2 }
      Negate // { arity: 2 }
        ReadStorage materialize.public.t1 // { arity: 2 }
      Threshold // { arity: 2 }
        Union // { arity: 2 }
          ReadStorage materialize.public.t1 // { arity: 2 }
          Negate // { arity: 2 }
            Project (#0{f1}, #2) // { arity: 2 }
              Map (null) // { arity: 3 }
                ReadStorage materialize.public.t3 // { arity: 2 }

Source materialize.public.t1
Source materialize.public.t2
Source materialize.public.t3

Target cluster: quickstart

EOF

query II
SELECT * FROM t2 EXCEPT ALL (SELECT * FROM t1 INTERSECT ALL SELECT f1, null::int FROM t3);
----
2 3
5 5

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a1.* FROM t3 AS a1 LEFT JOIN t2 AS a2 ON (a1.f1 = a2.nokey);
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f1}]] // { arity: 2 }
        Filter (#0{f1}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.t3 // { arity: 2 }
    cte l1 =
      Project (#0{f1}, #1{f2}) // { arity: 2 }
        Join on=(#0{f1} = #2{nokey}) type=differential // { arity: 3 }
          implementation
            %0:l0[#0{f1}]K » %1:t2[#0{nokey}]K
          Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{nokey}]] // { arity: 1 }
            Project (#1{nokey}) // { arity: 1 }
              Filter (#1{nokey}) IS NOT NULL // { arity: 2 }
                ReadStorage materialize.public.t2 // { arity: 2 }
  Return // { arity: 2 }
    Union // { arity: 2 }
      Negate // { arity: 2 }
        Project (#0{f1}, #1{f2}) // { arity: 2 }
          Join on=(#0{f1} = #2{f1}) type=differential // { arity: 3 }
            implementation
              %1[#0]UKA » %0:l0[#0{f1}]K
            Get l0 // { arity: 2 }
            ArrangeBy keys=[[#0{f1}]] // { arity: 1 }
              Distinct project=[#0{f1}] // { arity: 1 }
                Project (#0{f1}) // { arity: 1 }
                  Get l1 // { arity: 2 }
      ReadStorage materialize.public.t3 // { arity: 2 }
      Get l1 // { arity: 2 }

Source materialize.public.t2
  filter=((#1{nokey}) IS NOT NULL)
Source materialize.public.t3

Target cluster: quickstart

EOF

query II rowsort
SELECT a1.* FROM t3 AS a1 LEFT JOIN t2 AS a2 ON (a1.f1 = a2.nokey);
----
2 3
5 5
5 5
6 1