Window Functions not working after patch

1

I ran across an issue using window functions and I would like to get some feedback on whether it’s a known issue with Dremio

On Dremio 4.1.0, below query using windows function with Where clause works. On Dremio 4.2.1, same query does not work. To help diagnose, If I comment out the Where clause (WHERE RN = 1), query works on 4.2.1

SELECT PatientEpicId, City, County, StateOrProvince, StateOrProvinceAbbreviation, Country, Status FROM (
SELECT PatientEpicId, City, County, StateOrProvince, StateOrProvinceAbbreviation, Country, Status, ROW_NUMBER() OVER (PARTITION BY PatientEpicID ORDER BY _LastUpdatedInstant) as RN FROM Caboodle.CDW.FullAccess.PatientDim WHERE Status NOT IN (’*Deleted’, ‘*Unknown’, ‘*Unspecified’) AND PatientEpicId NOT IN (’’, ‘*Not Applicable’)
) WHERE RN = 1

2020-05-28 12:28:10,117 [21300b55-0813-d1c5-6eef-f09062f9a000/0:foreman-planning] ERROR c.d.s.commandpool.CommandWrapper - command 21300b55-0813-d1c5-6eef-f09062f9a000/0:foreman-planning failed
com.dremio.common.exceptions.UserException: ArrayIndexOutOfBoundsException
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:802)
at com.dremio.exec.planner.sql.SqlExceptionHelper.coerceException(SqlExceptionHelper.java:126)
at com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan(NormalHandler.java:60)
at com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan(HandlerToExec.java:59)
at com.dremio.exec.work.foreman.AttemptManager.plan(AttemptManager.java:392)
at com.dremio.exec.work.foreman.AttemptManager.lambda$run$1(AttemptManager.java:310)
at com.dremio.service.commandpool.CommandWrapper.run(CommandWrapper.java:62)
at com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$3(ContextMigratingExecutorService.java:192)
at com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run(ContextMigratingExecutorService.java:174)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.ArrayIndexOutOfBoundsException: null

2

Just today I also got an ArrayIndexOutOfBoundsException: null when try to use a window function (rank()). I’m using v. 4.1.8. My error log shows something very similar to the one posted here. We do not see the error, using our exact same query, on the 4.0.0 version.

3

@mcotter
Can you please enable a reflection on dataset “Caboodle.CDW.FullAccess.PatientDim” and try the query again?

4

Creating a Raw reflection allowed the query to run

5

@Ye_Li Are you working on a fix for this?

6

Hello @jabaier117

This is a known issue. The fix should be available in future releases.

Thanks,
@Rakesh_Malugu

1 Like
7

@Rakesh_Malugu So has this fix been released? If so, which version?

Thanks.

8

@jabaier117

The fix is not released yet. I can update the thread once it got released.

Until then, you can create the raw reflection as a work around.

Thanks,
@Rakesh_Malugu