SQL: NPE on filtered aggregation with "NOT IN"

[gianm]

This query throws an NPE during planning.

SELECT
  COUNT(*) filter(WHERE dim1 NOT IN ('1'))
  COUNT(dim2) filter(WHERE dim1 NOT IN ('1'))
FROM druid.foo

Seems to be a Calcite bug, https://issues.apache.org/jira/browse/CALCITE-1910.

The stack trace:

java.lang.NullPointerException
        at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:212)
        at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4426)
        at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.translateAgg(SqlToRelConverter.java:4888)
        at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4819)
        at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4663)
        at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
        at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4795)
        at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4663)
        at org.apache.calcite.sql.SqlNodeList.accept(SqlNodeList.java:153)
        at org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:2715)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:2656)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:658)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:620)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3066)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:556)

[gianm]

Patched upstream, fix should be in Calcite 1.15.0.

[jon-wei]

@gianm I tried upgrading to Calcite 1.15.0, testFilteredAggregationWithNotIn() in CalciteQueryTest now passes, but there's a regression where the query in testCountStarWithTimeFilterOnLongColumnUsingExtractEpoch() is now unplannable:

org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node [rel#5260:Subset#3.DRUID.[]] could not be implemented; planner state:

Root: rel#5260:Subset#3.DRUID.[]
Original rel:
LogicalAggregate(subset=[rel#5260:Subset#3.DRUID.[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 2.5, cumulative cost = {2.8125 rows, 0.0 cpu, 0.0 io}, id = 5258
  LogicalFilter(subset=[rel#5257:Subset#2.NONE.[]], condition=[AND(>=($0, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($0, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))]): rowcount = 25.0, cumulative cost = {25.0 rows, 100.0 cpu, 0.0 io}, id = 5256
    LogicalProject(subset=[rel#5255:Subset#1.NONE.[]], cnt=[$1]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io}, id = 5254
      LogicalTableScan(subset=[rel#5253:Subset#0.NONE.[]], table=[[druid, foo]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 5239

Sets:
Set#0, type: RecordType(TIMESTAMP(0) __time, BIGINT cnt, VARCHAR dim1, VARCHAR dim2, FLOAT m1, DOUBLE m2, OTHER unique_dim1)
	rel#5253:Subset#0.NONE.[], best=null, importance=0.6561
		rel#5239:LogicalTableScan.NONE.[](table=[druid, foo]), rowcount=100.0, cumulative cost={inf}
		rel#5271:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"foo"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","cnt","dim1","dim2","m1","m2","unique_dim1"],"legacy":false,"context":{"defaultTimeout":300000,"maxScatterGatherBytes":9223372036854775807,"sqlCurrentTimestamp":"2000-01-01T00:00:00Z"},"descending":false},signature={__time:LONG, cnt:LONG, dim1:STRING, dim2:STRING, m1:FLOAT, m2:DOUBLE, unique_dim1:COMPLEX}), rowcount=1.0, cumulative cost={inf}
	rel#5274:Subset#0.DRUID.[], best=rel#5273, importance=0.32805
		rel#5273:DruidQueryRel.DRUID.[](query={"queryType":"scan","dataSource":{"type":"table","name":"foo"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","cnt","dim1","dim2","m1","m2","unique_dim1"],"legacy":false,"context":{"defaultTimeout":300000,"maxScatterGatherBytes":9223372036854775807,"sqlCurrentTimestamp":"2000-01-01T00:00:00Z"},"descending":false},signature={__time:LONG, cnt:LONG, dim1:STRING, dim2:STRING, m1:FLOAT, m2:DOUBLE, unique_dim1:COMPLEX}), rowcount=1.0, cumulative cost={1.0 rows, 0.0 cpu, 0.0 io}
Set#1, type: RecordType(BIGINT cnt)
	rel#5255:Subset#1.NONE.[], best=null, importance=0.7290000000000001
		rel#5254:LogicalProject.NONE.[](input=rel#5253:Subset#0.NONE.[],cnt=$1), rowcount=100.0, cumulative cost={inf}
		rel#5275:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"foo"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["cnt"],"legacy":false,"context":{"defaultTimeout":300000,"maxScatterGatherBytes":9223372036854775807,"sqlCurrentTimestamp":"2000-01-01T00:00:00Z"},"descending":false},signature={cnt:LONG}), rowcount=1.0, cumulative cost={inf}
	rel#5277:Subset#1.DRUID.[], best=rel#5276, importance=0.36450000000000005
		rel#5276:DruidQueryRel.DRUID.[](query={"queryType":"scan","dataSource":{"type":"table","name":"foo"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["cnt"],"legacy":false,"context":{"defaultTimeout":300000,"maxScatterGatherBytes":9223372036854775807,"sqlCurrentTimestamp":"2000-01-01T00:00:00Z"},"descending":false},signature={cnt:LONG}), rowcount=1.0, cumulative cost={1.001 rows, 0.0 cpu, 0.0 io}
Set#2, type: RecordType(BIGINT cnt)
	rel#5257:Subset#2.NONE.[], best=null, importance=0.81
		rel#5256:LogicalFilter.NONE.[](input=rel#5255:Subset#1.NONE.[],condition=AND(>=($0, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($0, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))), rowcount=25.0, cumulative cost={inf}
		rel#5265:LogicalProject.NONE.[](input=rel#5264:Subset#4.NONE.[],cnt=$1), rowcount=25.0, cumulative cost={inf}
Set#3, type: RecordType(BIGINT EXPR$0)
	rel#5259:Subset#3.NONE.[], best=null, importance=0.9
		rel#5258:LogicalAggregate.NONE.[](input=rel#5257:Subset#2.NONE.[],group={},EXPR$0=COUNT()), rowcount=2.5, cumulative cost={inf}
		rel#5266:LogicalAggregate.NONE.[](input=rel#5264:Subset#4.NONE.[],group={},EXPR$0=COUNT()), rowcount=2.5, cumulative cost={inf}
	rel#5260:Subset#3.DRUID.[], best=null, importance=1.0
		rel#5261:AbstractConverter.DRUID.[](input=rel#5259:Subset#3.NONE.[],convention=DRUID,sort=[]), rowcount=2.5, cumulative cost={inf}
Set#4, type: RecordType(TIMESTAMP(0) __time, BIGINT cnt, VARCHAR dim1, VARCHAR dim2, FLOAT m1, DOUBLE m2, OTHER unique_dim1)
	rel#5264:Subset#4.NONE.[], best=null, importance=0.7290000000000001
		rel#5262:LogicalFilter.NONE.[](input=rel#5253:Subset#0.NONE.[],condition=AND(>=($1, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($1, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))), rowcount=25.0, cumulative cost={inf}



	at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:441)
	at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:291)
	at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:666)
	at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:368)
	at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:387)
	at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:336)
	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.calcite.CalciteQueryTest.getResults(CalciteQueryTest.java:6392)
	at io.druid.sql.calcite.CalciteQueryTest.testQuery(CalciteQueryTest.java:6350)
	at io.druid.sql.calcite.CalciteQueryTest.testQuery(CalciteQueryTest.java:6318)
	at io.druid.sql.calcite.CalciteQueryTest.testCountStarWithTimeFilterOnLongColumnUsingExtractEpoch(CalciteQueryTest.java:3187)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:168)
	at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
	at io.druid.sql.calcite.util.QueryLogHook$1.evaluate(QueryLogHook.java:101)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
	Suppressed: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node [rel#5295:Subset#8.BINDABLE.[]] could not be implemented; planner state:

Root: rel#5295:Subset#8.BINDABLE.[]
Original rel:
LogicalAggregate(subset=[rel#5295:Subset#8.BINDABLE.[]], group=[{}], EXPR$0=[COUNT()]): rowcount = 2.5, cumulative cost = {2.8125 rows, 0.0 cpu, 0.0 io}, id = 5293
  LogicalFilter(subset=[rel#5292:Subset#7.NONE.[]], condition=[AND(>=($0, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($0, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))]): rowcount = 25.0, cumulative cost = {25.0 rows, 100.0 cpu, 0.0 io}, id = 5291
    LogicalProject(subset=[rel#5290:Subset#6.NONE.[]], cnt=[$1]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io}, id = 5289
      LogicalTableScan(subset=[rel#5288:Subset#5.NONE.[]], table=[[druid, foo]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 5239

Sets:
Set#5, type: RecordType(TIMESTAMP(0) __time, BIGINT cnt, VARCHAR dim1, VARCHAR dim2, FLOAT m1, DOUBLE m2, OTHER unique_dim1)
	rel#5288:Subset#5.NONE.[], best=null, importance=0.6561
		rel#5239:LogicalTableScan.NONE.[](table=[druid, foo]), rowcount=100.0, cumulative cost={inf}
		rel#5336:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"foo"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["__time","cnt","dim1","dim2","m1","m2","unique_dim1"],"legacy":false,"context":{"defaultTimeout":300000,"maxScatterGatherBytes":9223372036854775807,"sqlCurrentTimestamp":"2000-01-01T00:00:00Z"},"descending":false},signature={__time:LONG, cnt:LONG, dim1:STRING, dim2:STRING, m1:FLOAT, m2:DOUBLE, unique_dim1:COMPLEX}), rowcount=1.0, cumulative cost={inf}
	rel#5324:Subset#5.BINDABLE.[], best=null, importance=0.7290000000000001
		rel#5323:InterpretableConverter.BINDABLE.[](input=rel#5288:Subset#5.NONE.[]), rowcount=100.0, cumulative cost={inf}
Set#6, type: RecordType(BIGINT cnt)
	rel#5290:Subset#6.NONE.[], best=null, importance=0.7290000000000001
		rel#5289:LogicalProject.NONE.[](input=rel#5288:Subset#5.NONE.[],cnt=$1), rowcount=100.0, cumulative cost={inf}
		rel#5337:DruidQueryRel.NONE.[](query={"queryType":"scan","dataSource":{"type":"table","name":"foo"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"virtualColumns":[],"resultFormat":"compactedList","batchSize":20480,"limit":9223372036854775807,"filter":null,"columns":["cnt"],"legacy":false,"context":{"defaultTimeout":300000,"maxScatterGatherBytes":9223372036854775807,"sqlCurrentTimestamp":"2000-01-01T00:00:00Z"},"descending":false},signature={cnt:LONG}), rowcount=1.0, cumulative cost={inf}
	rel#5321:Subset#6.BINDABLE.[], best=null, importance=0.81
		rel#5320:InterpretableConverter.BINDABLE.[](input=rel#5290:Subset#6.NONE.[]), rowcount=100.0, cumulative cost={inf}
		rel#5334:BindableProject.BINDABLE.[](input=rel#5324:Subset#5.BINDABLE.[],cnt=$1), rowcount=100.0, cumulative cost={inf}
Set#7, type: RecordType(BIGINT cnt)
	rel#5292:Subset#7.NONE.[], best=null, importance=0.81
		rel#5291:LogicalFilter.NONE.[](input=rel#5290:Subset#6.NONE.[],condition=AND(>=($0, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($0, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))), rowcount=25.0, cumulative cost={inf}
		rel#5305:LogicalProject.NONE.[](input=rel#5304:Subset#9.NONE.[],cnt=$1), rowcount=25.0, cumulative cost={inf}
	rel#5300:Subset#7.BINDABLE.[], best=null, importance=0.9
		rel#5315:InterpretableConverter.BINDABLE.[](input=rel#5292:Subset#7.NONE.[]), rowcount=25.0, cumulative cost={inf}
		rel#5325:BindableProject.BINDABLE.[](input=rel#5312:Subset#9.BINDABLE.[],cnt=$1), rowcount=25.0, cumulative cost={inf}
		rel#5327:BindableFilter.BINDABLE.[[]](input=rel#5321:Subset#6.BINDABLE.[],condition=AND(>=($0, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($0, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))), rowcount=25.0, cumulative cost={inf}
Set#8, type: RecordType(BIGINT EXPR$0)
	rel#5294:Subset#8.NONE.[], best=null, importance=0.9
		rel#5293:LogicalAggregate.NONE.[](input=rel#5292:Subset#7.NONE.[],group={},EXPR$0=COUNT()), rowcount=2.5, cumulative cost={inf}
		rel#5309:LogicalAggregate.NONE.[](input=rel#5304:Subset#9.NONE.[],group={},EXPR$0=COUNT()), rowcount=2.5, cumulative cost={inf}
	rel#5295:Subset#8.BINDABLE.[], best=null, importance=1.0
		rel#5296:AbstractConverter.BINDABLE.[](input=rel#5294:Subset#8.NONE.[],convention=BINDABLE,sort=[]), rowcount=2.5, cumulative cost={inf}
		rel#5297:InterpretableConverter.BINDABLE.[](input=rel#5294:Subset#8.NONE.[]), rowcount=2.5, cumulative cost={inf}
		rel#5301:BindableAggregate.BINDABLE.[](input=rel#5300:Subset#7.BINDABLE.[],group={},EXPR$0=COUNT()), rowcount=2.5, cumulative cost={inf}
		rel#5313:BindableAggregate.BINDABLE.[](input=rel#5312:Subset#9.BINDABLE.[],group={},EXPR$0=COUNT()), rowcount=2.5, cumulative cost={inf}
Set#9, type: RecordType(TIMESTAMP(0) __time, BIGINT cnt, VARCHAR dim1, VARCHAR dim2, FLOAT m1, DOUBLE m2, OTHER unique_dim1)
	rel#5304:Subset#9.NONE.[], best=null, importance=0.7290000000000001
		rel#5302:LogicalFilter.NONE.[](input=rel#5288:Subset#5.NONE.[],condition=AND(>=($1, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($1, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))), rowcount=25.0, cumulative cost={inf}
	rel#5312:Subset#9.BINDABLE.[], best=null, importance=0.81
		rel#5322:InterpretableConverter.BINDABLE.[](input=rel#5304:Subset#9.NONE.[]), rowcount=25.0, cumulative cost={inf}
		rel#5335:BindableFilter.BINDABLE.[[]](input=rel#5324:Subset#5.BINDABLE.[],condition=AND(>=($1, *(EXTRACT(FLAG(EPOCH), 1970-01-01 00:00:00), 1000)), <($1, *(EXTRACT(FLAG(EPOCH), 1970-01-02 00:00:00), 1000)))), rowcount=25.0, cumulative cost={inf}


		at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:441)
		at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:291)
		at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:666)
		at org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:368)
		at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:387)
		at org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:336)
		at io.druid.sql.calcite.planner.DruidPlanner.planWithBindableConvention(DruidPlanner.java:265)
		at io.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:117)
		... 36 more

[jon-wei]

upgrade patch for druid here: https://github.com/jon-wei/druid/tree/calcite15