[
https://issues.apache.org/jira/browse/PHOENIX-1749?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14482305#comment-14482305
]
James Taylor commented on PHOENIX-1749:
---------------------------------------
Good point, [~ayingshu]. I think that your idea would be easier. So you'd still
want to do this step:
- 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}
Then you can index into the RowProjector. But you'll need to set the columnRef
and orderPreserving on the visitor manually in this case. You'll can introduce
a new method on TrackOrderPreservingExpressionCompiler, by factoring the
following code out of resolveColumn() which is what is setting those now:
{code}
public void setColumnRef(ColumnRef ref) {
// If we encounter any non PK column, then we can't aggregate on-the-fly
// because the distinct groups have no correlation to the KV column
value
if (getColumnPKPosition(ref) < 0) {
orderPreserving = OrderPreserving.NO;
}
if (columnRef == null) {
columnRef = ref;
} else if (!columnRef.equals(ref)) {
// If we encounter more than one column reference in an expression,
// we can't assume the result of the expression will be key ordered.
// For example GROUP BY a * b
orderPreserving = OrderPreserving.NO;
}
}
{code}
Then, from the code in OrderByCompiler for the ordinal position case, you can
create your own ColumnRef like this:
{code}
ColumnRef ref = new ColumnRef(new TableRef(context.getCurrentTable(),
column.getPosition());
visitor.setColumnRef(ref);
{code}
You'll want to create test cases that cover these cases:
- SELECT \* FROM T ORDER BY 5;
- SELECT a,cf1.\*, b, cf2.\* FROM T ORDER BY 5, 10; // where the 5 and 10 index
into cf1 and cf2
- SELECT t1.\* FROM T1 t1 JOIN T2 t2 ORDER BY 3;
Along with a variety of other test cases.
> 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)
