[jira] [Commented] (CALCITE-1317) Rewrite "MAX" as "ORDER BY ... LIMIT ..."

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

    [ https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15380366#comment-15380366 ] 

Julian Hyde commented on CALCITE-1317:
--------------------------------------

[~maryannxue], [~jamestaylor], You might find this optimization useful, since ordered scans of tables' and secondary indexes' primary keys are easy in Phoenix.

> Rewrite "MAX" as "ORDER BY ... LIMIT ..."
> -----------------------------------------
>
>                 Key: CALCITE-1317
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1317
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Julian Hyde
>
> We could optimize 
> {code}
> SELECT *
> FROM emp
> WHERE empno = (SELECT max(empno) FROM emp)
> {code}
> to
> {code}
> SELECT *
> FROM emp
> ORDER BY empno DESC LIMIT 1
> {code}
> (using the fact that {{empno}} is unique and non-NULL). Similarly, we can rewrite
> {code}
> SELECT max(sal)
> FROM emp
> {code}
> to
> {code}
> SELECT sal
> FROM emp
> ORDER BY sal DESC LIMIT 1
> {code}
> (not making any assumptions about whether {{sal}} is unique or allows NULL values) and we can rewrite a query to find the highest paid employee(s) in each department
> {code}
> SELECT *
> FROM emp AS e
> WHERE sal = (
>   SELECT max(sal)
>   FROM emp AS e2
>   WHERE e2.deptno = e.deptno)
> {code}
> as
> {code}
> SELECT deptno, empno, sal
> FROM (
>   SELECT deptno, empno, sal, FIRST_VALUE(sal) OVER w AS topSal
>   FROM emp
>   WINDOW w AS (PARTITION BY deptno ORDER BY sal DESC))
> WHERE sal = topSal
> {code}
> We might benefit from a generalized {{Sort(limit)}} operator that can find the top N within any prefix of the sort key, not just the top N overall.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)