SQL: "not in" subquery non working

Hi, the following SQL query generates the error below:

select distinct someVar

from dataSource

where someVar not in

(

select distinct someVar

from dataSource

where anotherVar=‘someValue’

)

``

if, on line 3, ‘not in’ is simpy ‘in’ the query works without problems… any idea?

ERROR: Error -1 (00000) : Error while executing SQL “…”:

Remote driver error: RuntimeException: Error while applying rule ReduceExpressionsRule(Filter), args

[rel#73522:LogicalFilter.NONE.(input=rel#73521:Subset#9.NONE.,condition=NOT(CASE(=($68, 0), false, IS NOT NULL($72), true, IS NULL($70), null,

<($69, $68), null, false)))] -> NullPointerException: (null exception message)

``

Thanks

Hi, below the full stack trace and query as I have replicated the error in a java class I wrote. Is this a bug? I am really stuck with this… thanks for any help.

select distinct id

from dataSource

where id not in

(

select distinct id

from dataSource

where

warranty_type_l1 in (‘v1’,‘v2’)

or

goods in (‘DD’)

or

de in (‘BBBBBB’)

)

``

Remote driver error: RuntimeException: Error while applying rule ReduceExpressionsRule(Filter), args [rel#4618:LogicalFilter.NONE.(input=rel#4617:Subset#9.NONE.,condition=NOT(CASE(=($68, 0), false, IS NOT NULL($72), true, IS NULL($70), null, <($69, $68), null, false)))] -> NullPointerException: (null exception message)

at org.apache.calcite.avatica.Helper.createException(Helper.java:54)

at org.apache.calcite.avatica.Helper.createException(Helper.java:41)

at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)

at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218)

at it.nextbit.druidtests.Druid.main(Druid.java:64)

java.lang.RuntimeException: Error while applying rule ReduceExpressionsRule(Filter), args [rel#4618:LogicalFilter.NONE.(input=rel#4617:Subset#9.NONE.,condition=NOT(CASE(=($68, 0), false, IS NOT NULL($72), true, IS NULL($70), null, <($69, $68), null, false)))]

at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:235)

at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:650)

at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:368)

at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:313)

at io.druid.sql.calcite.planner.DruidPlanner.planWithDruidConvention(DruidPlanner.java:144)

at io.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:112)

at io.druid.sql.avatica.DruidStatement.prepare(DruidStatement.java:166)

at io.druid.sql.avatica.DruidMeta.prepareAndExecute(DruidMeta.java:186)

at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:219)

at org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:928)

at org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:880)

at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:95)

at org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:52)

at org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:129)

at io.druid.sql.avatica.DruidAvaticaHandler.handle(DruidAvaticaHandler.java:60)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)

at org.eclipse.jetty.server.Server.handle(Server.java:534)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108)

at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)

at java.lang.Thread.run(Thread.java:748)

Caused by: java.lang.NullPointerException

at org.apache.calcite.rex.RexLiteral.booleanValue(RexLiteral.java:623)

at io.druid.sql.calcite.expression.Expressions.toDruidExpression(Expressions.java:361)

at io.druid.sql.calcite.expression.Expressions.toDruidExpressions(Expressions.java:172)

at io.druid.sql.calcite.expression.Expressions.toDruidExpression(Expressions.java:294)

at io.druid.sql.calcite.planner.DruidRexExecutor.reduce(DruidRexExecutor.java:62)

at org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressionsInternal(ReduceExpressionsRule.java:549)

at org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:470)

at org.apache.calcite.rel.rules.ReduceExpressionsRule$FilterReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:149)

at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:211)

… 28 more

``

Hi Frankie,

This looks like a bug or at least like a missing feature. Could you please raise a GitHub issue for it? If you have a test case for CalciteQueryTest that illustrates the problem, even better.

Hi, I have opened issue 5480

Could you please let me know how to write a CalciteQueryTest and I’ll create the code as soon as I have some spare time.

Thanks for your help

Thanks for raising an issue.

For writing a unit test, check out the file CalciteQueryTest and look at the other tests in there for examples.

Will do. Thanks