Materialize/test/sqllogictest/parse_ident.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.
#
# The original source code is subject to the terms of the PostgreSQL license, a copy
# of which can be found in the LICENSE file at the root of this repository.

# transliterated from postgres/src/test/regress/sql/name.sql

query T
SELECT parse_ident('Schemax.Tabley')::text;
----
{schemax,tabley}

query T
SELECT parse_ident('"SchemaX"."TableY"')::text;
----
{SchemaX,TableY}

query T
SELECT parse_ident('foo.boo')::text;
----
{foo,boo}

query error string is not a valid identifier: "foo.boo\[\]"
SELECT parse_ident('foo.boo[]');

query T
SELECT parse_ident('foo.boo[]', false)::text;
----
{foo,boo}

query error string is not a valid identifier: " "
SELECT parse_ident(' ');

query error string is not a valid identifier: " .aaa"
SELECT parse_ident(' .aaa');

query error string is not a valid identifier: " aaa . "
SELECT parse_ident(' aaa . ');

query error string is not a valid identifier: "aaa.a%b"
SELECT parse_ident('aaa.a%b');

query error string is not a valid identifier: "X\rXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
SELECT parse_ident(E'X\rXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

query II
SELECT length(parse_ident[1]), length(parse_ident[2])
FROM
  (
    SELECT
      parse_ident(
        '"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx".yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'
      )
  );
----
414
289

query T
SELECT parse_ident(' first . "  second  " ."   third   ". "  ' || repeat('x',66) || '"')::text;
----
{first,"  second  ","   third   ","  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}

query error octal escapes are not supported
SELECT parse_ident(E'"c".X XXXX\002XXXXXX');

query error string is not a valid identifier: "1020"
SELECT parse_ident('1020');

query error string is not a valid identifier: "10.20"
SELECT parse_ident('10.20');

query error string is not a valid identifier: "."
SELECT parse_ident('.');

query error string is not a valid identifier: ".1020"
SELECT parse_ident('.1020');

query error string is not a valid identifier: "xxx.1020"
SELECT parse_ident('xxx.1020');

# Non-PG tests

query error string is not a valid identifier: ""
SELECT parse_ident('');

query error string is not a valid identifier: "\$abc"
SELECT parse_ident('$abc');

query T
SELECT parse_ident('"$abc"');
----
{$abc}

query error string is not a valid identifier: "abc\.\$def"
SELECT parse_ident('abc.$def');

query T
SELECT parse_ident('abc."$def"');
----
{abc,$def}

query error string is not a valid identifier: "abc\.def \$"
SELECT parse_ident('abc.def $');

query T
SELECT parse_ident('abc."def $"');
----
{abc,"def $"}

query T
SELECT parse_ident('abc.def $', false);
----
{abc,def}

query T
SELECT parse_ident('a$');
----
{a$}

query T
SELECT parse_ident('abc.d$');
----
{abc,d$}

query T
SELECT parse_ident('a$.d$');
----
{a$,d$}

query T
SELECT parse_ident('🌍');
----
{🌍}

query T
SELECT parse_ident('🌍.🌍');
----
{🌍,🌍}

query T
SELECT parse_ident('🌍.abc');
----
{🌍,abc}

query T
SELECT parse_ident('abc.🌍');
----
{abc,🌍}

query T
SELECT parse_ident('"🌍"');
----
{🌍}

query T
SELECT parse_ident('"🌍"."🌍"');
----
{🌍,🌍}

query T
SELECT parse_ident('"🌍".abc');
----
{🌍,abc}

query T
SELECT parse_ident('abc."🌍"');
----
{abc,🌍}

query T
SELECT parse_ident('  🌍 ');
----
{🌍}

query T
SELECT parse_ident('  🌍 .  🌍 ');
----
{🌍,🌍}

query T
SELECT parse_ident('"  🌍 "."  🌍 "');
----
{"  🌍 ","  🌍 "}

query T
SELECT parse_ident('  🌍 .abc');
----
{🌍,abc}

query T
SELECT parse_ident('abc.  🌍 ');
----
{abc,🌍}

query T
SELECT parse_ident('a.b.c.d.e.f');
----
{a,b,c,d,e,f}

query error string is not a valid identifier: "a\\0b"
SELECT parse_ident('a\0b');

query T
SELECT parse_ident('"a\0b"');
----
{"a\\0b"}

query error string is not a valid identifier: "a b"
SELECT parse_ident('a b');

query error string is not a valid identifier: "a\.b c"
SELECT parse_ident('a.b c');

simple
SELECT parse_ident('a."b');
----
db error: ERROR: string is not a valid identifier: "a.\"b"
DETAIL: String has unclosed double quotes.

statement ok
CREATE TABLE t (c text);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT parse_ident(c)::text FROM t;
----
Explained Query:
  Project (#1)
    Map (arraytostr(parse_ident(#0{c}, true)))
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

# normalizing object names; this is largely built on top of parse_ident

query T
SELECT mz_internal.mz_normalize_object_name('Dbz.Schemax.Tabley')::text;
----
{dbz,schemax,tabley}

query T
SELECT mz_internal.mz_normalize_object_name('"Dbz"."SchemaX"."TableY"')::text;
----
{Dbz,SchemaX,TableY}

query T
SELECT mz_internal.mz_normalize_object_name('Schemax.Tabley')::text;
----
{NULL,schemax,tabley}

query T
SELECT mz_internal.mz_normalize_object_name('"SchemaX"."TableY"')::text;
----
{NULL,SchemaX,TableY}

query error db error: ERROR: improper relation name \(too many dotted names\): Dbz\.Schemax\.Tabley\.Cola
SELECT mz_internal.mz_normalize_object_name('Dbz.Schemax.Tabley.Cola')::text;

query error db error: ERROR: improper relation name \(too many dotted names\): "Dbz"\."SchemaX"\."TableY"\.Cola
SELECT mz_internal.mz_normalize_object_name('"Dbz"."SchemaX"."TableY".Cola')::text;

query error string is not a valid identifier: ""
SELECT mz_internal.mz_normalize_object_name('');

query error string is not a valid identifier: " "
SELECT mz_internal.mz_normalize_object_name(' ');

query error string is not a valid identifier: "1020"
SELECT mz_internal.mz_normalize_object_name('1020');

query T
SELECT mz_internal.mz_normalize_object_name('"$abc"');
----
{NULL,NULL,$abc}

query T
SELECT mz_internal.mz_normalize_object_name('a$.d$');
----
{NULL,a$,d$}

query T
SELECT mz_internal.mz_normalize_object_name('a$.b$.c$');
----
{a$,b$,c$}

query T
SELECT mz_internal.mz_normalize_object_name('"🌍"');
----
{NULL,NULL,🌍}

query T
SELECT mz_internal.mz_normalize_object_name('"🌍"."🌍"."🌍"');
----
{🌍,🌍,🌍}

query T
SELECT mz_internal.mz_normalize_object_name('🌍.🌍.🌍');
----
{🌍,🌍,🌍}

query error db error: ERROR: improper relation name \(too many dotted names\): 🌍\.🌍\.🌍\.🌍
SELECT mz_internal.mz_normalize_object_name('🌍.🌍.🌍.🌍');

# normalizing schema names; this is largely built on top of parse_ident

query T
SELECT mz_internal.mz_normalize_schema_name('Dbz.Schemax')::text;
----
{dbz,schemax}

query T
SELECT mz_internal.mz_normalize_schema_name('"Dbz"."SchemaX"')::text;
----
{Dbz,SchemaX}

query T
SELECT mz_internal.mz_normalize_schema_name('Schemax')::text;
----
{NULL,schemax}

query T
SELECT mz_internal.mz_normalize_schema_name('"SchemaX"')::text;
----
{NULL,SchemaX}

query error db error: ERROR: improper schema name \(too many dotted names\): Dbz\.Schemax\.Tabley
SELECT mz_internal.mz_normalize_schema_name('Dbz.Schemax.Tabley')::text;

query error db error: ERROR: improper schema name \(too many dotted names\): "Dbz"\."SchemaX"\."TableY"
SELECT mz_internal.mz_normalize_schema_name('"Dbz"."SchemaX"."TableY"')::text;

query error string is not a valid identifier: ""
SELECT mz_internal.mz_normalize_schema_name('');

query error string is not a valid identifier: " "
SELECT mz_internal.mz_normalize_schema_name(' ');

query error string is not a valid identifier: "1020"
SELECT mz_internal.mz_normalize_schema_name('1020');

query T
SELECT mz_internal.mz_normalize_schema_name('"$abc"');
----
{NULL,$abc}

query T
SELECT mz_internal.mz_normalize_schema_name('a$.d$');
----
{a$,d$}

query T
SELECT mz_internal.mz_normalize_schema_name('"🌍"');
----
{NULL,🌍}

query T
SELECT mz_internal.mz_normalize_schema_name('"🌍"."🌍"');
----
{🌍,🌍}

query T
SELECT mz_internal.mz_normalize_schema_name('🌍.🌍');
----
{🌍,🌍}

query error db error: ERROR: improper schema name \(too many dotted names\): 🌍\.🌍\.🌍
SELECT mz_internal.mz_normalize_schema_name('🌍.🌍.🌍');

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (c text);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT mz_internal.mz_normalize_object_name(c)::text FROM t;
----
Explained Query:
  Project (#1)
    Map (arraytostr(case when (#0{c}) IS NULL then null else case when ((parse_ident(#0{c}, true) array_length 1) > 3) then error_if_null(null, ("improper relation name (too many dotted names): " || #0{c})) else (array_fill(null, array[(3 - (parse_ident(#0{c}, true) array_length 1))]) || parse_ident(#0{c}, true)) end end))
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF