[SQL] CASE CAST(.. as DATE) ELSE NULL doesn't work

CASE CAST(.. as DATE) ELSE NULL    seem to result in multiple conversions from string to date. That chain seems to fail as one of the string representations of the date is not supported by CAST expression.

Source: https://stackoverflow.com/questions/52861989/how-does-calcite-deal-with-data-conversion/52878780

Imported from Jira [BEAM-5789](https://issues.apache.org/jira/browse/BEAM-5789). Original Jira may contain additional context.
Reported by: kedin.