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)
