Datafusion downcasts decimal loosing precision  #13492

Activity

1. himadripal

   added

   bugSomething isn't workingSomething isn't working

   on Nov 19, 2024
2. himadripal commented on Nov 19, 2024

   

   himadripal

   on Nov 19, 2024

   ContributorAuthor

   More actions

   In Postgres the following query resulted in an error

   SELECT CAST(CAST(12345.6789 AS NUMERIC(24, 2)) AS NUMERIC(6, 2));
   

   [22003] ERROR: numeric field overflow Detail: A field with precision 6, scale 2 must round to an absolute value less than 10^4.
3. findepi commented on Nov 20, 2024

   

   findepi

   on Nov 20, 2024

   Member

   More actions

   But if we cast it to a larger decimal and then cast it down to (6,2), then DataFusion cast works fine and looses precision. As shown below

   > select cast( cast(12345.6789 as decimal(24,2)) as  decimal(6,2));
   +---------------------+
   | Float64(12345.6789) |
   +---------------------+
   | 1234.56             |
   +---------------------+

   Losing decimal digits (rounding) in a cast is OK.

   However here the cast changes value fundamentally, losing digits before the decimal dot, which is not OK.
   The original value was ≈12345, the cast result is ≈1234.
4. andygrove commented on Dec 3, 2024

   

   andygrove

   on Dec 3, 2024

   Member

   More actions

   I experimented with some test code in arrow-rs and see that there are validity checks when casting f64 to decimal but not when casting decimal to decimal.

   Setup code:

           let array = vec![Some(123456789)];
           let array = create_decimal_array(array, 24, 2).unwrap();
           println!("{:?}", array);
           // PrimitiveArray<Decimal128(24, 2)>
           // [
           //     123456789,
           // ]
   
           let input_type = DataType::Decimal128(24, 2);
           let output_type = DataType::Decimal128(6, 2);
           assert!(can_cast_types(&input_type, &output_type));
   
           let mut options = CastOptions::default();
           options.safe = false;

   Casting to decimal with smaller precision works when it should not:

           let result = cast_with_options(&array, &output_type, &options).unwrap();
           println!("{:?}", result);
           // PrimitiveArray<Decimal128(6, 2)>
           // [
           //     123456789,
           // ]

   Casting from f64 to decimal fails as expected:

           let array = Float64Array::from(vec![1234567.89]);
           let result = cast_with_options(&array, &output_type, &options).unwrap();
           // InvalidArgumentError("123456789 is too large to store in a Decimal128 of precision 6. Max is 999999")
5. himadripal commented on Dec 4, 2024

   

   himadripal

   on Dec 4, 2024

   ContributorAuthor

   More actions

   working on it.
6. himadripal commented on Dec 27, 2024

   

   himadripal

   on Dec 27, 2024

   ContributorAuthor

   More actions

   once the arrow release is out, we can create a Datafusion release and use that here to fix this
7. Jefffrey commented on Jun 28, 2026

   

   Jefffrey

   on Jun 28, 2026

   Contributor

   More actions

   fixed upstream, running on datafusion main:

   DataFusion CLI v54.0.0
   > select cast( cast(12345.6789 as decimal(24,2)) as  decimal(6,2));
   Optimizer rule 'simplify_expressions' failed
   caused by
   Arrow error: Invalid argument error: 12345.68 is too large to store in a Decimal128 of precision 6. Max is 9999.99
8. Jefffrey

   closed this as completedon Jun 28, 2026