59078

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

<h3>Question</h3>

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 very tiny table:

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

Here are the contents of the table (select * from temp):

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

Here is the query I'm running:

select time_, first_value(name ignore nulls) over (order by time_) first_name from temp;

Here are the results this query returns:

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

Here are the results I would expect (and desire) from this query:

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

Does the above query have a very fundamental syntax mistake? This issue occurs on Vertica Community Edition 7.1.1.


<h3>Answer1:</h3>

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 (order by time_ range between unbounded preceding and unbounded following) first_name <hr />

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 -- ---- 1 NULL 2 X 3 NULL 4 Y 5 NULL

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)
<h3>Answer2:</h3>

This is where my thinking takes me

select time_ ,first_value(name) over (order by case when name is null then 1 else 0 end,time_) FirstName from temp A order by time_

Returns

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

来源:https://stackoverflow.com/questions/40662817/unexpected-behavior-in-first-value-with-ignore-nulls-vertica

Recommend

  • ScrollView with two views, first view filling screen
  • How to checkout particular pull request
  • Jenkins using wrong version of Ruby(rvm)
  • WMI Linker Error on x64
  • Python: help(numpy) causes segfault on exit
  • SerialPort.Open() --IOException — “The parameter is incorrect.”
  • android:digits not allowing next button in keypad
  • MSBuild / Visual Studio distributed builds
  • How do you redirect to an external website with rails?
  • Why is Infinity-Infinity NaN? [duplicate]
  • How to get rid of event firing boiler plate code?
  • Isabelle: Why do I get completely different results when running try versus sledgehammer
  • Issue while running Linux Command using Java?
  • Perl is respecting '
  • Multiple Rows for same identifier
  • How to pivot table for year in SQL?
  • Deploy WAR file in Tomcat, Issue after Deployment
  • How to make html video autoplay on phones and tablets?
  • How do I use RestSharp to POST a login and password to an API?
  • Failure to Read Updated AnyLogic DB Values
  • Reshape dataframe to dataframe with unlimited rows and filling zeroes where no values
  • Copy Row if Cell Contains X to Different Sheet then delete row.
  • Dynamic reference casting depending on actual object type
  • Spongycastle is missing many algorithms when loaded on android
  • Nested AJAX Calls using .done
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • .htaccess redirect domain.com to www.domain.com
  • Synchronous Calls with jqGrid?
  • Request Access Token in Postman for Azure Function App protected by Azure AD B2C
  • How to redirect into different page by user type in php and mysql
  • time column in sqlite using gorm
  • When to use the tag in the head and body section of a html page? [duplicate]
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • Amazon Elastick BeanStalk error: Failed to create the AWS Elastic Beanstalk application version
  • Using redis as an LRU cache for postgres
  • What is the best way to cache and reuse immutable singleton objects in Java?
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner