[
https://issues.apache.org/jira/browse/CALCITE-7185?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
yang jie updated CALCITE-7185:
------------------------------
Description:
*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 ?";{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}
was:
*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}
> 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
>
> *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 ?";{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)
