[
https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17901797#comment-17901797
]
Julian Hyde commented on CALCITE-1317:
--------------------------------------
You’re right, that example is not correct if “emp” may be empty. The rewrite
must be used with care, only in contexts where you know the table is not empty,
or where an empty result and a null result have the same meaning (e.g. inside
“empno = (query)”).
> 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)
