Description:
I made a SQL using user variables like this.
set @rownum=0, @preval=null; select @rnk:=IF((@rownum := @rownum + 1) and (@preval <=> sal), @rnk, @rownum) AS rnk, @preval:=sal sal FROM emp order by sal;
This SQL works well except for 1st time immediate after connection to MySQL.
Only first time, @rnk sometimes returns NULL.
[First time after connect to MySQL]
mysql> set @rownum=0, @preval=null; select @rnk:=IF((@rownum := @rownum + 1) and (@preval <=> sal), @rnk, @rownum) AS rnk, @preval:=sal sal FROM emp order by sal;
Query OK, 0 rows affected (0.00 sec)
+------+------+
| rnk | sal |
+------+------+
| 1 | 800 |
| 2 | 950 |
| 3 | 1100 |
| 4 | 1250 |
| NULL | 1250 |
| 6 | 1300 |
| 7 | 1500 |
| 8 | 1600 |
| 9 | 2450 |
| 10 | 2850 |
| 11 | 2975 |
| 12 | 3000 |
| NULL | 3000 |
| 14 | 5000 |
+------+------+
2nd or later, it works well.
set @rnk=0, @rownum=0, @preval=null; select @rnk:=IF((@rownum := @rownum + 1) and (@preval <=> sal), @rnk, @rownum) AS rnk, @preval:=sal sal FROM emp order by sal;
+------+------+
| rnk | sal |
+------+------+
| 1 | 800 |
| 2 | 950 |
| 3 | 1100 |
| 4 | 1250 |
| 4 | 1250 |
| 6 | 1300 |
| 7 | 1500 |
| 8 | 1600 |
| 9 | 2450 |
| 10 | 2850 |
| 11 | 2975 |
| 12 | 3000 |
| 12 | 3000 |
| 14 | 5000 |
+------+------+
14 rows in set (0.00 sec)
This behavior occurs on MySQL official binary 5.0.x, 5.1.x.
When I use the binary 'Server version: 5.1.23-beta-GIS-community-GIS-log MySQL Community Server (GPL)' for Windows, this SQL make mysqld crash!
I doubt that there is some problem on using (initializing?) user variables.
How to repeat:
(1) Make emp table on test database;
create table emp(sal int);
insert into emp values(800);
insert into emp values(1600);
insert into emp values(1250);
insert into emp values(2975);
insert into emp values(1250);
insert into emp values(2850);
insert into emp values(2450);
insert into emp values(3000);
insert into emp values(5000);
insert into emp values(1500);
insert into emp values(1100);
insert into emp values(950);
insert into emp values(3000);
insert into emp values(1300);
(2) issue this SQL after connection to MySQL
set @rownum=0, @preval=null; select @rnk:=IF((@rownum := @rownum + 1) and (@preval <=> sal), @rnk, @rownum) AS rnk, @preval:=sal sal FROM emp order by sal;
Suggested fix:
[Workaround]
I can avoid to this problem with declaration of @rnk explicit like these,
set @rnk=null, @rownum=0, @preval=null; select @rnk:=IF((@rownum := @rownum + 1) and (@preval <=> sal), @rnk, @rownum) AS rnk, @preval:=sal sal FROM emp order by sal;
or
set @rnk=0, @rownum=0, @preval=null; select @rnk:=IF((@rownum := @rownum + 1) and (@preval <=> sal), @rnk, @rownum) AS rnk, @preval:=sal sal FROM emp order by sal;
But I think that omitting a declaration of @rnk equals to '@rnk=null'.
[Suggested fix]
This SQL should work on 1st time after connection to MySQL.
