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
But when i look into the SQL 92 standard. (i know it's old)
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.
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?