Materialize/test/sqllogictest/cockroach/case_sensitive_names.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/case_sensitive_names
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

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

# Case sensitivity of database names

# statement ok
# CREATE DATABASE D

# statement ok
# SHOW TABLES FROM d

# statement error target database or schema does not exist
# SHOW TABLES FROM "D"

# statement ok
# CREATE DATABASE "E"

# statement error target database or schema does not exist
# SHOW TABLES FROM e

# statement ok
# SHOW TABLES FROM "E"

# Case sensitivity of table names:
# When non-quoted, table names are normalized during creation.

statement ok
CREATE TABLE A(x INT)

statement error pgcode 42P01 unknown catalog item 'A'
SHOW COLUMNS FROM "A"

statement error pgcode 42P01 unknown catalog item 'A'
SHOW INDEXES ON "A"

# statement error pgcode 42P01 catalog item '"A"' does not exist
# SHOW CREATE TABLE "A"

# statement error pgcode 42P01 catalog item '"A"' does not exist
# SHOW GRANTS ON TABLE "A"

# statement error pgcode 42P01 catalog item '"test.A"' does not exist
# SHOW GRANTS ON TABLE test."A"

# statement error pgcode 42P01 catalog item '"A"' does not exist
# SHOW CONSTRAINTS FROM "A"

statement error pgcode 42P01 unknown catalog item 'A'
SELECT * FROM "A"

# statement error pgcode 42P01 catalog item '"A"' does not exist
# INSERT INTO "A"(x) VALUES(1)

# statement error pgcode 42P01 catalog item '"A"' does not exist
# UPDATE "A" SET x = 42

# statement error pgcode 42P01 catalog item '"A"' does not exist
# DELETE FROM "A"

# statement error pgcode 42P01 catalog item '"A"' does not exist
# TRUNCATE "A"

statement error pgcode 42P01 unknown catalog item 'A'
DROP TABLE "A"

statement ok
SHOW COLUMNS FROM a

statement ok
SHOW INDEXES ON a

# statement ok
# SHOW CREATE TABLE a

# statement ok
# SHOW CONSTRAINTS FROM a

statement ok
SELECT * FROM a

statement ok
INSERT INTO a(x) VALUES(1)

statement ok
UPDATE a SET x = 42

statement ok
DELETE FROM a

# statement ok
# TRUNCATE a

statement ok
DROP TABLE a

# When quoted, a table name does not get normalized during create, and
# must be thus quoted during use.

statement ok
CREATE TABLE "B"(x INT)

statement error pgcode 42P01 unknown catalog item 'b'
SHOW COLUMNS FROM B

statement error pgcode 42P01 unknown catalog item 'b'
SHOW INDEXES ON B

# statement error pgcode 42P01 catalog item 'b' does not exist
# SHOW CREATE TABLE B

# statement error pgcode 42P01 catalog item 'b' does not exist
# SHOW GRANTS ON TABLE B

# statement error pgcode 42P01 catalog item 'test.b' does not exist
# SHOW GRANTS ON TABLE test.B

# statement error pgcode 42P01 catalog item 'b' does not exist
# SHOW CONSTRAINTS FROM B

statement error pgcode 42P01 unknown catalog item 'b'
SELECT * FROM B

# statement error pgcode 42P01 catalog item 'b' does not exist
# INSERT INTO B(x) VALUES(1)

# statement error pgcode 42P01 catalog item 'b' does not exist
# UPDATE B SET x = 42

# statement error pgcode 42P01 catalog item 'b' does not exist
# DELETE FROM B

# statement error pgcode 42P01 catalog item 'b' does not exist
# TRUNCATE B

statement error pgcode 42P01 unknown catalog item 'b'
DROP TABLE B

statement ok
SHOW COLUMNS FROM "B"

statement ok
SHOW INDEXES ON "B"

# statement ok
# SHOW CREATE TABLE "B"

# statement ok
# SHOW GRANTS ON TABLE "B"

# statement ok
# SHOW GRANTS ON TABLE test."B"

# statement ok
# SHOW CONSTRAINTS FROM "B"

statement ok
SELECT * FROM "B"

statement ok
INSERT INTO "B"(x) VALUES(1)

statement ok
UPDATE "B" SET x = 42

statement ok
DELETE FROM "B"

# statement ok
# TRUNCATE "B"

statement ok
DROP TABLE "B"

# Case sensitivity of column names.

statement ok
CREATE TABLE foo(X INT, "Y" INT, "created_AT" timestamp)

query III colnames
SELECT x, X, "Y" FROM foo
----
x x Y

