Table name containing the restricted keyword "case" triggers an error

[matthieucan]

Describe the bug

The following SQL query:

with foo as (
  select 1 from database.schema.case
)
select * from foo;

while valid (at least in Snowflake SQL), will make sqlfmt error with:

sqlfmt encountered an error: Closing bracket ')' found at 51 does not match last opened bracket 'case' found at 46.

It seems to be because the table name case is also a reserved SQL keyword - but nonetheless allowed as a table name for certain databases. Interestingly, it only happens in a CTE, it works when running on the simpler

select 1 from database.schema.case;

To Reproduce

See above.

Expected behavior

Formatting/checking does not raise an error.

Actual behavior

See above.

Additional context

$ sqlfmt --version
sqlfmt, version 0.21.3

What is the output of pip list (or pipx list if you installed using pipx)?

n/a, let me know if that's needed.

[tconbeer]

Thanks for the report; would need to update the parser logic to support this.

The workaround is to quote it (which seems like a good idea anyway:

with foo as (
  select 1 from database.schema."case"
)
select * from foo;

[matthieucan]

Indeed! However I found this bug in SQL auto-generated by dbt: https://github.com/dbt-labs/dbt-utils?tab=readme-ov-file#recency-source which makes it hard to add quotes without changing the implementation upstream.

It is not blocking me however, as I do have workarounds. Thanks for the quick reply and the great tool!

[tconbeer]

I suggest having sqlfmt ignore your target directory and other generated code. Docs for that are here:
https://docs.sqlfmt.com/integrations/dbt

[tconbeer]

Just looked into this a little; Postgres, along with the '23, '16, and '92 ANSI standards consider case a reserved keyword. What they don't specify this that your code totally runs on Postgres:

create table "case" as select 1;
with foo as (
  select * from public.case
)
select * from foo;

Even select, with, and other very-reserved words work in place of case.

MySQL also runs your code, and they are explicit that you can use reserved keywords without quoting if they are qualified identifiers (preceded with a period). Snowflake seems unusual in its permissiveness with keywords.

So obviously this doesn't just impact snowflake and case -- need to re-think tokenizing any keywords and first check if they a preceded by a period.