Bug: Different results on first and second query using sql.array

[qpwo]

I might be doing something wrong, but

const sql = postgres(PG_CONFIG)
// undefined
const rows = [[1, [2,3]], [4, [5,6]], [7, [8,9]]]
// undefined
const formattedRows = sql(rows.map(([num,pair])=>[num,sql.array(pair)]))
// undefined
await sql`select num, pair from (VALUES ${formattedRows} ) as x(num, pair)`
/*
Result(3) [
  { num: '1', pair: '2,3' },
  { num: '4', pair: '5,6' },
  { num: '7', pair: '8,9' }
]
*/
await sql`select num, pair from (VALUES ${formattedRows} ) as x(num, pair)`
/*
Result(3) [
  { num: '1', pair: [ '2', '3' ] },
  { num: '4', pair: [ '5', '6' ] },
  { num: '7', pair: [ '8', '9' ] }
]
*/

The same thing happens if I inline my formattedRows

[qpwo]

Simplified:

> const rows = [[1, [2,3]], [4, [5,6]], [7, [8,9]]]
> await  sql`VALUES ${sql(rows.map(([num,pair])=>[num,sql.array(pair)]))}`.values()
Result(3) [ [ '1', '2,3' ], [ '4', '5,6' ], [ '7', '8,9' ] ]
> await  sql`VALUES ${sql(rows.map(([num,pair])=>[num,sql.array(pair)]))}`.values()
Result(3) [
  [ '1', [ '2', '3' ] ],
  [ '4', [ '5', '6' ] ],
  [ '7', [ '8', '9' ] ]
]
> 

[qpwo]

Is this due to the statement being unprepared in the first case but prepared in the second? That would surprise me because the statement is inlined in both cases. But it is the only explanation I can think of... I'm not sure if this bug would happen if you were e.g. inserting into a table where the types are not ambiguous.

[qpwo]

I have a more general question slightly relating to this bug, is there any difference in behavior between inline and separately assigned query helpers?

const columns = ['name', 'age']
// in-line helper:
await sql`select ${ sql(columns) } from users`
// assigned helper:
const sqlColumns = sql(columns)
await sql`select ${ sqlColumns } from users`

[porsager]

Uh very nice catch! It's not related to prepared statements, but to the type fetching done på Postgres.js to aid in array type detection. sql.array is called before the type info is available in the first query, but then in the next it's available. I'm a bit hung up these days, so I'll probably first be able to look closer next week. What is your desired output? sql.array shouldn't be necessary to use since v3 but it seems I've overlooked some edge cases related to it.

[porsager]

Wrt. the last question, there is no difference.

[qpwo]

What is your desired output?

I'm trying to insert many rows where one of the columns in each row is an array. Is there a better way to do this?

[porsager]

Wow, sorry for not responding here. There should be no issue when you're doing inserts. The issue above was with the specific use of sql.array and the very first query (before array types are fetched). sql.array is deprecated, so I'm gonna close this out and mark as won't fix.

You're welcome to reopen if there's an issue with inserts.