[
https://issues.apache.org/jira/browse/PHOENIX-1768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14377013#comment-14377013
]
Serhiy Bilousov commented on PHOENIX-1768:
------------------------------------------
I am not big in standards (very specific and boring reading) nor I insists that
Phoenix should fully comply with ANSI SQL (last time I check there was no
database fully comply with SQL standard) but looking into [ISO/IEC CD
9075-2:2013(E) p.412 sec.7.12 <group by clause>
|http://jtc1sc32.org/doc/N2301-2350/32N2311T-text_for_ballot-CD_9075-2.pdf]
what does they mean by <ordinary grouping set>
{noformat}
7.12 <group by clause>
Function
Specify a grouped table derived by the application of the <group by clause> to
the result of the previously specified clause.
Format
<group by clause> ::=
GROUP BY [ <set quantifier> ] <grouping element list>
<grouping element list> ::=
<grouping element> [ { <comma> <grouping element> }... ]
<grouping element> ::=
<ordinary grouping set>
| <rollup list>
| <cube list>
| <grouping sets specification>
| <empty grouping set>
<ordinary grouping set> ::=
<grouping column reference>
| <left paren> <grouping column reference list> <right paren>
<grouping column reference> ::=
<column reference> [ <collate clause> ]
<grouping column reference list> ::=
<grouping column reference> [ { <comma> <grouping column reference> }... ]
<rollup list> ::=
ROLLUP <left paren> <ordinary grouping set list> <right paren>
<ordinary grouping set list> ::=
<ordinary grouping set> [ { <comma> <ordinary grouping set> }... ]
<cube list> ::=
CUBE <left paren> <ordinary grouping set list> <right paren>
<grouping sets specification> ::=
GROUPING SETS <left paren> <grouping set list> <right paren>
<grouping set list> ::=
<grouping set> [ { <comma> <grouping set> }... ]
<grouping set> ::=
<ordinary grouping set>
| <rollup list>
| <cube list>
| <grouping sets specification>
| <empty grouping set>
<empty grouping set> ::=
<left paren> <right paren>
{noformat}
ORDER BY from the same doc to compare
{noformat}
10.10 <sort specification list>
Function
Specify a sort order.
Format
<sort specification list> ::=
<sort specification> [ { <comma> <sort specification> }... ]
<sort specification> ::=
<sort key> [ <ordering specification> ] [ <null ordering> ]
<sort key> ::=
<value expression>
<ordering specification> ::=
ASC
| DESC
<null ordering> ::=
NULLS FIRST
| NULLS LAST
{noformat}
> GROUP BY should support column position as well as column alias
> ---------------------------------------------------------------
>
> Key: PHOENIX-1768
> URL: https://issues.apache.org/jira/browse/PHOENIX-1768
> Project: Phoenix
> Issue Type: Bug
> Reporter: Serhiy Bilousov
>
> In postgreSQL (and many others DBs) you can specify not only column name for
> the GROUP BY but column number (position in SELECT part) as well as column
> alias.
> see:
> http://www.postgresql.org/docs/9.4/static/queries-order.html
> http://www.postgresql.org/docs/9.4/static/sql-select.html#SQL-GROUPBY
> Adding such support would be very helpful and sometimes necessary.
> I can provide real queries example if required but basically we want
> something like this
> given query
> SELECT a, b, TRUNC(current_date(),'HOUR') AS date_truncated FROM table
> we want
> GROUP BY 1, 2
> Having just column number would cover both but having column alias would make
> queries more readable and human friendly. Plus make it one little stem closer
> to postgreSQL and SQL standard.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
