Thanks very much for this information! I suspected as much; I’ll also take the time to browse the source code.
Meanwhile I’ve submitted a documentation bug and a feature request for more elaborate documentation.

I made some experiments, and have reached what I think is a safe (and ugly) use of variables, where order of evaluation can be expected. But it’s only based on “empirical study”… not on sound knowledge.

Regards

Thanks very much for this detailed review.
I see your point with you last example.
This is exactly why I’m saying nothing is really defined. The docs only talk about “parts of the statement”, but have no discussion of the time at which variables are actually evaluated (e.g. before or after ORDER BY).

For all I know, there *could be* a well defined behavior that we are simply unaware of. This is where I’d like to get more definitive answers. I realize this post may not be the best place to look for these answers, but I’m not sure where is.

I’ll be perfectly content if told there’s a defined behavior in 1,2,3 simplest cases (e.g. full table scan, scan+ORDER BY, etc.). I can work from there to achieve results.

I think you raise a few valid points. For instance I think that it is indeed correct to assume that one could force the order of evaluation with parenthesis if it is all contained within the same expression.

However, the way I see it, this will only force the per-row evaluation order. Major problems can still arise when your expression relies on assignments that happened during the processing of some row other than the current row. Since the semantics of the SELECT statement is to deliver a set of rows, there is now way to force the order of row processing before constructing the resultset.

A simple example:

SET @a:=0, @b:=0;
SELECT IF(@a:=@b+1, @b:=@a, NULL)
FROM sakila.film_text;

In this case, no columns are accessed and it really does not matter in what order the rows are processed. Most likely, they are scanned in the order that is convenient for the underlying storage engine. So this is safe.

Now let’s add a column:

SET @a:=0, @b:=0;
SELECT film_id, IF(@a:=@b+1, @b:=@a, NULL)
FROM sakila.film_text;

In this case, there is a covering index for film_id, and this is likely to be used. So, rows are processed in order of the index on film_id. Still, this does not matter for the result.

Now we change it still some:

SET @a:=0, @b:=0;
SELECT film_id, IF(@a:=@b+1, @b:=@a, NULL)
FROM sakila.film
ORDER BY title;

The problem is, are the variables evaluated before the ORDER BY, or at the same time as the ORDER BY? I see different plans being used, and I get different results as compared to the previous query (beyond row order)

It gets worse when we add a where. We would like the evaluations to be done after applying the WHERE, but what guarantee do we have?

just my 2 cts.