Problem with $p() syntax in SQLite

[andrewbaxter]

Input data

Which SQL and options did you provide as input?

select $p(x=y);

Expected Output

select $p(x=y);

Actual Output

select $p (x=y);

Usage

• How are you calling / using the library?
  cli
• What SQL language(s) does this apply to?
  sqlite
• Which SQL Formatter version are you using? (The exact version number.)
  15.6.10

I'm not 100% sure but I think in the tcl $() param syntax sqlite supports requires the variable name and following parens to be touching (no space). At least, from manual testing in the cli that seemed to be the case.

[nene]

So, what is this $p(x=y) syntax? What does that expression mean?

Best would be if you could point me to SQLite documentation for it.

[andrewbaxter]

Ah sorry, I didn't realize I already submitted this. It's right here: https://sqlite.org/lang_expr.html#parameters

$AAAA A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name $AAAA. The identifier name in this case can include one or more occurrences of "::" and a suffix enclosed in "(...)" containing any text at all. This syntax is the form of a variable name in the Tcl programming language. The presence of this syntax results from the fact that SQLite is really a Tcl extension that has escaped into the wild.

It can be tested with:

sqlite> .param set $a(x=4) '1234'
sqlite> select $a(x=4);
1234

The stuff in the () is meaningless, it's just treated as part of the parameter name in sqlite.

[nene]

Aha... OK, I see.

Looks like the $param syntax is not the same in SQLite as in other SQL dialects. The formatter only supports the $param part.

For now the workaround is to just define your own custom parameter type. Something like:

paramTypes: {
  custom: [{ regex: String.raw`\$[a-zA-Z_0-9]+(::[a-zA-Z_0-9]+)*(\([^)]*\))?` }],
}

[andrewbaxter]

Oh cool, I didn't know about that! I'm really surprised, that's a great feature. Anyway, that works for me.

Should I close this?

[nene]

Let's keep it open. I think I can also fix this in the library itself.

[andrewbaxter]

Oh and sorry, I totally messed up the title too... 🤦 I must be out of it today.