[ https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17814003#comment-17814003 ]
Julian Hyde commented on CALCITE-1317: -------------------------------------- [~jiajunbernoulli], I agree that those optimizations make sense. Note that converting "{{WHERE row_number()}} < constant" to "{{LIMIT}} constant" is the topic of CALCITE-3181 (and what you have described here is the special case where the {{GROUP BY}} key is empty). > 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 > Priority: Major > > 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 (v8.20.10#820010)