List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:August 10 2005 4:27pm
Subject:Re: SELECT vs SET for creating variables
View as plain text  
Bill Dodson wrote:

> I am using version 4.0.12-nt on a Windows 2000 machine.

I have 4.1.11.

> I have noticed some difference in the way SET and SELECT create 
> variables.  The following statements do not work the way I would expect.
> 
> SELECT @neededStep := 10;
> SELECT @startOfCenter := 7;
> SELECT @returnData :=
> IF(@neededStep <= @startOfCenter,
> CONCAT(@neededStep, ' <= ', @startOfCenter),
> CONCAT(@neededStep, ' > ', @startOfCenter)
> );
> 
> In the above example @returnData ends up being '10 <= 7', but when 
> @neededStep is less than 10 the expected results are found. (could this 
> be because @neededStep is stored as text and not a number?)

I get '10 > 7'.

> In the next example @returnData ends up being '10 > 7', as expected.  
> This seems to work for all values of @neededStep I have tested.
> 
> SET @neededStep := 10;
> SET @startOfCenter := 7;
> SELECT @returnData :=
> IF(@neededStep <= @startOfCenter,
> CONCAT(@neededStep, ' <= ', @startOfCenter),
> CONCAT(@neededStep, ' > ', @startOfCenter)
> );

This also gives me '10 > 7'.

> It would seem that SET is a better way to create variables from constant 
> values, but I would like to understand why.  Does anybody know what is 
> happening here?

I expect it's a bug which has since been fixed (though I didn't check the bugs 
db to be sure).  You are using a very old version of mysql -- 4.0.12 was 
released in March, 2003.  The current version in the 4.0.x series is 4.0.25. 
You can read the rather impressive list of bugs fixed since 4.0.12 in the 
manual <http://dev.mysql.com/doc/mysql/en/news-4-0-x.html>.  I'd suggest 
upgrading.

> Thanks for your time!
> bill

Michael

Thread
SELECT vs SET for creating variablesBill Dodson10 Aug
  • Re: SELECT vs SET for creating variablesMichael Stassen10 Aug
  • Re: SELECT vs SET for creating variablesPeter Brawley10 Aug