question Re: IllegalStateException: null using first_value function (Impala 2.9.0) in Archives of Support Questions (Read Only)

IllegalStateException: null using first_value function (Impala 2.9.0)

laanajar — Fri, 16 Sep 2022 12:49:27 GMT

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

Re: IllegalStateException: null using first_value function (Impala 2.9.0)

alex.behm — Mon, 05 Feb 2018 18:19:08 GMT

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.

Re: IllegalStateException: null using first_value function (Impala 2.9.0)

laanajar — Mon, 05 Feb 2018 21:14:44 GMT

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