Support INSERT ... AS row_alias [(col_alias_list)] for ON DUPLICATE KEY UPDATE

[kyleconroy]

Summary

MySQL 8.0.19 added a row alias on the inserted row (INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE col = new.col). It replaces the deprecated VALUES(col) function inside the update list. Marino's InsertIntoStmt (parser/parser.y:7878) has no slot for a row alias, so the new form does not parse.

MySQL version

Introduced in MySQL 8.0.19.

Current state in marino

grep -in 'row_alias\|RowAlias\|InsertRowAlias' parser/parser.y returns no matches. The current grammar only accepts column names from the target table on the right side of ON DUPLICATE KEY UPDATE.

Example SQL

Schema:

CREATE TABLE odk (id INT PRIMARY KEY, v INT);

Row alias only:

INSERT INTO odk (id, v) VALUES (1, 10) AS new
  ON DUPLICATE KEY UPDATE v = new.v;

Row alias with column aliases:

INSERT INTO odk (id, v) VALUES (1, 10) AS new(nid, nv)
  ON DUPLICATE KEY UPDATE v = nv;

INSERT ... SELECT form:

INSERT INTO odk (id, v)
  SELECT id, v FROM staging AS s
  AS new
  ON DUPLICATE KEY UPDATE v = new.v + odk.v;

Validation

All three forms above parse and execute successfully against MySQL 9.2.0 Community.

Notes for the implementer

• Extend the InsertIntoStmt grammar with [ AS Identifier [ '(' ColumnNameList ')' ] ] after the value/select source and before ON DUPLICATE KEY UPDATE.
• AST: add RowAlias *ast.CIStr and ColumnAliases []*ast.CIStr to InsertStmt.
• The aliases must be resolvable inside the ON DUPLICATE KEY UPDATE expressions. Pure parsing doesn't need name resolution, but the AST should preserve the names for downstream consumers.
• Reference: https://dev.mysql.com/doc/refman/9.2/en/insert-on-duplicate.html

[paultyng]

Some additional examples

Should parse

-- SET form with row alias (MySQL 8.0.19+)
INSERT INTO odk SET id = 1, v = 10 AS new
  ON DUPLICATE KEY UPDATE v = new.v;

-- INSERT IGNORE + row alias + ON DUPLICATE KEY UPDATE (MySQL 8.0.19+)
INSERT IGNORE INTO odk (id, v) VALUES (1, 10) AS new
  ON DUPLICATE KEY UPDATE v = new.v;

-- Legacy VALUES(col) function inside ON DUPLICATE KEY UPDATE.
-- Deprecated since MySQL 8.0.20, not removed; must continue to parse
-- so codebases can migrate incrementally.
INSERT INTO odk (id, v) VALUES (1, 10)
  ON DUPLICATE KEY UPDATE v = VALUES(v);

[paultyng]

I'm not sure what the implications are for REPLACE, that probably needs to be investigated a bit.