cockroachdb / cockroachdb/cockroach

sql/parser: disallow identifiers after placeholders without whitespace

Open
#112,039 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement E-quick-win T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.