Zoltan Haindrich created CALCITE-2444: -----------------------------------------
Summary: Rel2Sql: support c IN (1,2) Key: CALCITE-2444 URL: https://issues.apache.org/jira/browse/CALCITE-2444 Project: Calcite Issue Type: Bug Components: jdbc-adapter Affects Versions: 1.17.0 Reporter: Zoltan Haindrich Assignee: Zoltan Haindrich 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: {code} @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); } {code} The exception is raised because calcite expects that every IN is a subquery(because of sql2rel rewrites them) {code} 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] {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)