Repository navigation
Incorrect cast of integer columns to utf8 when comparing with utf8 constant #15161
Description
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
Activity
alamb commentedon Mar 11, 2025
👋 @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
jonahgao commentedon Mar 12, 2025
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_coercionis also used byunion.alamb commentedon Mar 13, 2025
I agree we should split the coercion rules for union and binary comparison
alamb commentedon Mar 13, 2025
I also put this issue on my wishlist for
47.0.0(April 2025) #15072AlonSpivack commentedon Feb 15, 2026
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_opfunction that was merged only handles:=and!=onlyInt8–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:
The
num_float = '5'case is especially dangerous - it silently returns zero rows instead of matching the value5.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 broken4. 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:
5. Summary of broken scenarios in v52
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 likeCannot 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:
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::Literalinside the type coercion rewriter, but instead should change the base type coercion rules themselves (which work onDataType, not on expression kind). He also pointed out that checking for literals won't handle string expressions likedate_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 commentedon Feb 18, 2026
@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 commentedon Feb 18, 2026
take
neilconway commentedon Feb 18, 2026
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:
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 likenumeric_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.
AlonSpivack commentedon Feb 19, 2026
Thanks @neilconway for looking into this so quickly and validating the severity of the issue!
I completely agree with you and @alamb that splitting the coercion rules (having one set for UNION/CASE and another strict set for comparisons) is the right architectural fix.
I see you've already opened PR #20426 to implement this approach. That's awesome.
I'll move over to the PR and leave some thoughts there regarding the edge cases you mentioned!
neilconway commentedon Feb 20, 2026
Alright, I spent some more time on this; I have PRs open for two possible approaches:
Approach (1) is what I was initially planning. We prefer string -> numeric coercions in comparison contexts, and numeric -> string coercions in contexts like UNION and CASE. That fixes the issue raised in this ticket, but it continues to allow queries that I think we should reject, like
WHERE text_col < 5,WHERE text_col < int_col, andWHERE text_col IN (5, 10).Approach (2) removes string <-> numeric coercions in comparison contexts entirely. Instead, we arrange for the type coercion analysis pass to check for situations where a numeric column is compared with a string literal, and then insert a coercion for that literal as a special case. This is stricter than (1) but I think it rejects more classes of dubious queries.
I initially thought that treating literals differently for type coercion seemed wrong, but on reflection I think it's okay: we are basically approximating the idea that string literals might not really be "string literals", they are more properly treated as "unknown literals" and then typed based on context. This is explicitly what Postgres does; we could consider a similar approach but IMO that's too large of a project for right now. So overall I think approach (2) is better.
I had Claude Code compare the two approaches:
If folks have an opinion on which approach they think is better (or would like to suggest an alternative), I'd love to hear it.
adriangb commentedon Feb 21, 2026
Matching Postgres’ behavior makes sense to me in general, but I do think there’s a negative user experience impact by making coercion stricter, especially since there is no way for implementers to add special cases or customize coercion atm.
AlonSpivack commentedon Feb 22, 2026
Thanks @neilconway for putting together such a clear comparison of the two approaches.
After reviewing both PRs, I'm in favor of Approach 2 (PR #20456). Here's my reasoning:
The key advantage of Approach 2 is that it catches errors at planning time rather than deferring them to runtime. Under Approach 1,
text_col < 5silently inserts aCAST(text_col AS Int64)that will crash at runtime if the column contains non-numeric data. The user gets no indication of the problem until a specific row triggers the cast failure, which could happen intermittently depending on the data. Approach 2 rejects this at planning time with a clear type mismatch error, which is the correct behavior.The
INlist handling is also a significant differentiator. Under Approach 1,int_col IN ('5', '10')still routes throughtype_union_coercion, resulting in lexicographic comparison - the exact class of bug we're trying to fix. Approach 2 handles this correctly by converting the literals at planning time.Regarding @adriangb's concern about stricter coercion impacting user experience: I think silently returning wrong results is a far worse UX than a clear planning error with an actionable message. Users who genuinely need string comparison can always use an explicit
CAST.Looking further ahead, I think it's worth acknowledging what @neilconway already noted: Approach 2 is fundamentally an approximation of Postgres's
unknowntype semantics. It works well for the common cases (literal comparisons,INlists,BETWEEN), but it won't cover non-literal string expressions likeint_col = '2025' || '-' || '01', and it requires the analyzer to handle each expression type individually.The "right" long-term solution is probably introducing an
unknown(orunresolved) type that string literals receive initially, which then gets resolved to a concrete type based on context - similar to how Postgres handles this. That would solve the problem at its root and cover all expression forms uniformly, not just the ones we explicitly handle in the analyzer.I fully understand this is a large undertaking - it touches the type system, the parser/planner, the analyzer, coercion rules, built-in function signatures, and likely hundreds of tests. It's not something to block the current fix on.
But if the community agrees this is the right long-term direction, I'd be happy to take part in driving it forward - from the initial design/RFC through implementation. I'm already deep in this area of the codebase due to hitting this bug in production, and I'd like to turn that experience into a meaningful contribution.
So my suggestion would be:
unknowntype as a long-term projectWhat do you think, @neilconway @alamb @adriangb?
alamb commentedon Feb 24, 2026
In general, I think making DataFusion match the behavior of another system is much more preferable than inventing its own behavior (the world has enough SQL dialects already in my opinion)
I agree it sounds pretty major. I think it would be worth sketching out what benefits such work would produce (other than potentially a "cleaner" codebase) -- it is a tough sell on API churn for downstream users if they don't get anything out of having to update their APIs
cetra3 commentedon Feb 25, 2026
Here's an example of where
Unknownmight be preferable: #20543neilconway commentedon Feb 26, 2026
I agree; the question is which existing system do we want to follow here. Postgres (and the SQL spec) are more strict here, whereas other systems like DuckDB and SparkSQL tend to be more permissive.
I won't try to give an exhaustive enumeration of all the possible constructs and how we might treat them, but here are a few notable examples we should decide on:
int_col < '123'-- allowed by ~everyone and currently evaluated incorrectly by DF. This clearly needs to be fixed.int_col < text_col-- rejected by Postgres, (modern) DuckDB, and the SQL spec, allowed by SparkSQL. Allowed by DF today.SELECT 1 UNION SELECT 'foo'-- rejected by Postgres and the SQL spec, allowed by SparkSQL, DuckDB, and DF today.SELECT 1 UNION SELECT t.text_col FROM t-- rejected by Postgres, DuckDB, and the SQL spec, allowed by SparkSQL and DF today.int_col IN ('1', '2', '3')-- allowed by ~everyone and currently misevaluated by DF.int_col IN (1, 2, '3')-- allowed by ~everyone.text_col IN (1, 2, '3')-- rejected by Postgres, allowed by SparkSQL.My personal preference is that enforcing types more strictly is the saner way to behave, but given Comet (as well as DF's historical behavior), maybe we should be more permissive?
Unless there's a consensus, maybe the pragmatic thing to do here is to make sure we implement the correct behavior for
int_col < '123'(andint_col IN ('1', '2')), and then defer more aggressive changes to coercion for the time being. i.e., that is roughly #20426, with a bit more work to fix theINcase. Let me know if that plan sounds reasonable to folks.mbutrovich commentedon Mar 4, 2026
Historically DataFusion errs more toward Postgres behavior. When in doubt, that's what I'll recommend.
fix: Prefer numeric in type coercion for comparisons (#20426)
fix: Prefer numeric in type coercion for comparisons (apache#20426)
Fix ClickBench EventDate column type (UInt16 → Date32) (#472)