Guarantees When Using User Variables To Number Rows

Using user variables to number rows I often find answers here on SO suggesting the use of user variables to number some thing or other. Perhaps the clearest example would be a quer

Solution 1:

You misread the statement. It relates to the order of expressions in the SELECT list, when using multiple variables. As presented, the ORDER BY on this single-variable statement has a guaranteed order up to the current version of MySQL and nothing in that text suggests it will change.

But guarantee the future? Who knows.

---

Regarding the breaking query, you've again misunderstood how MySQL works. Let's break down your query. Take note of this statement in the manual

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected

The order of processing of queries is roughly

FROM/JOINWHERE/ONGROUPBY/ROLLUPHAVINGUNIONSELECTORDERBY@variable resolution

Your "broken" query attempts to use the variable WITHIN the same level, which is just about as sinful as using a WHERE/HAVING clause against a column alias. That's why you'll never see MySQL variable-based row_numbering solutions using the variable on the same query-level, it is always in a subquery. The outer query can be considered the client of the inner query at which stage the variable/placeholder-expression has been rendered. By your argument, you can just as easily break it using a WHERE clause involving the @row directly (yes it will run!).