Incorrect cast of integer columns to utf8 when comparing with utf8 constant

[scsmithr]

Describe the bug

A comparison like column1 < '10' (where column1 is an int64) will cast column1 to utf8 instead of casting the utf8 constant to an integer.

Typically string constants in a sql query are treated as unknown, and preference should be on casting the "unknown" value to a target type.

To Reproduce

DataFusion CLI v46.0.0
> create table t1 as (values (1), (2), (3));
0 row(s) fetched. 
Elapsed 0.026 seconds.

> select * from t1 where column1 < '10';
+---------+
| column1 |
+---------+
| 1       |
+---------+
1 row(s) fetched. 
Elapsed 0.015 seconds.

> select * from t1 where column1 < 'hello';
+---------+
| column1 |
+---------+
| 1       |
| 2       |
| 3       |
+---------+
3 row(s) fetched. 
Elapsed 0.007 seconds.

> select arrow_typeof(column1) from t1 limit 1;
+--------------------------+
| arrow_typeof(t1.column1) |
+--------------------------+
| Int64                    |
+--------------------------+
1 row(s) fetched. 
Elapsed 0.010 seconds.

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

Expected behavior

column1 not to be cast to a string.

Postgres output:

postgres=# create table t1 as (values (1), (2), (3));
SELECT 3
postgres=# select * from t1 where column1 < '10';
 column1 
---------
       1
       2
       3
(3 rows)

postgres=# select * from t1 where column1 < 'hello';
ERROR:  invalid input syntax for type integer: "hello"
LINE 1: select * from t1 where column1 < 'hello';

Additional context

No response

[alamb]

👋 @scsmithr

FYI @jonahgao

I vaguely remember something related to this automatic coercion in the past 🤔

BTW @alan910127 and I are trying to fix the equality case in

• Avoid casting columns when comparing ints and strings #15035
• perf: unwrap cast for comparing ints =/!= strings #15110

[jonahgao]

I checked and found that Spark has the same behavior as PostgreSQL. Maybe we should use a different coercion rule for this since the current comparison_coercion is also used by union.

[alamb]

I checked and found that Spark has the same behavior as PostgreSQL. Maybe we should use a different coercion rule for this since the current comparison_coercion is also used by union.

I agree we should split the coercion rules for union and binary comparison

[alamb]

I also put this issue on my wishlist for

• Release DataFusion 47.0.0 (April 2025) #15072

[AlonSpivack]

I'm hitting this bug in production on DataFusion v52, and it's causing silently incorrect query results across multiple scenarios.

I want to add important context beyond what's already discussed here - this bug is wider than originally reported.

---

1. PR #15110 (merged) only fixed a very narrow subset of the problem

The cast_literal_to_type_with_op function that was merged only handles:

• Operators: = and != only
• Types: integer types only (Int8–Int64, UInt8–UInt64)

This means <, >, <=, >= are still broken for integers, and all operators are broken for float types (Float32, Float64). PR #15482 attempted to fix the broader problem at the type coercion level but was closed as stale in June 2025.

---

2. Float columns are also affected - not just integers

The original issue only mentions integer columns, but float columns have the same problem - and in some cases it's even worse:

CREATE TABLE t AS SELECT * FROM (VALUES (1.0), (5.0), (5.5), (325.0), (499.0)) AS t(num_float);
-- WRONG RESULTS: returns 499.0, 325.0 because '499.0' < '5' lexicographically

SELECT * FROM t WHERE num_float < '5';
-- WRONG RESULTS: returns 0 rows! CAST(5.0 AS Utf8) produces '5.0' which != '5'

SELECT * FROM t WHERE num_float = '5';
-- Might accidentally "work" because CAST(5.0 AS Utf8) = '5.0', but it's still doing string comparison

SELECT * FROM t WHERE num_float = '5.0';
-- WRONG: same lexicographic issue

SELECT * FROM t WHERE num_float > '5';

The num_float = '5' case is especially dangerous - it silently returns zero rows instead of matching the value 5.0, because the float gets cast to the string "5.0" which doesn't equal "5". No error, no warning, just wrong data.

---

3. The integer </> cases are equally broken

CREATE TABLE t2 AS SELECT * FROM (VALUES (1), (5), (325), (499), (1000)) AS t2(num_int);
-- WRONG RESULTS: returns 499, 325 etc. because '499' < '5' in string ordering

