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
