[ https://issues.apache.org/jira/browse/CALCITE-873?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16078520#comment-16078520 ]
Atri Sharma edited comment on CALCITE-873 at 7/7/17 6:53 PM: ------------------------------------------------------------- [~julianhyde] I fixed the tests (it runs now) and refactored the rule and have updated the PR. I am not sure as to how to let a collation define one member as constant, so I am replacing the collation with the needed keys for now. Please let me know if there is an alternate way. was (Author: atris): [~julianhyde] I fixed the tests and refactored the rule and have updated the PR. I am not sure as to how to let a collation define one member as constant, so I am replacing the collation with the needed keys for now. Please let me know if there is an alternate way. > 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 > > 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.4.14#64029)