SELECT FROM VALUES incorrectly throws an error about undefined values

[KnorpelSenf]

Bug Repro

There seems to be a bug in the way the SQL is composed via template strings. Consider the following code:

async function repro() {
    const design = 'd203e218-ea68-46c4-a7c5-a99986062931';
    rows = [{ pos: 0, stone: 'fcc6fdb8-8b7d-47ee-9c7e-891e68718d43', color: 1 }];
    await sql`
      WITH ${makeCheckpointCte(design)}
      INSERT INTO palette (design, checkpoint, pos, stone, color)
      SELECT 
        ${design},
        (SELECT checkpoint FROM next_checkpoint),
        input.pos,
        input.stone,
        input.color
      FROM (VALUES ${sql(rows, 'pos', 'stone', 'color')})
        AS input (pos, stone, color)`;
}
function makeCheckpointCte(design: string) {
  return sql`
    current_checkpoint AS (
      SELECT COALESCE(MAX(checkpoint), -1) AS checkpoint
        FROM design_checkpoints
        WHERE design = (${design})
    ), next_checkpoint AS (
      INSERT INTO design_checkpoints (design, checkpoint)
      SELECT (${design}), 1 + current.checkpoint
        FROM current_checkpoint current
      RETURNING checkpoint
    )`;
}

The line that does FROM (VALUES (...)) causes the following crash.

Error: UNDEFINED_VALUE: Undefined values are not allowed
    at handleValue (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:83:20)
    at stringifyValue (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:113:5)
    at /workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:125:7
    at Array.map (<anonymous>)
    at /workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:124:19
    at Array.map (<anonymous>)
    at valuesBuilder (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:123:16)
    at Object.values [as fn] (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:133:10)
    at Builder.build (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:71:17)
    at stringifyValue (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:109:38)
    at cachedError (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/query.js:170:23)
    at new Query (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/query.js:36:24)
    at sql (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/index.js:112:11)
    at Object.updatePalette (/workspaces/ddt-project-manager/target/src/server/database.js:94:19) {
  code: 'UNDEFINED_VALUE'
}

The SQL I used is close to what the SQL would be without this library. However, I had a suspicion that VALUES should perhaps be dropped. However, doing that only causes a different error.

With the line

      FROM (${sql(rows, 'pos', 'stone', 'color')})

the error becomes

TypeError: str.replace is not a function
    at escape (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:217:20)
    at /workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:213:22
    at Array.map (<anonymous>)
    at escapeIdentifiers (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:213:13)
    at Object.select [as fn] (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:139:12)
    at Builder.build (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:71:17)
    at stringifyValue (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:109:38)
    at stringify (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/types.js:100:16)
    at build (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/connection.js:223:20)
    at Object.execute (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/connection.js:167:7)
    at cachedError (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/query.js:170:23)
    at new Query (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/query.js:36:24)
    at sql (/workspaces/ddt-project-manager/node_modules/.pnpm/postgres@3.4.7/node_modules/postgres/cjs/src/index.js:112:11)
    at Object.updatePalette (/workspaces/ddt-project-manager/target/src/server/database.js:94:19)

so apparently this is not even caught as bad library usage.

Dropping the parenthesis around the interpolated values changes nothing.

Version Info

$ cat package.json | jq .dependencies.postgres
"3.4.7"
$ pnpm -v
10.11.0
$ node -v
v22.16.0

Related Issues

I have only found #1042 to be potentially related.

[KnorpelSenf]

The only workaround I have found so far is to construct a raw SQL from from all pieces, and send it via sql.unsafe. Required rolling my own sanitization.

[Sebbones]

@KnorpelSenf

I found another workaround. Passing the values as an array seems to work:

MERGE INTO "users"
USING (VALUES ${sql(users.map((user) => [user.id, user.name]))})
          AS data(user_id, user_name)
ON
....

Not very pretty but it works for me and no raw SQL constructing is needed :)

[KnorpelSenf]

Funny you should mention that, I discovered the same workaround yesterday but didn't get around to posting it here. Thanks for sharing!

[KnorpelSenf]

Please review my attempt to fix this bug: #1165