[
https://issues.apache.org/jira/browse/PHOENIX-1749?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14384997#comment-14384997
]
James Taylor commented on PHOENIX-1749:
---------------------------------------
It's very important to run the unit tests before submitting a patch,
[~ayingshu]. Our "mvn package" is failing with the error below. You must also
run "mvn verify" to run the full test suite prior to submitting a patch.
{code}
testOrderByDropped(org.apache.phoenix.compile.QueryOptimizerTest) Time
elapsed: 0.01 sec <<< ERROR!
java.sql.SQLFeatureNotSupportedException: *
at
org.apache.phoenix.parse.UnsupportedAllParseNodeVisitor.visit(UnsupportedAllParseNodeVisitor.java:65)
at
org.apache.phoenix.parse.WildcardParseNode.accept(WildcardParseNode.java:46)
at
org.apache.phoenix.compile.OrderByCompiler.compile(OrderByCompiler.java:107)
at
org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:498)
at
org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:449)
at
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:161)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:344)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:327)
at
org.apache.phoenix.jdbc.PhoenixStatement.compileQuery(PhoenixStatement.java:1043)
at
org.apache.phoenix.jdbc.PhoenixStatement.compileQuery(PhoenixStatement.java:1036)
at
org.apache.phoenix.jdbc.PhoenixStatement.optimizeQuery(PhoenixStatement.java:1030)
at
org.apache.phoenix.compile.QueryOptimizerTest.testOrderByDropped(QueryOptimizerTest.java:85){code}
The failing test can be modified to use an ORDER BY 'a',',b','c' instead, but
it also has turned up another issue: what should we do when wildcard operators
(*, cf.*, t.*) are used as select expressions and an ordinal position is used
for ORDER BY? We have a number of choices:
- treat it as an error condition and throw a SQLException. You can check for *
easily, as we only allow that if it's the only select expression. I suppose for
cf.* and t.* you could check if the node is an instance of
FamilyWildcardParseNode or TableWildcardParseNode.
- treat the ordinal as an ordinal into the PTable.getColumns() and/or
PTable.getColumnFamily(cf).getColumns() list. For the * case, this would be
pretty easy, as you can get the column list like this:
{code}
List<PColumn> columns = context.getCurrentTable().getTable().getColumns();
// TODO: add range check for index
PColumn column = columns.get(index);
PName family = column.getFamilyName();
node = new ColumnParseNode(TableName.create(null,family == null ? null :
family.getString()), "\"" column.getName().getString() + "\"");
{code}
The cf.* case is more complicated. Instead of just indexing directly into the
select nodes, you need to iterate through them, counting one for a non
FamilyWildcardParseNode, and counting however many columns are in the column
family
(context.getCurrentTable().getTable().getColumnFamily(node.getName()).getColumns().size()).
Not sure if we'd need to do something for the join case, which would likely be
painful.
- get the expression from the RowProjector instead of the parse node from the
select nodes. To do this, in QueryCompiler you'd need to compile the projection
(ProjectionCompiler.compile()) before the order by (OrderByCompiler.compile())
and then pass through the RowProjector to the OrderByCompiler. In this case,
you could index into the RowProjector
(rowProjector.getColumnProjector(index).getExpression()) instead of the select
nodes and the * and/or cf.* would have already been resolved and expanded. In
that case, the index would mean the nth column in the table if * was used. The
check you'd use would be:
{code}
ImmutableBytesWritable ptr = context.getTempPtr();
if ( expression.isStateless()
&& expression.getDeterminism() == Determinism.ALWAYS // is constant
&& expression.getDataType() != null
&& expression.getDataType().isCoercibleTo(PLong.INSTANCE) // is whole
number
&& expression.evaluate(null, ptr)
&& ptr.getLength() != 0) { // evaluates to non null
int index = expression.getDataType().getCodec().decodeInt(ptr,
expression.getSortOrder());
// add error check for index being in the correct range as you already
have
rowProjector.getColumnProjector(index).getExpression();
}
{code}
Seems like the first option is the best for now. We can do the second or third
as follow up work.
> ORDER BY should support column position as well as column alias
> ---------------------------------------------------------------
>
> 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)
