[ https://issues.apache.org/jira/browse/CALCITE-2444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16597368#comment-16597368 ]
Zoltan Haindrich commented on CALCITE-2444: ------------------------------------------- [~julianhyde] I've updated the pullrequest; I tried to address all of your comments. Let me know if there are any more issues with it. > 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 > Priority: Major > > 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)