>>>>> "Thimble" == Thimble Smith <tim@stripped> writes:
Thimble> On Fri, Feb 16, 2001 at 11:40:36AM +0000, Paul Necklonger wrote:
>> SELECT @a:=1;
>> SELECT code,@a:=@a+1 FROM tab WHERE code>=1 AND @a<=5
>> This query seems to return exactly 4 rows, because @a increments first
>> and vary from 2 to 5 (due to WHERE condition). But really this query
>> returns 10 rows. Its because @a<=5 condition evaluates only once. But it
>> does evaluate! Try to issue this query again! After first query @a=11,
>> and second query returns 0 rows.
Thimble> The optimizer treats the @a <= 5 expression as a constant, and
Thimble> gets rid of it. It isn't (yet?) smart enough to notice that @a
Thimble> is being assigned to in the same statement, and thus will change.
The problem is that @a may not change at all during execution.
MySQL may under some conditions optimize queries as follows:
CREATE TEMPORARY TABLE foo SELECT code FROM tab WHERE code>=1 AND @a<=5
SEND to USER 'SELECT code,@a:=@a+1 FROM foo'
The above is especially true for queries that involves distinct.
With SQL you can't never predict when or how many times the field part
is executed, because the optimizer is allowed to re-arrange things to
get the execution as fast as possible.