List:Internals« Previous MessageNext Message »
From:Thimble Smith Date:February 16 2001 3:29pm
Subject:Re: Question about @variables in SELECT
View as plain text  
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 Smith <tim@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Development Team
/_/  /_/\_, /___/\___\_\___/   Boone, NC  USA
Question about @variables in SELECTPaul Necklonger16 Feb
  • Re: Question about @variables in SELECTThimble Smith16 Feb
    • Re: Question about @variables in SELECTMichael Widenius17 Feb