ClickHouse / ClickHouse/clickhouse-java

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

Abierto
#2,762 3 comentarios 0 reacciones 0 asignados Ver en GitHub
area:data-type bug investigating jdbc-v2
Lenguaje dominante
Java
Estrellas
1.6k
Forks
636
Merge medio
2 d 23 h
PR fusionados (30 d)
29

Descripción

- 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

Guía de contribución

Abrir la guía de contribución

Línea de trabajo

Comienza con la ruta de JDBC v2 PreparedStatement, reproduce la consulta Decimal(38,10) con setBigDecimal y compara el SQL generado con la solución alternativa setString. Verifica el comportamiento con las versiones de ClickHouse y los valores de ejemplo proporcionados; se considera terminado cuando el filtro BigDecimal devuelve la fila esperada y la regresión está cubierta por una prueba de lectura JDBC adecuada.

Escrito por el modelo de indexación a partir del texto del issue.

Evaluación

Stack tecnológico
java, sql
Área
database
Tipo de issue
Error
Dificultad
3/5
Tiempo estimado
1-2 días
Estado de actividad
Tranquilo
Claridad
Bastante claro
Aptitud para principiantes
62/100

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.