Materialize/test/sqllogictest/transform/notice/index_key_empty.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 TO true
----
COMPLETE 0

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

# Disable rbac checks in order to select from mz_notices.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false
----
COMPLETE 0

statement ok
CREATE SCHEMA notices;

statement ok
SET SCHEMA = notices;

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

# Create an index that emits the optimizer notice.
statement ok
CREATE INDEX t_idx_empty_key ON t();

# Verify that the notice is shown in EXPLAIN.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR INDEX t_idx_empty_key;
----
materialize.notices.t_idx_empty_key:
  ArrangeBy keys=[[]]
    ReadStorage materialize.notices.t

Source materialize.notices.t

Target cluster: quickstart

Notices:
  - Notice: Empty index key. The index will be completely skewed to one worker thread, which can lead to performance problems.
    Hint: CREATE DEFAULT INDEX is almost always better than an index with an empty key. (Except for cross joins with big inputs, which are better to avoid anyway.)

EOF

# Verify that the same notice can be found in the catalog.
query TTTTTTTT
SELECT
  n.notice_type, n.message, n.redacted_message, n.hint, n.redacted_hint, n.action, n.redacted_action, n.action_type
FROM
  mz_internal.mz_notices n JOIN
  mz_catalog.mz_indexes idx ON(n.object_id = idx.id)
WHERE
  idx.name = 't_idx_empty_key'
----
Empty index key
Empty index key. The index will be completely skewed to one worker thread, which can lead to performance problems.
NULL
CREATE DEFAULT INDEX is almost always better than an index with an empty key. (Except for cross joins with big inputs, which are better to avoid anyway.)
NULL
Drop the enclosing index and re-create it using `CREATE DEFAULT INDEX ON` instead.
NULL
plain_text

# Drop the catalog item associated with the notice.
statement ok
DROP INDEX t_idx_empty_key;

# Verify that the notice is no longer in the catalog.
query TTTTTTTT
SELECT
  n.notice_type, n.message, n.redacted_message, n.hint, n.redacted_hint, n.action, n.redacted_action, n.action_type
FROM
  mz_internal.mz_notices n JOIN
  mz_catalog.mz_indexes idx ON(n.object_id = idx.id)
WHERE
  idx.name = 't_idx_empty_key'
----

# This further tests `drop_plans_and_metainfos`
statement ok
DROP SCHEMA notices CASCADE;