[
https://issues.apache.org/jira/browse/CALCITE-7185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18020881#comment-18020881
]
Jie Yang edited comment on CALCITE-7185 at 9/17/25 9:32 AM:
------------------------------------------------------------
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 want to pass the correct timezone, 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.
was (Author: JIRAUSER310955):
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: Jie Yang
> 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)
