[FEATURE] Add support for CASE statement in OpenSearch PPL

[RyanL1997]

Description

Currently, there is no native support for SQL-style CASE expressions in eval. This limitation makes it difficult to implement multi-branch conditional logic in a clear and maintainable way. Users are required to nest multiple IF() functions, which reduces readability and increases the complexity of query construction and maintenance.

Goal

Add support for standard CASE WHEN ... THEN ... ELSE ... END syntax in PPL, particularly within eval, to allow users to define conditional logic more intuitively and clearly. This functionality would align PPL with widely adopted SQL syntax and improve query clarity in scenarios requiring multi-condition branching.

Workarounds and alternatives

Nested IF() expressions have been used to replicate CASE logic. However, this approach becomes cumbersome and error-prone as the number of conditions increases, and lacks the structural clarity provided by CASE statements.

Reference

• Example by using IF (as the work around for now):

| eval grade = if(score >= 95, "A+",
             if(score >= 90, "A",
               if(score >= 85, "B+",
                 if(score >= 80, "B",
                   if(score >= 75, "C+",
                     if(score >= 70, "C",
                       if(score >= 65, "D+",
                         if(score >= 60, "D", "F"))))))))
# As more conditions are added, readability degrades significantly

• Example by using CASE:

| eval grade = case(
    when score >= 95 then "A+",
    when score >= 90 then "A",
    when score >= 85 then "B+",
    when score >= 80 then "B",
    when score >= 75 then "C+",
    when score >= 70 then "C",
    when score >= 65 then "D+",
    when score >= 60 then "D",
    else "F"
)

[penghuo]

did u consdider other alternatives?
for instance, Kusto/Splunk

case(predicate_1, then_1, [predicate_2, then_2, ...] else), The value of the first then_i whose predicate_i evaluates to true, or the value of else if neither of the predicates are satisfied.

[LantaoJin]

Case function has already supported in PPL-Spark. We will follow its syntax in V3.
https://github.com/opensearch-project/opensearch-spark/blob/6832737577119221819c49235c212020c0214fed/ppl-spark-integration/src/main/antlr4/OpenSearchPPLParser.g4#L489-L491

Link to #3536

[anasalkouz]

@LantaoJin just to confirm. is this will be supported as part of Calcite project? can we add 3.0 label?

[LantaoJin]

@LantaoJin just to confirm. is this will be supported as part of Calcite project? can we add 3.0 label?

Will support it via Calcite. But 3.0.0-beta has cutoff, does it mean no new feature will be allowed to add in 3.0.0-GA? If yes, this feature will be planned to deliver in 3.1.0.

#3558 both support it with/without Calcite. With the patch, user can run case function in PPL in v2 engine as well as long as it could be merged to 3.0 branch.