ClickHouse / ClickHouse/clickhouse-java

JDBC driver passes decimal value without quotes, then where clause works wrong

Open
#2,762 3 comments 0 reactions 0 assignees View on GitHub
area:data-type bug investigating jdbc-v2
Dominant language
Java
Stars
1.6k
Forks
636
Avg merge
2d 23h
Merged PRs (30d)
29

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:
```java
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:
```sql
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:
```java
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
```java

```

#### 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 TABLE` statements for tables involved:
* Sample data for all these tables, use [clickhouse-obfuscator](https://github.com/ClickHouse/ClickHouse/blob/master/programs/obfuscator/Obfuscator.cpp#L42-L80) if necessary

Contributor guide

Open the contributing guide

Research direction

Start with the JDBC v2 PreparedStatement path, reproducing the Decimal(38,10) query with setBigDecimal and comparing its generated SQL with the setString workaround. Verify the behavior against the supplied ClickHouse versions and sample values; done means the BigDecimal filter returns the expected row and the regression is covered by an appropriate JDBC read test.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
62/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.