ClickHouse / ClickHouse/clickhouse-java

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

Ouverte
#2,762 3 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
area:data-type bug investigating jdbc-v2
Langage dominant
Java
Étoiles
1.6k
Forks
636
Merge moyen
2 j 23 h
PR mergées (30 j)
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

Guide de contribution

Ouvrir le guide de contribution

Piste de recherche

Commencez par le chemin JDBC v2 PreparedStatement, reproduisez la requête Decimal(38,10) avec setBigDecimal et comparez le SQL généré avec le contournement setString. Vérifiez le comportement avec les versions de ClickHouse et les valeurs d’exemple fournies ; le travail est terminé lorsque le filtre BigDecimal renvoie la ligne attendue et que la régression est couverte par un test de lecture JDBC approprié.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
java, sql
Domaine
database
Type d'issue
Bug
Difficulté
3/5
Temps estimé
1-2 jours
Activité
Calme
Clarté
Plutôt claire
Accessibilité débutants
62/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.