sql - Unexpected behavior in FIRST_VALUE() with IGNORE NULLS (Vertica) -

i'm seeing unexpected behavior in vertica's first_value() analytic function ignore nulls parameter. appears return null when shouldn't.

the issue occurs in tiny table:

drop table if exists temp; create table temp (time_ timestamp(6), name varchar(10)); insert temp (time_) values ('2016-03-18 20:32:16.144'); insert temp (time_, name) values ('2016-03-18 20:52:09.062', 'abc'); 

here contents of table (select * temp):

time_                   | name ------------------------+-------- 2016-03-18 20:32:16.144 | <null> 2016-03-18 20:52:09.062 | abc 

here query i'm running:

select time_,   first_value(name ignore nulls) on (order time_) first_name temp; 

here results query returns:

time_                   | first_name ------------------------+------------ 2016-03-18 20:32:16.144 | <null> 2016-03-18 20:52:09.062 | abc 

here results expect (and desire) query:

time_                   | first_name ------------------------+------------ 2016-03-18 20:32:16.144 | abc 2016-03-18 20:52:09.062 | abc 

does above query have fundamental syntax mistake? issue occurs on vertica community edition 7.1.1.

the function works expected.
over (order time_) shortcut over (order time_ range unbounded preceding) shortcut over (order time_ range between unbounded preceding , current row), means every row sees rows preceded it, including itself.
first row sees therefore there isn't non null value in scope.

if want first non null value of whole scope, have specify whole scope:

first_value(name ignore nulls) on      (order time_ range between unbounded preceding , unbounded following) first_name 

---

no, definitly not bug.

you've have been using syntax sum(x) on (order y) running totals , default window of range unbounded preceding seemed natural you.
since had not define explicit window first_value function, have been using same default window.

here test case:

ts val -- ---- 1  null 2  x 3  null 4  y 5  null 

what expect following function?

last_value (val) order (by ts) 

what expect following function?

last_value (val ignore nulls) order (by ts)