0

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.

xmar
  • 1,729
  • 20
  • 48
  • you need to add somtion like '', that's the rule – nbk Jun 24 '22 at 17:44
  • That would be a set with an element being an empty string. I would like an empty set. – xmar Jun 24 '22 at 17:45
  • 1
    *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 Jun 24 '22 at 17:50
  • You're right there. I can see here that the SQL spec explictly does not allow it: https://jakewheat.github.io/sql-overview/sql-1999-grammar.html#_8_4_in_predicate – xmar Jun 25 '22 at 19:27

1 Answers1

0

Try to use arrayExists instead:

SELECT 1
WHERE arrayExists(x -> x == '2', cast([/* list of checked strings */], 'Array(String)'))
vladimir
  • 13,428
  • 2
  • 44
  • 70
  • Thanks. I accept it as it gives a proper workaround solution to the original problem. If it is of use to anyone, using an actual table field, then it should be `SELECT ... WHERE arrayExists(x -> x == field, cast([/* list of checked strings */], 'Array(String)'))`. – xmar Jun 25 '22 at 19:32