29

Is there a possibility to do something like this?

SELECT 
    @z:=SUM(item),
    2*@z
FROM
    TableA;

I always get NULL for the second column. The strange thing is, that while doing something like

SELECT 
    @z:=someProcedure(item),
    2*@z
FROM
    TableA;

everything works as expected. Why?

Braiam
  • 1
  • 11
  • 47
  • 78
user2370579
  • 321
  • 1
  • 4
  • 7
  • 2
    I very much expect `@z:=someProcedure(item), 2*@z` working above is just a happy coincidence. – Arjan Nov 19 '14 at 08:12

4 Answers4

38

MySQL documentation is quite clear on this:

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.

You can do what you want using a subquery:

select @z, @z*2
from (SELECT @z:=sum(item)
      FROM TableA
     ) t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 3
    The query is a rather complicated one, so I dont want to add anymore subqueries. And why is it working with procedures? Using a variable on the procedure is much more faster than using the procedure twice. So I just dont get it, why this should not work with a the sum(item) as well. – user2370579 May 23 '13 at 13:56
  • But the subselect is part of the select statement, so your suggestion is also undefined. – philipxy May 23 '17 at 08:08
  • @philipxy . . . The subquery is run before the outer query, so the ordering is defined. – Gordon Linoff May 23 '17 at 11:10
  • @GordonLinoff That is unsupported by the documentation and is explicitly contradicted by [a bug report reply specifically about it](https://bugs.mysql.com/bug.php?id=47516). A SELECT statement text that both reads and writes to the same variable has undefined behaviour. I would be very happy if you could supply some evidence to the contrary. Empirical tests do not count as evidence. Just because everybody does it, and practically speaking for now should do it because the risk is worth it, doesn't make it defined. – philipxy May 23 '17 at 17:24
  • 1
    @philipxy . . . The bug report is not about a variable defined in a *subquery*. And, although the documentation is not as explicit as I would like, SQL requires that the `FROM` clause be evaluated before the `SELECT`, so a variable defined in a subquery should be safe. – Gordon Linoff May 24 '17 at 02:51
  • @GordonLinoff The response says a SELECT statement with `@v` both read and written is undefined. The semantics of a select statement with legal use of `@v` is irrelevant. (Although also poorly described.) – philipxy May 24 '17 at 05:58
  • @philipxy . . . They are two different `SELECT`s. – Gordon Linoff May 24 '17 at 11:43
  • The whole is one "select statement" per the MySQL documentation & the variable is both read & written in it. The bug response & manual sections on variables & assignment don't limit the restriction on both reading & writing to the parts of a select statement that are not a subquery. We disagree. PS [MySQL Server Blog](https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/) – philipxy Jun 15 '20 at 00:18
  • @philipxy . . . I get your point. It is unclear whether "statement" applies to the entire `select` or just to one level of subqueries. That said, it is interesting that the blog is superseded by later, pre-8.0 versions of MySQL where you need to do the ordering in a subquery. All that said, MySQL has happily deprecated variable assignments in `SELECT` statements . . . so there is something we can at least both celebrate. – Gordon Linoff Jun 15 '20 at 00:31
  • Amen. (Muted 'Hallelujah'?) PS I really appreciate your perseverance. PS I wish I could rediscover a certain Percona writeup where they say they examined the code of some version(s) & showed that using CASE in a certain specific way in a query (that I think was in outer SELECT) gave the "expected" semantics/result. – philipxy Jun 15 '20 at 00:51
3

Works in mysql 5.5

select @code:=sum(2), 2*@code

+---------------+---------+
| @code:=sum(2) | 2*@code |
+---------------+---------+
|             2 |       4 |
+---------------+---------+
Ravi Parekh
  • 5,253
  • 9
  • 46
  • 58
  • 11
    No, this does not work in my MySQL 5.6 setup; the second column yields `NULL` on first invocation, and returns 2 times *the previous result* if run again, which might fool one into thinking it did work. Now, interesting enough, both `select @code:=2, 2*@code` and `select @code:=rand(), 2*@code;` *do* work in the same installation (today). So, given the documentation (see Gordon's answer), I'd not rely on this to return some defined result. – Arjan Nov 19 '14 at 08:10
1
mysql> select @z := sum(5), if(@z := sum(5), 2*@z, 0) ;
+--------------+------------------------------+
| @z := sum(5) | if(@z := sum(5), 2*@z, null) |
+--------------+------------------------------+
|            5 |                           10 |
+--------------+------------------------------+

I believe wrapping the 2*@z in the if statement will ensure the sum is performed BEFORE the additional calculation.

pala_
  • 8,901
  • 1
  • 15
  • 32
  • 4
    What's the point of assigning to `@z` if you're performing the same calculation in both places where `@z` is used?! – rodrigo-silveira Sep 08 '16 at 21:05
  • Wrapping in an if statement does force (my version) to execute the statement before going into the `then` portion of the statement. – Artistan Jul 11 '20 at 15:56
0

we can't safely define and use a variable in the same select statement.Best Approch is to not SET a Variable in the SELECT Query. Alternative Query is

select temp.z,temp.z*2
    from (SELECT sum(item) As `z`
          FROM TableA
         )temp;
Rana Muhammad Usama
  • 292
  • 3
  • 10