[
https://issues.apache.org/jira/browse/HIVE-4214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13609681#comment-13609681
]
Harish Butani commented on HIVE-4214:
-------------------------------------
The behavior today is similar to UDAF evaluation and group by:
- the UDAF is evaluated in the PTFOp
- the non UDAF parts of an expression are evaluated in the child Select
Operator.
- the over clause is associated with all UDAFs in the Select Clause.
- So you can express things like (see are test cases from
windowing_expressions.q)
{noformat}
select p_mfgr, p_retailprice, p_size,
round(sum(p_retailprice),2) = round((sum(lag(p_retailprice,1)) -
first_value(p_retailprice)) + last_value(p_retailprice),2) over(distribute by
p_mfgr sort by p_retailprice),
max(p_retailprice) - min(p_retailprice) = last_value(p_retailprice) -
first_value(p_retailprice)
over(distribute by p_mfgr sort by p_retailprice)
from part;
select p_mfgr, p_retailprice, p_size,
rank() over (distribute by p_mfgr sort by p_retailprice) as r,
sum(p_retailprice) over (distribute by p_mfgr sort by p_retailprice rows
between unbounded preceding and current row) as s2,
sum(p_retailprice) - 5 over (distribute by p_mfgr sort by p_retailprice rows
between unbounded preceding and current row) as s1
from part;
{noformat}
- Can you post what output you are expecting for the first query you posted?
- Have to look at whether we can switch the second form you posted. This will
make the first e.g. much more cumbersome
to write.
> OVER accepts general expression instead of just function
> --------------------------------------------------------
>
> Key: HIVE-4214
> URL: https://issues.apache.org/jira/browse/HIVE-4214
> Project: Hive
> Issue Type: Bug
> Components: PTF-Windowing
> Affects Versions: 0.11.0
> Reporter: Alan Gates
>
> The query:
> select s, i, avg(d) / 10.0 over (partition by s order by i) from over100k;
> runs (and produces meaningless output).
> Over should not allow the arithmetic expression. Only a UDAF or PTF function
> should be valid there. The correct way to write this query should be
> select s, i, avg(d) over (partition by s order by i) / 10. 0 from over100k;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
