You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Yiming Liu <li...@gmail.com> on 2016/07/08 03:10:30 UTC
PreparedStatement validation in Between operator
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 (刘一鸣)
Re: PreparedStatement validation in Between operator
Posted by Yiming Liu <li...@gmail.com>.
Thanks, Julian.
JIRA filed. https://issues.apache.org/jira/browse/CALCITE-1310
2016-07-08 11:31 GMT+08:00 Julian Hyde <jh...@apache.org>:
> 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 <li...@gmail.com> 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 (刘一鸣)
>
>
--
With Warm regards
Yiming Liu (刘一鸣)
Re: PreparedStatement validation in Between operator
Posted by Julian Hyde <jh...@apache.org>.
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 <li...@gmail.com> 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 (刘一鸣)