Possible Bug in Pinot LAG window function. (1.2.0)

select
	  ORDER_ID,
	  ORDER_NUMBER,
	  CUSTORDER_ID,
	  ORDER_VALIDATION_CODE,
	  POD_CODE,
	  DELIVERY_FROM_DAT,
	  DELIVERY_TO_DAT,
	  CTL_CRE_TS,
	  CTL_MOD_TS,
	  ORDER_STATUS_CD,
	  SAREA_ID,
	    LAG(ON_HOLD_ORDER_AND_LOCKED_FLAG, 1, 0) OVER (PARTITION BY ORDER_ID ORDER BY CTL_MOD_TS) AS prev_is_active
		from
		Orders
  )

If default is not set, the result return correctly with the last row returning a NULL for prev_is_active since no row before it. However setting the default of 0 throws an unrelated timestamp error. Could this be related to NULL handling?

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:156)
	at org.apache.pinot.common.proto.PinotQueryWorkerGrpc$MethodHandlers.invoke(PinotQueryWorkerGrpc.java:284)
...
Caused by: java.lang.RuntimeException: Caught exception while submitting request: 1473823763000000159, stage: 2
	at org.apache.pinot.query.service.server.QueryServer.lambda$submit$1(QueryServer.java:144)
	at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)
	... 3 more
Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: Failed to instantiate WindowFunction for function: LAG
	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.lambda$submit$1(QueryServer.java:141)
	... 4 more
...
Caused by: java.lang.RuntimeException: Failed to instantiate WindowFunction for function: LAG
	at org.apache.pinot.query.runtime.operator.window.WindowFunctionFactory.construnctWindowFunction(WindowFunctionFactory.java:56)
	at org.apache.pinot.query.runtime.operator.WindowAggregateOperator.<init>(WindowAggregateOperator.java:145)
	at org.apache.pinot.query.runtime.plan.PhysicalPlanVisitor.visitWindow(PhysicalPlanVisitor.java:107)
	at org.apache.pinot.query.runtime.plan.PhysicalPlanVisitor.visitWindow(PhysicalPlanVisitor.java:65)
...
Caused by: java.lang.reflect.InvocationTargetException
	at jdk.internal.reflect.GeneratedConstructorAccessor151.newInstance(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
...
Caused by: java.lang.UnsupportedOperationException: Cannot convert value from INTEGER to TIMESTAMP
	at org.apache.pinot.common.utils.PinotDataType$5.toTimestamp(PinotDataType.java:300)
	at org.apache.pinot.common.utils.PinotDataType$10.convert(PinotDataType.java:593)
	at org.apache.pinot.common.utils.PinotDataType$10.convert(PinotDataType.java:545)
	at org.apache.pinot.query.runtime.operator.window.value.LagValueWindowFunction.<init>(LagValueWindowFunction.java:63)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submit(QueryDispatcher.java:198)
org.apache.pinot.query.service.dispatch.QueryDispatcher.submitAndReduce(QueryDispatcher.java:95)
org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:219)
org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:133)

Can you check with 1.3? Also cc: @Gonzalo Ortiz

I got the same error. The behavior is a bit confusing, First Image works but not the second. swapping the position of the row number windows make it work.

I missed this message. I'll try to take a look tomorrow morning (CEST)

Sure. Basically, if the row number is not the first column the LAG function fails if a default value is passed to the function. Without a default value passed, it works irrespective of the row number position or even it does not existing in the subquery

thanks for that complete report. If I may ask, can you reproduce it on a quickstart? That would be very helpful to fix it

Thanks for the quick fix. No worries, I managed to make it work for now.