MySQL `JSON_TABLE` not supported

[sgielen]

Version

1.25.0

What happened?

A migrations file using the MySQL JSON_TABLE function cannot be parsed by sqlc. (Documentation for MySQL, MariaDB)

Relevant log output

# package 
migrations/2.sql:8:19: syntax error near "(p.emails, '$[*]' COLUMNS(email TEXT PATH '$')) AS pe;"

Database schema

-- migrations/1.sql
CREATE TABLE person (
  emails TEXT NOT NULL
);

-- migrations/2.sql
CREATE TABLE person_email (
  email TEXT NOT NULL
);

INSERT INTO person_email (`email`)
  SELECT pe.email
  FROM person AS p
  JOIN JSON_TABLE(p.emails, '$[*]' COLUMNS(email TEXT PATH '$')) AS pe;

SQL queries

-- name: GetEmails :many
SELECT email FROM person_email;

Configuration

version: "2"
sql:
- engine: "mysql"
  queries: "queries.sql"
  schema: "migrations"
  gen:
    go:
      out: "gendb"

Playground URL

https://play.sqlc.dev/p/0bed956ccb54908516d6a6f70813e4cf1182bb978b548689034de7813eb7efce

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[sgielen]

Since this is a INSERT INTO (...) SELECT query which isn't relevant for the schema, it would also be an acceptable workaround if we could simply allow errors in unsupported queries (#1756) or perhaps allowing sqlc generate to ignore the statement completely, e.g. with an -- sqlc:ignore line above it or perhaps by excluding the file altogether in sqlc.yaml.

[sgielen]

Filed #3130 to allow for a work-around while this is being investigated further.