[bug][multistage] IN/NOT_IN function issue

[walterddr]

IN and NOT_IN operation has several caveats

1. it can be compiled into Sarg range search for example
   • floatField NOT IN (3, 5) --> Sarg{(-INF, 3) OR (3, 5) OR (5, +INF)}
   • intField IN(1,2,3,4,10,11) --> Sarg{[1,4] OR [10,11]}
2. it should be compiled without range search as non-range operation (just IN(field, literal_array) and NOT_IN(field, literal_array))
   • currently it is not possible b/c SqlToRelConverter will always convert it into or. see: SqlToRelConverter#convertInToOr

Because --> not all range searches are efficient in Pinot, this post a problem

[Jackie-Jiang]

Find a failing query (NOT IN within FILTER clause):

SET useStarTree = false; SELECT OriginStateName, Dest, count(ArrivalDelayGroups) FILTER ( WHERE OriginStateName NOT IN ('Michigan', 'Utah', 'Montana', 'Kentucky')), avg(Cancelled), sum(WeatherDelay) FROM mytable WHERE Flights BETWEEN 1 AND 1 AND Flights <> 1 AND OriginCityName >= 'Jackson/Vicksburg, MS' AND OriginStateName <= 'South Dakota' GROUP BY OriginStateName, Dest ORDER BY OriginStateName, Dest

Exception:

java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Cannot find function with name: NOT_IN
	at java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:396)
	at java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2096)
	at org.apache.pinot.query.service.server.QueryServer.submit(QueryServer.java:125)
	at org.apache.pinot.common.proto.PinotQueryWorkerGrpc$MethodHandlers.invoke(PinotQueryWorkerGrpc.java:284)
...
Caused by: java.lang.IllegalStateException: Cannot find function with name: NOT_IN
	at com.google.common.base.Preconditions.checkState(Preconditions.java:601)
	at org.apache.pinot.query.runtime.operator.operands.FunctionOperand.<init>(FunctionOperand.java:51)
	at org.apache.pinot.query.runtime.operator.operands.TransformOperandFactory.getTransformOperand(TransformOperandFactory.java:77)
	at org.apache.pinot.query.runtime.operator.operands.TransformOperandFactory.getTransformOperand(TransformOperandFactory.java:34)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submit(QueryDispatcher.java:148)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submitAndReduce(QueryDispatcher.java:92)
org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:191)
org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:288)