Comments - User-Defined Variables
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Beware order of evaluation of rows when sorting. It would appear (at least as far as I've tested it) that when statements of the form:
SELECT @var:=<expression> FROM...
are executed, @var will always correspond to the value in the last row returned, as you might expect.
But if you do something like:
SELECT DISTINCT IFNULL(@var:=Name,'unknown') FROM Customers ORDER BY <some non-indexed expression> LIMIT 10
The statement containing the variable assignment will be executed *before* the order-by. In this case, the value left in @var at the end might not even correspond to any of the rows returned!
It would be good if the docs could clarify under exactly what conditions the order-of-variable-assignment-evaluation corresponds to the order-of-returned-rows, and especially under what conditions this can differ?
SELECT @var:=<expression> FROM...
are executed, @var will always correspond to the value in the last row returned, as you might expect.
i think the sql is exactly the same as mySQL because in mySQL it generates different values according to the row
whereas in mariaDB all rows are filled in with the same value in the last row
so, what is the best solution? anyone can explain it