Shahzeb-DataEngineer opened a new issue, #26651:
URL: https://github.com/apache/datafusion/issues/26651
### Describe the bug
Writing a signed integer type's minimum value with a postfix cast fails the
whole query, even though the value is representable in the target type and
equivalent spellings return it correctly:
```sql
SELECT -2147483648::INT;
-- Error: Optimizer rule 'simplify_expressions' failed
-- caused by
-- Failed to cast field 'lit' from Int64 to Int32
-- caused by
-- Arrow error: Cast error: Can't cast value 2147483648 to type Int32
```
The same failure occurs for every signed integer width:
```sql
SELECT -9223372036854775808::BIGINT; -- same failure (UInt64 -> Int64,
value 9223372036854775808)
SELECT -32768::SMALLINT; -- same failure (Int64 -> Int16,
value 32768)
SELECT -128::TINYINT; -- same failure (Int64 -> Int8, value
128)
```
It is not limited to bare SELECTs. It also fails in `VALUES`, in a `WHERE`
filter against a real table, and in `INSERT`:
```sql
CREATE TABLE t(x BIGINT) AS SELECT CAST('-9223372036854775808' AS BIGINT) AS
x;
SELECT * FROM t WHERE x = -9223372036854775808::BIGINT; -- optimizer
failure (above)
INSERT INTO t VALUES (-9223372036854775808::BIGINT); -- optimizer
failure (above)
```
`SET datafusion.optimizer.skip_failed_rules = true` does not rescue the
query: planning then finishes, but the same cast fails again at execution time.
Equivalent spellings all work and return the minimum value:
```sql
SELECT -9223372036854775808; -- Int64 min (bare literal)
SELECT CAST(-2147483648 AS INT); -- -2147483648
SELECT CAST('-9223372036854775808' AS BIGINT); -- Int64 min
```
### To Reproduce
Run any of the failing statements above in datafusion-cli (verified on main,
DataFusion 55.1.0):
```sql
SELECT -2147483648::INT;
```
### Expected behavior
`-2147483648::INT` evaluates to `Int32(-2147483648)`, and likewise for the
other widths. This matches PostgreSQL semantics and DataFusion's own behavior
for the control spellings above.
### Root cause analysis
`EXPLAIN` (with `skip_failed_rules = true`, which lets planning finish)
shows the planned tree directly:
```
ProjectionExec: (- CAST(2147483648 AS Int32))
ProjectionExec: (- CAST(9223372036854775808 AS Int64)) -- literal planned
as UInt64
```
The SQL planner binds the postfix cast to the unsigned positive magnitude
and leaves the unary minus outside: `-(CAST(<magnitude> AS <type>))`. The
positive magnitude of a signed type's minimum is by construction not
representable in that type (2147483648 is not an Int32, 9223372036854775808 is
not an Int64). Constant folding in the `simplify_expressions` optimizer rule
then evaluates the inner CAST eagerly, and the Arrow cast error aborts the
whole query.
The planner already folds the sign into the literal for the bare-literal
spelling (`SELECT -9223372036854775808;` works) in `parse_sql_unary_op` in
`datafusion/sql/src/expr/unary_op.rs`, where `UnaryOperator::Minus`
special-cases a `Value::Number` operand. A cast of a number literal does not
get the same treatment: it falls through to the general path that wraps the
cast in `Expr::Negative`.
A targeted fix would be to extend that special case: when unary minus is
applied to a cast whose operand is a number literal, fold the sign into the
literal first and then apply the cast, so the cast sees the representable
negative value.
Happy to contribute a fix for this.
### Additional context
Related observation, same mechanism, lesser severity: a genuinely invalid
constant cast, e.g. `SELECT CAST('2021-02-29' AS DATE);`, is likewise reported
as `Optimizer rule 'simplify_expressions' failed` wrapping the real cast error.
The substance of that error is correct; only the framing looks like an internal
optimizer failure.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
