Problem executing Common Table Expression (WITH query)

[aplatoff]

Hello,

Driver fails to execute WITH query as below:

    const translation = [["en", "My Label"], ["ru", "Моя Метка"]]
    console.log(translation)

    const id = (await sql`
      with t as (
        insert into i18n.text default values returning id
      ) insert into i18n.translation (id, lang, text)
        select id, lang, text from t
          cross join (values ${sql(translation)}) as l(lang, text)
      returning id`)[0].id

    return id

Execution log:

❯ bun run src/index.ts
[
  [ "en", "My Label" ], [ "ru", "Моя Метка" ]
]
212 | function escapeIdentifiers(xs, { transform: { column } }) {
213 |   return xs.map(x => escapeIdentifier(column.to ? column.to(x) : x)).join(',')
214 | }
215 |
216 | export const escapeIdentifier = function escape(str) {
217 |   return '"' + str.replace(/"/g, '""').replace(/\./g, '"."') + '"'
                         ^
TypeError: str.replace is not a function. (In 'str.replace(/"/g, '""')', 'str.replace' is undefined)
      at escape (/Users/andrey/work/datalake/node_modules/postgres/src/types.js:217:20)
      at map (:1:11)
      at escapeIdentifiers (/Users/andrey/work/datalake/node_modules/postgres/src/types.js:220:8)
      at stringify (/Users/andrey/work/datalake/node_modules/postgres/src/types.js:308:16)
      at build (/Users/andrey/work/datalake/node_modules/postgres/src/connection.js:389:16)
      at execute (/Users/andrey/work/datalake/node_modules/postgres/src/connection.js:318:18)
      at ReadyForQuery (/Users/andrey/work/datalake/node_modules/postgres/src/connection.js:881:23)
      at handle (/Users/andrey/work/datalake/node_modules/postgres/src/connection.js:807:5)
      at data (/Users/andrey/work/datalake/node_modules/postgres/src/connection.js:559:28)
      at emit (node:events:180:48)
      at addChunk (node:stream:2029:22)
      at readableAddChunk (node:stream:1983:30)
      at data (node:net:76:9)
      at cachedError (/Users/andrey/work/datalake/node_modules/postgres/src/query.js:170:23)
      at new Query (/Users/andrey/work/datalake/node_modules/postgres/src/query.js:34:10)
      at sql (/Users/andrey/work/datalake/node_modules/postgres/src/index.js:210:8)
      at /Users/andrey/work/datalake/packages/proto/src/api.ts:39:23
      at cachedError (/Users/andrey/work/datalake/node_modules/postgres/src/query.js:170:23)
      at new Query (/Users/andrey/work/datalake/node_modules/postgres/src/query.js:34:10)
      at sql (/Users/andrey/work/datalake/node_modules/postgres/src/index.js:210:8)
      at /Users/andrey/work/datalake/packages/proto/src/api.ts:39:23

Bun v1.1.20 (macOS arm64)

Am I use driver properly? Query works well if executed in cockroachdb console. Also can be bun related, I did not checked with other runtimes.

[aplatoff]

When I split into 2 queries everything works well, but it would be nice to use WITH queries in many cases:

    const id = (await sql`insert into i18n.text default values returning id`)[0].id
    const translation = Object.entries(label).map((label) => [id, ...label])
    await sql`insert into i18n.translation values ${sql(translation)}`

[Louis-Tian]

I can reproduce this with NodeJS runtime. Definitely looks like a bug to me.
I was able to simplify it down to something like

  await sql`
    with t as (
      insert into foo values (1, 2)
    )
    select * from (values ${sql(translation)}) as t(lang, text)
  `;

and it will still produce the same error.

It seems to happen whenever you do have a CTE with an insert and followed by a dynamic "values" of nested array. My guess is that the parser is mistreating the dynamic value as part of the insert statement.

[Louis-Tian]

In the mean time, you can try to lift the dynamic values bit before the insert, that might get you around the problem. Something like

  with x as (
    select * from (values ${sql(translation)}) as l(lang, text)
  ),
  t as (
      insert into i18n.text default values returning id
  ) 
  insert into i18n.translation (id, lang, text)
    select id, lang, text from t cross join x
    returning id