List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 10 2000 11:44am
Subject:Re: user-defined variables
View as plain text  
Hi!

>>>>> "Sasha" == Sasha Pachev <sasha@stripped> 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
Thread
user-defined variablesGeorge Lefter17 Jan
  • Re: user-defined variablesSasha Pachev17 Jan
    • Re: user-defined variablesGeorge Lefter17 Jan
      • Re: user-defined variablesSasha Pachev17 Jan
        • Re: user-defined variablesMichael Widenius10 Mar