Skip to content

Coercion between floating point types and exact numeric types should result in floating point type as the common super type #14272

Open

Description

@findepi

Describe the bug

When coercing decimal with other numeric types, the result is always decimal.
However, when one of operands is floating point, the result should be float.
Floating point's range of possible values cannot be fit into a decimal. Also NaN and Infinity cannot be converted to a decimal.

To Reproduce

> SELECT arrow_typeof(a) FROM (SELECT '1'::float UNION ALL SELECT '1'::decimal(10)) t(a);
+-------------------+
| arrow_typeof(t.a) |
+-------------------+
| Decimal128(17, 7) |
| Decimal128(17, 7) |
+-------------------+
> SELECT '1'::decimal(10,0) = '1e99'::float;
Arrow error: Cast error: Cannot cast to Decimal128(17, 7). Overflowing on inf

Expected behavior

PostgreSQL

postgres=# SELECT pg_typeof(a) FROM (SELECT '1'::float UNION ALL SELECT '1'::decimal(10)) t(a);
    pg_typeof
------------------
 double precision
 double precision
postgres=# SELECT '1'::decimal(10,0) = '1e99'::float;
 ?column?
----------
 f

(Note: PostgreSQL's decimal can express NaN and Infinity)

Additional context

No response

Activity

  1. neilconway commented on May 22, 2026

    @neilconway
    Contributor

    take

  2. andygrove commented on Nov 16, 2026

    @andygrove
    Member

    Some more detail on this, from #27335 (now closed as a duplicate).

    For comparisons, IN, CASE, COALESCE and unions, Float64 is coerced to Decimal128(30, 15) (binary.rs#L1395). Besides the overflow / NaN errors above, values below 1e-15 round to zero, so some queries silently return wrong results:

    SELECT CAST('1e20' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1));  -- error: too large for Decimal128(30, 15)
    SELECT CAST('NaN'  AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1));  -- error: Overflowing on NaN
    SELECT CAST('1e-20' AS DOUBLE) > CAST(0 AS DECIMAL(2, 1));   -- false (expected true)

    Arithmetic (double + decimal) already gives Float64.

    Spark also casts the decimal to DOUBLE for comparisons (DecimalPrecisionTypeCoercion.scala#L182-L185) and for CASE / IF / COALESCE / IN (TypeCoercionHelper.scala#L194-L195).

    One thing a fix has to keep working is TPC-H q6 (#4024). l_discount between 0.06 - 0.01 and 0.06 + 0.01 compares a DECIMAL(15, 2) column with float literals. 0.06 + 0.01 is 0.06999999999999999, which rounds back to 0.07 as Decimal128(30, 15) but not as a double. Some ways to handle that:

    • only use DOUBLE when the float side is not a constant;
    • use DOUBLE when float literals are already parsed as decimals (parse_float_as_decimal, Spark dialect);
    • make the rule configurable.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions