zvonimir-dd opened a new issue, #2778:
URL: https://github.com/apache/datafusion-sqlparser-rs/issues/2778
### Summary
PostgreSQL's row-locking clause has four lock strengths: `FOR UPDATE`, `FOR
NO KEY UPDATE`, `FOR SHARE` and `FOR KEY SHARE`. sqlparser reads only `FOR
UPDATE` and `FOR SHARE`. `FOR NO KEY UPDATE` and `FOR KEY SHARE`, which
PostgreSQL's own referential-integrity triggers use (`SELECT 1 FROM ONLY t x
WHERE … FOR KEY SHARE OF x`), are rejected.
### Reproduction (current `main`, c6cec56b)
```rust
use sqlparser::{dialect::PostgreSqlDialect, parser::Parser};
Parser::parse_sql(&PostgreSqlDialect {}, "SELECT id FROM t WHERE id = 1 FOR
NO KEY UPDATE").unwrap();
Parser::parse_sql(&PostgreSqlDialect {}, "SELECT id FROM t WHERE id = 1 FOR
KEY SHARE OF t").unwrap();
```
```
sql parser error: Expected: one of UPDATE or SHARE, found: NO at Line: 1,
Column: 35
sql parser error: Expected: one of UPDATE or SHARE, found: KEY at Line: 1,
Column: 35
```
`SELECT id FROM t WHERE id = 1 FOR SHARE OF t SKIP LOCKED` parses. All of
these run on PostgreSQL (checked on 17.11):
```sql
CREATE TABLE t (id int PRIMARY KEY);
SELECT id FROM t WHERE id = 1 FOR NO KEY UPDATE;
SELECT id FROM t WHERE id = 1 FOR KEY SHARE OF t;
SELECT id FROM t FOR KEY SHARE NOWAIT;
SELECT id FROM t FOR NO KEY UPDATE SKIP LOCKED;
```
### Grammar
From
https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE:
```
FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ]
where lock_strength can be one of
UPDATE
NO KEY UPDATE
SHARE
KEY SHARE
```
### Proposed change
Add two variants to `LockType`, for example `NoKeyUpdate` and `KeyShare`,
displayed as `NO KEY UPDATE` and `KEY SHARE`. Parse them in `parse_lock`, where
`FOR` is followed by `NO KEY UPDATE` or `KEY SHARE`, in the PostgreSQL and
generic dialects. `OF …`, `NOWAIT` and `SKIP LOCKED` then follow as they do for
the other two strengths. Round-trip tests for each strength, with and without
`OF` and the wait policy, would cover it.
This is independent of `FROM ONLY t`, which the same trigger queries use and
#2770 addresses.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
