From: Paul Necklonger
Date: February 16 2001 11:40am
Subject: Question about @variables in SELECT
List-Archive: http://lists.mysql.com/internals/493
Message-Id: <3A8D11B4.CB421582@kis.kiev.ua>
MIME-Version: 1.0
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit

Hi!
It was great idea to add support for @variables and especially the :=
operator in queries.
But I wonder why all expressions in WHERE with @variables calculate only
once.
For example:
Suppose tab is table with 1 column - code decimal(2,0). It has 10 rows -
code vary from 1 to 10.
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.
Lets rewrite query like this:
SELECT code,@a:=@a+1 AS col1 FROM tab WHERE code>=1 AND col1<=5
Unfortunately, this will cause error.Such queries would be very handy in
search engines, when you need only N hits for a moment. One can hold
cursor or create temporary table, but its tricky and too complex. Such
queries can be limited from the beginning, but how to advice MySQL to
stop after N rows retrieved?