ClickHouse / ClickHouse/clickhouse-java

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

オープン
#2,762 コメント 3 件 リアクション 0 件 担当者 0 名 GitHub で見る
area:data-type bug investigating jdbc-v2
主要言語
Java
スター
1.6k
フォーク
636
平均マージ
2日 23時間
マージ済み PR(30日)
29

説明

- 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

コントリビューションガイド

コントリビューションガイドを開く

調査の方向性

JDBC v2 PreparedStatement パスから始め、setBigDecimal を使って Decimal(38,10) クエリを再現し、生成された SQL を setString の回避策と比較します。提供された ClickHouse のバージョンとサンプル値に対して動作を検証します。完了の条件は、BigDecimal フィルターが期待される行を返し、回帰が適切な JDBC read test でカバーされていることです。

索引モデルが issue の本文から書いたものです。

評価

技術スタック
java, sql
領域
database
issue の種類
バグ
難易度
3/5
見積もり時間
1〜2日
活発さ
静か
明瞭さ
おおむね明確
初心者へのやさしさ
62/100

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。