sqlclickhousesql-in

Clickhouse SQL: IN operator fails on the empty parenthesized list of expressions

Using ClickHouse, my query is failing if I pass into the query an empty set to IN clause.

This works:

SELECT 1
WHERE '2' IN ('2','3');

This fails:

SELECT 1
WHERE '2' IN ();

Error:

Expected one of: token, DoubleColon, non-empty parenthesized list of expressions. (SYNTAX_ERROR) (version 22.3.7.28 (official build))

Apparently some flavours of SQL allow to not fail syntactically because of using an empty set inside an IN clause, treating the result of the condition as a false. In here I tested this and both work from a syntactical point of view.

EDIT: Only SQLite, not SQL. SQL spec explicitly does not allow an empty IN ().

Is it there a setting in Clickhouse to go either way, is it possible for ClickHouse to not fail on this query? I haven't found such setting here.

Solution

  • Try to use arrayExists instead:

    SELECT 1
WHERE arrayExists(x -> x == '2', cast([/* list of checked strings */], 'Array(String)'))