[
https://issues.apache.org/jira/browse/CALCITE-1719?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15964840#comment-15964840
]
Julian Hyde edited comment on CALCITE-1719 at 4/11/17 7:25 PM:
---------------------------------------------------------------
Suppose you want to find the 3 highest paid employees. In standard SQL you'd
write {code}SELECT * FROM Emp ORDER BY sal DESC FETCH 3 ROWS{code} and on
PostgreSQL, MySQL, Calcite and many other DBs you can write {code}SELECT * FROM
Emp ORDER BY sal DESC LIMIT 3{code} But {code}SELECT * FROM (SELECT * FROM Emp
ORDER BY sal DESC) LIMIT 3{code} has two related problems: it's non-standard
SQL (the standard only allows {{ORDER BY}} at the top level), and it's not
clear whether the database is supposed to preserve ordering as the rows come
out of the sub-query. (FWIW, I checked MySQL and PostgreSQL: even though it's
non-standard, they do support it, provided you give the sub-query an alias.)
Re 1. It kind of makes sense. Just like it kind of makes sense that if I write
{{f(g(), h());}} in a programming language then {{g()}} will be evaluated
before {{h()}} (I believe that Java guarantees it, C does not, and in Haskell
there's no way to tell). But by guaranteeing it as part of the language
semantics, we are tying the hands of the optimizer.
Re 2. If the sub-query has an explicit {{ORDER BY}} we should observe it. We
can achieve that in {{SqlToRelConverter}}. When we create the {{Sort}} to
implement the {{LIMIT}}, if the input is also a {{Sort}}, we should give it the
same sort key as the underlying {{Sort}}. But only at sql-to-rel time, because
at other times a {{Sort}} might be due to something other than an explicit
{{ORDER BY}}.
We should also make sure this works for explicitly ordered views: {code}CREATE
VIEW v AS SELECT * FROM Emp ORDER BY sal DESC;
SELECT * FROM v LIMIT 3;{code}
But we make no guarantees for implicit ordering, such as physically ordered
tables in Phoenix. If {{Emp}} is ordered by {{empno}} then neither {{SELECT *
FROM Emp LIMIT 3}} nor {{SELECT * FROM (SELECT * FROM Emp) LIMIT 3}} is
guaranteed to return the employees with the lowest {{empno}} values.
was (Author: julianhyde):
Suppose you want to find the 3 highest paid employees. In standard SQL you'd
write {code}SELECT * FROM Emp ORDER BY sal DESC FETCH 3 ROWS{code} and on
PostgreSQL, MySQL, Calcite and many other DBs you can write {code}SELECT * FROM
Emp ORDER BY sal DESC LIMIT 3{code} But {code}SELECT * FROM (SELECT * FROM Emp
ORDER BY sal DESC) LIMIT 3{code} has two related problems: it's non-standard
SQL (the standard only allows {{ORDER BY}} at the top level), and it's not
clear whether the database is supposed to preserve ordering as the rows come
out of the sub-query.
Re 1. It kind of makes sense. Just like it kind of makes sense that if I write
{{f(g(), h());}} in a programming language then {{g()}} will be evaluated
before {{h()}} (I believe that Java guarantees it, C does not, and in Haskell
there's no way to tell). But by guaranteeing it as part of the language
semantics, we are tying the hands of the optimizer.
Re 2. If the sub-query has an explicit {{ORDER BY}} we should observe it. We
can achieve that in {{SqlToRelConverter}}. When we create the {{Sort}} to
implement the {{LIMIT}}, if the input is also a {{Sort}}, we should give it the
same sort key as the underlying {{Sort}}. But only at sql-to-rel time, because
at other times a {{Sort}} might be due to something other than an explicit
{{ORDER BY}}.
We should also make sure this works for explicitly ordered views: {code}CREATE
VIEW v AS SELECT * FROM Emp ORDER BY sal DESC;
SELECT * FROM v LIMIT 3;{code}
But we make no guarantees for implicit ordering, such as physically ordered
tables in Phoenix. If {{Emp}} is ordered by {{empno}} then neither {{SELECT *
FROM Emp LIMIT 3}} nor {{SELECT * FROM (SELECT * FROM Emp) LIMIT 3}} is
guaranteed to return the employees with the lowest {{empno}} values.
> SortCollapseRule for collapsing nested sorts
> --------------------------------------------
>
> Key: CALCITE-1719
> URL: https://issues.apache.org/jira/browse/CALCITE-1719
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Gian Merlino
> Assignee: Julian Hyde
>
> Useful for subqueries like {{SELECT * FROM (...) LIMIT X}}, where the inner
> query might have an order by or limit.
> Patch in https://github.com/apache/calcite/pull/410.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
