[ 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