search

tconbeer / sqlfmt

sqlfmt formats your dbt SQL files so you don't have to
https://sqlfmt.com
Apache License 2.0
373 stars 15 forks source link

Parsing error on valid SQL using string literals with single quotes #553

Open albertsgrc opened 6 months ago

albertsgrc commented 6 months ago

Describe the bug sqlfmt fails to parse valid SQL when using string literals that contain single quotes.

To Reproduce

select '''' || 'quoted_text' || ''''

Expected behavior sqlfmt runs without errors.

Actual behavior

1 file had errors while formatting.
0 files left unchanged.
test.sql
    sqlfmt encountered an error: Could not parse SQL at position 35: '''

Additional context Reproducible from https://sqlfmt.com/

These two are formatted without errors by sqlfmt, so it's only some specific combinations that break the parsing:

select '''quoted_text'''
select '''' || 'quoted_text'
tconbeer commented 6 months ago

My guess is we're parsing this as a triple-quoted expression followed by a single quote. We get this right (postgres dollar sign quotes):

select $$'$$ || 'quoted_text' || $$'$$

I thought triple quotes for string literals were more common, but a quick search of pg, mysql, sql server, snowflake, and bq shows that only bigquery allows them. maybe we should pull those out into a bq-specific dialect.