[FORMATTING] Identifier 'groups' is treated as a keyword for language dialect sqlite

[linusjf]

Input data

Which SQL and options did you provide as input?

DROP TABLE IF EXISTS groups;
CREATE TABLE groups (group_id INTEGER PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE contacts_groups (
contact_id INTEGER,
group_id INTEGER,
PRIMARY KEY (contact_id, group_id),
FOREIGN KEY (contact_id) REFERENCES contacts (contact_id) ON DELETE CASCADE ON UPDATE NO ACTION,
FOREIGN KEY (group_id) REFERENCES groups (group_id) ON DELETE CASCADE ON UPDATE NO ACTION
);

Expected Output

No change

Actual Output

The identifier groups is capitalised.

Usage

• How are you calling / using the library?
• CLI
• What SQL language(s) does this apply to?
• sqlite
• Which SQL Formatter version are you using?
• 15.4.9

[nene]

Thanks for reporting. Fixed now.

[linusjf]

https://sqlite.org/lang_keywords.html

Apparently, GROUPS is a reserved keyword in SQLite. But I haven't seen any usage for it as yet. I am new to SQLite.

[linusjf]

https://www.sqlite.org/windowfunctions.html
Apparently, there is GROUPS as a window function. I haven't tried it as yet.

[nene]

sqlite.org/lang_keywords.html

Apparently, GROUPS is a reserved keyword in SQLite. But I haven't seen any usage for it as yet. I am new to SQLite.

No. This page just list all the keywords. Some of them are reserved keywords, but not all of them.

You wouldn't be able to use GROUPS as an identifier if it were a reserved keyword.

[linusjf]

sqlite.org/lang_keywords.html
Apparently, GROUPS is a reserved keyword in SQLite. But I haven't seen any usage for it as yet. I am new to SQLite.

No. This page just list all the keywords. Some of them are reserved keywords, but not all of them.

You wouldn't be able to use GROUPS as an identifier if it were a reserved keyword.

https://www.sqlite.org/syntax/frame-spec.html

Apparently, the framespec uses the GROUPS keyword. My bad, apparently. I wrote up the issue in a hurry.

[nene]

No that's fine. I know where it's used.

Because of the architecture of SQL Formatter it's not able to properly distinguish between identifiers and keywords. So it takes the approach of only uppercasing reserved keywords. This means it won't uppercase all keywords. But it's better than misidentifying identifiers as keywords and uppercasing them.

[linusjf]

I can live with using backticks to surround identifiers that are keywords.

[nene]

It's not about you. It's about consistent approach to handling the keywords by the formatter.

I really need to remove quite a few more keywords from the lists that SQL Formatter uses.

[linusjf]

Ok. Do test how the fix handles this.

SELECT
  a,
  b,
  c,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c GROUPS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING EXCLUDE GROUP
  ) AS group_concat
FROM
  t1
ORDER BY
  a;

[linusjf]

I understand that you prefer users move to https://github.com/nene/prettier-plugin-sql-cst since updates are no longer available on sql-formatter. My concerns were two-fold:

1. The new parser is not stable and fully supports only two dialects.
2. Prettier forces the plugin to be installed in each project where it's needed. There is no global install option available.

That said, I've switched to the new parser. I'll update you if I encounter any bugs or issues.

nene/prettier-plugin-sql-cst#36

[nene]

Fixed in 15.4.10