You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Coercion between floating point types and exact numeric types should result in floating point type as the common super type #14272
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)
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.5ASDECIMAL(2, 1)); -- error: too large for Decimal128(30, 15)SELECT CAST('NaN'AS DOUBLE) > CAST(1.5ASDECIMAL(2, 1)); -- error: Overflowing on NaNSELECT CAST('1e-20'AS DOUBLE) > CAST(0ASDECIMAL(2, 1)); -- false (expected true)
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);
Activity
neilconway commentedon May 22, 2026
take
andygrove commentedon Nov 16, 2026
Some more detail on this, from #27335 (now closed as a duplicate).
For comparisons,
IN,CASE,COALESCEand unions,Float64is coerced toDecimal128(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:Arithmetic (
double + decimal) already givesFloat64.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.01compares aDECIMAL(15, 2)column with float literals.0.06 + 0.01is0.06999999999999999, which rounds back to0.07asDecimal128(30, 15)but not as a double. Some ways to handle that:parse_float_as_decimal, Spark dialect);