Bug #78861 user variables does not affect sometimes
Submitted: 16 Oct 2015 11:01
Reporter: 帅 Bang Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: user variables

[16 Oct 2015 11:01] 帅 Bang
Description:
Firstly , we create two tables t1 and t2
mysql> create table t1(c1 int);
mysql> insert into t1 values(1),(2),(3);
mysql> create table t2(c1 int);
mysql> insert into t2 values(1),(2),(3);

after this  we :

mysql> set @a = 1;
mysql> set @b = 0;
mysql> select @a := @a + 10, @b  from  t1 where c1 in (select @a := @b + 1  from t1 t2 where t2.c1 = t1.c1 ) ;
+---------------+------+
| @a := @a + 10 | @b   |
+---------------+------+
|            11 |    0 |
|            21 |    0 |
|            31 |    0 |
+---------------+------+

but ,if we type the following command  instead, what we get is :

mysql> select @a := @a + 10, @b:=0  from  t1 where c1 in (select @a := @b + 1  from t1 t2 where t2.c1 = t1.c1 ) ;
+---------------+-------+
| @a := @a + 10 | @b:=0 |
+---------------+-------+
|            11 |     0 |
|            11 |     0 |
|            11 |     0 |
+---------------+-------+

in the latter case, the result is totally different. It seems that @a := @b + 1  has not been executed at all. so , why ? 

thx a lot

How to repeat:
Firstly , we create two tables t1 and t2
create table t1(c1 int);
insert into t1 values(1),(2),(3);
create table t2(c1 int);
insert into t2 values(1),(2),(3);

//////////////////////////////////////////session 1////////////////////////////////////

mysql> set @a = 1;
mysql> set @b = 0;
mysql> select @a := @a + 10, @b  from  t1 where c1 in (select @a := @b + 1  from t1 t2 where t2.c1 = t1.c1 ) ;
+---------------+------+
| @a := @a + 10 | @b   |
+---------------+------+
|            11 |    0 |
|            21 |    0 |
|            31 |    0 |
+---------------+------+

////////////////////////////////////////////////session 2///////////////////////////////////////

mysql> set @a = 1;
mysql> set @b = 0;

mysql> select @a := @a + 10, @b:=0  from  t1 where c1 in (select @a := @b + 1  from t1 t2 where t2.c1 = t1.c1 ) ;
+---------------+-------+
| @a := @a + 10 | @b:=0 |
+---------------+-------+
|            11 |     0 |
|            11 |     0 |
|            11 |     0 |
+---------------+-------+