Vladimir Dmitrienko created IGNITE-25173:
--------------------------------------------
Summary: System object names are auto-capitalized, leading to
unexpected query behavior
Key: IGNITE-25173
URL: https://issues.apache.org/jira/browse/IGNITE-25173
Project: Ignite
Issue Type: Bug
Components: sql
Affects Versions: 3.0.0-beta1
Reporter: Vladimir Dmitrienko
System object names like table names, distribution zone names are capitalized
implicitly unless enclosed in double quotes.
This behavior can lead to unexpected behavior when querying tables or
referencing a distribution zone in a CREATE TABLE statement, especially when a
user relies on the original casing they provided.
Consider the following examples:
1. Unquoted table name:
{code:java}
sql-cli> CREATE TABLE person (ID INT PRIMARY KEY, NAME VARCHAR);
// 1.
sql-cli> SELECT * FROM person;
OK
// 2.
sql-cli> SELECT * FROM PERSON;
OK
// 3.
sql-cli> SELECT * FROM "person";
SQL query execution error
Failed to validate query. From line 1, column 15 to line 1, column 22:
Object 'person' not found within 'PUBLIC'; did you mean 'PERSON'?
// 4.
sql-cli> SELECT * FROM "PERSON";
OK{code}
In example #3, the query fails event despite using the original casing as
defined in the CREATE TABLE statement.
2. Quoted table name:
{code:java}
sql-cli> CREATE TABLE "person_in_quotes" (ID INT PRIMARY KEY, NAME VARCHAR);
// 1.
sql-cli> SELECT * FROM person_in_quotes;
SQL query execution error
Failed to validate query. From line 1, column 15 to line 1, column 30:
Object 'PERSON_IN_QUOTES' not found within 'PUBLIC';
did you mean 'person_in_quotes'?
// 2.
sql-cli> SELECT * FROM 'person_in_quotes';
SQL query execution error
Failed to parse query:
Encountered "'person_in_quotes'" at line 1, column 15
// 3.
sql-cli> SELECT * FROM PERSON_IN_QUOTES;
SQL query execution error
Failed to validate query.
>From line 1, column 15 to line 1, column 30:
Object 'PERSON_IN_QUOTES' not found within 'PUBLIC';
did you mean 'person_in_quotes'?
// 4.
sql-cli> SELECT * FROM "person_in_quotes";
OK{code}
When the table name is quoted in the CREATE TABLE statement, it becomes
case-sensitive. The only way to reference it successfully is to use double
quotes with the exact same casing. Additionally, the error messages in examples
#1 and #3 are misleading, as they suggest using single quotes
('person_in_quotes'), which results in a validation error.
3. Quoted zone name:
{code:java}
sql-cli> CREATE ZONE "test_zone" WITH STORAGE_PROFILES='default';
// 1.
sql-cli> CREATE TABLE person_with_zone (ID INT PRIMARY KEY, NAME VARCHAR) ZONE
test_zone;
SQL query execution error
Distribution zone with name 'TEST_ZONE' not found.{code}
Since the zone name was quoted when created, referencing it without quotes
fails. The error message does not clarify the case-sensitivity issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
