Window Function Bug using PRECEDING

["Joy, Jeff" <je...@optum.com>]

I'm looking to identify the version of HIVE that includes a fix for the following bug in Hive's implementation of PRECEDING in support of Window Functions.

Example:

sum( recordCount ) over(    partition by logType, dayOfWeek
                                                order by ingestionDate
                                                rows between 8 preceding and 1 preceding )

The problem is that Hive is using a row frame that EXCLUDES the and # preceding specified...in this case, the 1st preceding row. The frame should be INCLUSIVE of this row.
When using "0 PRECEDING" here, Hive correctly INCLUDES the 0th or current row. But for any value > 0 in this location, Hive EXCLUDES that terminating row.
The result is that it is impossible to use PRECEDING to define a row frame that ends with the 1st preceding record.

The problem does not show up when using FOLLOWING so I can work around this by reversing the ORDER BY sorting and using FOLLOWING instead, BUT, not everyone will pick up on this issue.

Jeff Joy
Principal Data Engineer
Big Data Platform Development
Cyber Defense | Enterprise Information Security
Optum Technology
763-694-7314


Our United Culture  The way forward
Integrity  |  Compassion  |  Relationships  |  Innovation  |  Performance




This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.