Deparser emits empty IN () for = ANY / <> ALL over empty array constants — rejected by ClickHouse < 25.x

[iskakaushik]

Summary

When a ScalarArrayOpExpr with a constant empty array is pushed down, the deparser emits an empty tuple after IN/NOT IN, producing remote SQL like (col IN ()). Older ClickHouse servers reject this: ≤22.x with DB::Exception: Syntax error: failed at position N (')'): ... Expected ... (SYNTAX_ERROR), 23.x–24.x with Function tuple requires at least one argument (NUMBER_OF_ARGUMENTS_DOESNT_MATCH), 24.8 with Function 'in' is supported only if second argument is constant or table expression (UNSUPPORTED_METHOD). ClickHouse ≥ ~25.x accepts IN () with correct semantics, so the failure is version-dependent on the remote side.

All references below are pinned to commit 94256f0.

Trigger conditions

• SELECT * FROM ft WHERE col = ANY('{}'::int[]); → remote (col IN ())
• SELECT * FROM ft WHERE col <> ALL('{}'::int[]); → remote (col NOT IN ())
• Parameterized queries WHERE col = ANY($1) with an empty-array bind, whenever PostgreSQL uses a custom plan (default for the first five executions of a prepared statement, and later whenever estimated cheaper): eval_const_expressions folds the extern Param into a Const, which then takes the IN path. Under a generic plan the same query takes the has({pN:Array(T)}, col) path with a server-side bound parameter and works — so the same application query fails intermittently depending on plan choice.

Code walkthrough

1. deparseScalarArrayOpExpr (src/deparse.c:3749) dispatches = ANY(Const) (line 3772) and <> ALL(Const) (line 3798) to deparseAsIn.
2. deparseAsIn (src/deparse.c:3726-3742) appends " IN "/" NOT IN ", sets context->array_as_tuple = true, and deparses the array Const.
3. deparseConst (src/deparse.c:2296-2298) routes array output to deparseArray (src/deparse.c:2157); the 1-D branch (lines 2196-2218) writes (, loops over nitems elements, writes ) — for a zero-element array this is exactly (). There is no empty-array guard here, unlike deparseArrayExpr (src/deparse.c:3922-3932), which CAST-wraps empty ARRAY[] expressions.
4. Shippability never blocks it: T_Const is unconditionally shippable (src/deparse.c:362) and the T_ScalarArrayOpExpr check (src/deparse.c:484-498) only validates the operator.

Note: a Param right-hand side does not hit this path (deparseAsIn requires IsA(arg2, Const)); params are sent as native ClickHouse {pN:Type} placeholders with param_pN form parts (src/deparse.c:2431-2438, src/http_streaming.c:167-173), so only plan-time constants are affected.

Observed remote error (older ClickHouse)

DB::Exception: Syntax error: failed at position N (')'): ... Expected one of: <list varies by version> (SYNTAX_ERROR)

with the position pointing at the ) of the empty IN () mid-statement (observed in production at stable mid-statement offsets, consistent with constant-folded application queries).

Impact

Every occurrence is a hard query failure surfaced through remote-error handling — the query never executes remotely even though the PostgreSQL-side semantics are trivial (= ANY('{}') is constant false, <> ALL('{}') constant true). Applications that build = ANY(array) filters from possibly-empty lists (a common ORM pattern) hit this whenever the empty case is plan-time constant and the remote server is older than ~25.x. There is currently no regression test covering empty arrays in ANY/ALL pushdown.

[theory]

This is a bug in earlier versions of ClickHouse. I can see three ways to resolve this issue:

1. Document this behavior for earlier versions of ClickHouse and leave it. We have a lot of features that diverge on earlier versions; this is just another
2. Remove the use of IN() and always use has(), which seems to work on all versions, but may not be as efficient, since it's less commonly used so will have had fewer reasons for ClickHouse to optimize it
3. Add a check to see if the arg is an array and if it has zero values and not to use IN() in that case

I think #3 is probably the simplest, TBH; I've implemented it in #285.