Quoted Table Column Identifiers Not Allowing ! Character

[inviscid]

According to the MySQL standard a quoted name should be able to contain most ASCII characters except NULL but including !. When I run the following command I get a syntax error:

CREATE TABLE IF NOT EXISTS `bangtest` (
    `Quoted!Name!With!Bangs` varchar(20) NULL, 
    `Quoted$Name$With$Dollars` varchar(20) NULL,
    `SimpleName` varchar(20) NULL 
) PROPERTIES (
    "storage_volume" = "my_volume",
    "datacache.enable" = "true"
);

StarRocks produces a syntax error:

SQL Error [1064] [42000]: Getting analyzing error. Detail message: Incorrect column name 'Quoted!Name!With!Bangs'.

If I remove the ! characters, the table creates as expected.

Steps to reproduce the behavior (Required)

Run the following CREATE TABLE command:

CREATE TABLE IF NOT EXISTS `bangtest` (
    `Quoted!Name!With!Bangs` varchar(20) NULL, 
    `Quoted$Name$With$Dollars` varchar(20) NULL,
    `SimpleName` varchar(20) NULL 
) PROPERTIES (
    "storage_volume" = "my_volume",
    "datacache.enable" = "true"
);

Expected behavior (Required)

Table should create with quoted column names that contain ! character

Real behavior (Required)

A syntax error is produced that indicates the ! character is not allowed in quoted column identifier names

StarRocks version (Required)

3.2.2-269e832

[inviscid]

A lower priority but I also noticed that we cannot create tables with - in the name even though it is a quoted table name. The columns create correctly with a - in the name.

This fails:

CREATE DATABASE `this-is-a-dash-test`;

But should be legit according to MySQL docs.

[inviscid]

Following up on this one to add more context.

According to the MySQL guide I think ! is allowed because it falls in the range. The Unicode value for ! is U+0021.

See reference here: https://dev.mysql.com/doc/refman/8.3/en/identifiers.html

Which specifies the following:

Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

• ASCII: U+0001 .. U+007F
• Extended: U+0080 .. U+FFFF

[inviscid]

it looks like the column name check specifically excludes the ! character. Based on the comments above the column name check function, wondering if that constraint still applies with all the recent changes in v3.3.

starrocks/fe/fe-core/src/main/java/com/starrocks/sql/analyzer/FeNameFormat.java

Lines 39 to 42 in 4feeec0

	// Now we can not accept all characters because current design of delete save delete cond contains column name,
	// so it can not distinguish whether it is an operator or a column name
	// the future new design will improve this problem and open this limitation
	private static final String COLUMN_NAME_REGEX = "^[^\0=<>!\\*]{1,1024}$";

[packy92]

Thanks, we will check it.

[inviscid]

@packy92 Not sure if this is related but thought I'd link here in case it is all part of the same logic: #40292

[packy92]

Due to some historical reasons, our delete operation requires that the column name of the predicate in the statement cannot contain some special symbols, such as <, >, =, !. We currently have no plans to relax this restriction. Regarding whether the database name can contain more special symbols, we need to do some test to verify.

[inviscid]

Thanks for checking on the feasibility.

[github-actions]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!