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 |
+---------------+-------+