How are empty parameterized arrays resolved?

[NickSchmitt]

Hello, what SQL is produced when a parameterized array is empty? e.g. what SQL is produced for select * from table where id in ({ idList: Array(String) }); when idList is []?

I ask because the client executes this query without errors, but without the client a NUMBER_OF_ARGUMENTS_DOESNT_MATCH exception is thrown for an empty tuple.

[slvrtrn]

You can check the system.query_log table for that.

For example:

const rs = await client.query({
  query: `
    WITH cte AS (SELECT number FROM system.numbers LIMIT 10)
    SELECT number FROM cte WHERE number IN ({ empty_array: Array(Int32) })
  `,
  format: 'JSONEachRow',
  query_params: {
    empty_array: [],
  },
})
console.info('Result (array params):', await rs.json())

Prints:

Result (array params): []

The generated query (by ClickHouse) from system.query_log:

WITH cte AS (SELECT number FROM system.numbers LIMIT 10) 
SELECT number FROM cte WHERE number IN _CAST([], 'Array(Int32) ') FORMAT JSONEachRow

With a non-empty array:

const rs = await client.query({
  query: `
    WITH cte AS (SELECT number FROM system.numbers LIMIT 10)
    SELECT number FROM cte WHERE number IN ({ non_empty_array: Array(Int32) })
  `,
  format: 'JSONEachRow',
  query_params: {
    non_empty_array: [1, 3, 7],
  },
})
console.info('Result (array params):', await rs.json())

Prints:

Result (array params): [ { number: '1' }, { number: '3' }, { number: '7' } ]

From system.query_log:

WITH cte AS (SELECT number FROM system.numbers LIMIT 10) 
SELECT number FROM cte WHERE number IN _CAST([1, 3, 7], 'Array(Int32) ') FORMAT JSONEachRow

[NickSchmitt]

I appreciate such a quick and helpful response. I'll look more into system.query_log. Thanks @slvrtrn