LinuxQuestions.org - Help! SQL User Variable doesn't store decimals when Variables are Reset

I am currently working on a query that makes use of user variables. I notice one small issue (bug?) depending on whether or not I reset a user variable with the "SET" command or don't before executing the query.

When I do reset the user variables, some of my variables will not hold decimal points (7.56734234 gets turned into and stored as 7). However when I run the query afterwards without resetting the user variables, the correct value (with the decimal) is stored.

Example with query:

First Run:

Code:

set @signal = 0,

@roi = 0,

@counter = 0,

@gain_loss = 0,

@prev_symbol = 0;



SELECT date, price, ma_pe_increases, ma_pe_decreases,

@roi := if(@counter = 3, if((price / @signal) > 1.50, 1.50, price / @signal), 0) as roi,

@signal := if(ma_pe_increases >= 6 AND ma_pe_decreases = 2, @signal := price, if(@counter > 0 AND @counter < 3 AND @prev_symbol = symbol, @signal := @signal, 0)) as signal,

@counter := if(@signal != 0, if(@counter = 0, 1, @counter + 1), 0) as counter,

@gain_loss := if(@roi != 0, if(@roi > 1, 1, 0), 0) as gain_loss,

@prev_symbol := symbol as prev_symbol

FROM monthly_data_6monthavg

LIMIT 240\G

First Run Results:

mysql> set @signal = 0,
-> @roi = 0,
-> @counter = 0,
-> @gain_loss = 0,
-> @prev_symbol = 0;
Query OK, 0 rows affected (0.00 sec)

...
*************************** 229. row ***************************
date: 2007-01-01
price: 7.06
ma_pe_increases: 6
ma_pe_decreases: 2
roi: 0
signal: 7.0599999427795
counter: 1
gain_loss: 0
prev_symbol: FLWS
*************************** 230. row ***************************
date: 2007-02-01
price: 7.31
ma_pe_increases: 6
ma_pe_decreases: 3
roi: 0
signal: 7
counter: 2
gain_loss: 0
prev_symbol: FLWS
*************************** 231. row ***************************
date: 2007-03-01
price: 7.78
ma_pe_increases: 6
ma_pe_decreases: 4
roi: 0
signal: 7
counter: 3
gain_loss: 0
prev_symbol: FLWS
...
Signal should equal 7.0599999427795 in all rows I listed, but instead it gets converted to 7 when stored in a user variable.

Second Run (without resetting user variables):

Code:

SELECT date, price, ma_pe_increases, ma_pe_decreases,

@roi := if(@counter = 3, if((price / @signal) > 1.50, 1.50, price / @signal), 0) as roi,

@signal := if(ma_pe_increases >= 6 AND ma_pe_decreases = 2, @signal := price, if(@counter > 0 AND @counter < 3 AND @prev_symbol = symbol, @signal := @signal, 0)) as signal,

@counter := if(@signal != 0, if(@counter = 0, 1, @counter + 1), 0) as counter,

@gain_loss := if(@roi != 0, if(@roi > 1, 1, 0), 0) as gain_loss,

@prev_symbol := symbol as prev_symbol

FROM monthly_data_6monthavg

LIMIT 240\G

Second Run Results:

*************************** 229. row ***************************
date: 2007-01-01
price: 7.06
ma_pe_increases: 6
ma_pe_decreases: 2
roi: 0
signal: 7.0599999427795
counter: 1
gain_loss: 0
prev_symbol: FLWS
*************************** 230. row ***************************
date: 2007-02-01
price: 7.31
ma_pe_increases: 6
ma_pe_decreases: 3
roi: 0
signal: 7.0599999427795
counter: 2
gain_loss: 0
prev_symbol: FLWS
*************************** 231. row ***************************
date: 2007-03-01
price: 7.78
ma_pe_increases: 6
ma_pe_decreases: 4
roi: 0
signal: 7.0599999427795
counter: 3
gain_loss: 0
prev_symbol: FLWS

...

Notice that the correct value of 7.0599999427795 each time, even when passed through a user variable when the user variables ARE NOT reset prior to running the main query.

Does anyone know why this is and what I can do to fix this?