ClickHouse / ClickHouse/ClickHouse
Inconsistent Float64 values when inserting equivalent numeric literals (scientific vs decimal notation)
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Company or project name
_No response_
### Describe the unexpected behaviour
When inserting numeric literals into a Float64 column, ClickHouse stores different values depending on how the same numeric value is written (scientific notation vs decimal literal).
Although the two literals are mathematically equivalent, they result in different stored values and different query results. This is unexpected and problematic for data ingestion pipelines.
### Which ClickHouse versions are affected?
25.11.2.24 (clickhouse-jdbc-0.9.4-all.jar)
### How to reproduce
```sql
DROP TABLE IF EXISTS v;
CREATE TABLE v
(
value Float64
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO v VALUES (5.54874E+7);
INSERT INTO v VALUES (55487400);
SELECT value FROM v;
```
### Observed behavior
The two rows are not equal, and the query output may look like:
```
55487400
55487400.00000001
```
Despite representing the same numeric value, ClickHouse treats them as different.
### Expected behavior
Both inserts should result in the same stored value, since:
```mathematica
5.54874E+7 == 55487400
```
At minimum, ClickHouse should parse numeric literals consistently just like MySQL, regardless of whether scientific notation or decimal notation is used.
### Why this is a problem
This behavior is surprising and causes real issues in practice:
* ETL tools, JDBC drivers, or JSON serializers may emit numbers in scientific notation or fixed decimal format depending on context.
* Re-ingesting the same dataset through different paths can produce different stored values.
* This breaks idempotency assumptions for ingestion pipelines and makes equality checks unreliable.
From a user’s perspective, two equivalent numeric literals should not produce different results at insert time.
Contributor guide
Assessment
This issue has not been assessed yet.