SQL API pushdown drops floating-point literal types
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
| 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](https://github.com/cube-js/cube/issues/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](https://github.com/cube-js/cube/pull/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](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html).
Contributor guide
Research direction
Start at WrappedSelectNode::generate_sql_for_literal and trace how Float32, Float64, and Decimal128 values use dialect cast templates. Verify that integral and fractional float literals preserve their planned types across the shared renderer and relevant dialects, while existing integer-division behavior remains unchanged; check compatibility with dialects such as MySQL.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- backend-api-design, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 74/100