You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hop.apache.org by "magiclen (via GitHub)" <gi...@apache.org> on 2025/12/03 13:54:14 UTC
[I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
magiclen opened a new issue, #6111:
URL: https://github.com/apache/hop/issues/6111
### Apache Hop version?
2.16.0
### Java version?
21.0.8
### Operating system
Linux
### What happened?
### Description
When reading a `float(53)` or numeric value such as: `55487400.0`
Apache Hop serializes it as: `5.54874E+7`
Even though the value can be represented exactly as a decimal integer.
This causes downstream databases (e.g., ClickHouse Float64) to parse and store a *different* floating-point value such as: `55487400.00000001`
This rounding drift happens because scientific notation forces additional floating-point parsing and cannot preserve exact integer precision.
### Expected
- Hop should **not** convert integer-like floats into scientific notation.
- Or provide an option to disable scientific-notation formatting for numeric fields.
- Or preserve original decimal representation when no precision is gained.
### Steps to Reproduce
1. Read a SQL Server `float(53)` value `55487400.0` in Hop.
2. Pass it through a Number/BigNumber field.
3. Output using "Table Output".
4. Observe Hop sending `5.54874E+7` instead of `55487400`.
### A Quick Test On ClickHouse
```sql
SELECT
toFloat64('5.54874E+7') AS a,
reinterpretAsUInt64(a) AS bits_a,
toFloat64('55487400') AS b,
reinterpretAsUInt64(b) AS bits_b;
```
`a` and `b` represent different values.
### Issue Priority
Priority: 2
### Issue Component
Component: Hop Gui, Component: Database, Component: Transforms
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "vbhanuchander-lang (via GitHub)" <gi...@apache.org>.
vbhanuchander-lang commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-5152073689
I've been digging into this and believe I've found the exact mechanism, so sharing it here. I've opened #7744 with a fix.
The formatting layer isn't the culprit — both of Hop's default masks render the value correctly:
```
DecimalFormat("####0.0#########") -> 55487400.0 (default NUMBER mask)
DecimalFormat("######0.0###################") -> 55487400.0 (default BIGNUMBER mask)
```
It comes from the Number to BigNumber conversion instead. `BigDecimal.valueOf(double)` is specified as `new BigDecimal(Double.toString(val))`, and `Double.toString(55487400.0)` returns `"5.54874E7"`. That parses into an unscaled value of 554874 with a **scale of -2**, and it is that negative scale which makes `BigDecimal.toString()` emit `5.54874E+7`:
```java
BigDecimal bd = BigDecimal.valueOf(55487400.0);
bd.scale(); // -2
bd.unscaledValue(); // 554874
bd.toString(); // "5.54874E+7"
bd.toPlainString(); // "55487400"
```
The `+` sign is a useful giveaway that this is the BigDecimal path rather than a bare `Double.toString()`, which would give `5.54874E7` without it.
In `ValueMetaBase` this happens in `getBigNumber()` for `TYPE_NUMBER` (all three storage-type branches) and in `convertStringToBigNumber()`. The negative scale then leaks out through every consumer that serializes with `toString()`:
- JDBC drivers that inline statement parameters — the Table Output case reported here
- `writeBigNumber()`, used by the binary row serialization
- `getDataXml()`, used by the XML data serialization
One clarification on the downstream drift: at the Java level `Double.parseDouble("5.54874E+7") == Double.parseDouble("55487400")` is `true`, so the `55487400.00000001` you observed is coming from ClickHouse's own string-to-Float64 parsing rather than from information lost in the string Hop emits. That is consistent with what you found about ClickHouse PR #52791 affecting string-to-float conversion while INSERT statements behaved as before. Hop still should not be emitting scientific notation here, so a Hop-side fix is warranted regardless — it just means the fix removes the trigger rather than the rounding itself.
The fix in #7744 routes those conversions through a helper that rescales to zero when the scale would be negative. Rescaling upwards from a negative scale is exact, so no rounding occurs and the numeric value is unchanged; values that already convert to a non-negative scale keep their exact previous scale. The full `core` module suite passes (1039 tests).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "bamaer (via GitHub)" <gi...@apache.org>.
bamaer commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3607136112
This looks like a data type mismatch between MS SQL -> Hop -> Clickhouse. Have you tried adding a Select Values in your pipeline and cast the type for your field to the type your Clickhouse target database expects?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "magiclen (via GitHub)" <gi...@apache.org>.
magiclen commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3609861919
> I will try this tomorrow
>
> [ClickHouse/ClickHouse#52791](https://github.com/ClickHouse/ClickHouse/pull/52791)
It affects string-to-float conversion, but INSERT statements seem to still behave as they originally did.
In other words, these still store different values:
```sql
INSERT INTO TAG_VALUE VALUES (fromUnixTimestamp64Nano(173566111000000), 5.54874E+7);
INSERT INTO TAG_VALUE VALUES (fromUnixTimestamp64Nano(173566111000000), 55487400);
```
But the following expressions end up producing the same value:
```sql
SELECT
toFloat64('5.54874E+7') AS a,
reinterpretAsUInt64(a) AS bits_a,
toFloat64('55487400') AS b,
reinterpretAsUInt64(b) AS bits_b
toFloat64('55487400.0') AS c,
reinterpretAsUInt64(c) AS bits_c;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "magiclen (via GitHub)" <gi...@apache.org>.
magiclen commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3607435742
I will try this tomorrow
ClickHouse/ClickHouse#52791
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
hansva closed issue #6111: [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases
URL: https://github.com/apache/hop/issues/6111
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "bamaer (via GitHub)" <gi...@apache.org>.
bamaer commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3607234692
Ok, that's what you meant with "Pass it through a Number/BigNumber field.", that wasn't clear.
I don't have a ClickHouse environment to test, but your a and b values in ClickHouse appear different because they are. Wouldn't the easiest option be to convert to an Integer in your Select Values and let ClickHouse or its JDBC driver handle the conversion to Float64?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "magiclen (via GitHub)" <gi...@apache.org>.
magiclen commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3607299684
I use number/bignumber in Hop because the type of the source value is float(53) rather than an integer. The dataset contains many floating-point values, including some whose fractional part is zero.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3627358954
The way that setting values on a PreparedStatement in a JDBC work in Hop is that it's done here for the various data types:
https://github.com/apache/hop/blob/f2f9b125ac62bbef6a69ea0069a91bf027a009e6/core/src/main/java/org/apache/hop/core/row/value/ValueMetaBase.java#L5928
You can see for the various data types how the options in the `IDatabaseMeta` implementation affect this behavior. It's important to have a healthy dose of distrust versus the quality of the JDBC drivers in these cases. If needed we can tweak the Clickhouse implementation over here:
https://github.com/apache/hop/blob/main/plugins/databases/clickhouse/src/main/java/org/apache/hop/databases/clickhouse/ClickhouseDatabaseMeta.java
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Hop serializes numeric values into scientific notation, causing precision loss in downstream databases (hop)
Posted by "magiclen (via GitHub)" <gi...@apache.org>.
magiclen commented on issue #6111:
URL: https://github.com/apache/hop/issues/6111#issuecomment-3607209735
Yes, I tried Number/BigNumber
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org