bug: case when then return wrong results

[xudong963]

Search before asking

• I had searched in the issues and found no similar issues.

Version

main

What's Wrong?

databend

mysql> select
    ->        case a+1 when b then 111 when c then 222
    ->         when d then 333  when e then 444 else 555 end as col6
    -> from t1
    -> order by col6;
+------+
| col6 |
+------+
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
|  111 |
+------+

pg

postgres=# select
postgres-#        case a+1 when b then 111 when c then 222
postgres-#         when d then 333  when e then 444 else 555 end as col6
postgres-# from t1
postgres-# order by col6;
 col6
------
  111
  111
  111
  111
  222
  222
  222
  333
  333
  333
  333
  333
  333
  333
  333
  333
  444
  444
  444
  444
  444
  555
  555
  555
  555
  555
  555
  555
  555
  555
(30 rows)

How to Reproduce?

Use table t1 in https://github.com/datafuselabs/databend/blob/main/tests/logictest/suites/ydb/select1-1.test

Then run

select
       case a+1 when b then 111 when c then 222
        when d then 333  when e then 444 else 555 end as col6
from t1
order by col6;

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[sundy-li]

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

This is case expression with case_value, it should rewrite to

CASE
    WHEN when_value  = case_value THEN statement_list
    [WHEN when_value  = case_value THEN statement_list] ...
    [ELSE statement_list]
END CASE