jayzhan211 opened a new issue, #27424:
URL: https://github.com/apache/datafusion/issues/27424
### Describe the bug
Comparing or combining a `Decimal32` or `Decimal64` value with an `Int64`
(any plain integer literal such as `5`) casts the decimal to `Int64` and drops
its fractional digits. A `Decimal32(9, 2)` holding `5.50` compares equal to
`5`, is not greater than `5`, and `5.50 + 1` returns `6` as an `Int64`. These
are wrong rows, not errors. The same values as `Decimal128` give the right
answers.
SQL `DECIMAL(p, s)` columns are `Decimal128`, so today this hits Arrow data
that already has these types (from `arrow_cast`, IPC files, or record batches
registered as tables). #17747 would make SQL produce them too.
### To Reproduce
```sql
CREATE TABLE t AS SELECT
arrow_cast(CAST(5.5 AS DECIMAL(9, 2)), 'Decimal32(9, 2)') AS d32,
arrow_cast(CAST(5.5 AS DECIMAL(18, 2)), 'Decimal64(18, 2)') AS d64;
-- Query 1
SELECT d32 > 5 FROM t;
-- Query 2
SELECT d32 = 5 FROM t;
-- Query 3
SELECT d64 > 5 FROM t;
-- Query 4
SELECT d32 + 1 FROM t;
-- Query 5
SELECT d64 * 2 FROM t;
```
### Expected behavior
| Query | DataFusion returns | Expected |
|---|---|---|
| 1 | `false` | `true` |
| 2 | `true` | `false` |
| 3 | `false` | `true` |
| 4 | `6` (`Int64`) | `6.50` (a decimal) |
| 5 | `10` (`Int64`) | `11.00` (a decimal) |
DuckDB 1.1.3 returns the expected results. Tested on main (`3b0d0c8e19`);
the coercion functions below are identical in `55.1.0` and `56.0.0-rc1`.
### Additional context
In `datafusion/expr-common/src/type_coercion/binary.rs`,
`coerce_numeric_type_to_decimal32` has no arm for `Int32`, `UInt32`, `Int64` or
`UInt64`, and `coerce_numeric_type_to_decimal64` has none for `Int64` or
`UInt64`, since those integers don't fit the variant. Both callers treat that
`None` as "not a decimal case" and fall through to the integer rules:
- Queries 1 to 3: `get_common_decimal_type` returns `None` through `?`, so
`binary_numeric_coercion` falls to `numerical_coercion`, which picks `Int64`.
The logical plan shows `CAST(t.d64 AS Int64) > Int64(5)`.
- Queries 4 and 5: `math_decimal_coercion` returns `None` the same way, and
arithmetic falls back to `mathematics_numerical_coercion`, also `Int64`.
A fix could coerce to a wider decimal variant when the integer doesn't fit,
e.g. `Decimal32(9, 2)` with `Int64` to `Decimal128(22, 2)`, as
`get_wider_decimal_type_cross_variant` already does for two decimals. #27124 is
rewriting `get_common_decimal_type` for #27095 and keeps the `?` on these two
helpers, so the two changes touch the same lines.
### Willingness to contribute
I'm not planning to fix this myself, so anyone is welcome to pick it up.
Feel free to ping me with questions about the cause or when you'd like a review.
--
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]
