Help! SQL User Variable doesn't store decimals when Variables are Reset

farmerjoe · 07-22-2008, 06:44 PM

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?

farmerjoe · 07-22-2008, 07:45 PM

I figured out what it was from the MySQL documentation (yes I know I am a moron):

"Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement...To avoid problems with this behavior, either do not set and use the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it."

I simply changed my SET statement to set the initial values from 0 to 0.0.