Define a variable within select and use it within the same select

Question

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?

Hagiographa

I very much expect @z:=someProcedure(item), 2*@z working above is just a happy coincidence. – Gosport

Answer 1

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;

Candace

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. – Hagiographa

But the subselect is part of the select statement, so your suggestion is also undefined. – Showdown

@Showdown . . . The subquery is run before the outer query, so the ordering is defined. – Candace

@GordonLinoff That is unsupported by the documentation and is explicitly contradicted by a bug report reply specifically about it. 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. – Showdown

@Showdown . . . 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. – Candace

@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.) – Showdown

@Showdown . . . They are two different SELECTs. – Candace

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 – Showdown

@Showdown . . . 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. – Candace

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. – Showdown

Answer 2

Works in mysql 5.5

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

+---------------+---------+
| @code:=sum(2) | 2*@code |
+---------------+---------+
|             2 |       4 |
+---------------+---------+

Unemployment

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. – Gosport

Answer 3

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.

Morphology

What's the point of assigning to @z if you're performing the same calculation in both places where @z is used?! – Kayleigh

Wrapping in an if statement does force (my version) to execute the statement before going into the then portion of the statement. – Aeniah

Answer 4

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;

Haggadist