cockroachdb / cockroachdb/cockroach
sql/parser: disallow identifiers after placeholders without whitespace
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
This is related to #111037.
We currently allow identifiers after placeholders without separation. For example:
```
defaultdb> PREPARE p(INT) AS SELECT $1a;
PREPARE
```
This matched Postgres's behavior up until Postgres 15 (see the commit where this change was made: https://github.com/postgres/postgres/commit/2549f0661bd28571d7200d6f82f752a7ee5d47e1). Now Postgres returns an error:
```
marcus=# PREPARE p(INT) AS SELECT $1a;
ERROR: 42601: trailing junk after parameter at or near "$1a"
LINE 1: PREPARE p(INT) AS SELECT $1a;
^
LOCATION: scanner_yyerror, scan.l:1245
```
We should match this behavior as well.
I did notice a confusing behavior with Postgres, that we should take into consideration. While Postgres and the SQL:2023 spec allow for underscores in numeric literals (see #111038), placeholders cannot have underscores, e.g., `$1_0`. However, rather than result in a syntax error, Postgres seems to ignore everything after an underscore in a placeholder expression. Here's some examples showing the behavior:
```
marcus=# PREPARE p(INT) AS SELECT $1 foo;
PREPARE
marcus=# EXECUTE p(1); -- The column is "foo".
foo
-----
1
(1 row)
marcus=# DEALLOCATE p;
DEALLOCATE
marcus=# PREPARE p(INT) AS SELECT $1_0_0_0;
PREPARE
marcus=# EXECUTE p(1); -- The column is anonymous, rather than "_0_0_0".
?column?
----------
1
(1 row)
```
I think this is likely an oversight and we should produce a syntax error for placeholder expressions with underscores, similar to the error for any other identifier token after a placeholder, to avoid confusion.
Jira issue: CRDB-32206
Contributor guide
Assessment
This issue has not been assessed yet.