Diff uppercases fields if they match a reserved word in CREATE INDEX statement

[iquadrat]

The output incorrectly capitalizes fields in output statements if they match a reserved word.

Example:

Given the schmea test.ddl:

CREATE TABLE AccountByEmailWithRole (
    AccountId BYTES(16) NOT NULL,
    key STRING(MAX) NOT NULL,
    value BYTES(16) NOT NULL,
) PRIMARY KEY(AccountId,key);

CREATE UNIQUE INDEX AccountByEmailWithRole_Key ON AccountByEmailWithRole(key);

And a black original.ddl.

Then, running
java -jar target/spanner-ddl-diff-1.0-jar-with-dependencies.jar --allowRecreateIndexes --originalDdlFile original.ddl --newDdlFile test.ddl --outputDdlFile alter.ddl

results in

CREATE TABLE AccountByEmailWithRole (AccountId BYTES(16) NOT NULL, KEY STRING(MAX) NOT NULL, value BYTES(16) NOT NULL) PRIMARY KEY (AccountId ASC, KEY ASC);

CREATE UNIQUE INDEX AccountByEmailWithRole_Key ON AccountByEmailWithRole (KEY ASC);

Note the capital "KEY" fields instead of "key".

Expected output (as in the old version):

CREATE TABLE AccountByEmailWithRole (AccountId BYTES(16) NOT NULL, key STRING(MAX) NOT NULL, value BYTES(16) NOT NULL) PRIMARY KEY (AccountId ASC, key ASC);

CREATE UNIQUE INDEX AccountByEmailWithRole_Key ON AccountByEmailWithRole (key ASC);

I did a bisection and this change was introduced in commit 5a72ed3

[iquadrat]

The problem seems to be that now the code always uses ASTTreeUtils.tokensToString which does not distinguish between column names and keywords.

[nielm]

It is impossible to fix this fully, as this tool does not include a full SQL parser/validator...

#60 will include a partial fix which preserves case of column names in most of the places where they are used in DDL.

However there will always be cases where reserved words will be still capitalized -- such as in the SQL expressions in CHECK constraints and generated columns.

The workaround is to quote the column name in backticks: `key`

[nielm]

#67 fixes the second part of this -- SQL expressions no longer uppercase reserved words.

This does mean that if there is a difference in case between old and new SQL expressions (check constraints, generated columns) -- eg AND vs and then this tool will see them as different.

[iquadrat]

Thanks! I am not sure about the workaround.. I have tried this but seems then it will also add the backticks to the column names.