MySQL/MariaDB - order by inside subquery

Question

I used the following query with MySQL 5.5 (or previous versions) for years without any problems:

SELECT t2.Code from (select Country.Code from Country order by Country.Code desc ) AS t2;

The order of the result was always descending as I needed.

Last week, I just migrated to a new MySQL Version (In fact, I migrated to MariaDB 10.0.14) and now the same query with the same database is not sorted descending anymore. It is sorted ascending (or sorted using the natural order, not sure in fact).

So, can somebody could tell me if this is a bug or if this is a change of the behaviour in recent versions of MySQL/MariaDB?

Possibly relevant: https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/ — Rick James, Mar 03 '16 at 03:52
Does this answer your question? [Is order by clause allowed in a subquery](https://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery) — philipxy, Apr 06 '23 at 06:08

score 38 · Accepted Answer · edited Jun 20 '20 at 09:12

38

After a bit of digging, I can confirm both your scenarios:

MySQL 5.1 does apply the ORDER BY inside the subquery.

MariaDB 5.5.39 on Linux does not apply the ORDER BY inside the subquery when no LIMIT is supplied. It does however correctly apply the order when a corresponding LIMIT is given:

SELECT t2.Code 
FROM (
  SELECT Country.Code FROM Country ORDER BY Country.Code DESC LIMIT 2
) AS t2;

Without that LIMIT, there isn't a good reason to apply the sort inside the subquery. It can be equivalently applied to the outer query.

Documented behavior:

As it turns out, MariaDB has documented this behavior and it is not regarded as a bug:

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, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.

So MariaDB also recommends applying the ORDER BY in the outermost query, or a LIMIT if necessary.

Note: I don't currently have access to a proper MySQL 5.5 or 5.6 to confirm if the behavior is the same there (and SQLFiddle.com is malfunctioning). Comments on the original bug report (closed as not-a-bug) suggest that MySQL 5.6 probably behaves the same way as MariaDB.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 15 '14 at 00:36

Michael Berkowski

267,341
46
444
390

1

@Michael, It is **typical** for `order by` not to apply inside a subquery isn't it? I don't think any db would guarantee that ordering. – Pacerier Oct 01 '15 at 16:17
1

@Pacerier As noted in the quoted docs "_SQL standard does not even allow the ORDER BY clause to appear in this subquery_", so most RDBMS probably won't allow it. – Michael Berkowski Oct 01 '15 at 16:24
There is no other way to have this work without sort working in a sub query. `SELECT sq.*, group_concat(tg.group_id) as group_ids FROM (SELECT t.id, t.query, t.serial_no FROM tokens t WHERE t.changed_at > ? AND t.serial_no > ? ORDER BY t.serial_no ASC LIMIT ?) sq LEFT JOIN token_groups tg ON sq.id = tg.token_id GROUP BY sq.id` Without the limited result in the subquery, the outer query is very costly (due to the aggregating function) – Ravi Sanwal Feb 14 '23 at 21:27
Result sets have order per an order by, tables have no row order. No ordering is guaranteed for an inner select with an order by. [Is order by clause allowed in a subquery](https://stackoverflow.com/q/2101908/3404097) [Order Of Execution of the SQL query](https://stackoverflow.com/a/4596739/3404097) – philipxy Apr 07 '23 at 00:39
@RaviSanwal See my comment above. "There is no other way to have this work without sort working in a sub query." There are queries for the desired result, but they don't involve nested ORDER BY without LIMIT, and no row order is guaranteed or meaningful for a nested ORDER BY without or after LIMIT. "is very costly" You have no sound reason to believe that. You have an unjustified & incorrect mental model of DBMS execution. A particular DBMS, version & query has a certain plan; a DBMS version might publish (nonstandard) implementation guarantees. – philipxy Apr 07 '23 at 00:43
I guess I should put a disclaimer. My observations were based on MySQL 5.7 and the queries produced different results. The one above and where we directly join the two tables and put the order and limit outside. This could be a DBMS implementation thing. Some other DBMS could logically infer that there is a sort and limit on a table so would automatically reduce the results before joining and aggregating but MySQL doesn't do that. (Or rather didn't do it), thus we had to move the order and limit into the subquery. Order is indeed guaranteed in a subquery when LIMIT is provided. Again, MySQL. – Ravi Sanwal Apr 08 '23 at 01:38
1

@RaviSanwal What is not guaranteed by the documentation is not guaranteed. Your idiosyncratic theory has no justification. Some results from some runs justify nothing, including re reruns of the same inputs. The semantics does not involve any ordering other than before the LIMIT, which produces an unordered set of rows. You fundamentally misunderstand the semantics. PS See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify 1 non-sole non-poster commenter `x` per comment about that comment. – philipxy Apr 09 '23 at 10:29

score 5 · Answer 2 · answered Feb 20 '19 at 04:45

5

In newer versions of MySQL and MariaDB you can force the ORDER BY in a sub query by applying a LIMIT. If you don't want to limit the rows, use the biggest number of BIGINT as a LIMIT.

This may come in handy at times, when the sub query needs to be generated in a desired order, for applying line numbers, for example.

answered Feb 20 '19 at 04:45

Thalueng

53
1
3

I think this "trick" has been shown to fail in obscure cases. – Rick James Feb 10 '23 at 19:26

score -1 · Answer 3 · edited Apr 09 '23 at 10:16

-1

Use order + limit insert subquery like this :

SELECT * FROM (
  SELECT * 
  FROM some_table 
  ORDER BY date DESC
  LIMIT 0,18446744073709551615
) AS a GROUP BY person
;

It will keep the order before grouping.

edited Apr 09 '23 at 10:16

philipxy

14,867
6
39
83

answered Apr 06 '23 at 04:20

Anggit Herdianto

35
6

This adds nothing to answers already here, such an answer should not be posted. Also this question is a duplicate, so it should be closed & not answered. – philipxy Apr 06 '23 at 05:56
No, there is no guarantee it will keep the order. See my comments at the accepted answer: https://stackoverflow.com/questions/26372511/mysql-mariadb-order-by-inside-subquery/75945788#comment133967330_26372638 – philipxy Apr 09 '23 at 10:24

MySQL/MariaDB - order by inside subquery

3 Answers3

Documented behavior:

Linked

Related