[multistage] IS NULL/ IS NOT NULL getting skipped from the queries using multistage

[abhioncbr]

Explain query results for the case/when transformation function based on NULL value comparison shows skipping of the transformation function. For example

EXPLAIN PLAN FOR SELECT CASE WHEN ArrTimeBlk IS NOT NULL THEN 'null11' ELSE ArrTimeBlk END AS val FROM airlineStats 

Execution Plan
LogicalProject(val=['null11':VARCHAR])
  LogicalTableScan(table=[[airlineStats]])

another example

EXPLAIN PLAN FOR SELECT CASE WHEN ArrTimeBlk IS NULL THEN 'null11' ELSE ArrTimeBlk END AS val FROM airlineStats 

Execution Plan
LogicalProject(val=[$10])
  LogicalTableScan(table=[[airlineStats]])

Where clause based query

EXPLAIN PLAN FOR SELECT ArrTimeBlk FROM airlineStats where ArrTimeBlk is not null
Execution Plan
LogicalProject(ArrTimeBlk=[$10])
  LogicalTableScan(table=[[airlineStats]])

EXPLAIN PLAN FOR SELECT ArrTimeBlk FROM airlineStats where ArrTimeBlk is null
Execution Plan
LogicalValues(tuples=[[]])

Using docker pinot:1.0.0 release image

[Jackie-Jiang]

This is a known issue. Currently in multi-stage engine column is registered as non-null, so IS NULL and IS NOT NULL are just short circuited. We are considering adding nullable into FieldSpec which can be picked by multi-stage engine to register the column.
cc @walterddr

[walterddr]

👍 on @Jackie-Jiang 's point. we already have a pending review PR for this for a long time:
#10423

however there are debates regarding how to handle configs, how to handle mis-configured scenarios and more; we will likely come up with a better design soon to address this

[walterddr]

CC @ankitsultana who also worked on #10423, #10381

[walterddr]

closing this as duplicate of #10381