[
https://issues.apache.org/jira/browse/CALCITE-7185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18020729#comment-18020729
]
Mihai Budiu commented on CALCITE-7185:
--------------------------------------
If col1 has type BIGINT, the compiler has to insert casts somewhere to enable
comparing a BIGINT to a TIMESTAMP value.
However, in the query nothing says that ? is a TIMESTAMP, so I suspect that the
query plan does not contain the necessary casts. Calcite is statically typed,
the query plan is fully generated when you submit a query. But for this example
the actual casts needed are only known at runtime, when you submit values for ?.
But I haven't looked closely how Calcite handles parameters in queries, so I
could be wrong.
> Calcite PreparedStatement with Timestamp parameters causes PostgreSQL type
> mismatch (timestamp without time zone >= bigint)
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7185
> URL: https://issues.apache.org/jira/browse/CALCITE-7185
> Project: Calcite
> Issue Type: Bug
> Components: avatica, core
> Affects Versions: 1.40.0
> Environment: - Database: PostgreSQL
> - Framework: Apache Calcite
> - JDBC Driver: PostgreSQL JDBC Driver
> - Java Version: 21
> Reporter: yang jie
> Priority: Major
>
> When executing a query with {{PreparedStatement}} using Calcite connection
> and binding {{Timestamp}} parameters(col1 type is timestamp in table1).the
> query fails with a PostgreSQL type mismatch error.
> {code:java}
> CalciteConnection calciteConn = null;
> // create datasource,connection,schema....
> String finalSql = "select * from table1 where col1 between ? and ?";{code}
> {code:java}
> try (PreparedStatement ps = calciteConn.prepareStatement(finalSql)) {
> ps.setTimestamp(1, Timestamp.valueOf("2024-01-01 00:00:00"));
> ps.setTimestamp(2, Timestamp.valueOf("2024-01-31 23:59:59"));
> try (ResultSet rs = ps.executeQuery()) {
> // process results
> }
> } {code}
> *Actual Result:*
> Execution throws the following exception:
> {code:java}
> Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist:
> timestamp without time zone >= bigint
> Hint: No operator matches the given name and argument types. You might need
> to add explicit type casts.
> Position: 114
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
> at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
> at
> org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
> at
> org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
> at
> org.apache.calcite.runtime.ResultSetEnumerable.enumeratorBasedOnPreparedStatement(ResultSetEnumerable.java:294)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
