[jira] [Resolved] (HIVE-9000) LAST_VALUE Window function returns wrong results
[
https://issues.apache.org/jira/browse/HIVE-9000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ashutosh Chauhan resolved HIVE-9000.
Resolution: Fixed
Fix Version/s: (was: 0.14.1)
As Navis pointed out, result from Hive is indeed correct. I further verified on
postgres. Mark, you can use alternative query suggested by Navis if you want
your resultset to look like that.
LAST_VALUE Window function returns wrong results
Key: HIVE-9000
URL: https://issues.apache.org/jira/browse/HIVE-9000
Project: Hive
Issue Type: Bug
Components: PTF-Windowing
Affects Versions: 0.13.1
Reporter: Mark Grover
Priority: Critical
LAST_VALUE Windowing function has been returning bad results, as far as I can
tell from day 1.
And, it seems like the tests are also asserting that LAST_VALUE gives the
wrong result.
Here's the test output:
https://github.com/apache/hive/blob/branch-0.14/ql/src/test/results/clientpositive/windowing_navfn.q.out#L587
The query is:
{code}
select t, s, i, last_value(i) over (partition by t order by s) from over10k
where (s = 'oscar allen' or s = 'oscar carson') and t = 10
{code}
The result is:
{code}
t si last_value(i)
---
10oscar allen 65662 65662
10oscar carson65549 65549
{code}
{{LAST_VALUE( i )}} should have returned 65549 in both records, instead it
simply ends up returning i.
Another way you can make sure LAST_VALUE is bad is to verify it's result
against LEAD(i,1) over (partition by t order by s). LAST_VALUE being last
value should always be more (in terms of the specified 'order by s') than the
lead by 1. While this doesn't directly apply to the above query, if the
result set had more rows, you would clearly see records where lead is higher
than last_value which is semantically incorrect.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Resolved] (HIVE-9000) LAST_VALUE Window function returns wrong results
[
https://issues.apache.org/jira/browse/HIVE-9000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ashutosh Chauhan resolved HIVE-9000.
Resolution: Invalid
LAST_VALUE Window function returns wrong results
Key: HIVE-9000
URL: https://issues.apache.org/jira/browse/HIVE-9000
Project: Hive
Issue Type: Bug
Components: PTF-Windowing
Reporter: Mark Grover
Priority: Critical
LAST_VALUE Windowing function has been returning bad results, as far as I can
tell from day 1.
And, it seems like the tests are also asserting that LAST_VALUE gives the
wrong result.
Here's the test output:
https://github.com/apache/hive/blob/branch-0.14/ql/src/test/results/clientpositive/windowing_navfn.q.out#L587
The query is:
{code}
select t, s, i, last_value(i) over (partition by t order by s) from over10k
where (s = 'oscar allen' or s = 'oscar carson') and t = 10
{code}
The result is:
{code}
t si last_value(i)
---
10oscar allen 65662 65662
10oscar carson65549 65549
{code}
{{LAST_VALUE( i )}} should have returned 65549 in both records, instead it
simply ends up returning i.
Another way you can make sure LAST_VALUE is bad is to verify it's result
against LEAD(i,1) over (partition by t order by s). LAST_VALUE being last
value should always be more (in terms of the specified 'order by s') than the
lead by 1. While this doesn't directly apply to the above query, if the
result set had more rows, you would clearly see records where lead is higher
than last_value which is semantically incorrect.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
