Search code examples
mysqlsql-order-bylimit

Strange behavior in mySQL 8 with order by, limit and parentheses

Supposing I have a table with the following values

enter image description here

I perform the following query and get the attached results

SELECT * FROM testvalues ORDER BY textval;

enter image description here

Everything is good so far. I will now add a LIMIT statement.

SELECT * FROM testvalues ORDER BY textval LIMIT 3;

enter image description here

Still everything looks good. But watch what happens when I add a parentheses to the query.

(SELECT * FROM testvalues ORDER BY textval) LIMIT 3;

enter image description here

Any ideas on why the parentheses causes the order to be incorrect. It would seem that the use of parentheses would only serve to ensure that the inner query is completed, and therefore ordered, prior to the limit being applied. But it appears that it causes the limit to be applied and ordering to be completely discarded. Is this a bug? A known bug? I did not see anything reported.

This came about because of a recent upgrade from MySQL5.5 to MySQL8 and how they handle UNION queries that contain order by and/or limit operations. Perhaps too many parentheses are being used regardless of if they are part of a union. Some of our SQL is generated so there may be cases where the parentheses are added by the engine automatically anticipating that the result might be used in a larger unioned query. Anyway. I digress from the core question.

EDIT/UPDATE:

As nbk and nick pointed out the use of parentheses, however innocuous they may seem in this instance, causes MySQL to handle this as a subquery. However, the following query actually works which seems contrary to the explanation given. 

SELECT root.* FROM (SELECT * FROM testvalues ORDER BY textval) AS root LIMIT 3;

enter image description here

Now this is in the full form of a subquery however it would appear that it does apply the sorting in the subquery and then limits the results.

EDIT 2:

Following up on Nick's response. Yes, this is an example of an actual query that has been simplified for clarity and uses mock data to keep from leaking sensitive real data.

I think I have my answer to the original question which is essentially "MySQL has decided to optimize this as a subquery and therefore ignore the ORDER BY". This brings me to a head scratcher. Why are the following queries handled differently when they would appear to be functionally equivalent.

(SELECT * FROM testvalues ORDER BY textval) LIMIT 3;

SELECT root.* FROM (SELECT * FROM testvalues ORDER BY textval) AS root LIMIT 3;
Solution

  • Ordering inside subqueries/derived tables does not affect the final output; those rows are ordered only by the outer level ORDER BY clause. Since you don't have one, the ordering of your results in the last query is indeterminate. It would work properly if written as 

    (SELECT * FROM testvalues) ORDER BY textval LIMIT 3;

    There is a description of this behaviour in the MariaDB knowledge base.

    Note that when you try a "real" subquery i.e.

    SELECT root.* FROM (SELECT * FROM testvalues ORDER BY textval) AS root LIMIT 3

    The optimiser will propagate the ORDER BY clause because it meets the conditions described in the manual:

    The optimizer propagates an ORDER BY clause in a derived table or view reference to the outer query block if these conditions are all true:

    • The outer query is not grouped or aggregated.

    • The outer query does not specify DISTINCT, HAVING, or ORDER BY.

    • The outer query has this derived table or view reference as the only source in the FROM clause.

    When these conditions are not true:

    Otherwise, the optimizer ignores the ORDER BY clause.