Can you please log a JIRA case for this? I was able to reproduce it by adding a
single line to SqlValidatorTest.testBind:
sql("select * from emp where deptno between ? and ?").ok();
and also
sql("select * from emp where ? between deptno and ?").ok();
sql("select * from emp where ? between ? and deptno").ok();
The fix is probably to give SqlBetweenOperator a SqlOperandTypeInference like
the one in SqlBinaryOperator, where the type of unknown operands is deduced to
be the same as the type of known operands.
Julian
> On Jul 7, 2016, at 8:10 PM, Yiming Liu <[email protected]> wrote:
>
> Hi Calcite developers,
>
> I was using Kylin for JDBC query. I know Kylin's SQL parser is based on
> Calcite. The following PreparedStatement does not work in SQL validation
> phase.
>
> I did some investigation on the Calcite 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.
>
> I did the check on Calcite 1.6-1.8.
>
> 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);
> }
> }
>
>
> Following is the detail exception log
>
> 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
> --
> With Warm regards
>
> Yiming Liu (刘一鸣)
