Unexpected Behavior In First_value() With Ignore Nulls (vertica)

I'm seeing unexpected behavior in Vertica's FIRST_VALUE() analytic function with the IGNORE NULLS parameter. It appears to return NULL when it shouldn't. The issue occurs in this v

Solution 1:

The function works as expected. over (order by time_) is a shortcut for over (order by time_ range unbounded preceding) which is a shortcut for over (order by time_ range between unbounded preceding and current row), which means every row sees only the rows that preceded it, including itself. The first row sees only itself therefore there isn't a non NULL value in its scope.

If you want the first non NULL value of the whole scope, you have to specify the whole scope:

first_value(name ignore nulls) over 
    (orderby time_ rangebetween unbounded preceding and unbounded following) first_name

---

No, this is definitly not a bug.

You've probably have been using syntax like sum(x) over (order by y) for running totals and the default window of RANGE UNBOUNDED PRECEDING seemed very natural to you. Since you had not define an explicit window for the FIRST_VALUE function, you have been using the same default window.

Here is another test case:

ts val
-- ----1NULL2  X
3NULL4  Y
5NULL

What would you expect to get from the following function?

last_value (val) order (by ts)

What would you expect to get from the following function?

last_value (val ignore nulls) order (by ts)

Solution 2:

This is where my thinking takes me

select time_
      ,first_value(name) over (orderbycasewhen name isnullthen1else0end,time_) FirstName
from temp A
orderby time_

Returns

time_               FirstName
20:32:16.1440000    abc20:52:09.0620000    abc