phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shuxiong Ye (JIRA)" <>
Subject [jira] [Commented] (PHOENIX-1987) SIGN built-in function should be order preserving
Date Fri, 05 Jun 2015 10:12:00 GMT


Shuxiong Ye commented on PHOENIX-1987:

    public void testOrderByOrderPreservingRev() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute("CREATE TABLE t (k1 date not null, k2 date not null,
k3 date not null, v varchar, constraint pk primary key(k1,k2 DESC,k3))");
        String[] queries = {
                "SELECT * FROM T ORDER BY INVERT(k1),k2",
                "SELECT * FROM T ORDER BY INVERT(k1)",
                 "SELECT * FROM T ORDER BY TRUNC(k1, 'DAY') DESC, CEIL(k2, 'HOUR') DESC",
// <---- I have a question here.
                "SELECT * FROM T ORDER BY k1 DESC",
        String query;
        for (int i = 0; i < queries.length; i++) {
            query = queries[i];
            QueryPlan plan = conn.createStatement().unwrap(PhoenixStatement.class).compileQuery(query);
            assertTrue("Expected order by to be compiled out: " + query, plan.getOrderBy()

In the third query, why is CEIL(k2, 'HOUR') DESC instead of CEIL(k2, 'HOUR')?
In the primary key definition, "primary key(k1,k2 DESC,k3))", k2 is descending.

When the order by clause is "TRUNC(k1, 'DAY') DESC, CEIL(k2, 'HOUR')", it will be opposite
with the primary key definition, "primary key(k1,k2 DESC,k3))", so the QueryPlan has an empty
order by, for we can scan the data forward. Is my understanding right?


we should also add this to SignFunction
    public int getKeyFormationTraversalIndex() {
        return 0;

> SIGN built-in function should be order preserving
> -------------------------------------------------
>                 Key: PHOENIX-1987
>                 URL:
>             Project: Phoenix
>          Issue Type: Sub-task
>            Reporter: James Taylor
>            Assignee: Shuxiong Ye
>             Fix For: 5.0.0, 4.5.0, 4.4.1
>         Attachments: 0001-PHOENIX-1987-SIGN-built-in-function-should-be-order_v2.patch
> The SIGN built-in function preserves the order of its input. This needs to be explicitly
implemented in SignFunction by implementing the following method:
> {code}
>     public OrderPreserving preservesOrder() {
>         return OrderPreserving.YES;
>     }
> {code}
> This will allow certain optimizations reducing the amount of memory and/or buffering
that is required if SIGN is used in a GROUP BY or ORDER BY clause.

This message was sent by Atlassian JIRA

View raw message