[CALCITE-6248] Illegal dates are accepted by casts

[mihaibudiu]

This is the second attempt to provide a partial fix for [CALCITE-6248]. The fix is partial, because the bug is in Avatica, but Avatica CI fails if this fix is not applied. I have already merged a PR for this issue, but here I revert the changes in that prior PR. I apologize for this change of mind, it has become apparent that the previous choice for the fix was incorrect. The fix is about CAST(string to DATE). The assumption is that this cast should be more lenient than DATE literals in parsing the string, but should still require the date to be correct. The plan is to merge this PR, then attempt to merge the corresponding Avatica PR: https://github.com/apache/calcite-avatica/pull/238. If that PR passes, it can be merged. Then some leftover code in Calcite can be cleaned-up when a new Calcite release picks up a new Avatica release. Unfortunately I don't know of an easy way to test this PR before it's merged.

[mihaibudiu]

This is the PR which is being reverted in this one: https://github.com/apache/calcite/pull/3685

[sonarqubecloud[bot]]

Quality Gate passed

Issues
1 New issue
0 Accepted issues

Measures
0 Security Hotspots
50.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarCloud

[mihaibudiu]

It looks like this CI failure is to be expected, since it fails with Avatica main. That should pass once Avatica main contains the corresponding fix.

[JiajunBernoulli]

@mihaibudiu Maybe you can public test version in calcite-avatica. Then update calcite-avatica in calcite to test.

[mihaibudiu]

I sent a message on the dev list about this. I still have to figure out how to do this. We need simultaneous changes to avatica and calcite to make this pass.

[mihaibudiu]

This is ready for review, to enable migration to Avatica 1.25.

[mihaibudiu]

I am thinking to merge this PR, since there isn't much to review, so we can proceed with Avatica.