5 messages in com.mysql.lists.mysqlRe: user-defined variables
FromSent OnAttachments
George Lefter16 Jan 2000 14:07 
Sasha Pachev17 Jan 2000 08:29 
George Lefter17 Jan 2000 09:52 
Sasha Pachev17 Jan 2000 11:08 
Michael Widenius10 Mar 2000 03:44 
Subject:Re: user-defined variables
From:Michael Widenius (mon@monty.pp.sci.fi)
Date:03/10/2000 03:44:09 AM
List:com.mysql.lists.mysql

Hi!

"Sasha" == Sasha Pachev <sas@mysql.com> writes:

Sasha> George Lefter wrote:

hi.

On Mon, 17 Jan 2000, Sasha Pachev wrote:

The first thing I would try would be (@var := locate(..)) as l1 -- force the operator precedence -- if this does not work, post back, I'll give it a close look.

i've tried (@var := locate(..)) as l1, it makes no difference. i also managed to find a simpler example.

mysql> select * from T;

+------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)

mysql> select (@aa:=id) as a, (@bb:=@aa+3) as b from T;

+------+------+ | a | b | +------+------+ | 1 | 4 | | 2 | 5 | | 3 | 6 | | 4 | 7 | | 5 | 8 | +------+------+ 5 rows in set (0.01 sec)

mysql> select (@aa:=id) as a, (@bb:=@aa+3) as b from T HAVING b=5;

Empty set (0.01 sec)

mysql> select (@aa:=id) as a, (@bb:=@aa+3) as b from T ORDER by b DESC;

+------+------+ | a | b | +------+------+ | 1 | 4 | | 2 | 5 | | 3 | 6 | | 4 | 7 | | 5 | 8 | +------+------+ 5 rows in set (0.01 sec)

Sasha> Ok, this is a bug, thanks for reporting it -- we'll look into it and fix Sasha> it.

This wasn't that easy to fix :(

For now, I have instead updated the manual with the following:

------------- *NOTE:* In a `SELECT' statement, each expression is only evaluated when it's sent to the client. This means that one can't in the `HAVING', `GROUP BY' or `ORDER BY' clause refer to expression that involves variables that are set in the `SELECT' part. For example, the following statement will NOT work as expected:

SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;

The reason is that `@aa' will not contain the value of the current row, but the value of `id' for the previous accepted row.

-------------

Regards, Monty