[
https://issues.apache.org/jira/browse/CALCITE-1310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15367207#comment-15367207
]
Julian Hyde commented on CALCITE-1310:
--------------------------------------
I was able to reproduce it by adding a few lines to
{{SqlValidatorTest.testBind}}:
{code}
sql("select * from emp where deptno between ? and ?").ok();
sql("select * from emp where ? between deptno and ?").ok();
sql("select * from emp where ? between ? and deptno").ok();
{code}
The fix is probably to give {{SqlBetweenOperator}} a
{{SqlOperandTypeInference}} like the one in {{SqlBinaryOperator}} (used for
{{<}} for example), where the type of unknown operands is deduced to be the
same as the type of known operands.
> SqlBetweenOperator should defer SqlOperandTypeInference for PreparedStatement
> -----------------------------------------------------------------------------
>
> Key: CALCITE-1310
> URL: https://issues.apache.org/jira/browse/CALCITE-1310
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.7.0, 1.6.0, 1.8.0
> Reporter: Yiming Liu
> Assignee: Julian Hyde
> Priority: Minor
>
> The following PreparedStatement does not work in SQL validation phase.
> {code}
> final String sql = "select count( * ) from kylin_sales where
> LSTG_SITE_ID between ? and ?";
> try (PreparedStatement stmt = conn.prepareStatement(sql)) {
> stmt.setInt(1, 0);
> stmt.setInt(2, 5);
> try (ResultSet rs = stmt.executeQuery()) {
> printResultSet(rs);
> }
> }
> {code}
> It seems the SqlBetweenOperator does not infer the operand type when parsing.
> So in the SQL validation phase, the NULL operandTypeInference will throw out
> exceptions. At the other side, The SqlInOperator will defer the parameter
> type when parsing.
> Following is the detail exception log
> {noformat}
> Caused by: org.apache.calcite.runtime.CalciteContextException: At line 1,
> column 61: Illegal use of dynamic parameter
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:715)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:703)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3932)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1565)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1648)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:3374)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:3366)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2961)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:848)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:834)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:207)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:808)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:522)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:534)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:226)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:194)
> ... 78 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Illegal use
> of dynamic parameter
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
> ... 101 more
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