statement error db error: ERROR: column "X" does not exist\nHINT: The similarly named column "x" does exist.
SELECT "X" FROM foo

statement error column "y" does not exist\nHINT: The similarly named column "Y" does exist.
SELECT Y FROM foo

simple
SELECT creaetd_at FROM foo
----
db error: ERROR: column "creaetd_at" does not exist
HINT: The similarly named column "created_AT" does exist. Make sure to surround case sensitive names in double quotes.

# The following should not be ambiguous.
query II colnames
SELECT Y, "Y" FROM (SELECT x as y, "Y" FROM foo)
----
y Y

statement ok
CREATE TABLE foo_multi_case ("cAsE" INT, "CASE" INT)

statement error ERROR: column "foo_multi_case.case" does not exist\nHINT: There are similarly named columns that do exist: "foo_multi_case.cAsE", "foo_multi_case.CASE". Make sure to surround case sensitive names in double quotes.
SELECT foo_multi_case.case FROM foo_multi_case;

statement ok
CREATE TABLE j1 ("X" int);

statement ok
INSERT INTO j1 VALUES (1), (2), (3);

statement ok
CREATE TABLE j2 ("X" int, "Y" int);

statement ok
INSERT INTO j2 VALUES (1, 5), (2, 7), (8, 9);

statement ok
CREATE TABLE j3 ("X" int, "Z" int);

statement ok
INSERT INTO j3 VALUES (1, 7), (10, 11), (12, 13);

statement error db error: ERROR: column "Y" does not exist\nHINT: The similarly named column "y" does exist.
SELECT "Y" FROM ( SELECT "X" as y FROM j1 NATURAL JOIN j2 );

query I
SELECT y FROM ( SELECT "X" as y FROM j1 NATURAL JOIN j2 );
----
1
2

# Even though the column "Y" exists, it should not get suggested since it's not in scope.

statement error db error: ERROR: column "y" does not exist
SELECT "X", y FROM j1 LEFT JOIN ( SELECT "X", "Z" FROM j2 LEFT JOIN j3 USING ("X") ) USING ("X");

# Case sensitivity of view names.

mode standard

statement ok
CREATE VIEW XV AS SELECT X, "Y" FROM foo

query TT
SHOW CREATE VIEW xv
----
materialize.public.xv
CREATE VIEW materialize.public.xv AS SELECT x, "Y" FROM materialize.public.foo;

query error unknown catalog item 'XV'
SHOW CREATE VIEW "XV"

statement ok
CREATE VIEW "YV" AS SELECT X, "Y" FROM foo

query TT
SHOW CREATE VIEW "YV"
----
materialize.public.YV
CREATE VIEW materialize.public."YV" AS SELECT x, "Y" FROM materialize.public.foo;

query error unknown catalog item 'yv'
SHOW CREATE VIEW YV

mode cockroach

# Case sensitivity of index names.

statement ok
CREATE TABLE a(x INT, y INT, CONSTRAINT FooIdx PRIMARY KEY(x))

statement ok
CREATE INDEX I ON a(y)

# statement error index "I" not found
# SELECT * FROM a@"I"

# statement error index "FooIdx" not found
# SELECT * FROM a@"FooIdx"

# statement error index "I" not found
# SELECT * FROM a ORDER BY INDEX a@"I"

# statement error index "FooIdx" not found
# SELECT * FROM a ORDER BY INDEX a@"FooIdx"

# statement error index "I" does not exist
# DROP INDEX a@"I"

# statement ok
# SELECT * FROM a@I

# statement ok
# SELECT * FROM a@FooIdx

# statement ok
# SELECT * FROM a ORDER BY INDEX a@I

# statement ok
# SELECT * FROM a ORDER BY INDEX a@FooIdx

statement ok
DROP INDEX I

# Unicode sequences are preserved.

statement ok
CREATE TABLE Amelie("Amélie" INT, "Amélie" INT)

statement ok
INSERT INTO Amelie VALUES (1, 2)

# # Check that the column names were encoded properly
# query I
# SELECT ordinal_position FROM information_schema.columns WHERE table_name = 'amelie' AND column_name::BYTES = b'Ame\xcc\x81lie'
# ----
# 1

# query I
# SELECT ordinal_position FROM information_schema.columns WHERE table_name = 'amelie' AND column_name::BYTES = b'Am\xc3\xa9lie'
# ----
# 2

# Check that the non-normalized names propagate throughout until results.

query II colnames
SELECT "Amélie", "Amélie" FROM Amelie
----
Amélie Amélie
2      1

# Check that function names are also recognized case-insensitively.
query T
SELECT AsCIi('abc')
----
97