[jira] [Resolved] (CALCITE-873) Prevent sort when ORDER BY not necessary due to equality constraints

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-873?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-873.
---------------------------------
       Resolution: Fixed
    Fix Version/s: 1.17.0

Fixed in [a0928af7|http://git-wip-us.apache.org/repos/asf/calcite/commit/a0928af7]; thanks [~atris] for persisting with this, which I believe is Calcite's longest ever pull-request - over 2 1/2 years from beginning to end!

> Prevent sort when ORDER BY not necessary due to equality constraints
> --------------------------------------------------------------------
>
>                 Key: CALCITE-873
>                 URL: https://issues.apache.org/jira/browse/CALCITE-873
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: James Taylor
>            Assignee: Atri Sharma
>            Priority: Major
>             Fix For: 1.17.0
>
>
> We're working on an optimization in Phoenix to optimize away an ORDER BY when it is known based on equality expressions in the WHERE clause that it is not necessary (PHOENIX-2194). It'd be great if Calcite could do that as well.
> Here's a example, given the following schema:
> {code}
> CREATE TABLE T (
>   K1 VARCHAR,
>   K2 VARCHAR,
>   K3 VARCHAR,
>   CONSTRAINT pk PRIMARY KEY (K1, K2, K3));
> {code}
> In the following queries, no sort is necessary:
> {code}
> SELECT * FROM T WHERE K1='A' ORDER BY K2,K3;
> SELECT * FROM T WHERE K2='B' ORDER BY K1,K3;
> SELECT * FROM T WHERE K1='A' AND K2='B' ORDER BY K3;
> {code}
> There are also some edge cases where a function may be known to select a *prefix* of the column value where it's still ok to not sort:
> {code}
> SELECT * FROM T WHERE K1='A' AND SUBSTR(K2,1,3)='ABC' ORDER BY K2;
> {code}
> But if another column is included in the ORDER BY after the prefixing, a sort would still be necessary:
> {code}
> SELECT * FROM T WHERE K1='A' AND SUBSTR(K2,1,3)='ABC' ORDER BY K2,K3;
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)