I have a table named as "t1", my table is-
+------+------+
| c1 | c2 |
+------+------+
| 1 | 12 |
| 2 | 13 |
| 3 | 14 |
+------+------+
I want to get all rows along with the previous values of col2. The query I use:-
Select c1,@prev,@prev:=c2 from t1;
I get following output-
+------+---------+------------+
| c1 | @prev | @prev:=c2 |
+------+---------+------------+
| 1 | NULL | 12 |
| 2 | NULL | 13 |
| 3 | NULL | 14 |
+------+---------+------------+
I was expecting to get only NULL in the first row. But I am getting NULL in all rows. Please explain why it's giving NULL in all rows.
The reason for the NULLs is documented here:
Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement.
Since @prev is not defined before it is read for the first time, we cannot even say, what type it has.
Now you could preset it to some value like set @prev = 0;. But 0 might be a valid value, so you probably want it to be NULL for the first row. set @prev = null; will neither work. But you can do the following:
set @prev = -1; -- define type as SIGNED
set @prev = null; -- set NULL
Now you query should return
c1 @prev @prev:=c2
1 null 12
2 12 13
3 13 14
You can also define the type and assign NULL in a single statement:
set @prev = cast(null as signed);
See Demo
However - When you read and write a user variable in the same statement (other than SET) - the behavior is not defined. In the documentation you will find the following statements:
It is also possible to assign a value to a user variable in statements other than
SET. (This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.)
...
As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.
...
For other statements, such as
SELECT, you might get the results you expect, but this is not guaranteed.
I've marked the important parts in bold, so you can see that it's not recommended to use the variables in that way.
Newer versions (MySQL 8.0 and MariaDB 10.2) now support window functions like LEAD() and LAG(). So you can write the query the following way:
Select c1, c2, lag(c2) over (order by c1) as prev
from t1
This is not only reliable for future MySQL versions. It will also work on many (all major) RDBM-systems.