Solution to the problem that order by is not effective in MySQL subquery

By chance, I discovered that a SQL statement produced different results when executed on different MySQL instances.

Problem Description

Create two tables, product table product_tbl and product operation record table product_operation_tbl, to simulate the business scenario. The structure and data are as follows:

Next, you need to query the latest modification time of all products using the following statement:

select t1.id, t1.name, t2.product_id, t2.created_at from product_tbl t1 left join (select * from product_operation_log_tbl order by created_at desc) t2 on t1.id = t2.product_id group by t1.id;

From the results, we can see that the subquery first sorts all the records in product_operation_log_tbl in reverse order by creation time (created_at), then joins them with product_tbl to find out the latest modification time of the product.

On the MySQL instance in region A, querying the latest modification time of a product can yield correct results. However, on the MySQL instance in region B, the modification time obtained is not the latest, but the oldest. By simplifying the statement, we found that the order by created_at desc statement in the subquery was not effective on the instance in region B.

Troubleshooting process

Could it be that the region affects the behavior of MySQL? After DBA investigation, it was found that the MySQL in area A was version 5.6, and the MySQL in area B was version 5.7, and this article was found:

https://blog.csdn.net/weixin_42121058/article/details/113588551

According to the description in the article, MySQL version 5.7 will ignore the order by statement in the subquery. However, what is puzzling is that the MySQL version we used to simulate the business scenario is 8.0, and this problem does not occur. Use Docker to start MySQL 5.6, 5.7, and 8.0 instances respectively to repeat the above operation. The results are as follows:

As you can see, only MySQL version 5.7 ignores the order by in the subquery. Is it possible that 5.7 introduced a bug and later versions fixed it?

Root cause of the problem

Continuing to search for documents and information, I found the following description in the official forum:

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.

The cause of the problem is clear. It turns out that in the SQL standard, the definition of a table is an unsorted data set, and a SQL subquery is a temporary table. According to this definition, the order by in the subquery will be ignored. At the same time, the official response also gave a solution: move the subquery's order by to the outermost select statement.

Summarize

In the SQL standard, order by in a subquery is not valid.

MySQL 5.7 exposes the problem because it complies with the SQL standard at this point, but this writing method is still effective in MySQL 5.6/8.0

This is the end of this article about the problem of order by not taking effect in MySQL subquery. For more relevant content about MySQL subquery order by not taking effect, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

Reference Documentation

https://stackoverflow.com/questions/26372511/mysql-mariadb-order-by-inside-subquery

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/