ClickHouse / ClickHouse/clickhouse-java
JDBC driver passes decimal value without quotes, then where clause works wrong
- Vorherrschende Sprache
- Java
- Sterne
- 1.6k
- Forks
- 636
- Ø Merge
- 2 T. 23 Std.
- Gemergte PRs (30 T.)
- 29
Beschreibung
- 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
Beitragsleitfaden
Rechercherichtung
Beginne mit dem JDBC v2 PreparedStatement-Pfad, reproduziere die Decimal(38,10)-Abfrage mit setBigDecimal und vergleiche das generierte SQL mit dem setString-Workaround. Überprüfe das Verhalten anhand der bereitgestellten ClickHouse-Versionen und Beispielwerte; abgeschlossen ist die Aufgabe, wenn der BigDecimal-Filter die erwartete Zeile zurückgibt und die Regression durch einen geeigneten JDBC-Lese-Test abgedeckt ist.
Vom Indexierungsmodell aus dem Issue-Text verfasst.
Bewertung
- Tech-Stack
- java, sql
- Bereich
- database
- Issue-Typ
- Bug
- Schwierigkeit
- 3/5
- Geschätzter Aufwand
- 1-2 Tage
- Aktivitätsstatus
- Ruhig
- Klarheit
- Größtenteils klar
- Anfängerfreundlichkeit
- 62/100