[ https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17813895#comment-17813895 ]
Jiajun Xie commented on CALCITE-1317: ------------------------------------- Oh, I am thinking about similar optimizations recently: - Empty OVER {code:java} SELECT orderkey, orderstatus FROM ( SELECT row_number() OVER () rn, orderkey, orderstatus FROM orders ) WHERE rn <= 5{code} to {code:java} SELECT orderkey, orderstatus FROM ORDERS LIMIT 5 {code} - OVER with ORDER BY {code:java} SELECT orderkey, orderstatus FROM ( SELECT row_number() OVER (ORDER BY orderstatus) rn, orderkey, orderstatus FROM orders ) WHERE rn <= 5 {code} to {code:java} SELECT orderkey, orderstatus FROM orders ORDER BY orderkey LIMIT 5{code} > 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)