Geeks With Blogs
marocanu2001 technicle blog

I discovered a nasty bug in the way Sybase Iq last_value over partition works. More precisely it returns random values. On the other hand first_value is working fine so for now I am using that one instead, with reverse ordering in partitions. But it makes my queries a little harder to understand.

Try this eg:

Let's say we have table TestLastValueTable

TableId     RequestId       Sender        MessageNumber      Location
1                    1                      alan                           2                     London
2                    1                      mary                          3                     Paris
3                    1                      mike                          1                     Sevilla
4                    2                      gaga                         2                      Paris
5                    2                      gugu                         3                      Sevilla

When using the following query:

Select Id, requestId , last_value(sender) over (partition by requestId order by messageNum desc)  as lastRequestSender ,
    first_value(location) over (partition by requestId order by messageNum desc)  as firstRequestLocation
From TestLastValueTable
order by Id


I expected the outcome to be

Id,    requestId,    lastRequestSender,    firstRequestLocation
1,          1,                   'mike',                             'Paris'
2,          1,                   'mike',                             'Paris'
3,          1,                   'mike',                             'Paris'
4,          2,                   'gaga',                             'Bucharest'
5,          2,                   'gaga',                             'Bucharest'

And it actually is :

Id,    requestId,    lastRequestSender,    firstRequestLocation
1,           1,                  'alan',                                   'Paris'
2,           1,                  'mary',                                  'Paris'
3,           1,                  'mike',                                  'Paris'
4,          2,                   'gaga',                                  'Bucharest'
5,          2,                   'gugu',                                  'Bucharest'
 

UPDATE: This bug is related to the partition, changing the partition to being specified by "rows between unbounded preceding and unbounded following" (which should not make any difference) , makes last_value to return the correct value:

Select Id, requestId , last_value(sender) over (partition by requestId order by messageNum desc rows between unbounded preceding and unbounded following)  as lastRequestSender ,
    first_value(location) over (partition by requestId order by messageNum desc)  as firstRequestLocation
From TestLastValueTable
order by Id

 

 

Posted on Thursday, August 11, 2011 10:11 PM | Back to top


Comments on this post: Nasty bug in Sybase Iq analytical function Last_value over partition

# Netezza has the same issue
Requesting Gravatar...
I'm using NZ 6 and it does the exact same thing! Curious, isn't it.
Left by Yary on Oct 01, 2012 7:47 AM

# It is according to spec!
Requesting Gravatar...
Just discovered that this behavior is according to SQL spec! The window isn't always all rows in the partition, and it usually bites people using "last_value" and sometimes "nth_value"
Left by Yary on Oct 01, 2012 10:25 AM

Your comment:
 (will show your gravatar)


Copyright © marocanu2001 | Powered by: GeeksWithBlogs.net