JDBC does not recognize CH errors in the middle of a result

[den-crane]

SELECT toString(cityHash64(number)) 
FROM  numbers(10000000000)    
where intDiv(1,number-100000)>-1000000000000000 


....
15335767028551339235
12760187302687605805
11100438807872808134
708232450366267289
8950872048444443462
12595162315755727141
15351880352077599325
13448237354462061702
2932317051899664345
4513747946220550376
2548036246277231731
Code: 153, e.displayText() = DB::Exception: Division by zero (version 20.8.18.32 (official build))

Now the error is just one more value. No JAVA exception.

[den-crane]

hm, but only in the case with a single column.

[zhicwu]

Thanks @den-crane. I was able to reproduce the issue on 21.3 as well. I think the problem is server responded 200, so unless we parse each line of the result(regardless what the output format is), we'll run into this kind of issue.

I also tried different output format and multiple columns, but unfortunately they all have the same issue.

curl -v 'http://localhost:8123/?query=SELECT+2,+1+FROM+system.numbers+where+intDiv(1,number-100000)>-10
0000000000000+FORMAT+RowBinary'
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8123 (#0)
> GET /?query=SELECT+2,+1+FROM+system.numbers+where+intDiv(1,number-100000)>-100000000000000+FORMAT+RowBinary HTTP/1.1
> Host: localhost:8123
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Date: Wed, 05 May 2021 23:21:27 GMT
< Connection: Keep-Alive
< Content-Type: application/octet-stream
< X-ClickHouse-Server-Display-Name: ***
< Transfer-Encoding: chunked
< X-ClickHouse-Query-Id: c2ecb703-4aef-4101-b386-b1d3036ef75c
< X-ClickHouse-Format: RowBinary
< X-ClickHouse-Timezone: Etc/UTC
< Keep-Alive: timeout=3
< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}
<
...
Code: 153, e.displayText() = DB::Exception: Division by zero: while executing 'FUNCTION intDiv(1 :: 1, minus(number, 100000) :: 4) -> intDiv(1, minus(number, 100000)) Int8 : 2' (version 21.3.9.83 (official build))
* Connection #0 to host localhost left intact

[zhicwu]

With http response 200, error only happens when 1) there's network issue(IOException); or 2) JDBC driver failed to parse value(e.g. trying the parse the error message as a number). As a result, when output format is TabSeparated*(or similar text-based format) and the ResultSet only contains one column, and either the column type is String or we use resultSet.getString() to retrieve value, there will be no exception.

This cannot be fixed easily on client side without changing output format, because user can write arbitrary SQL - it's hard to tell if there's error or not from result of below SQL:

select '1' as a
union all
select 'Code: 153, e.displayText() = DB::Exception: Division by zero: while executing ''FUNCTION intDiv(1 :: 1, minus(number, 100000) :: 4) -> intDiv(1, minus(number, 100000)) Int8 : 2'' (version 21.3.9.83 (official build))'

@den-crane suggested to look into ClickHouse/ClickHouse#21136. Before http interface is enhanced, I hope switching to RowBinary format can fix the issue from client side.

[zhicwu]

In 0.3.1 and earlier version, query SELECT '2' FROM system.numbers where intDiv(1,number-100000)>-100000000000000 will return below result without any exception - the error message became last line of result set:

'2'
Code: 153. DB::Exception: Division by zero: while executing 'FUNCTION intDiv(1 :: 1, minus(number, 100000) :: 4) -> intDiv(1, minus(number, 100000)) Int8 : 2'. (ILLEGAL_DIVISION) (version 21.11.4.14 (official build))

Starting from 0.3.2, depending on protocol you chose, the result is slightly different:

• http - deserialization error(because we now use binary format), not ideal

java.io.UncheckedIOException: Failed to read column #1(total 1): '2' String

• grpc - expected error

Error: DB::Exception: Division by zero: while executing 'FUNCTION intDiv(1 :: 1, minus(number, 100000) :: 4) -> intDiv(1, minus(number, 100000)) Int8 : 2' on server ClickHouseNode(addr=grpc:192.168.3.16:9100, db=default)@340446604
SQLState:  07000
ErrorCode: 1002

[den-crane]

clickhouse-jdbc-0.4.0-shaded.jar:

SELECT toString(cityHash64(number)) FROM  numbers(10000000000) where intDiv(1,number-1000000)>-1000000000000000

java.io.IOException: Reached end of input stream after reading 22 of 51 bytes

[zhicwu]

Thanks @den-crane. The inner exception should be changed to StreamCorruptedException as it's more specific. There's buffer(size = 4096 but it's customizable) in ClickHouseInputStream, so if there's no fake error(rare but possible) and the error message is not too long, I should be able to extract the error.

Using curl:

echo "SELECT toString(cityHash64(number)) FROM  numbers(10000000000) where intDiv(1,number-1000000)>-1000000000000000" | curl -X POST 'http://localhost:8123/' --data-binary @-
...
18400166420351108711
Code: 153. DB::Exception: Division by zero: while executing 'FUNCTION intDiv(1 :: 1, minus(number, 1000000) :: 4) -> intDiv(1, minus(number, 1000000)) Int8 : 2'. (ILLEGAL_DIVISION) (version 22.8.13.20 (official build))

Using Java:

java.io.UncheckedIOException: Failed to read column #1 of 1: toString(cityHash64(number)) String
	at com.clickhouse.data.ClickHouseDataProcessor.nextRecord(ClickHouseDataProcessor.java:142)
	at com.clickhouse.data.ClickHouseDataProcessor.access$000(ClickHouseDataProcessor.java:23)
	at com.clickhouse.data.ClickHouseDataProcessor$RecordsIterator.next(ClickHouseDataProcessor.java:49)
	at com.clickhouse.data.ClickHouseDataProcessor$RecordsIterator.next(ClickHouseDataProcessor.java:35)
	at com.clickhouse.examples.jdbc.Test.client(Test.java:332)
	at com.clickhouse.examples.jdbc.Test.main(Test.java:578)
Caused by: java.io.IOException: Reached end of input stream after reading 22 of 51 bytes
	at com.clickhouse.data.stream.AbstractByteArrayInputStream.readBytes(AbstractByteArrayInputStream.java:236)
	at com.clickhouse.data.stream.AbstractByteArrayInputStream.readBuffer(AbstractByteArrayInputStream.java:163)
	at com.clickhouse.data.ClickHouseInputStream.readString(ClickHouseInputStream.java:1025)
	at com.clickhouse.data.ClickHouseInputStream.readUnicodeString(ClickHouseInputStream.java:1061)
	at com.clickhouse.data.format.BinaryDataProcessor.readTextString(BinaryDataProcessor.java:668)
	at com.clickhouse.data.format.ClickHouseRowBinaryProcessor.readAndFill(ClickHouseRowBinaryProcessor.java:249)
	at com.clickhouse.data.ClickHouseDataProcessor.nextRecord(ClickHouseDataProcessor.java:132)
	... 5 more