ClickHouse / ClickHouse/ClickHouse

Inconsistent Float64 values when inserting equivalent numeric literals (scientific vs decimal notation)

Open
#92,628 0 comments 0 reactions 0 assignees View on GitHub
comp-sql-syntax external unexpected behaviour
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.