IllegalStateException: null using first_value function (Impala 2.9.0)

[#1]

Hi,

I've upgraded Impala from 2.7.0 to 2.9.0 and one of my queries stopped working after that.

On running this query:

select first_value(transxref) 
over (partition by substr(gbm.transxref,1,(instr(gbm.transxref,'/'))-1)
order by substr(gbm.transxref,1,(instr(gbm.transxref,'/'))-1), reportdate)
from gbm_partition gbm

I  receive error: "IllegalStateException: null"

But if I will change order by clause and will run query below - it will work.

select first_value(transxref) 
over (partition by substr(gbm.transxref,1,(instr(gbm.transxref,'/'))-1)
order by transxref, reportdate)
from gbm_partition gbm

1. Can anybody please suggest, if there are any incompatibilities between 2.7.0 and 2.9.0 (I haven't found anything in Release Notes)?

2. If there are - can anybody please suggest a workaround for this issue?

Thanks in advance,

Anastasiia

[#2 · Accepted solution]

Thanks for your report and apologies for this issue. We're aware of the problem and are workign on a fix:

https://issues.apache.org/jira/browse/IMPALA-6473

This is not a deliberate incompatibility, but simply a bug.

Due to how Impala executes analytic functions, you should be able to work around the issue by ommitting the "substr(gbm.transxref,1,(instr(gbm.transxref,'/'))-1)" portion in the ORDER BY clause of the analytic function.

[#3]

Thank you a lot for quick response!

-------EDIT----------

Also, I have found a possible walk around with using split_part() function instead of substr() - it will work for my particular case