[jira] [Created] (CALCITE-7185) Calcite PreparedStatement with Timestamp parameters causes PostgreSQL type mismatch (timestamp without time zone >= bigint)

[yang jie (Jira)]

yang jie created CALCITE-7185:
---------------------------------

             Summary: 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
            Reporter: yang jie


*Description:*
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 ?";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)