[
https://issues.apache.org/jira/browse/HIVE-4663?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13714201#comment-13714201
]
Harish Butani commented on HIVE-4663:
-------------------------------------
You can express this as:
{noformat}
select distinct
DELOGCE, l
from
(
select kastr.DELOGCE,
, lag(kastr.DEWNKNR) over ( partition by kastr.DEKTRNR order by kastr.DETRADT,
kastr.DEVPDNR ) as l
from RTAVP_DRKASTR kastr
) a
{noformat}
What is going on is: we do the windowing function processing last, after the
joins, group by, having etc. Distinct is handled internally as Group By and so
is performed before Windowing. Hence the error message.
You can get around the issue by stacking an outer query as above. Not clear if
the semantics you are expecting, is what the Spec mandates. Will check on
Postgres.
> Needlessly adding analytical windowing columns to my select
> -----------------------------------------------------------
>
> Key: HIVE-4663
> URL: https://issues.apache.org/jira/browse/HIVE-4663
> Project: Hive
> Issue Type: Bug
> Components: SQL
> Affects Versions: 0.11.0
> Reporter: Frans Drijver
>
> Forgive the rather cryptic title, but I was unsure what the best summary
> would be. The situation is as follows:
> If I have query in which I do both a select of a 'normal' column and an
> analytical function, as so:
> {quote}
> select distinct
> kastr.DELOGCE
> , lag(kastr.DEWNKNR) over ( partition by kastr.DEKTRNR order by
> kastr.DETRADT, kastr.DEVPDNR )
> from RTAVP_DRKASTR kastr
> ;
> {quote}
> I get the following error:
> {quote}
> FAILED: SemanticException Failed to breakup Windowing invocations into
> Groups. At least 1 group must only depend on input columns. Also check for
> circular dependencies.
> Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line
> 3:41 Expression not in GROUP BY key 'DEKTRNR'
> {quote}
> The way around is to also put the analytical windowing columns in my select,
> as such:
> {quote}
> select distinct
> kastr.DELOGCE
> , lag(kastr.DEWNKNR) over ( partition by kastr.DEKTRNR order by
> kastr.DETRADT, kastr.DEVPDNR )
> , kastr.DEKTRNR
> , kastr.DEWNKNR
> , kastr.DETRADT
> , kastr.DEVPDNR
> from RTAVP_DRKASTR kastr
> ;
> {quote}
> Obviously this is generally unwanted behaviour, as it can widen the select
> significantly
--
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
