I'm attempting to use a PreparedStatement placeholder for an argument to a sql aggregation function. The query works fine if I replace the ?
placeholder with a numeric value and get rid of the setDouble
call.
public static String QUERY = """
SELECT DS_GET_QUANTILE(DS_QUANTILES_SKETCH("logins"), ?) as "rank"
FROM (SELECT ip, sum("login-counts") AS "attempts"
FROM "my-datasource"
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY 1)
""";
void queryFails() throws SQLException {
var connection = jdbcTemplate.getJdbcTemplate().getDataSource().getConnection();
var preparedStatement = connection.prepareStatement(QUERY);
preparedStatement.setDouble(1, .75);
var resultSet = preparedStatement.executeQuery();
}
The error is:
org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT DS_GET_QUANTILE(DS_QUANTILES_SKETCH("attempts"), ?) as "rank"
FROM (SELECT ip, sum("login-login_attempts") AS "attempts"
FROM "metrics-default"
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY 1)
at org.apache.calcite.avatica.Helper.createException(Helper.java:54)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:358)
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
...
java.lang.RuntimeException: org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 58: Cannot apply 'DS_GET_QUANTILE' to arguments of type 'DS_GET_QUANTILE(<OTHER>, <ANY>)'. Supported form(s): 'DS_GET_QUANTILE(<ANY>, <NUMERIC>)'
at org.apache.druid.sql.avatica.DruidStatement.closeAndPropagateThrowable(DruidStatement.java:377)
at org.apache.druid.sql.avatica.DruidStatement.prepare(DruidStatement.java:181)
at org.apache.druid.sql.avatica.DruidMeta.prepare(DruidMeta.java:158)
It seems like it is passing the ?
to the database unsubstituted.
Is this a bug in the avatica driver or are placeholders not allowed in certain areas of a query?
Given the root cause error message:
java.lang.RuntimeException: org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 58: Cannot apply 'DS_GET_QUANTILE' to arguments of type 'DS_GET_QUANTILE(<OTHER>, <ANY>)'. Supported form(s): 'DS_GET_QUANTILE(<ANY>, <NUMERIC>)'
This is a problem with type inference for the parameter. Try explicitly casting the parameter to a numeric type, e.g. cast(? as double)
.