kumarUjjawal opened a new issue, #27095:
URL: https://github.com/apache/datafusion/issues/27095
### Describe the bug
A comparison between an integer column and a decimal with a large scale
fails at execution time.
The common type for the comparison keeps the scale of the decimal, and caps
the precision at 38. The integer digits do not fit in the result, so the cast
of the integer column fails.
### To Reproduce
```sql
CREATE TABLE t (a INT) AS VALUES (-1), (0), (5);
SELECT * FROM t WHERE a != CAST(0.9 AS DECIMAL(38, 38));
```
```text
Failed to cast field 'a' from Int32 to Decimal128(38, 38)
caused by
Arrow error: Invalid argument error:
-1.00000000000000000000000000000000000000 is too small to store in a Decimal128
of precision 38. Min is -0.99999999999999999999999999999999999999
```
The scale does not need to be 38. With scale 30, the common type has room
for 8 integer digits:
```sql
CREATE TABLE u (a INT) AS VALUES (1), (123456789);
SELECT * FROM u WHERE a > CAST(0.5 AS DECIMAL(38, 30));
```
```text
Failed to cast field 'a' from Int32 to Decimal128(38, 30)
caused by
Arrow error: Invalid argument error:
123456789.000000000000000000000000000000 is too large to store in a Decimal128
of precision 38. Max is 99999999.999999999999999999999999999999
```
A decimal column gives the same error as a decimal literal:
```sql
CREATE TABLE d (x DECIMAL(38, 30)) AS VALUES (0.5);
SELECT * FROM u JOIN d ON u.a > d.x;
```
The same error occurs with `SET datafusion.sql_parser.parse_float_as_decimal
= true` and a literal such as `0.90000000000000000000000000000000000000`.
Tested on main (`c120619de`).
### Expected behavior
The comparisons return rows, as in PostgreSQL 17:
* `a != 0.9` returns `-1`, `0`, `5`.
* `a > 0.5` returns `1`, `123456789`.
### Additional context
In `datafusion/expr-common/src/type_coercion/binary.rs`,
`get_common_decimal_type` converts `Int32` to `Decimal128(10, 0)`. Then
`get_wider_decimal_type` computes precision `max(s1, s2) + max(p1 - s1, p2 -
s2)` = 38 + 10 = 48 and scale 38. `create_decimal128_type` caps the precision
at 38 and keeps the scale:
```rust
fn create_decimal128_type(precision: u8, scale: i8) -> DataType {
DataType::Decimal128(
DECIMAL128_MAX_PRECISION.min(precision),
DECIMAL128_MAX_SCALE.min(scale),
)
}
```
The result is `Decimal128(38, 38)`, which cannot hold any integer digit.
The two decimal code paths are not consistent.
`get_wider_decimal_type_cross_variant` (for example `Decimal64` with
`Decimal128`) returns `None` when the required precision is too large.
`get_wider_decimal_type` (same variant) caps the precision instead.
Possible fixes:
* Widen to `Decimal256` when the required precision exceeds 38.
* Reduce the scale so that the integer digits fit. This drops fractional
digits of the decimal side.
Both fixes change the coerced type of existing queries.
--
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]
