fix!: incorrect coercion when comparing with string literals

[alan910127]

Which issue does this PR close?

• Closes #15161.

Rationale for this change

Currently, DataFusion handles comparisons between numbers and string literals differently from a number of databases. It coerces the number to a string, whereas other databases cast the literal to the column type and emit an error if the cast fails. This behavior can be unintuitive.

What changes are included in this PR?

Updated TypeCoercionRewriter::coerce_binary_op to cast string literals to the column type if one is present on either side of a comparison expression.

Are these changes tested?

• Updated existing tests to reflect the new type coercion behavior.
• In push_down_filter.slt, some explain tests now produce no output when queries fail due to invalid casts. For now, I have updated these tests to expect empty output, but further adjustments may be needed.

Are there any user-facing changes?

Yes. Queries that previously coerced numbers into strings will now fail if the string literal cannot be cast to the column type.

Example

Before this change (success)

> CREATE TABLE t AS SELECT CAST(123 AS int) a;
> SELECT * FROM t WHERE a = '2147483648'; -- Not a valid i32
+---+
| a |
+---+
+---+

After this change (error)

> CREATE TABLE t AS SELECT CAST(123 AS int) a;
> SELECT * FROM t WHERE a = '2147483648'; -- Not a valid i32
type_coercion
caused by
Error during planning: Cannot coerce '2147483648' to type 'Int32'

[github-actions[bot]]

Thank you for your contribution. Unfortunately, this pull request is stale because it has been open 60 days with no activity. Please remove the stale label or comment or this will be closed in 7 days.