ODBC Date and Time Literals Crash Parser

[jptrosclair]

Describe the bug Try to format a query that uses ODBC date and time literal syntax.

Microsoft - Date, Time, and Timestamp Escape Sequences MariaDB - Date and Time Literals

Reproduce: echo "SELECT {ts '2022-10-11 00:00:00'}" | ./node_modules/.bin/sql-formatter

Expected behavior Correctly format or gracefully handle unexpected syntax instead of crashing

Actual behavior Exception thrown in JS console:

Error: Parse error: Unexpected "{ts '2022-" at line 1 column 3318
    at TokenizerEngine.createParseError (TokenizerEngine.js:53:12)
    at TokenizerEngine.tokenize (TokenizerEngine.js:35:22)
    at Tokenizer.tokenize (Tokenizer.js:16:47)
    at LexerAdapter.tokenize (createParser.js:16:76)
    at LexerAdapter.reset (LexerAdapter.js:17:24)
    at Parser.feed (nearley.js:281:15)
    at Object.parse (createParser.js:26:18)
    at TransactSqlFormatter.parse (Formatter.js:60:49)
    at TransactSqlFormatter.format (Formatter.js:53:22)
    at format (sqlFormatter.js:69:36)

Usage

• How are you calling / using the library? Client side through angular pipe
• What SQL language(s) does this apply to? Any that use ODBC Date and Time literal syntax
• Which SQL Formatter version are you using? 11.0.2

[nene]

Thanks for reporting. This syntax is indeed not supported. It's really the first time I see such syntax.

Turns out the node-sql-parser library also doesn't support this syntax. Apparently it's not particularly widely used, or otherwise this problem surely would have been reported by somebody over the years.

Anyway, it's still a valid bug. We'll see if we can fix it. But it won't be a simple fix to make.

[jptrosclair]

Today is also the first time I've come across this syntax. I understand it's rare and while supporting it would be good, another consideration is adding a way to gracefully handle unexpected syntax. Rather than the having the parser throw an exception treating the unknown characters as just literal text without any special formatting would be a positive improvement, I think.

[nene]

This issue likely won't get fixed in any foreseeable feature as it's such a niche feature of MySQL syntax. However, since 15.1.0 there's now a way to skip the code like this by using disable/enable comments, like so:

/* sql-formatter-disable */
SELECT {ts '2022-10-11 00:00:00'};
/* sql-formatter-enable */

This effectively disables the parsing of this code that would currently cause the formatter to crash. Might be helpful if you only have a few places that use this syntax. Not much of help, when most of your code relies on this.