[
https://issues.apache.org/jira/browse/CALCITE-7185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18020881#comment-18020881
]
yang jie commented on CALCITE-7185:
-----------------------------------
After debugging the Avatica source code, I found the problem may lies in the
{{serialToLocal}} method of the {{TypedValue.java}} file:
{code:java}
private static Object serialToLocal(ColumnMetaData.Rep rep, Object value) {
assert value != null;
if (rep.clazz.isInstance(value)) {
return value;
}
switch (rep) {
case BYTE:
return ((Number) value).byteValue();
case SHORT:
return ((Number) value).shortValue();
case INTEGER:
case JAVA_SQL_DATE:
case JAVA_SQL_TIME:
return ((Number) value).intValue();
case LONG:
case JAVA_UTIL_DATE:
case JAVA_SQL_TIMESTAMP:
return ((Number) value).longValue();
case FLOAT:
return ((Number) value).floatValue();
case DOUBLE:
return ((Number) value).doubleValue();
case NUMBER:
return value instanceof BigDecimal ? value
: value instanceof BigInteger ? new BigDecimal((BigInteger) value)
: value instanceof Double ? new BigDecimal((Double) value)
: value instanceof Float ? new BigDecimal((Float) value)
: new BigDecimal(((Number) value).longValue());
case BYTE_STRING:
return ByteString.ofBase64((String) value);
case ARRAY:
// List<Object>
return value;
default:
throw new IllegalArgumentException("cannot convert " + value + " ("
+ value.getClass() + ") to " + rep);
}
}{code}
{code:java}
{code}
If I return a Timestamp type in the JAVA_UTIL_DATE and JAVA_SQL_TIMESTAMP
cases, the previous error（ERROR: operator does not exist: timestamp without
time zone >= bigint） can be resolved, but the parameter values pushed down to
the database will have timezone issues.However, if i modify it in this way,
then in serialToLocal i also need to add new parameter: Calendar. Sametime, I
need to modify the relevant code in CalciteConnectionImpl to pass in the
Calendar parameter. At the same time, other places where serialToLocal is
referenced would also need to be changed.
> 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)
