[MySQL] Incorrect placement of CTEs in INSERT/REPLACE INTO ... SELECT queries

In MySQL syntax, CTEs in an INSERT/REPLACE INTO ... SELECT need to be placed immediately before the SELECT part of the query. However, these types queries produced by Kysely are invalid:

const person = await db
  .with('cte', db => db.selectFrom('pet')
    .select('name'))
  .replaceInto('person')
  .columns(['first_name', 'last_name', 'age'])
  .expression(eb => eb.selectFrom('cte')
    .select(eb => ['cte.name', eb.lit(null), eb.lit(1)]))
  .execute();

Results in:

WITH
  `cte` AS (
    SELECT
      `name`
    FROM
      `pet`
  ) # invalid syntax, WITH should be before select
REPLACE INTO
  `person` (`first_name`, `last_name`, `age`)
SELECT
  `cte`.`name`,
  NULL,
  1
FROM
  `cte`

The expected syntax is

REPLACE INTO
  `person` (`first_name`, `last_name`, `age`)
WITH
  `cte` AS (
    SELECT
      `name`
    FROM
      `pet`
  )
SELECT
  `cte`.`name`,
  NULL,
  1
FROM
  `cte`

From the MySQL reference manual:

A WITH clause is permitted in these contexts: [...]

Immediately preceding SELECT for statements that include a SELECT statement

The same syntax error also occurs when using insertInto() instead of replaceInto(). However, it appears explain() correctly adds the CTEs before SELECT.

kysely-org / kysely

[MySQL] Incorrect placement of CTEs in INSERT/REPLACE INTO ... SELECT queries #928