SELECT * FROM t2 WHERE num_int < '5';
-- This one works (fixed by PR #15110), but only for = and !=

SELECT * FROM t2 WHERE num_int = '5';

---

4. What Postgres does (the correct behavior)

Postgres treats the string literal as "unknown" type and casts it to the column type - not the other way around:

postgres=# SELECT * FROM t WHERE num_float < '5';
-- Returns all values less than 5.0 (correct numeric comparison)
postgres=# SELECT * FROM t WHERE num_float = '5';

-- Matches the value 5.0 (correctly casts '5' to float)
postgres=# SELECT * FROM t WHERE num_float < 'hello';

ERROR:  invalid input syntax for type double precision: "hello"

---

5. Summary of broken scenarios in v52

Scenario	Fixed?	What happens
int_col = '5'	✅ (PR #15110)	Correct
int_col != '5'	✅ (PR #15110)	Correct
int_col < '5'	❌	Wrong results
int_col > '5'	❌	Wrong results
int_col <= '5'	❌	Wrong results
int_col >= '5'	❌	Wrong results
float_col = '5'	❌	Wrong results
float_col = '5.0'	✅	Accidentally works via string match
float_col != '5'	❌	Wrong results
float_col < '5'	❌	Wrong results
float_col > '5'	❌	Wrong results

---

6. My thoughts on the fix

I think the ideal solution is what @scsmithr suggested - fix this at the type coercion level: when comparing a numeric column (int or float) with a string literal, cast the string literal to the column's numeric type, not the other way around. If the cast fails (e.g., num_col < 'hello'), throw a clear planning error like Cannot coerce 'hello' to type 'Float64'.

At the very least, even if the full coercion fix takes time, I think DataFusion should throw an error/exception immediately when it encounters a string value on one side of any comparison operator (=, !=, <, >, <=, >=) with a numeric column on the other side, with a message explaining that implicit string-to-column casting is not supported and that the types are incompatible. This would prevent the current situation where users silently get wrong results without any indication.

If a user actually wants string comparison behavior, they can explicitly cast in the SQL:

-- Explicit cast: user is intentionally asking for string comparison
SELECT * FROM t WHERE CAST(num_float AS TEXT) < '5';

This way, the default behavior is safe (either correct numeric comparison or a clear error), and users who truly want string comparison can opt into it explicitly.

---

7. Status question

Is anyone currently working on a revised approach after PR #15482 was closed?

In that PR, @alamb [noted](#15482 (comment)) that the fix shouldn't check for Expr::Literal inside the type coercion rewriter, but instead should change the base type coercion rules themselves (which work on DataType, not on expression kind). He also pointed out that checking for literals won't handle string expressions like date_col = '2025'||'-'||'02'. Separately in PR #15110, @alamb [agreed with @scsmithr](#15110 (comment)) that changing the coercion rules is a better plan than trying to fix it up afterwards in the optimizer.

Has there been any progress on that direction? This is a correctness bug that silently returns wrong data. In my production system I was getting incorrect results for a long time before I tracked it down to this coercion issue. I'd love to help move this forward if there's a way I can contribute.

[neilconway]

@AlonSpivack Thanks for flagging this! I'm sorry that this resulted in incorrect query results in production for you.

At a high-level, I agree with your analysis, and also that this is a serious issue that has not been sufficiently addressed by the prior work in this area. I'll take a look at coming up with a plan on how to address the problem.

[neilconway]

take

[neilconway]

when comparing a numeric column (int or float) with a string literal, cast the string literal to the column's numeric type, not the other way around.

I don't think making type coercion behave differently for literals vs. columns (of the same type) is the right approach.

I think @alamb's comment above is well-put:

I agree we should split the coercion rules for union and binary comparison

For UNION (and CASE, coercion within an IN list, maybe other situations), we want to find the most general type that might be able to hold both values. So we want numeric -> string.

Otherwise, we want string -> numeric. In an expression like numeric_col < '500', the user obviously intends a numeric comparison. And an expression like numeric_col < 'foo' is invalid and we can safely reject it.

So we need two different sets of type coercion rules, one for UNION, CASE, and maybe a few other situations, and a different set of rules for the rest of the time.