topic Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results in Data Engineering

Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

alexisjohnson — Thu, 18 Nov 2021 18:46:17 GMT

Hi, I'm wondering if this is the expected behavior when using last or last_value in a window function? I've written a query like this:

select
    col1,
    col2,
    last_value(col2) over (partition by col1 order by col2) as column2_last
from values
    (1, 10), (1, 11), (1, 12),
    (2, 20), (2, 21), (2, 22);

In Snowflake I get the following results. The behavior feels right, since we're partitioning by col1 and ordering by col2 (https://docs.snowflake.com/en/sql-reference/functions/last_value.html).

In Databricks, I get:

It seems like the ORDER BY doesn't get applied... Perhaps I am writing the query incorrectly?

Any insight into this behavior would be very helpful. Also, I'm curious why last and last_value are synonyms? https://docs.databricks.com/sql/language-manual/functions/last.html

Thanks!

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

Hubert-Dudek — Thu, 18 Nov 2021 20:44:55 GMT

yes indeed it looks like a bug with LAST_VALUE,

Min gives correct results:

Max hmm only when desc is specified (shouldn't affect as max is max but only with descending sorting it works as should be):

for last_value is exactly like on your example (so nothing helps - I tried many ways).

@Kaniz Fatma maybe someone from inside databricks could look at it

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

alexisjohnson — Thu, 18 Nov 2021 20:47:44 GMT

Agreed. Interesting find that `max` behaves funny when `desc` isn't specified.

I'd also note that `first_value` yields the correct result.

@Kaniz Fatma Is there a process for filing bugs in Databricks? It'd be great to get this fixed.

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

alexisjohnson — Thu, 18 Nov 2021 20:48:28 GMT

Thank you for your help, @Hubert Dudek !

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

Anonymous — Fri, 19 Nov 2021 16:38:49 GMT

@Alexis Johnson - Here is where you can open a support ticket:

Re: Window function using last/last_value with PARTITION BY/ORDER BY has unexpected results

Carv — Wed, 12 Jul 2023 20:00:31 GMT

For those stumbling across this; it seems LAST_VALUE emulates the same functionality as it does in SQL Server which does not, in most people's minds, have a proper row/range frame for the window. You can adjust it with the below syntax.

I understand last_value emulating what is in my mind a mistake from sql_server but I don't understand why last() (an alias for last_value) has to use the same window row range.

SELECT col1 , col2 , LAST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 ASC) as column2_last , LAST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as column2_last_unbound , FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 ASC) as column2_first , FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2 DESC) as column2_first_reversed FROM values (1,10), (1,12), (1,11), (2,20), (2,21), (2,22);