ClickHouse / ClickHouse/clickhouse-java
JDBC driver passes decimal value without quotes, then where clause works wrong
- 主要言語
- 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