Repository navigation
JDBC driver passes decimal value without quotes, then where clause works wrong #2762
Description
- jdbc-v2
- jdbc-read
Description
I have a MergeTree() test table with Decimal(38,10) values like 12345678901234567001.1234567890.
When I execute a PreparedStatement like:
try (PreparedStatement pstmt = conn.prepareStatement("select * from test_table where col_decimal < ?")) {
pstmt.setBigDecimal(1, new BigDecimal("12345678901234567008.1234567890"));
ResultSet rs = pstmt.executeQuery();
...no rows are returned (though you may see that 12345678901234567001.1234567890 < 12345678901234567008.1234567890)
I believe this is because the JDBC driver sends the following statement to the database:
http-outgoing-0 >> "User-Agent: ClickHouse JDBC Driver V2/0.8.6 clickhouse-java-v2/0.8.6 (Linux; jvm:17.0.7) Apache-HttpClient/5.3.1[\r][\n]"
...
http-outgoing-0 >> "SELECT * FROM test_table WHERE col_decimal < 12345678901234567008.1234567890[\r][\n]"
http-outgoing-0 >> "0[\r][\n]"
http-outgoing-0 >> "[\r][\n]"
http-outgoing-0 << "HTTP/1.1 200 OK[\r][\n]"
http-outgoing-0 << "X-ClickHouse-Summary: {"read_rows":"11","read_bytes":"187","written_rows":"0","written_bytes":"0","total_rows_to_read":"11","result_rows":"0","result_bytes":"0","elapsed_ns":"3362900"}[\r][\n]"
If I execute the same statement in SQL client, I also do not receive the rows. However, if I quote the literal and execute:
SELECT * FROM test_table WHERE col_decimal < '12345678901234567008.1234567890'then rows are filtered correctly.
So it looks like a bug on JDBC driver side, it should had quoted the decimal literal.
Workaround
If I set PreparedStatement param as String, not BigDecimal, like this:
try (PreparedStatement pstmt = conn.prepareStatement("select * from test_table where col_decimal < ?")) {
pstmt.setString(1, "12345678901234567008.1234567890");
ResultSet rs = pstmt.executeQuery();
...then the driver does send a quoted literal and filter works fine. But it's certainly not how things are supposed to be, setBigDecimal() should have worked as well.
Expected Behaviour
Row with value 12345678901234567001.1234567890 is returned by filter.
Code Example
see above
Configuration
Client Configuration
Environment
- Cloud
- Client version: JDBC driver clickhouse-jdbc-0.8.6-all.jar
- Language version: JDK 17
- OS: Linux
ClickHouse Server
- ClickHouse Server version: 25.8.16.34
- ClickHouse Server non-default settings, if any: none
CREATE TABLEstatements for tables involved:- Sample data for all these tables, use clickhouse-obfuscator if necessary
Activity
chernser commentedon Jul 29, 2026
@anesterenok
Thank you for reporting!
What is the table definition?
JDBC should not change representation as server should handle numeric correctly. If we wrap value as literal then we break queries not expecting it.
anesterenok commentedon Jul 30, 2026
My table is simple, just
I believe the analysis in ClickHouse/clickhouse-connect#808 is correct.
But I strongly disagree with its notion of "narrow high-precision edge case".
Yes, 38 digits is pretty rare, but Float64 has only ~15 significant digits, and so is not suited for e.g. most financial applications. Some legacy systems in that field still use int64 types for amounts, because they don't have decimal type but need 18 significant digits.
And the issue here would affect decimal(18, S) just as well.
chernser commentedon Jul 30, 2026
@anesterenok
Thank you for explanation! I will look into it.
We have related feature to map big integers to string for systems that do not handle them well. May be it will be an answer to this.
Quote Decimal query parameters to preserve precision