Wrong values inserted using JDBC PreparedStatement for java.sql.Date[] type

[lukaseder]

Describe the bug

When inserting a Date[] Java type into an Array(Date) column, then a wrong value is being inserted.

Steps to reproduce

Create this table:

create table t (id int primary key, a Array(Date), d Date) engine = MergeTree();

Then run this program:

try (PreparedStatement s = connection.prepareStatement("insert into t values (1, ?, ?)")) {
    s.setObject(1, new Date[] { Date.valueOf("2000-01-01") });
    s.setDate(2, Date.valueOf("2000-01-01"));
    s.executeUpdate();
}

Now, validate the results:

select a, d from t;

The scalar date is correct, but the date array isn't:

|a             |d         |
|--------------|----------|
|['1975-06-22']|2000-01-01|

I'm running this in CEST time zone, in case this matters.

Expected behaviour

The expoected result is:

|a             |d         |
|--------------|----------|
|['2000-01-01']|2000-01-01|

(The formatting is from Dbeaver and can be ignored)

Configuration

Environment

• Client version: com.clickhouse:clickhouse-jdbc:0.6.0-patch4
• Language version: openjdk version "21.0.2" 2024-01-16 LTS
• OS: Microsoft Windows [Version 10.0.22631.3447]

ClickHouse server

• ClickHouse Server version: 24.4.1.2088
• ClickHouse Server non-default settings, if any: N/A

[lukaseder]

Workaround: Use java.time.LocalDate[] instead:

try (PreparedStatement s = connection.prepareStatement("insert into t values (1, ?, ?)")) {
    s.setObject(1, new LocalDate[] { LocalDate.parse("2000-01-01") });
    s.setDate(2, Date.valueOf("2000-01-01"));
    s.executeUpdate();
}

[chernser]

@lukaseder thank you for reporting the issue.

[ghost]

I encountered the same issue when using the java.sql.PreparedStatement.setObject() function with a column that has a Date data type. Here is my debugging:

create table t (a int, b date) engine = MergeTree order by a;

insert into t (a, b) values (1, '2025-08-18'), (2, 2025-08-18);

I can insert a date value without using single quote wrappers, and it won't cause any errors.

select a, b from t;

The result is:

a	b
1	2025-08-18
2	1975-06-23

Different values are present in the b column.

I am using ClickHouse JDBC version 0.5.0 with ClickHouse server version 23.10.3.5.

[chernser]

Triage Report

• Arrays should be passed as product of com.clickhouse.jdbc.ClickHouseConnection#createArrayOf
• If invalid type is passed then exception should be thrown
• Update docs if needed