SQL: not in operator can cause NullPointerException

[fmarelli]

The query below returns NullPointerException, it works ok if 'not in' at line 3 is changed to 'in'

select distinct id 
 from dataSource
 where id not in
 (
 select distinct id
from dataSource
where 
var1 in ('v1','v2')
or
var2 in ('DD')
or
var3 in ('BBBBBB')
)

Error stack trace

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

[meazza]

{
"error": "Unknown exception",
"errorMessage": "Cannot build plan for query: SELECT COUNT(*) AS TheCount FROM data_source where dt='2018-01-01' AND biz_line NOT IN (select biz_line from data_source where dt='2018-01-01')",
"errorClass": "io.druid.java.util.common.ISE",
"host": null
}

it seems return 500 ISE now, is there a plan to support this feature: not in sub-query expression. If it's necessary, I'd like to contribute to this feature. @gianm

[tecigo]

is this prioritiesed?

[vogievetsky]

Ok so good news, bad news.

Good news: This is 'fixed' in the sense that it no longer throws an NPE.

Bad news: it still does not work not it tells you correctly that it can not build a plan for a NOT IN

This is tracked in #8518, I am going to close this one as it is now effectively a dupe.