Cannot store float boundary values correctly like Float.MAX_VALUE

[cantfu]

When I insert Float.MAX_VALUE into table column with Float64 type, it seems cannot store the correct precision!

reproduct:

1. jdbc version: 0.2.4/0.3.2-patch2
   clickhouse version: 21.3.5.42

2. create table：

   CREATE TABLE default.`TEST_Boundary`
   (
       `byte` Int32,
       `double1` Nullable(Float64),
       `double2` Nullable(Float64)
   )
   ENGINE = MergeTree
   ORDER BY byte
   SETTINGS index_granularity = 8192;

3. insert value using jdbc

   PreparedStatement preparedStatement = ckConnection
            .prepareStatement("insert into default.`TEST_Boundary` values(1, ?, ?)");
   preparedStatement.setObject(1, Float.MAX_VALUE);
   preparedStatement.setObject(2, Double.MAX_VALUE);
   preparedStatement.addBatch();
   preparedStatement.executeBatch();

4. select using jdbc

   1			3.4028235000000003E38			1.7976931348623157E308
   

We can find that Double.MAX_VALUE can be stored correctly, but Float.MAX_VALUE cannot(should be 3.4028235E38).
Is it a jdbc bug? or it is a clickhouse issue?

[zhicwu]

I can reproduce the issue but it's not a bug in JDBC driver. Why not use decimal which is more reliable?

From what I saw, the generated query was insert into default.`TEST_Boundary` values(1, 3.4028235E38, 1.7976931348623157E308), which is correct.

local :) select toFloat64(3.4028235e+38), toFloat64('3.4028235e+38')

┌─toFloat64(3.4028235e38)─┬─toFloat64('3.4028235e+38')─┐
│            3.4028235e38 │      3.4028235000000003e38 │
└─────────────────────────┴────────────────────────────┘

local :) select toFloat64(1.7976931348623157e+308), toFloat64('1.7976931348623157e+308')

┌─toFloat64(1.7976931348623157e308)─┬─toFloat64('1.7976931348623157e+308')─┐
│            1.7976931348623157e308 │               1.7976931348623157e308 │
└───────────────────────────────────┴──────────────────────────────────────┘

[cantfu]

All right, thanks for your reply.
Why not use decimal is because I can not specify the precision for decimal in my scenario..

ok, It does not seem to be a JDBC driver bug, I will try other solutions. Thanks again.

[zhicwu]

@cantfu, I'm closing this issue but you may track status and discuss more in ClickHouse/ClickHouse#33701.

[zhicwu]

Will take the workaround of removing E notion for both Float32 and Float64.

For example: Float.MAX_VALUE in Java will be stringified as 340282346638528860000000000000000000000 instead of 3.4028235e+38.

I'll also try to make it part of the 0.3.2-patch3 release, if it's an easy fix and performance penalty is not much...

[cantfu]

Will take the workaround of removing E notion for both Float32 and Float64.

@zhicwu hi, taking workaround of removing E notion means it will cost more bytes on data transfer.
and Will it affect performance for converting to workaround?

I'll also try to make it part of the 0.3.2-patch3 release, if it's an easy fix and performance penalty is not much...

Will fix only on 0.3.2-patch3?

[zhicwu]

traffic and performance

It depends on your implementation. If you're using values function for insertion, the JDBC driver has to apply the workaround for you, and in this case, there will be a bit more data transferred from client to server, and performance will be impacted. However, if you're using input function for insertion like shown in batch insert examples, it simply works with no additional cost because it's all binary.

Will fix only on 0.3.2-patch3?

0.3.2 still has the legacy driver, although it will be retired in 0.4.0. I'll see if I can patch both drivers in the release.

[zhicwu]

Not going to fix this until I can address the performance issue.