You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2016/07/16 00:27:20 UTC
[jira] [Created] (CALCITE-1317) Rewrite "MAX" as "ORDER BY ...
LIMIT ..."
Julian Hyde created CALCITE-1317:
------------------------------------
Summary: 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)