GeezerVison

Purpose

PostgreSQL 18 accepts several forms of UPDATE, DELETE and MERGE that the parser rejects, in the PostgreSQL dialect and without a dialect:

UPDATE ONLY t AS x SET a = 1;                       -- relation_expr_opt_alias
DELETE FROM t * x USING u WHERE x.a = u.a;
MERGE INTO ONLY (t) x USING s ON x.id = s.id WHEN MATCHED THEN DELETE;

UPDATE t SET a = 1 WHERE CURRENT OF c;               -- where_or_current_clause
DELETE FROM t WHERE CURRENT OF c RETURNING *;

UPDATE t SET a = 1, (b, c) = ROW(2, DEFAULT);        -- set_clause

MERGE INTO t USING s ON t.id = s.id
  WHEN MATCHED AND t.a > 1 THEN DO NOTHING           -- merge_when_clause
  WHEN NOT MATCHED BY SOURCE THEN DO NOTHING
  WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;       -- merge_insert
MERGE INTO t USING s ON t.id = s.id
  WHEN NOT MATCHED THEN INSERT (id, a) OVERRIDING SYSTEM VALUE VALUES (1, 2);

This change parses them, following gram.y of PostgreSQL 18 (UPDATE, DELETE, MERGE).

Current design (As-is)

• Update, Delete and Merge hold the target as a Table. ONLY is refused as a table name in front of another name (RelationOptions.isOnlyAhead), and no production reads it as a keyword there, so UPDATE ONLY t …, DELETE FROM ONLY t … and MERGE INTO ONLY t … fail. t * fails as well. LOCK and TRUNCATE already share RelationTarget() and TableReference for the same PostgreSQL relation_expr.
• WHERE is always an expression, so WHERE CURRENT OF c fails at OF.
• UpdateSets() writes the two alternatives of an assignment twice (first item, following items). A column list takes a parenthesized list or a sub-select only, so (a, b) = ROW(1, 2) fails.
• MergeDelete, MergeUpdate and MergeInsert each write their WHEN … prefix, in toString() and again in MergeDeParser (six copies). There is no DO NOTHING action, and MergeInsertClause() requires VALUES (…) directly after the optional column list.

Target design (To-be)

Target (relation_expr_opt_alias). A DmlTarget() production is shared by UPDATE, DELETE and MERGE. With ONLY in front of a name, or * after it, it reuses RelationTarget() and reads the alias after it; otherwise it is TableWithAlias() as before.

• Update, Delete and Merge keep the target in a TableReference (getTableReference() / setTableReference() / withTableReference()). getTable() and setTable() read and replace the table of that reference, so there is one source for the table.
• TableReference.appendWithAliasTo() renders ONLY t AS x, ONLY (t) x and t * AS x; toString() and the deparsers both use it. A target without a scope renders through Table as before, with its alias and hints.
• MySQL and MariaDB have no ONLY there, so in those dialects the forms stay as they are on master.

WHERE CURRENT OF cursor. Update and Delete get a cursorName (getCursorName() / setCursorName() / withCursorName()), read by a shared WhereCurrentOfClause(). The cursor is a name (ColId), not an expression: getWhere() stays null, and WHERE CURRENT OF c AND … or a qualified cursor name is rejected as in PostgreSQL. The positioned forms are standard SQL and are read in every dialect; two Oracle corpus cases (condition17.sql, condition18.sql) now parse.

SET items (set_clause). UpdateSets() is a list of one UpdateSet() production. A column list takes any expression as its source, as gram.y does ('(' set_target_list ')' '=' a_expr): ROW(…), DEFAULT, a function call. The expression is the single element of UpdateSet.getValues(), in the way a sub-select already is; (a, b) = (1, 2) keeps one value per column. ROW(…) is the Function it is in other expressions (a = ROW(1, 2)).

MERGE.

• MergeWhen (MATCHED, NOT_MATCHED, NOT_MATCHED_BY_SOURCE, NOT_MATCHED_BY_TARGET) renders the WHEN … [AND …] THEN prefix once for toString() and the deparser. MergeDelete, MergeUpdate and MergeInsert derive getWhen() from their existing side.
• MergeDoNothing is the new action for all four conditions. MergeOperationVisitor gets a default visit(MergeDoNothing, …) so existing visitors compile and skip it; MergeOperationVisitorAdapter, MergeDeParser, MergeValidator and TablesNamesFinder visit its predicate.
• MergeInsert reads INSERT DEFAULT VALUES (isDefaultValues(): an insert without a values list) and OVERRIDING {SYSTEM | USER} VALUE (getOverridingMode(), the enum of Insert). As in gram.y, DEFAULT VALUES takes neither a column list nor OVERRIDING.
• Oracle's WHERE / DELETE WHERE clauses, SQL Server's OUTPUT and the BY SOURCE / BY TARGET pairing checks are unchanged.

Rejected, as by PostgreSQL: UPDATE ONLY t * …, UPDATE (t) …, DELETE FROM t AS x * …, WHERE CURRENT OF sc.c, SET () = ROW(1, 2), SET (a + 1) = ROW(1), WHEN MATCHED THEN INSERT DEFAULT VALUES, INSERT (id) DEFAULT VALUES, INSERT OVERRIDING SYSTEM VALUE DEFAULT VALUES, THEN DO NOTHING WHERE ….

Not part of this change: ONLY in a USING or FROM source (MERGE INTO t USING ONLY s …), which fails on master as well.

Validation

• New PostgreSQLModifyFormsTest: 157 tests (targets, cursor forms, SET sources, MERGE clauses; model fields, toString(), StatementDeParser, reparse, rejected forms, MySQL/MariaDB/Oracle/SQL Server/SQLite readings, statement boundaries, visitors, TablesNamesFinder, validation).

• ./gradlew spotlessApply cleanTest check jar: 13,491 tests, 0 failures (25 skipped), including checkGrammarAmbiguity, Checkstyle, PMD, SpotBugs and JaCoCo.

• Compared with master (e27929f76) on a corpus of PostgreSQL 18 and MySQL 8.4 statements (DDL/DCL and other statements, parsed with the dialect and without): 7 PostgreSQL statements that failed now parse and round-trip (UPDATE ONLY … AS, UPDATE … WHERE CURRENT OF, UPDATE … SET (…) = ROW(…), DELETE FROM ONLY … AS, DELETE … WHERE CURRENT OF, MERGE … DO NOTHING, MERGE … INSERT DEFAULT VALUES), in both modes; no regressions and no other changes.

• PostgreSQL 18.6 accepts the syntax of 104 statements that this branch parses, 82 of which master rejects; some stop only at execution, for example with an unknown cursor. It reports a syntax error for 59 statements that this branch rejects. Eight statements differ:

  • Six that PostgreSQL rejects parse here as on master: DELETE FROM only (a table named only, valid in MySQL), UPDATE t SET a = DEFAULT + 1 (gram.y accepts it), MERGE without a WHEN clause, and the three Oracle forms INSERT … VALUES (…) WHERE …, UPDATE SET … WHERE … and UPDATE SET … DELETE WHERE ….
  • MERGE … WHEN MATCHED THEN DO NOTHING WHEN MATCHED THEN DELETE parses: gram.y accepts it and PostgreSQL reports the unreachable second clause after parsing. The same clauses with a condition on the first one are accepted by the server.
  • MERGE INTO t x USING ONLY s … is accepted by PostgreSQL and fails here as on master (see above).

AI assistance: prepared with Claude Code.