[ https://issues.apache.org/jira/browse/CALCITE-3242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16906162#comment-16906162 ]
Danny Chan commented on CALCITE-3242: ------------------------------------- Hi, [~atharv.raj] Now Calcite would always try to convert the "in value expression" pattern to "not" RexCall[1], so you can do this too. I think you should have to use the SqlStdOperatorTable.NOT_IN operator when the RHS of in is a subquery. But i'm not sure if this pattern can do rel-to-sql conversion correctly. [1] https://github.com/apache/calcite/blob/5ec3a2a503dcf26fe1b3cad8a5a9467264213dcf/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1429 > RelToSqlConverter handling "NOT IN" incorrectly > ----------------------------------------------- > > Key: CALCITE-3242 > URL: https://issues.apache.org/jira/browse/CALCITE-3242 > Project: Calcite > Issue Type: Bug > Components: core > Reporter: Atharv Rajput > Assignee: Atharv Rajput > Priority: Minor > Fix For: 1.21.0 > > Original Estimate: 1h > Remaining Estimate: 1h > > When RelNode is having `NOT IN` operator, it's being converted incorrectly by > RelToSqlConverter. For example > {code:java} > @Test public void testNotInOperator() { > final RelBuilder builder = relBuilder().scan("EMP"); > final RexNode condition = > builder.call(SqlStdOperatorTable.NOT_IN, builder.field("DEPTNO"), > builder.literal(20), builder.literal(30)); > final RelNode root = relBuilder().scan("EMP").filter(condition).build(); > final String sql = toSql(root); > final String expectedSql = "SELECT *\n" > + "FROM \"scott\".\"EMP\"\n" > + "WHERE \"DEPTNO\" NOT IN (20, 30)"; > assertThat(sql, isLinux(expectedSql)); > } > {code} > Above test fails with error: > {code:java} > Expected: is "SELECT *\nFROM \"scott\".\"EMP\"\nWHERE \"DEPTNO\" NOT IN (20, > 30)" > but: was "SELECT *\nFROM \"scott\".\"EMP\"\nWHERE \"DEPTNO\" NOT IN 20 NOT IN > 30" > {code} > -- This message was sent by Atlassian JIRA (v7.6.14#76016)