[jira] [Comment Edited] (CALCITE-3242) RelToSqlConverter handling "NOT IN" incorrectly

["Atharv Rajput (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16905886#comment-16905886 ] 

Atharv Rajput edited comment on CALCITE-3242 at 8/13/19 7:25 AM:
-----------------------------------------------------------------

In our application we are creating Rel using RelBuilder based on some options provided by user on UI. Recently we added support for "NOT_IN" operator on UI and when we create Rel (which is similar to what I have mentioned in test case using SqlStdOperatorTable.NOT_IN) we get translated query which is syntactically incorrect.

Yesterday, I created a test case in SqlToRelConverterTest for query:
{code:java}
select * from emp where deptno not in (20, 30){code}
And did debug corresponding Rel, which models WHERE clause condition like:
{code:java}
condition = {RexCall@3049} "NOT(OR(=($7, 20), =($7, 30)))"
 op = {SqlPrefixOperator@3055} "NOT"
 operands = {SingletonImmutableList@3056}  size = 1
  0 = {RexCall@3065} "OR(=($7, 20), =($7, 30))"
   op = {SqlBinaryOperator@3067} "OR"
   operands = {RegularImmutableList@3068}  size = 2
    0 = {RexCall@3835} "=($7, 20)"
    1 = {RexCall@3836} "=($7, 30)"
   type = {BasicSqlType@3057} "BOOLEAN"
   digest = "OR(=($7, 20), =($7, 30))"{code}
If I convert Rel to SQL again, resultant query is:
{code:java}
SELECT 1
FROM `CATALOG`.`SALES`.`EMP`
WHERE NOT (`DEPTNO` = 20 OR `DEPTNO` = 30){code}
I suppose you were referring to this in your first comment. We can change our code to create condition in this way as well.

Few questions though:
 * Would there be an issue if we support the Rel that I mentioned in test case using SqlStdOperatorTable.NOT_IN instead of breaking it in SqlStdOperatorTable.NOT and SqlStdOperatorTable.OR operators.
 * In which scenario one should use SqlStdOperatorTable.NOT_IN operator?

Let me know if I'm missing something as my understanding is very basic in Calcite. Apologies for long comment, appreciate your response.

 


was (Author: atharv.raj):
In our application we are creating Rel using RelBuilder based on some options provided by user on UI. Recently we added support for "NOT_IN" operator on UI and when we create Rel (which is similar to what I have mentioned in test case using SqlStdOperatorTable.NOT_IN) we get translated query which is syntactically incorrect.

Yesterday, I created a test case in SqlToRelConverterTest for query:
{code:java}
select * from emp where deptno not in (20, 30){code}
And did debug corresponding Rel, which models WHERE clause condition like:
{code:java}
condition = {RexCall@3049} "NOT(OR(=($7, 20), =($7, 30)))"
 op = {SqlPrefixOperator@3055} "NOT"
 operands = {SingletonImmutableList@3056}  size = 1
  0 = {RexCall@3065} "OR(=($7, 20), =($7, 30))"
   op = {SqlBinaryOperator@3067} "OR"
   operands = {RegularImmutableList@3068}  size = 2
    0 = {RexCall@3835} "=($7, 20)"
    1 = {RexCall@3836} "=($7, 30)"
   type = {BasicSqlType@3057} "BOOLEAN"
   digest = "OR(=($7, 20), =($7, 30))"{code}
If I convert Rel to SQL again, resultant query is:
{code:java}
SELECT 1
FROM `CATALOG`.`SALES`.`EMP`
WHERE NOT (`DEPTNO` = 20 OR `DEPTNO` = 30){code}
I suppose you were referring to this in your first comment. We can change our code to create condition in this way as well.

One question though, can't we support the Rel that I mentioned in test case using SqlStdOperatorTable.NOT_IN instead of breaking it in SqlStdOperatorTable.NOT and SqlStdOperatorTable.OR operators. Apologies for long comment, I just wanted to explain my scenario. Let me know if I'm missing something as my understanding is very basic in Calcite.

 

> 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)