Scalar CASE function only supports up to 15 WHEN THEN clauses #14126
Description
- Currently, the CASE scalar function only supports up to 15 WHEN THEN clauses, and this restriction exists because there is a separate implementation for scalar CASE with 3 arguments, 4 arguments, and so on.
- See Support up to 15 boolean expressions for casewhen scalar function #11566 for instance, which increased the number of supported clauses from 5 to 15.
- The equivalent transform function, however, has no such restriction on the number of supported clauses -
- This leads to very confusing user experiences, since most users won't be aware about Pinot internal implementation details like transform functions vs scalar functions and which situations use which type of function. So, certain queries with > 15 when then clauses in a case function will fail with an error like
IllegalArgumentException: Unsupported function: CASE with argument types: .../IllegalArgumentException: Unsupported function: CASE with ... argumentswhereas some other queries with > 15 when then clauses in a case function will succeed. - For instance, in the multi-stage query engine, a projection in a leaf stage will use the transform function variant, whereas a projection in an intermediate stage will use the scalar function variant. We need to support an arbitrary number of WHEN THEN clauses in the scalar CASE function as well.
- Example of a query that currently fails in the multi-stage query engine (since intermediate stages can only use scalar functions, not transform functions):
SELECT CASE
WHEN val BETWEEN 1 AND 5 THEN 0
WHEN val BETWEEN 5 AND 10 THEN 1
...
...
WHEN val BETWEEN 95 AND 100 THEN 19
ELSE 20 END
FROM (SELECT val FROM mytable ORDER BY val LIMIT 100);
- Example of a query that currently fails in the single-stage query engine (since post-aggregation internally uses scalar functions):
SELECT CASE
WHEN SUM(val) BETWEEN 1 AND 5 THEN 0
WHEN SUM(val) BETWEEN 5 AND 10 THEN 1
...
...
WHEN SUM(val) BETWEEN 95 AND 100 THEN 19
ELSE 20 END
FROM mytable;