[FORMATTING] Field named "password" converted to uppercase when "keywordCase" is "upper"

[vitorwdson]

Input data

Which SQL and options did you provide as input?

select
    id, username, password, email
from
    users
where
    username = 'foo' and password = 'bar';

Expected Output

SELECT
    id,
    username,
    password,
    email
FROM
    users
WHERE
    username = 'foo'
    AND password = 'bar';

Actual Output

SELECT
    id,
    username,
    PASSWORD,
    email
FROM
    users
WHERE
    username = 'foo'
    AND PASSWORD = 'bar';

Usage

• How are you calling / using the library?

sql-formatter -c ./sql-formatter.json

And the config is this

{
  "language": "postgresql",
  "tabWidth": 4,
  "keywordCase": "upper",
  "dataTypeCase": "upper",
  "identifierCase": "lower",
  "linesBetweenQueries": 2
}

• What SQL language(s) does this apply to?
  PostgreSQL

• Which SQL Formatter version are you using?
  15.1.3

[nene]

Thanks for reporting.

The PostgreSQL keywords list currently contains too many non-reserved keywords, like PASSWORD. Should likely remove most of them. There's a similar problem with DB2 and DB2i dialects. But with PostgeSQL it should be simpler to fix.

[nene]

Related to #156

[nene]

Fixed and released in 15.2.0

[karlhorky]

@nene this is now causing the following, is this expected?

CREATE TABLE animals (
  id integer PRIMARY key generated always AS identity
);

Config (attempt to match style of official PostgreSQL docs):

{
  language: 'postgresql',
  keywordCase: 'upper',
  identifierCase: 'lower',
  dataTypeCase: 'lower',
  functionCase: 'lower',
}

This is an unusual formatting of KEY, GENERATED, ALWAYS, and IDENTITY (these are converted to lowercase with the config above)

Before this change, the formatting resulted in the common style in the ecosystem:

CREATE TABLE animals (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY
);

[nene]

Yep, that's an unfortunate side-effect of this change. Basically there are two choices in SQL Formatter:

• Detect too many words as keywords and end up uppercasing column names with keywordCase: upper.
• Detect too few words as keywords and end up not uppercasing these keywords with keywordCase: upper.

For most people, who want the keywords to be uppercased, the first one is more of a problem than the latter. The latter becomes a larger issue when you use the identifierCase: lower option, which forces all non-keywords to lowercase. That's also the reason I've labeled this option as "experimantal".

I could implement some patches to detect sequences like PRIMARY KEY and GENERATED ALWAYS AS IDENTITY. But these would be just band-aid fixes on top of the fundamental underlying problem.

[karlhorky]

Makes sense, definitely!

If you think it's ok to add some sequences, I will help testing the sequences (and proposing any other sequences)

[nene]

If you're interested in making a pull request, it's pretty much just a matter of adding these sequences to the reservedPhrases array.

[karlhorky]

ah ok that looks not so scary, opening a PR now

[karlhorky]

@nene PR open:

• Add primary key, generated column reserved phrases #714