Bug #1963 User variables may remain between rows in select

• Submitted: 26 Nov 2003 16:57
• Modified: 21 Aug 2005 9:19
• Reporter: Ondra Zizka
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 4.0.13
• OS: Any (All)
• CPU Architecture: Any

[26 Nov 2003 16:57] Ondra Zizka

Description:
I think it would be very useful to let user variables remain between rows in selects. Eg., when I need to compute the time between events (eg. user's accesses), I could use:
  SELECT timex-@a AS delay, @a:=timex, FROM_UNIXTIME(timex) FROM accesses WHERE user_id = 805492982
This would return number of seconds between two accesses of user 805492982 in the column 'delay'.
This feature would be useful many other ways, just use your fantasy. Every time when you have some relationship between successive rows, eg. logs.

How to repeat:
SELECT timex-@a AS delay, @a:=timex, FROM_UNIXTIME(timex) FROM accesses WHERE user_id = 805492982

Currently this returns NULLs in the 'delay' column.

Suggested fix:
The @a variable should hold the last value assigned to it (in my example, the value of the 'timex' column of the previous row).

Thanks a lot if sb decides to implement.

[26 Nov 2003 17:00] Ondra Zizka

Yeah, I forgot to have the result ordered, thus:
   SELECT timex-@a AS delay, @a:=timex, FROM_UNIXTIME(timex) FROM accesses WHERE
user_id = 805492982 ORDER BY timex ASC

[21 Aug 2005 9:19] Ondra Zizka

In 4.1.10 this works! Cool. Closing the feature request.

DROP TABLE test.log;
CREATE TABLE test.log (
  timex INT UNSIGNED NOT NULL
);
INSERT INTO test.log VALUES (510),(520), (550), (610);

SET @a = NULL;
SELECT timex-@a AS delay, @a:=timex, FROM_UNIXTIME(timex) FROM test.log;

----->

+-------+-----------+----------------------+
| delay | @a:=timex | FROM_UNIXTIME(timex) |
+-------+-----------+----------------------+
|  NULL |       510 | 1970-01-01 01:08:30  |
|    10 |       520 | 1970-01-01 01:08:40  |
|    30 |       550 | 1970-01-01 01:09:10  |
|    60 |       610 | 1970-01-01 01:10:10  |
+-------+-----------+----------------------+
4 rows in set (0.02 sec)