[
https://issues.apache.org/jira/browse/DRILL-1248?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Arina Ielchiieva updated DRILL-1248:
------------------------------------
Description:
when I select using a function and alias the resultant function value it won't
parse properly saying the alias is ambiguous. I know that this is a debatable /
questionable topic, but with this engine being so flexible it seems that in
order to support all of the formatting, casting, etc.. that will likely occur
having the group by support an alias would be a big deal. This in my opinion is
nothing like an ordinal group by.
This works:
select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'))
from BLAH group by extract(year from to_date(crimes.datetime, 'MM/DD/YYYY
hh:mm:ss a'));
This doesn't:
select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a')) as
mygroup from BLAH group by mygroup
*For documentation*
Positional / named alias are allowed in group by / order by / having clauses:
{noformat}
select length(n_name), n_regionkey from cp.`tpch/nation.parquet` group by 1, 2;
select length(n_name) as len, n_regionkey as key from cp.`tpch/nation.parquet`
group by len, key;
select length(n_name) as len, count(*) as cnt from cp.`tpch/nation.parquet`
group by length(n_name) having cnt > 1;
select n_regionkey, n_name from cp.`tpch/nation.parquet` order by 1, 2;
select n_regionkey as r, n_name as n from cp.`tpch/nation.parquet` order by r,
n;
{noformat}
was:
when I select using a function and alias the resultant function value it won't
parse properly saying the alias is ambiguous. I know that this is a debatable /
questionable topic, but with this engine being so flexible it seems that in
order to support all of the formatting, casting, etc.. that will likely occur
having the group by support an alias would be a big deal. This in my opinion is
nothing like an ordinal group by.
This works:
select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'))
from BLAH group by extract(year from to_date(crimes.datetime, 'MM/DD/YYYY
hh:mm:ss a'));
This doesn't:
select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a')) as
mygroup from BLAH group by mygroup
> Add support for using aliases in group by
> -----------------------------------------
>
> Key: DRILL-1248
> URL: https://issues.apache.org/jira/browse/DRILL-1248
> Project: Apache Drill
> Issue Type: Improvement
> Components: SQL Parser
> Reporter: Jim Scott
> Assignee: Arina Ielchiieva
> Priority: Major
> Labels: doc-impacting, ready-to-commit
> Fix For: 1.15.0
>
>
> when I select using a function and alias the resultant function value it
> won't parse properly saying the alias is ambiguous. I know that this is a
> debatable / questionable topic, but with this engine being so flexible it
> seems that in order to support all of the formatting, casting, etc.. that
> will likely occur having the group by support an alias would be a big deal.
> This in my opinion is nothing like an ordinal group by.
> This works:
> select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'))
> from BLAH group by extract(year from to_date(crimes.datetime, 'MM/DD/YYYY
> hh:mm:ss a'));
> This doesn't:
> select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'))
> as mygroup from BLAH group by mygroup
> *For documentation*
> Positional / named alias are allowed in group by / order by / having clauses:
> {noformat}
> select length(n_name), n_regionkey from cp.`tpch/nation.parquet` group by 1,
> 2;
> select length(n_name) as len, n_regionkey as key from
> cp.`tpch/nation.parquet` group by len, key;
> select length(n_name) as len, count(*) as cnt from cp.`tpch/nation.parquet`
> group by length(n_name) having cnt > 1;
> select n_regionkey, n_name from cp.`tpch/nation.parquet` order by 1, 2;
> select n_regionkey as r, n_name as n from cp.`tpch/nation.parquet` order by
> r, n;
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
