| SQL API expression |
Generated constant |
Result |
100.0 * matched / NULLIF(total, 0) |
100 (wrong) |
33 |
CAST(100 AS DOUBLE) * matched / NULLIF(total, 0) |
100 (wrong) |
33 |
100.1 * matched / NULLIF(total, 0) |
100.1 |
33.366666666666 |
Casting the aggregate to DOUBLE is a working workaround.
Possibly related to #8359, but an identical root cause is not yet confirmed.
Cause and fix
WrappedSelectNode::generate_sql_for_literal renders Float32 and Float64 values with format!("{f}"). Integral floats lose their decimal point, so the DB infers integer arithmetic. Constant folding also routes CAST(100 AS DOUBLE) through this path.
The proposed fix preserves the planned type through existing dialect cast templates. It applies to all float values, including 0.5. This matches existing Decimal128 rendering, which already emits casts preserving precision and scale. Initial type inference and intentional integer division remain unchanged, including the behavior covered by #11319.
Compatibility
This shared renderer affects multiple dialects. Preserving float types can change results for users relying on the previously incorrect truncation. Dialect/version support for explicit casts also matters; MySQL added FLOAT/DOUBLE casts in 8.0.17.
100.0 * matched / NULLIF(total, 0)100(wrong)CAST(100 AS DOUBLE) * matched / NULLIF(total, 0)100(wrong)100.1 * matched / NULLIF(total, 0)100.1Casting the aggregate to DOUBLE is a working workaround.
Possibly related to #8359, but an identical root cause is not yet confirmed.
Cause and fix
WrappedSelectNode::generate_sql_for_literalrenders Float32 and Float64 values withformat!("{f}"). Integral floats lose their decimal point, so the DB infers integer arithmetic. Constant folding also routesCAST(100 AS DOUBLE)through this path.The proposed fix preserves the planned type through existing dialect cast templates. It applies to all float values, including
0.5. This matches existing Decimal128 rendering, which already emits casts preserving precision and scale. Initial type inference and intentional integer division remain unchanged, including the behavior covered by #11319.Compatibility
This shared renderer affects multiple dialects. Preserving float types can change results for users relying on the previously incorrect truncation. Dialect/version support for explicit casts also matters; MySQL added FLOAT/DOUBLE casts in 8.0.17.