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?
