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.
The optimizer treats the @a <= 5 expression as a constant, and
gets rid of it. It isn't (yet?) smart enough to notice that @a
is being assigned to in the same statement, and thus will change.
> 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?
Why can't you use LIMIT? (Followups to this question probably
belong on mysql@stripped, please.)
Tim
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Tim Smith <tim@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team
/_/ /_/\_, /___/\___\_\___/ Boone, NC USA
<___/ www.mysql.com
