github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/case_sensitive_names (about)

     1  # LogicTest: !3node-tenant
     2  # Case sensitivity of database names
     3  
     4  statement ok
     5  CREATE DATABASE D
     6  
     7  statement ok
     8  SHOW TABLES FROM d
     9  
    10  statement error target database or schema does not exist
    11  SHOW TABLES FROM "D"
    12  
    13  statement ok
    14  CREATE DATABASE "E"
    15  
    16  statement error target database or schema does not exist
    17  SHOW TABLES FROM e
    18  
    19  statement ok
    20  SHOW TABLES FROM "E"
    21  
    22  # Case sensitivity of table names:
    23  # When non-quoted, table names are normalized during creation.
    24  
    25  statement ok
    26  CREATE TABLE A(x INT)
    27  
    28  statement error pgcode 42P01 relation "A" does not exist
    29  SHOW COLUMNS FROM "A"
    30  
    31  statement error pgcode 42P01 relation "A" does not exist
    32  SHOW INDEXES FROM "A"
    33  
    34  statement error pgcode 42P01 relation "A" does not exist
    35  SHOW CREATE TABLE "A"
    36  
    37  statement error pgcode 42P01 relation "A" does not exist
    38  SHOW GRANTS ON TABLE "A"
    39  
    40  statement error pgcode 42P01 relation "test.A" does not exist
    41  SHOW GRANTS ON TABLE test."A"
    42  
    43  statement error pgcode 42P01 relation "A" does not exist
    44  SHOW CONSTRAINTS FROM "A"
    45  
    46  statement error pgcode 42P01 relation "A" does not exist
    47  SELECT * FROM "A"
    48  
    49  statement error pgcode 42P01 relation "A" does not exist
    50  INSERT INTO "A"(x) VALUES(1)
    51  
    52  statement error pgcode 42P01 relation "A" does not exist
    53  UPDATE "A" SET x = 42
    54  
    55  statement error pgcode 42P01 relation "A" does not exist
    56  DELETE FROM "A"
    57  
    58  statement error pgcode 42P01 relation "A" does not exist
    59  TRUNCATE "A"
    60  
    61  statement error pgcode 42P01 relation "A" does not exist
    62  DROP TABLE "A"
    63  
    64  statement ok
    65  SHOW COLUMNS FROM a
    66  
    67  statement ok
    68  SHOW INDEXES FROM a
    69  
    70  statement ok
    71  SHOW CREATE TABLE a
    72  
    73  statement ok
    74  SHOW CONSTRAINTS FROM a
    75  
    76  statement ok
    77  SELECT * FROM a
    78  
    79  statement ok
    80  INSERT INTO a(x) VALUES(1)
    81  
    82  statement ok
    83  UPDATE a SET x = 42
    84  
    85  statement ok
    86  DELETE FROM a
    87  
    88  statement ok
    89  TRUNCATE a
    90  
    91  statement ok
    92  DROP TABLE a
    93  
    94  # When quoted, a table name does not get normalized during create, and
    95  # must be thus quoted during use.
    96  
    97  statement ok
    98  CREATE TABLE "B"(x INT)
    99  
   100  statement error pgcode 42P01 relation "b" does not exist
   101  SHOW COLUMNS FROM B
   102  
   103  statement error pgcode 42P01 relation "b" does not exist
   104  SHOW INDEXES FROM B
   105  
   106  statement error pgcode 42P01 relation "b" does not exist
   107  SHOW CREATE TABLE B
   108  
   109  statement error pgcode 42P01 relation "b" does not exist
   110  SHOW GRANTS ON TABLE B
   111  
   112  statement error pgcode 42P01 relation "test.b" does not exist
   113  SHOW GRANTS ON TABLE test.B
   114  
   115  statement error pgcode 42P01 relation "b" does not exist
   116  SHOW CONSTRAINTS FROM B
   117  
   118  statement error pgcode 42P01 relation "b" does not exist
   119  SELECT * FROM B
   120  
   121  statement error pgcode 42P01 relation "b" does not exist
   122  INSERT INTO B(x) VALUES(1)
   123  
   124  statement error pgcode 42P01 relation "b" does not exist
   125  UPDATE B SET x = 42
   126  
   127  statement error pgcode 42P01 relation "b" does not exist
   128  DELETE FROM B
   129  
   130  statement error pgcode 42P01 relation "b" does not exist
   131  TRUNCATE B
   132  
   133  statement error pgcode 42P01 relation "b" does not exist
   134  DROP TABLE B
   135  
   136  statement ok
   137  SHOW COLUMNS FROM "B"
   138  
   139  statement ok
   140  SHOW INDEXES FROM "B"
   141  
   142  statement ok
   143  SHOW CREATE TABLE "B"
   144  
   145  statement ok
   146  SHOW GRANTS ON TABLE "B"
   147  
   148  statement ok
   149  SHOW GRANTS ON TABLE test."B"
   150  
   151  statement ok
   152  SHOW CONSTRAINTS FROM "B"
   153  
   154  statement ok
   155  SELECT * FROM "B"
   156  
   157  statement ok
   158  INSERT INTO "B"(x) VALUES(1)
   159  
   160  statement ok
   161  UPDATE "B" SET x = 42
   162  
   163  statement ok
   164  DELETE FROM "B"
   165  
   166  statement ok
   167  TRUNCATE "B"
   168  
   169  statement ok
   170  DROP TABLE "B"
   171  
   172  # Case sensitivity of column names.
   173  
   174  statement ok
   175  CREATE TABLE foo(X INT, "Y" INT)
   176  
   177  query III colnames
   178  SELECT x, X, "Y" FROM foo
   179  ----
   180  x x Y
   181  
   182  statement error column "X" does not exist
   183  SELECT "X" FROM foo
   184  
   185  statement error column "y" does not exist
   186  SELECT Y FROM foo
   187  
   188  # The following should not be ambiguous.
   189  query II colnames
   190  SELECT Y, "Y" FROM (SELECT x as y, "Y" FROM foo)
   191  ----
   192  y Y
   193  
   194  # Case sensitivity of view names.
   195  
   196  statement ok
   197  CREATE VIEW XV AS SELECT X, "Y" FROM foo
   198  
   199  query TT
   200  SHOW CREATE VIEW xv
   201  ----
   202  xv  CREATE VIEW xv (x, "Y") AS SELECT x, "Y" FROM test.public.foo
   203  
   204  query error pgcode 42P01 relation "XV" does not exist
   205  SHOW CREATE VIEW "XV"
   206  
   207  statement ok
   208  CREATE VIEW "YV" AS SELECT X, "Y" FROM foo
   209  
   210  query TT
   211  SHOW CREATE VIEW "YV"
   212  ----
   213  "YV"  CREATE VIEW "YV" (x, "Y") AS SELECT x, "Y" FROM test.public.foo
   214  
   215  query error pgcode 42P01 relation "yv" does not exist
   216  SHOW CREATE VIEW YV
   217  
   218  # Case sensitivity of index names.
   219  
   220  statement ok
   221  CREATE TABLE a(x INT, y INT, CONSTRAINT Foo PRIMARY KEY(x)); CREATE INDEX I ON a(y)
   222  
   223  statement error index "I" not found
   224  SELECT * FROM a@"I"
   225  
   226  statement error index "Foo" not found
   227  SELECT * FROM a@"Foo"
   228  
   229  statement error index "I" not found
   230  SELECT * FROM a ORDER BY INDEX a@"I"
   231  
   232  statement error index "Foo" not found
   233  SELECT * FROM a ORDER BY INDEX a@"Foo"
   234  
   235  statement error index "I" does not exist
   236  DROP INDEX a@"I"
   237  
   238  statement ok
   239  SELECT * FROM a@I
   240  
   241  statement ok
   242  SELECT * FROM a@Foo
   243  
   244  statement ok
   245  SELECT * FROM a ORDER BY INDEX a@I
   246  
   247  statement ok
   248  SELECT * FROM a ORDER BY INDEX a@Foo
   249  
   250  statement ok
   251  DROP INDEX a@I
   252  
   253  # Unicode sequences are preserved.
   254  
   255  statement ok
   256  CREATE TABLE Amelie("Amélie" INT, "Amélie" INT); INSERT INTO Amelie VALUES (1, 2)
   257  
   258  # Check that the column names were encoded properly
   259  query I
   260  SELECT ordinal_position FROM information_schema.columns WHERE table_name = 'amelie' AND column_name::BYTES = b'Ame\xcc\x81lie'
   261  ----
   262  1
   263  
   264  query I
   265  SELECT ordinal_position FROM information_schema.columns WHERE table_name = 'amelie' AND column_name::BYTES = b'Am\xc3\xa9lie'
   266  ----
   267  2
   268  
   269  # Check that the non-normalized names propagate throughout until results.
   270  
   271  query II colnames
   272  SELECT "Amélie", "Amélie" FROM Amelie
   273  ----
   274  Amélie Amélie
   275  2      1
   276  
   277  # Check that function names are also recognized case-insensitively.
   278  query I
   279  SELECT LENGTH('abc') -- lint: uppercase function OK
   280  ----
   281  3