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

# 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 FROM "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 FROM 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 FROM 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 FROM "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)

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

statement error column "X" does not exist
SELECT "X" FROM foo

statement error column "y" does not exist
SELECT Y FROM foo

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

# 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 pgcode 42P01 relation "XV" does not exist
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 pgcode 42P01 relation "yv" does not exist
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