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

["James Taylor (JIRA)" <ji...@apache.org>]

James Taylor created CALCITE-873:
------------------------------------

             Summary: 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: Julian Hyde


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
(v6.3.4#6332)