[CALCITE-2444] Handle IN expressions when converting SqlNode to SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Fix Version/s: 1.18.0
• Component/s: jdbc-adapter
• Assignee: Zoltan Haindrich
• Reporter: Zoltan Haindrich
• Created: 03/Aug/18 08:23
• Updated: 13/Aug/20 07:52
• Resolved: 17/Sep/18 18:09

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]

Issue Links

• blocks: HIVE-20822 Improvements to push computation to JDBC from Calcite
• relates to: CALCITE-4173 Add internal SEARCH operator and Sarg literal, replacing use of IN in RexCall
• links to: PR805