MySQL 8.0.19+ ... AS alias ... ON DUPLICATE KEY syntax parsing problem

[jessepeterson]

Version

1.22.0

What happened?

MySQL 8.0.19 deprecated the former ON DUPLICATE KEY syntax for the newer column reference style but sqlc doesn't seem to like it.

Note the old style VALUES() is now deprecated:

The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.

Relevant log output

$ sqlc generate
# package sqlc
query.sql:20:24: syntax error near "as new"

Database schema

CREATE TABLE dep_names (
  mykey VARCHAR(255) NOT NULL,
  a VARCHAR(255) NULL,
  b VARCHAR(255) NULL,
  c VARCHAR(255) NULL,
  d VARCHAR(255) NULL,
  e VARCHAR(255) NULL,
  PRIMARY KEY (mykey)
);

SQL queries

-- name: TestInsert :exec
INSERT INTO dep_names 
  (mykey, a, b, c, d, e)
VALUES 
  (?, ?, ?, ?, ?, ?) as new
ON DUPLICATE KEY UPDATE 
  a = new.a,
  b = new.b,
  c = new.c,
  d = new.d,
  e = new.e;

Configuration

version: 2
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "sqlc"
        out: "sqlc"

Playground URL

https://play.sqlc.dev/p/99bd4e6529ffaae574dacb08c653ada803cc5c95a4a42e795e4a39cb039a4a47

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[orisano]

The mysql engine relies on the tidb parser. Currently it does not work because tidb does not support that syntax.

https://github.com/pingcap/tidb/issues/29259

[Jille]

@SnoozeThis https://github.com/pingcap/tidb/issues/29259

[SnoozeThis]

(https://snoozeth.is/xBlBhXIndU8) I will wait until https://github.com/pingcap/tidb/issues/29259 is closed and then add a comment.