Thread: Pg, Netezza, and... Sybase?
Is there a link between Sybase and Postgres? I ask because I came across a bug in Netezza, and Netezza has a well-known Postgres lineage, but when web-searching the bug, the first thing I found was a Sybase reference- http://geekswithblogs.net/marocanu2001/archive/2011/08/11/nasty-bug-in-sybase-iq-analytical-function-last_value-over-partition.aspx and indeed that is the exact bug I found in Netezza! "first_value" works great, "last_value" has window size 1 unless you also say "rows between unbounded preceding and unbounded following". Two unrelated commercial products with the same bug makes me wonder... does/did Postgres also have this issue, with both NZ and Sybase importing the same buggy code?
On Mon, Oct 1, 2012 at 03:49:14PM -0400, yary wrote: > Is there a link between Sybase and Postgres? > > I ask because I came across a bug in Netezza, and Netezza has a > well-known Postgres lineage, but when web-searching the bug, the first > thing I found was a Sybase reference- > > http://geekswithblogs.net/marocanu2001/archive/2011/08/11/nasty-bug-in-sybase-iq-analytical-function-last_value-over-partition.aspx > > and indeed that is the exact bug I found in Netezza! "first_value" > works great, "last_value" has window size 1 unless you also say "rows > between unbounded preceding and unbounded following". > > Two unrelated commercial products with the same bug makes me wonder... > does/did Postgres also have this issue, with both NZ and Sybase > importing the same buggy code? There is no code connection between Sybase and Postgres. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
yary <not.com@gmail.com> writes: > Is there a link between Sybase and Postgres? Not that I've ever heard of. > I ask because I came across a bug in Netezza, and Netezza has a > well-known Postgres lineage, but when web-searching the bug, the first > thing I found was a Sybase reference- > http://geekswithblogs.net/marocanu2001/archive/2011/08/11/nasty-bug-in-sybase-iq-analytical-function-last_value-over-partition.aspx > and indeed that is the exact bug I found in Netezza! "first_value" > works great, "last_value" has window size 1 unless you also say "rows > between unbounded preceding and unbounded following". That isn't a bug, it's the behavior required by the SQL standard. The blogger you cite has apparently not bothered to read same (or much of any documentation), or he would know that the default window frame is NOT unbounded preceding to unbounded following. Our own docs point out specifically that you probably want a nondefault frame for last_value --- see http://www.postgresql.org/docs/9.2/static/functions-window.html towards the bottom of the page. regards, tom lane
> > and indeed that is the exact bug I found in Netezza! "first_value" > works great, "last_value" has window size 1 unless you also say "rows > between unbounded preceding and unbounded following". > What Tom said but: "...has window size 1" is not correct. The window size is larger but the returned value is always just going to be the current (last) row so it only appears to be "size 1" superficially. The query's frame/range/window specification is independent of the functions that use the window. If you are confused as to what is currently in the frame specification you should use something like ARRAY_AGG(...) OVER (...) To capture the relevant frame data into a single value. David J.
Thanks to all for the education! The bug was in my understanding (and that bloggers)... and the diverse SQL implementations are doing what they're meant to. I'll read up more.