Avoid casting columns when comparing ints and strings

[alamb]

Is your feature request related to a problem or challenge?

• related to Should PruningPredicate coerce? #14944

The usecase is a predicate like this, where month_id is an integer:

month_id = '202502'

In DataFusion this results in casting the month_id column to a Utf8 and then is compared to the string '202502'

This is non ideal for at least 3 reasons:

1. Converting many rows to strings is expensive
2. Comparing strings is much slower than comparing integers
3. many data sources (like parquet) only handle predicates in the form of <col> <op> <const> and not cast(<col>) <op> <const> so these predicates can be pushed down

Here is an example (note the predicate in the plan below is CAST(foo.month_id AS Utf8) = Utf8("2024") rather than foo.month_id = Int32("2024")):

DataFusion CLI v46.0.0
> create table foo(month_id int) as values (1), (2), (3);
0 row(s) fetched.
Elapsed 0.003 seconds.

> explain select * from foo where month_id = '2024';
+---------------+-------------------------------------------------------+
| plan_type     | plan                                                  |
+---------------+-------------------------------------------------------+
| logical_plan  | Filter: CAST(foo.month_id AS Utf8) = Utf8("2024")     |
|               |   TableScan: foo projection=[month_id]                |
| physical_plan | CoalesceBatchesExec: target_batch_size=8192           |
|               |   FilterExec: CAST(month_id@0 AS Utf8) = 2024         |
|               |     DataSourceExec: partitions=1, partition_sizes=[1] |
|               |                                                       |
+---------------+-------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.008 seconds.

Describe the solution you'd like

I would like the filter in the plan above to be FilterExec: month_id = 2024 (no cast on month_id)

Other notes:

1. I think this applies to = and !=
2. I don't think it can be done for <, <=, >= and > as the semantics for comparing ints and strings is different than equality.

Describe alternatives you've considered

We already have something called unwrap_in_cast that does this transformation

@jayzhan211 has moved this code in this PR

• Move UnwrapCastInComparison into Simplifier #15012

I think once that PR merged, that would be the natural place to add this optimization

Additional context

No response

[alan910127]

take

[alamb]

We have now merged

• Move UnwrapCastInComparison into Simplifier #15012

@alan910127 thank you for working on this issue. My suggestion on how to start is to write some tests first

I would personally suggest following the model of this test:

datafusion/datafusion/optimizer/src/simplify_expressions/unwrap_cast.rs

Lines 502 to 507 in da42933

	fn test_unwrap_cast_comparison_unsigned() {
	// "cast(c6, UINT64) = 0u64 => c6 = 0u32
	let schema = expr_test_schema();
	let expr_input = cast(col("c6"), DataType::UInt64).eq(lit(0u64));
	let expected = col("c6").eq(lit(0u32));
	assert_eq!(optimize_test(expr_input, &schema), expected);

And add an expression like c1 = 'foo' (c1 is defined here)

let expr = col("c1").eq(lit("123"))

And the expected expression should be that the column is compared to a int32 literal (not a cast on the column):

let expected_expr = col("c1").eq(lit(123i32))

[alan910127]

Got it, thanks for the suggestion!

[jayzhan211]

month_id is integer and "2024" is utf8.

In type_coercion, we cast month_id to utf8 based on the coercion rule.
CAST(foo.month_id AS Utf8) = Utf8("2024")

Instead of unwrapping the cast after cast. How about we fix the type coercion to avoid the cast at the first place. Given the rhs is literal, we can try to parse the string to i32 and check if it is valid. I expect we end up foo.month_id = 2024 in type_coercion. Although this only works for literal case, if both are columns, then we can only convert it to string since we don't know the value at this point.

We can fix the logic in unwrap cast #15110 first, after that we can try on this one

[alamb]

month_id is integer and "2024" is utf8.

In type_coercion, we cast month_id to utf8 based on the coercion rule. CAST(foo.month_id AS Utf8) = Utf8("2024")

Instead of unwrapping the cast after cast. How about we fix the type coercion to avoid the cast at the first place. Given the rhs is literal, we can try to parse the string to i32 and check if it is valid. I expect we end up foo.month_id = 2024 in type_coercion. Although this only works for literal case, if both are columns, then we can only convert it to string since we don't know the value at this point.

We can fix the logic in unwrap cast #15110 first, after that we can try on this one

• I agree perf: unwrap cast for comparing ints =/!= strings #15110 should be fixed

I was thinking that this optimization would be good to do in case someone explicitly built the expression

.. where int_col::varchar = '123'

But I agree once #15110 is fixed, this situation might be unlikely