Using $action in tsql merge causes crash

[rmja]

Describe the bug
When using $action in a MERGE in tsql, see https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16#action, then the formatter craches.

For example, consider this:

MERGE INTO
    DestinationTable WITH (HOLDLOCK)
AS Target
USING
    SourceTable
AS Source
    ON Target.Id = Source.Id
WHEN NOT MATCHED THEN
    INSERT (
        Name,
        Updated,
        Created
    )
    VALUES (
        Source.Name,
        GETUTCDATE(),
        GETUTCDATE()
    )
WHEN MATCHED THEN
    UPDATE SET
        Target.Name = Source.Name,
        Target.Updated = Source.Updated
OUTPUT
    COALESCE(Inserted.Id, Source.Id) AS SourceId,
    $action AS Action
;

Expected behavior
That the script is being formatted

Actual behavior
This currently emits the error Error: Parse error: Unexpected "$action AS" at line 25 column 5.:

[error] testmerge.sql: Error: Parse error: Unexpected "$action AS" at line 25 column 5.
[error] SQL dialect used: "transactsql".
[error]     at TokenizerEngine.createParseError (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:15873:12)
[error]     at TokenizerEngine.tokenize (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:15863:22)
[error]     at Tokenizer.tokenize (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:15983:65)
[error]     at LexerAdapter.tokenize (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:16826:37)
[error]     at LexerAdapter.reset (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:16361:24)
[error]     at Parser.feed (C:\Source\Utiliread\node_modules\nearley\lib\nearley.js:281:15)
[error]     at Object.parse (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:16833:34)
[error]     at Formatter.parse (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:17542:49)
[error]     at Formatter.format (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:17537:22)
[error]     at formatDialect (file:///C:/Source/Test123/node_modules/sql-formatter/dist/index.js:17660:57)

Usage

• How are you calling / using the library? Using prettier-plugin-sql
• What SQL language(s) does this apply to? tsql
• Which SQL Formatter version are you using? 15.0.2

[nene]

So, if I understand it correctly, it's this special word $action that can be used inside the MERGE statement. Not something like $foo. Could one use $ACTION?

[nene]

A quick workaround is to use the paramTypes option:

paramTypes: { named: ['@', '$'] }

[rmja]

Yes, it is not case sensitive. It works equally well with $ACTION. I tried calling it $action2. Then sql server produces the following error Invalid pseudocolumn "$action2". So, I guess $action is called a pseudocolumn.

[nene]

So I implemented a fix for this, by just including $ACTION to list of keywords. This also means that when keywordCase:upper is used, it'll be converted to uppercase. Alternatively it could instead be treated as a special identifier. I'm not really sure which way would be better.

At least it doesn't cause a crash any more :)

[nene]

Released the fix in 15.4.8

[rmja]

Oh my god. You are awesome. Thank you for this!