Invalid query is generated by setObject due to bad type cast

[rileythomp]

Description

.setObject will generate an invalid query because it casts the parameter to a type that is not valid for the function that the parameter is passed to. For example: select parseDateTimeBestEffort(?); with (t/local-date 2019 11 30) will generate select parseDateTimeBestEffort(CAST ('2019-11-30' AS Date32)); which gives

Code: 43. DB::Exception: Illegal type Date32 of first argument of function parseDateTimeBestEffort: In scope SELECT parseDateTimeBestEffort(CAST('2019-11-30', 'Date32')). (ILLEGAL_TYPE_OF_ARGUMENT) (version 25.2.2.39 (official build))

This happens when you specify the targetSqlType parameter of .setObject.

On 0.9.0 it generates select parseDateTimeBestEffort('2019-11-30'); so it seems like this was introduced in 0.9.1 by #2508 and #2670. This seems to be the code adding the cast: https://github.com/ClickHouse/clickhouse-java/blob/main/jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java#L1034-L1047

Steps to reproduce

clj -Sdeps '{:deps {com.clickhouse/clickhouse-jdbc {:mvn/version "0.9.5"} org.lz4/lz4-java {:mvn/version "1.8.0"}}}'

(do
    (import '[java.sql DriverManager])
    (require '[java-time.api :as t])
    (with-open [conn (DriverManager/getConnection "jdbc:clickhouse://localhost:8123/default" "default" "")
                stmt (.prepareStatement conn "select parseDateTimeBestEffort(?);")]
      (.setObject stmt 1 (t/local-date 2019 11 30) java.sql.Types/DATE)
      (let [rs (.executeQuery stmt)]
        (.next rs)
        (.getObject rs 1))))

See that

SELECT event_time, query, exception
FROM system.query_log 
ORDER BY event_time DESC LIMIT 20;

returns select parseDateTimeBestEffort(CAST ('2019-11-30' AS Date32));

Note that the error only occurs when the java.sql.Types/DATE parameter of .setObject is specified.

Error Log or Exception StackTrace

Execution error (ServerException) at com.clickhouse.client.api.internal.HttpAPIClientHelper/readError (HttpAPIClientHelper.java:403).
Code: 43. DB::Exception: Illegal type Date32 of first argument of function parseDateTimeBestEffort: In scope SELECT parseDateTimeBestEffort(CAST('2019-11-30', 'Date32')). (ILLEGAL_TYPE_OF_ARGUMENT) (version 25.2.2.39 (official build))

Expected Behaviour

A query with an invalid cast isn't generated, and #inst "2019-11-30T05:00:00.000000000-00:00" is returned from the example above, like it is in 0.9.0.

Code Example

Configuration

Client Configuration

Environment

• Cloud
• Client version:
• Language version:
• OS:

ClickHouse Server

• ClickHouse Server version:
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[chernser]

Good day, @rileythomp !

Thank you for reporting!