Optimize large case-when in simple form to constant lookup

[TennyZhuang]

PostgreSQL supports a simple-form case-when expression:

create table test (a int);
insert into test values (1),(2),(3),(5);
SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

In such case, if there is a large number of arms (e.g. 500+), it's better to optimize it to a constant lookup.

struct LookupExpression<S: Scalar> {
    arms: HashMap<Option<S>, BoxedExpression>,
    fallback: BoxedExpression,
}

In addition, if users write a case-when expression that can be converted to the simple form, we can also normalize it in optimizer easily:

SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

Note: the corner cases should be covered:

SELECT 
       CASE a
              WHEN 1 THEN 'one'
              WHEN 1 THEN 'oneone'
              ELSE 'other',
       CASE a::decimal 
              WHEN 1.0 THEN 'one'
              WHEN 1 THEN 'oneone'
              ELSE 'other'
       END
    FROM test;

[chenzl25]

I suggest performing this optimization in the binder phase because it is more straightforward and easier to implement.

risingwave/src/frontend/src/binder/expr/mod.rs

Line 457 in b9b3519

pub(super) fn bind_case(

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

[xzhseh]

Some future to-dos:

1. refactor the current bind_case, some branches are pretty similar and could be potentially combined together.
2. regarding corner case, e.g., the one mentioned in above description
3. CBO for constant lookup, and we can get rid of a preset limit for number of arms (i.e., 30 at present)

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.