[
https://issues.apache.org/jira/browse/HIVE-24804?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
László Bodor updated HIVE-24804:
--------------------------------
Description:
Currently, in Hive, we can run a windowing function with range specification
but without an ORDER BY clause:
{code}
create table vector_ptf_part_simple_text(p_mfgr string, p_name string,
p_retailprice double, rowindex string);
select p_mfgr, p_name, rowindex,
count(*) over(partition by p_mfgr range between 1 preceding and current row) as
cs1,
count(*) over(partition by p_mfgr range between 3 preceding and current row) as
cs2
from vector_ptf_part_simple_text;
{code}
This is confusing, becuase without an order by clause, the range is is out of
context, we don't know by which column should we calculate the range.
Tested on Postgres, it throws an exception:
{code}
create table vector_ptf_part_simple_text(p_mfgr varchar(10), p_name
varchar(10), p_retailprice integer, rowindex varchar(10));
select p_mfgr, p_name, rowindex,
count(*) over(partition by p_mfgr range between 1 preceding and current row) as
cs1,
count(*) over(partition by p_mfgr range between 3 preceding and current row) as
cs2
from vector_ptf_part_simple_text;
*RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column*
{code}
was:
Currently, in Hive, we can run a windowing function with range specification
but without an ORDER BY clause:
{code}
create table vector_ptf_part_simple_text(p_mfgr string, p_name string,
p_retailprice double, rowindex string);
select p_mfgr, p_name, rowindex,
count(*) over(partition by p_mfgr range between 1 preceding and current row) as
cs1,
count(*) over(partition by p_mfgr range between 3 preceding and current row) as
cs2
from vector_ptf_part_simple_text;
{code}
This is confusing, becuase without an order by clause, the range is is out of
context, we don't know by which column should we calculate the range.
Tested on Postgres, it throws an exception:
{code}
create table vector_ptf_part_simple_text(p_mfgr varchar(10), p_name
varchar(10), p_retailprice integer, rowindex varchar(10));
select p_mfgr, p_name, rowindex,
count(*) over(partition by p_mfgr order by p_name range between 1 preceding and
current row) as cs1,
count(*) over(partition by p_mfgr order by p_name range between 3 preceding and
current row) as cs2
from vector_ptf_part_simple_text;
*RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column*
{code}
> RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
> --------------------------------------------------------------------------
>
> Key: HIVE-24804
> URL: https://issues.apache.org/jira/browse/HIVE-24804
> Project: Hive
> Issue Type: Bug
> Reporter: László Bodor
> Assignee: László Bodor
> Priority: Major
>
> Currently, in Hive, we can run a windowing function with range specification
> but without an ORDER BY clause:
> {code}
> create table vector_ptf_part_simple_text(p_mfgr string, p_name string,
> p_retailprice double, rowindex string);
> select p_mfgr, p_name, rowindex,
> count(*) over(partition by p_mfgr range between 1 preceding and current row)
> as cs1,
> count(*) over(partition by p_mfgr range between 3 preceding and current row)
> as cs2
> from vector_ptf_part_simple_text;
> {code}
> This is confusing, becuase without an order by clause, the range is is out of
> context, we don't know by which column should we calculate the range.
> Tested on Postgres, it throws an exception:
> {code}
> create table vector_ptf_part_simple_text(p_mfgr varchar(10), p_name
> varchar(10), p_retailprice integer, rowindex varchar(10));
> select p_mfgr, p_name, rowindex,
> count(*) over(partition by p_mfgr range between 1 preceding and current row)
> as cs1,
> count(*) over(partition by p_mfgr range between 3 preceding and current row)
> as cs2
> from vector_ptf_part_simple_text;
> *RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column*
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
