[
https://issues.apache.org/jira/browse/PHOENIX-1749?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14393862#comment-14393862
]
James Taylor commented on PHOENIX-1749:
---------------------------------------
As [~julianhyde] mentioned, it's best if we handle the SELECT * FROM T ORDER BY
3 case correctly, with the 3 in this case meaning the third projected column in
the select expressions. I think the best way to handle this is with this logic:
- In QueryCompile.compileSingleFlatQuery(), compile the projection before the
order by. This guarantees that any errors will be found (i.e. invalid column
family names, etc.) in the select expressions so we won't need to worry about
that when we compile the order by. That involves switching the order of these
two lines:
{code}
OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy,
limit, isInRowKeyOrder);
RowProjector projector = ProjectionCompiler.compile(context, select,
groupBy, asSubquery ? Collections.<PDatum>emptyList() : targetColumns);
{code}
- Write a method in OrderByCompiler that, given an ordinal, correctly traverses
through the select nodes and returns the correct one (manufacturing one as
necessary). So instead of just indexing into the selectNodes, we need to step
through them and increment a currentSelectNode counter by more than one if we
see a wildcard parse node. If the ordinal is within the range of the
currentSelectNode, then we have a match and can return a ParseNode.
- If we encounter a WildcardParseNode it's pretty easy because we only
allow that if it's the only select expression. You can get the set of table
columns like this:
{code}
PTable table = context.getResolver().getTables().get(0);
checkOrdinalWithinRange(ordinal, table.getColumns().size());
PColumn col = table.getColumns().get(ordinal);
{code}
- If we encounter a FamilyWildcardParseNode, then we increment the
currentSelectNode by resolving the family (we know it's valid as it's already
been validated) and adding the number of columns in the column family like this:
{code}
PTable table = context.getResolver().getTables().get(0);
PColumnFamily family = table.getFamily(cfWildcard.getName());
currentSelectNode += family.getColumns().size();
{code}
- If we encounter a TableWildcardParseNode, then we increment the
currentSelectNode by incrementing it by the number of columns in that table,
like this:
{code}
TableName tableName = tableWildcard.getTableName();
PTable table = context.getResolver().resolveTable(tableName.getSchemaName(),
tableName.getTableName());
currentSelectNode += table.getColumns().size();
{code}
- For anything else (which would just be a regular expression), we
increment currentSelectNode by one.
- When the ordinal is within range of currentSelectNode and what
currentSelectNode will be incremented to, we can return the select node or
manufacture one based on the PColumn being referenced like this:
{code}
boolean isJoin = resolver.getTables().size() > 1;
PName family = column.getFamily();
String tableName;
String familyOrTableName;
if (isJoin) {
if (family == null) {
tableName = null;
familyOrTableName = tableRef.getAlias();
} else {
tableName = tableRef.getAlias();
familyOrTableName = family.getString();
}
} else {
tableName = null;
familyOrTableName = family == null ? null : family.getString();
}
node = new ColumnParseNode(TableName.create(tableName, familyOrTableName),
"\"" column.getName().getString() + "\"");
{code}
> ORDER BY should support ordinal position as well as expression
> --------------------------------------------------------------
>
> Key: PHOENIX-1749
> URL: https://issues.apache.org/jira/browse/PHOENIX-1749
> Project: Phoenix
> Issue Type: Bug
> Reporter: Serhiy Bilousov
> Assignee: Alicia Ying Shu
> Attachments: PHOENIX-1749-v1.patch, PHOENIX-1749.patch
>
>
> In postgreSQL (and many others DBs) you can specify not only column name for
> the ORDER 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
> ORDER BY 1 ASC, 2 DESC
> ORDER BY date_truncated
> 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)
