Remove invalid optimization of CASE-WHEN expressions

[ranma42]

This PR fixes #33751 by removing the invalid optimization. Additionally, the CompareTo-specific optimization is extended to handle the CompareTo(x, y) == {-1, 0, 1} case (which was previously handled by the generic optimization) in order to prevent regressions.

[ranma42]

I need some guidance regarding the tests: are they adequate?

• I am unsure if I added them to the correct place
• I added tests for the "complex" case (results can be TRUE, FALSE or NULL), under the assumption that they are mainly checking the values rather than the SQL (which I would assume would be more relevant if the PR added/updated the optimization); the issue was originally detected on a WHERE filter, but the optimization can also run in projection contexts, so it should preserve the difference between FALSE and NULL

[maumar]

@ranma42 thanks for doing the work! For the tests, I would suggest to use NullSemantics model (NullSemanticsQueryFixtureBase, NullSemanticsQueryTestBase) rather than Northwind. Northwind is somewhat special in our tests, given it has particular well-known structure and data, and we try to keep the configuration clean. We do the customization using ITestModelCustomizer, but it's probably overkill for this.

For the tests themselves, I would maybe do one test in select and one in where, so that we test both "simple" and "complex" cases.

[ranma42]

I moved the tests to the NullSemanticsQueryTestBase and added tests for the simple (WHERE/filter) case. Note that the tests do not really involve NULLs or NULL-related semantics/optimizations.

[maumar]

@ranma42 thank you for the contribution!