sql-formatter-org / sql-formatter

A whitespace formatter for different query languages
https://sql-formatter-org.github.io/sql-formatter/
MIT License
2.37k stars 405 forks source link

Snowflake Colon (:) Prefix Causes Error #778

Closed chiefupstart closed 2 months ago

chiefupstart commented 2 months ago

Describe the bug In Snowflake, a (:) prefix is used for binding variables in SQL statements like:

SELECT *
FROM conform.DimSeas as s
WHERE s.SeaOrganizationIdentifierSea ='32'
   AND :BATCH_PERIOD_LIST in ('ALL','ANY') or 'CURRENT' in (select VALUE from table(split_to_table(:BATCH_PERIOD_LIST,',')))
   AND :SAID_LIST in ('ALL','ANY') or 'IMART' in (select VALUE from table(split_to_table(:SAID_LIST,',')));
 ;

Sql-formatter will throw an error on the lines with :BATCH_PERIOD_LIST, :SAID_LIST, etc.

Expected behavior I expected sql-formatter to parse the SQL.

Actual behavior

Parse error at token: : at line 19 column 32
Unexpected PROPERTY_ACCESS_OPERATOR token: {"type":"PROPERTY_ACCESS_OPERATOR","raw":":","text":":","start":587}. Instead, I was expecting to see one of the following:
A ")" based on: ...
A ASTERISK token based on: ...
A LIMIT token based on: ...
Etc...

Usage

nene commented 2 months ago

Use the paramTypes config option to define the type of variable binding syntax you're using.