ORDER BY usage in subquery (derived table) SQL standard allowed or not

Question

MariaDB documentation is mentioning that ORDER BY clause in subqueries (derived table) are never allowed by SQL standards.

Meaning the SQL query

SELECT
 field1
 , field2
FROM (
 SELECT
 field1
 , field2
 FROM
 table1
 ORDER BY field2
) alias

is never allowed by SQL standards

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery

see source

But when i look into the SQL 92 standard. (i know it's old)

4.9 Tables

...
...
A table is either a base table, a viewed table, or a derived table. A base table is either a persistent base table, a global tempo- rary table, a created local temporary table, or a declared local temporary table.
...
...
A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of a <query expression>. The values of a derived table are derived from the values of the underlying tables when the <query expression> is evaluated.
...
...

The simply underlying tables of derived tables and cursors are defined in Subclause 7.9, "<query specification>", Subclause 7.10, "<query expression>", and Subclause 13.1, "<declare cursor>". A viewed table has no simply underlying tables.

They mention cursors let see what they say about cursors and what they are.

4.21 Cursors

...
...

A cursor in the open state identifies a table, an ordering of the rows of that table, and a position relative to that ordering. If the <declare cursor> does not include an <order by clause>, or includes an <order by clause> that does not specify the order of the rows completely, then the rows of the table have an order that is defined only to the extent that the <order by clause> specifies an order and is otherwise implementation-dependent.

So they mention here a cursor in open state can be a table is either a base table, a viewed table, or a derived table. if you look how they define what a table is in section 4.9 Tables

If i read this i could be interpreting it as using ORDER BY in derived table (like the SQL query above) is perfect valid by SQL standards and it's up to vendors if they would allow ORDER BY there as a option.
But i would be interpreting it wrong english is not mine mother laungauge, so the question is iám interpreting it correctly?

• Jonathan Fite: There isn't a production RDBMS that follows the standard exactly. The various ways that each of them differ from the standard allows them to do some things better than others. MSSQL allows ORDER BY in derived tables, but only if you also specify a TOP limitation. It's common to see TOP 100 PERCENT if you need to specify an ORDER BY and also get all the results. But I would avoid writing it that way if possible. Save the ORDER BY for the last step if you need it and ideally let the client sort the data.
• user1822: The order by in the derived table is essentially useless as the only way to guarantee a specific sort order for the overall result is to use an order by in the outer most query. The only situation where the order by is even needed if used together with a row limit (e.g. fetch first 1 rows only)
• Raymond Nijland: There isn't a production RDBMS that follows the standard exactly. iám aware of that @JonathanFite like using tableless selects which are also not allowed by SQL standards. But most off them of that allow it with a dummy table.. But I would avoid writing it that way if possible. Save the ORDER BY for the last step if you need it and ideally let the client sort the data. yes i do that as much as possible already, i've learned SQL on MySQL which allowes alot.. But thanks for the comment
• Raymond Nijland: The order by in the derived table is essentially useless as the only way to guarantee a specific sort order for the overall result is to use an order by in the outer most query. indeed that totally makes sense @a_horse_with_no_name i totally catch that.. The only situation where the order by is even needed if used together with a row limit (e.g. fetch first 1 rows only) Yes or with TOP(), LIMIT then you would change the resultset within .. Thanks for the comment.

Answer 1

A cursor is not a table, it refers to a table or a view of that table. The cursor has an order only to the extent that the query that defines the cursor has an order by. A subquery within that larger query would not define the order, and the fact that the query is being used for a cursor doesn’t make an order by in subquery legal syntax.

Basically, as with any other query, if you don’t have an order by on the outermost layer, then the optimizer is free to return the results in what it deems is the easiest/quickest way. It may take advantage of indexes or caches or anything else that allows it to produce the correct result, but is not constrained to do so.

• Raymond Nijland: Basically, as with any other query, if you don’t have an order by on the outermost layer, then the optimizer is free to return the results in what it deems is the easiest/quickest way. indeed and why would the optimizer choose to return the outer sql different when the optimizer already knows the inner sql is ordered for this query keeping the same order is the easiest/quickest way for this query? Assuming the ORDER BY in subqueries was allowed by SQL standard?
• Raymond Nijland: Also out of the scope of this question because the question was about ORDER BY in subqueries..ideally the SQL optimizer should see SELECT field1 , field2 FROM ( SELECT field1 , field2 FROM table1 ORDER BY field2 ) alias is the same as SELECT field1 , field2 FROM table1 ORDER BY field2
• jmoreno: @RaymondNijland: I am saying that the part you refer to doesn’t define a special syntax where order by in a sub query is allowed. As to why a query optimizer would choose to do otherwise, that is the wrong question. The question would be what does the vendor gain or loose by making that guarantee. It looses freedom to choose something better, and it gains nothing. Introducing a non-conforming syntax that cost you something and gains nothing, isn’t useful. As for a query optimizer, consider multiple sub queries, each with their own order by, now it has to perform a useless sort on each.
• Raymond Nijland: Thanks i mostly definitely misinterpret the parts i've quoted from the SQL standards and some parts from your answer.. Your comment made it more clear.. it indeed also seams the more modern MySQL versions are removing the ORDER BY in subqueries also after optimizing
• Raymond Nijland: i know i mentioned SQL 92 in the question but the ORDER BY in derived tables is SQL standard dependent it seams.. In the SQL 2016 it is allowed SQL:2016, Part 2, 4.15.3 Derived tables section it defines "the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning..." Meaning ORDER BY is allowed in combination with fetch first <number> rows only