Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1310

Infer type of arguments to BETWEEN operator

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.8.0, 1.7.0, 1.6.0
    • Fix Version/s: 1.9.0
    • Component/s: core
    • Labels:
      None

      Description

      SqlBetweenOperator should defer SqlOperandTypeInference for PreparedStatement. The following PreparedStatement does not work in SQL validation phase.

              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);
                  }
              }
      

      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

      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
      

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        I was able to reproduce it by adding a few lines to SqlValidatorTest.testBind:

         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();
        

        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.

        Show
        julianhyde Julian Hyde added a comment - I was able to reproduce it by adding a few lines to SqlValidatorTest.testBind : 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(); 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.
        Hide
        yimingliu Billy Liu added a comment -

        Hi Julian,

        Has proposed a simple fix for this one, please help review.
        https://github.com/apache/calcite/pull/252

        Show
        yimingliu Billy Liu added a comment - Hi Julian, Has proposed a simple fix for this one, please help review. https://github.com/apache/calcite/pull/252
        Hide
        julianhyde Julian Hyde added a comment -
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/63c51d0c . Thanks for the PR, Yiming Liu !
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.9.0 (2016-09-22)

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.9.0 (2016-09-22)

          People

          • Assignee:
            yimingliu Billy Liu
            Reporter:
            yimingliu Billy Liu
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development