[
https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17901631#comment-17901631
]
suibianwanwan commented on CALCITE-1317:
----------------------------------------
Hi, [~julianhyde]. I am trying to do a similar optimization. I observe that the
second example in the description is not equivalent without constraints
{code:java}
SELECT max(sal)
FROM emp
{code}
to
{code:java}
SELECT sal
FROM emp
ORDER BY sal DESC LIMIT 1
{code}
(When the number of rows is empty, max(sal) will return null, and order by sal
limit 1 will return empty rows)
> 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)
