Materialize/test/sqllogictest/attributes/mir_unique_keys.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.

statement ok
CREATE TABLE t (
  a int,
  b int
)

statement ok
CREATE TABLE u (
  c int,
  d int
)

# A global aggregation has a key []

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR SELECT sum(a) FROM t
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(#0{a})] // { keys: "([])" }
        Project (#0{a}) // { keys: "()" }
          ReadStorage materialize.public.t // { keys: "()" }
  Return // { keys: "([])" }
    Union // { keys: "([])" }
      Get l0 // { keys: "([])" }
      Map (null) // { keys: "()" }
        Union // { keys: "()" }
          Negate // { keys: "()" }
            Project () // { keys: "([])" }
              Get l0 // { keys: "([])" }
          Constant // { keys: "([])" }
            - ()

Source materialize.public.t

Target cluster: quickstart

EOF

# all columns that have unique values are unique keys of an ok constant

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (VALUES (1, 2, 3), (4, 2, 4));
----
Explained Query:
  Constant // { keys: "([0], [2])" }
    - (1, 2, 3)
    - (4, 2, 4)

Target cluster: quickstart

EOF

statement ok
CREATE VIEW v as SELECT c, d FROM u GROUP BY c, d;

statement ok
CREATE DEFAULT INDEX on v;

# join + unique key sets being split by a predicate `<column1> = <column2>`

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM
(SELECT sum(a) as a, b FROM t GROUP BY b) t
INNER JOIN
(SELECT * FROM v WHERE c = d) u
ON t.b = u.d;
----
Explained Query:
  Project (#1{sum_a}, #0{b}, #2{c}, #0{b}) // { keys: "([1])" }
    Join on=(#0{b} = #3{d}) type=differential // { keys: "([0])" }
      ArrangeBy keys=[[#0{b}]] // { keys: "([0])" }
        Reduce group_by=[#1{b}] aggregates=[sum(#0{a})] // { keys: "([0])" }
          Filter (#1{b}) IS NOT NULL // { keys: "()" }
            ReadStorage materialize.public.t // { keys: "()" }
      ArrangeBy keys=[[#1{d}]] // { keys: "([0], [1])" }
        Filter (#0{c} = #1{d}) // { keys: "([0], [1])" }
          ReadIndex on=v v_primary_idx=[*** full scan ***] // { keys: "([0, 1])" }

Source materialize.public.t
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.v_primary_idx (*** full scan ***)

Target cluster: quickstart

EOF

# topk limit = 1 + filter column = constant

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR
(SELECT a, c::double FROM
    (SELECT DISTINCT c FROM u) grp,
    LATERAL (
        SELECT a FROM t
        WHERE b = grp.c
        LIMIT 1
    ))
EXCEPT ALL
(SELECT c, d::double FROM v WHERE c = 1)
----
Explained Query:
  Threshold // { keys: "()" }
    Union // { keys: "()" }
      Project (#1{a}, #2) // { keys: "([1])" }
        Map (integer_to_double(#0{c})) // { keys: "([0], [2])" }
          TopK group_by=[#0{c}] limit=1 // { keys: "([0])" }
            Project (#0{c}, #1{a}) // { keys: "()" }
              Join on=(#0{c} = #2{b}) type=differential // { keys: "()" }
                ArrangeBy keys=[[#0{c}]] // { keys: "([0])" }
                  Distinct project=[#0{c}] // { keys: "([0])" }
                    Project (#0{c}) // { keys: "()" }
                      Filter (#0{c}) IS NOT NULL // { keys: "()" }
                        ReadStorage materialize.public.u // { keys: "()" }
                ArrangeBy keys=[[#1{b}]] // { keys: "()" }
                  Filter (#1{b}) IS NOT NULL // { keys: "()" }
                    ReadStorage materialize.public.t // { keys: "()" }
      Negate // { keys: "()" }
        Project (#0{c}, #2) // { keys: "([1])" }
          Filter (#0{c} = 1) // { keys: "([1], [2])" }
            Map (integer_to_double(#1{d})) // { keys: "([0, 1], [0, 2])" }
              ReadIndex on=v v_primary_idx=[*** full scan ***] // { keys: "([0, 1])" }

Source materialize.public.t
  filter=((#1{b}) IS NOT NULL)
Source materialize.public.u
  filter=((#0{c}) IS NOT NULL)

Used Indexes:
  - materialize.public.v_primary_idx (*** full scan ***)

Target cluster: quickstart

Notices:
  - Notice: Index materialize.public.v_primary_idx on v(c, d) is too wide to use for literal equalities `c = 1`.
    Hint: If your literal equalities filter out many rows, create an index whose key exactly matches your literal equalities: (c).

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR
SELECT 1 = (Select * FROM generate_series(1, 100000) limit 3)
----
Explained Query:
  With
    cte l0 =
      TopK limit=3 monotonic // { keys: "()" }
        FlatMap generate_series(1, 100000, 1) // { keys: "()" }
          Constant // { keys: "([])" }
            - ()
    cte l1 =
      Union // { keys: "()" }
        Get l0 // { keys: "()" }
        Project (#1) // { keys: "()" }
          FlatMap guard_subquery_size(#0{count}) // { keys: "()" }
            Reduce aggregates=[count(*)] // { keys: "([])" }
              Project () // { keys: "()" }
                Get l0 // { keys: "()" }
  Return // { keys: "()" }
    Project (#1) // { keys: "()" }
      Map ((#0 = 1)) // { keys: "()" }
        Union // { keys: "()" }
          Get l1 // { keys: "()" }
          Map (null) // { keys: "()" }
            Union // { keys: "()" }
              Negate // { keys: "()" }
                Distinct project=[] // { keys: "([])" }
                  Project () // { keys: "()" }
                    Get l1 // { keys: "()" }
              Constant // { keys: "([])" }
                - ()

Target cluster: quickstart

EOF