Why the order of evaluation for expressions involving user variables is undefined?

Question

From MySQL Manual the output of the following query is not guaranteed to be same always.

SET @a := 0;

SELECT 
@a AS first,
@a := @a + 1 AS second,
@a := @a + 1 AS third,
@a := @a + 1 AS fourth,
@a := @a + 1 AS fifth,
@a := @a + 1 AS sixth;

---

Output:

first second third fourth fifth sixth 
  0     1      2     3     4      5

Quoting from the Manual:

However,the order of evaluation for expressions involving user variables is undefined;

I want to know the story behind.

So my question is : Why the order of evaluation for expressions involving user variables is undefined?

Kolomna

Answer 1

The order of evaluation of expressions in the select is undefined. For the most part, you only notice this when you have variables, because the errors result in erroneous information.

Why? The SQL standard does not require the order of evaluation, so each database is free to decide how to evaluate the expressions. Typically such decisions are left to the optimizer.

Clovah

Just to add, the order of select expression evaluation has typically been left to right; people generally run into problems when they try doing stuff like IF(x, @i, @i := @i + 1) and assume MySQL will not evaluate the assignment expression when x is true. – Geny

Do you have any reference on why that is dangerous? It is easy enough to use case (which is documented to be sequential), but I thought if() had the same semantics. – Clovah

I'm not 100% sure that one specifically is dangerous, it is just the general... uh... "format" (for lack of a better word) I see when people start running into problems with session variables. – Geny

@Geny . . . I do realize that when I use if()with assignments, the assignment is in the conditional part, not either option. We should be able to agree that that expression does get evaluated regardless of the outcome ;) – Clovah

Yes in the conditional part obviously, just saying I have seen it happen in expressions you wouldn't expect to be evaluated. I couldn't reproduce it with the example I gave in a straight up SELECT FROM DUAL scenario, but perhaps the optimizer does can affect it in more complicated scenarios. – Geny

Answer 2

TL;DR MySQL user-defined variables are not intended to be used that way. An SQL statement describes a result set, not a series of operations. The documentation isn't clear about what variable assignments even mean. But you can't both read and write a variable. And assignment order within SELECT clause is not defined. And all you can assume is that assignments in an outer SELECT clause are done for some one output row.

---

Almost all the code you see like yours has undefined behaviour. Some sensible people demonstrate via the implementation code for operators & optimization what a particular implementation actually does. But that behaviour can't be relied on for the next release.

Read the documentation. Reading and writing the same variable is undefined. When it's not done, any variable read is fixed within a statement. There is no order to assignments. For SELECTs with only DETERMINISTIC functions (whose values are determined by argument values) the result is defined by a conceptual evaluation execution. But there is no connection between that and user variable. What an assignment ever means is not clear: the documention says "each select expression is evaluated only when sent to the client". This seems to be saying that there's no guarantee a row is even "selected" except in the sense of put into a result set per an outermost SELECT clause. The order of assignments in a SELECT is not defined. And even if assignments are conceptually done for every row, they can only depend on the row value, so that's the same as saying the assignment is done only once, for some row. And since assignment order is not defined, that row can be any row. So assuming that that is what the documentation means, all you can expect is that if you don't read and write from the same variable in a SELECT statement then each variable assignment in the outermost SELECT will have happened in some order for one output row.

Hygienics

Answer 3

It depends on database's optimizer's decision. That's why it's uncertain. But mostly optimizer decides as the way we predict the result.

Hirza