4 ms·

> The query planner now omits ORDER BY clauses on subqueries and views if removing those clauses does not change the semantics of the query. That's fair and al

> The query planner now omits ORDER BY clauses on subqueries and views if removing those clauses does not change the semantics of the query. That's fair and allowed by the standard, but it will break some things.

ComputerGuru 5y ago
It says “if it doesn’t change the semantics of the query” and not “if it isn’t guaranteed by the spec,” which means it actually should only happen in cases where it doesn’t break anything, eg “foo in (… only now without an implicit order)” but not “foo == (select foo from (… now without implicit order) limit 1))” - at least, presumably.
- cryptonector 5y ago
  Well, I guess I'll have to play with it. It's not entirely clear from the release notes what "semantics" means in this case. In principle in relational algebra ORDER BY is simply not meaningful because it's all unordered sets. In practice ORDER BY is essential. In principle all ORDER BY clauses in sub-queries can just be deleted, but in practice it can affect aggregation functions not built on commutative operations.
  - emmelaich 5y ago
    > what "semantics" means An almost Clintonian phrase! :-)
cldellow 5y ago
I think I recall when this feature was discussed and implemented. If it's what I'm thinking of, this concern was carefully considered: If a subquery in the FROM clause has an ORDER BY, that ORDER BY is omitted if all of the following conditions are also true: 1. There is no LIMIT clause in the subquery 2. The subquery was not one of the virtual subqueries added internally by SQLite for window-function processing 3. The subquery is not part of the FROM clause in an UPDATE-FROM statement 4. The outer query does not use any aggregate functions other than the built-in count(), min(), and/or max() functions. 5. Either the outer query has its own ORDER BY clause or else the subquery is one term of a join. from https://sqlite.org/forum/forumpost/878ca7a9be0862af?t=h https://sqlite.org/forum/forumpost/878ca7a9be0862af?t=h
- cryptonector 5y ago
  Thanks! That works for me!

ComputerGuru 5y ago

It says “if it doesn’t change the semantics of the query” and not “if it isn’t guaranteed by the spec,” which means it actually should only happen in cases where it doesn’t break anything, eg “foo in (… only now without an implicit order)” but not “foo == (select foo from (… now without implicit order) limit 1))” - at least, presumably.

cryptonector 5y ago
Well, I guess I'll have to play with it. It's not entirely clear from the release notes what "semantics" means in this case. In principle in relational algebra ORDER BY is simply not meaningful because it's all unordered sets. In practice ORDER BY is essential. In principle all ORDER BY clauses in sub-queries can just be deleted, but in practice it can affect aggregation functions not built on commutative operations.
- emmelaich 5y ago
  > what "semantics" means An almost Clintonian phrase! :-)

cldellow 5y ago

I think I recall when this feature was discussed and implemented. If it's what I'm thinking of, this concern was carefully considered: If a subquery in the FROM clause has an ORDER BY, that ORDER BY is omitted if all of the following conditions are also true: 1. There is no LIMIT clause in the subquery 2. The subquery was not one of the virtual subqueries added internally by SQLite for window-function processing 3. The subquery is not part of the FROM clause in an UPDATE-FROM statement 4. The outer query does not use any aggregate functions other than the built-in count(), min(), and/or max() functions. 5. Either the outer query has its own ORDER BY clause or else the subquery is one term of a join. from https://sqlite.org/forum/forumpost/878ca7a9be0862af?t=h https://sqlite.org/forum/forumpost/878ca7a9be0862af?t=h

cryptonector 5y ago
Thanks! That works for me!