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

0
votes

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.

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.

1
sqlclickhousesql-in
you need to add somtion like '', that's the rule - nbk
That would be a set with an element being an empty string. I would like an empty set. - xmar
Apparently some flavours of SQL allow to not fail syntactically because of using an empty set inside an IN clause only SQLite allows it and the link you tried your code runs on SQLite. - forpas

1 Answers

0
votes

Try to use arrayExists instead:

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