Skip to content

Further simplify CASE ... WHEN expressions #17589

@alamb

Description

@alamb

Is your feature request related to a problem or challenge?

One form of Case statements look like

CASE 
  WHEN <expr> THEN <expr>
  WHEN <expr> THEN <expr>
  ...
   ELSE
END

If there is a WHEN true then all subsequent case statements can not execute. So for example

CASE 
  WHEN x=y THEN 1 
  WHEN true THEN 2 
  ELSE 3    -- can not be hit
END

Simplified to

CASE 
  WHEN x=y THEN 1 
  ELSE 2 
END

And likewise

CASE 
  WHEN x=y THEN 1 
  WHEN y=z THEN 2 
  WHEN true THEN 3 
  WHEN a=b THEN 5 -- can not be hit
  ELSE 5    -- can not be hit
END

Simplified to

CASE 
  WHEN x=y THEN 1 
  WHEN y=z THEN 2 
  ELSE 3
END

Describe the solution you'd like

Implement the above simplifcation / optimization along with tests:

  1. Unit tests in the simplifier
  2. sqllogictests

Describe alternatives you've considered

in #17450 @EeshanBembi implemented one form of this simplification and I think it could be extended fairly straightforwardly:

You can see this by running an explain plan (see that the name is changed to 1):

> explain select CASE WHEN true then 1 else 2 END;
+---------------+-------------------------------+
| plan_type     | plan                          |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
|               | │       ProjectionExec      │ |
|               | │    --------------------   │ |
|               | │  CASE WHEN Boolean(true)  │ |
|               | │     THEN Int64(1) ELSE    │ |
|               | │        Int64(2) END:      │ |
|               | │             1             │ |
|               | └─────────────┬─────────────┘ |
|               | ┌─────────────┴─────────────┐ |
|               | │     PlaceholderRowExec    │ |
|               | └───────────────────────────┘ |
|               |                               |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.002 seconds.

However, it doesn't handle the case

Additional context

No response

Metadata

Metadata

Assignees

Labels

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions