Expression Simplification for`Expr::Case` expressions

[alamb]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
In certain situations , IOx is likely going to make predicates that look like the following

CASE 
  WHEN col IS NULL THEN '' 
  ELSE col 
END

that basically map null to the empty string

When applying them to certain specialized chunks (or row groups) we will know there are no NULLs in the col or all NULLs and thus we will end up rewriting it to something like

CASE 
  WHEN true THEN '' 
  ELSE col 
END

Also in general, when applying other simplifications / constant folding I can imagine other situations where CASE can be folded such as

CASE
  WHEN extract(day) from now() = 0 THEN 'Monday'
  WHEN extract(day) from now() = 1 THEN 'Tuesday'
  WHEN extract(day) from now() = 2 THEN 'Wednesday'
  WHEN extract(day) from now() = 3 THEN 'Monday'
  ...
  ELSE 'other day'
END

So I think it is worth adding into DataFusion generally

Describe the solution you'd like
I would like to add cases to the rewrite rules here:
https://github.com/apache/arrow-datafusion/blob/03075d5f4b3fdfd8f82144fcd409418832a4bf69/datafusion/src/optimizer/simplify_expressions.rs#L440-L454

Some rules I can think of are:

1. When there is a literal true in the cases preceded by 0 or 1 literal falses or nulls, use that.
2. When all the cases are false, --> the otherwise

Additional context
See https://github.com/influxdata/influxdb_iox/pull/3557 for more details

[liukun4515]

When applying them to certain specialized chunks (or row groups) we will know there are no NULLs in the col or all NULLs and thus we will end up rewriting it to something like

@alamb
Do you mean that you can get the no NULLS or all NULLS from the statistics of the chunk or row group of parquet?

[alamb]

Do you mean that you can get the no NULLS or all NULLS from the statistics of the chunk or row group of parquet?

Yes.

The usecase is that in one of our supported query languages (InfluxQL) a missing column is treated as though it were '' rather than null and we add a case statement to do this mapping.

However, the common case is the column is either always present or always absent (aka either all null or not null) in which case I would like to remove the mapping to avoid the overhead

[alamb]

Another simplification that can be done that @tustvold came up with is:

CASE 
  WHEN X THEN A
  WHEN Y THEN B 
  ...
  ELSE Q
END

assuming the type of A and B are boolean, can be rewritten to

(X AND A) OR (Y AND B) OR ... Q

Which can then be subjected to the existing simplification AND / OR simplification rules

[tustvold]

I think the boolean transformation as written is subtly wrong, I think I have the correct one but I'm just double-checking it

[Jefffrey]

The following PRs implement simplification rules for case:

• Convert boolean case expressions to boolean logic #1719
• feat: Simplify CASE WHEN true THEN expr to expr #17450
• perf: Simplify CASE for any WHEN TRUE #17602
• Optimize CASE expressions by removing WHEN false branches #17628
  • Hopefully merged soon; related issue for reference Simplify CASE WHEN false THEN ... #17590

When the last one is merged I think that covers what is described in the issue; @alamb any other suggested simplifications otherwise we can close this issue with that last PR

[alamb]

Thanks @Jefffrey

[jackkleeman]

Related: #17743