MySQL user variables and SUM function

Question

I'm trying to do some calculations within a SELECT query, using user variables to do so. This works fine, until I start using functions like SUM to collect data from joined tables.

Simplified example:

SET @a = 1;

SELECT @a := SUM(1 + 1) AS sum, @a

Result:

+------+------+
| sum  | @a   |
+------+------+
|    2 |    1 |
+------+------+

I'd expect @a to be 2 here.

Other example:

SELECT @b := SUM(1 + 1) AS sum, @b;

+------+------+
| sum  | @b   |
+------+------+
|    2 | NULL |
+------+------+

Now it's NULL, because @b wasn't SET before the query.

It seems that the variable isn't overwritten with the result of the SUM function. Is there any way to fix this?

Answer 1

As stated in the documentation:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

To the second part of your question. You can initialize your @variable within a query like this (subqueries get evaluated first):

SELECT @b := SUM(1 + 1) AS sum, @b FROM (SELECT @b:=0) b