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

Handle IN expressions when converting SqlNode to SQL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.17.0
    • Fix Version/s: 1.18.0
    • Component/s: jdbc-adapter
    • Labels:
      None

      Description

      It is not possible to convert an Rel tree into an sql string if it contains a filter which has an IN:

      example: `select * from emp where empno in (10,20)`
      note: Calcite's sql parser translates INs into either ORs or into a subquery.

      The following testcase can be added to RelToSqlConverterTest to reproduce the issue:

        @Test public void testHiveIn() {
          // this can't be tested using "sql" because Calcite's sql parser replaces INs with ORs or subqueries.
          final RelBuilder builder = RelBuilder.create(RelBuilderTest.config().build());
          RexBuilder rexBuilder = builder.getRexBuilder();
          RelNode root =
                  builder.scan("EMP")
                          .filter(rexBuilder.makeCall(SqlStdOperatorTable.IN, builder.field("DEPTNO"),
                                  builder.literal(20), builder.literal(21)))
                          .build();
          RelNode rel = root;
      
          SqlDialect dialect = SqlDialect.DatabaseProduct.HIVE.getDialect();
          final RelToSqlConverter converter = new RelToSqlConverter(dialect);
          final SqlNode sqlNode = converter.visitChild(0, rel).asStatement();
          String sqlStr = sqlNode.toSqlString(dialect).getSql();
          assertEquals("select * from emp where deptno in (10,20)", sqlStr);
        }
      

      The exception is raised because calcite expects that every IN is a subquery(because of sql2rel rewrites them)

      Caused by: java.lang.reflect.InvocationTargetException
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_171]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_171]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_171]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
              at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:103) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:109) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:173) ~[calcite-core-1.17.0.jar:1.17.0]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_171]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_171]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_171]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
              at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524) ~[calcite-core-1.17.0.jar:1.17.0]
              ... 61 more
      Caused by: java.lang.ClassCastException: org.apache.calcite.rex.RexCall cannot be cast to org.apache.calcite.rex.RexSubQuery
              at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:540) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:166) ~[calcite-core-1.17.0.jar:1.17.0]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_171]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_171]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_171]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
              at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:103) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:109) ~[calcite-core-1.17.0.jar:1.17.0]
              at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:173) ~[calcite-core-1.17.0.jar:1.17.0]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_171]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_171]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_171]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
              at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524) ~[calcite-core-1.17.0.jar:1.17.0]
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                kgyrtkirk Zoltan Haindrich
                Reporter:
                kgyrtkirk Zoltan Haindrich
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: