1

I have the following stored function for calculating ELO ranking:

CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT)
RETURNS DOUBLE DETERMINISTIC
BEGIN
  DECLARE wynik DOUBLE;
  SET wynik = 
    1.0E0 / (1.0E0 + POWER(1.0E1, przeciwnika - mój + 0.0E0) / 4.0E2);
  RETURN wynik;
END//

CREATE FUNCTION nowyRanking(mój INT, przeciwnika INT, wynik DOUBLE)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE wynik INT;
  SET wynik = 
    mój + ROUND(3.0E1 * (wynik - spodziewanyWynik(mój, przeciwnika)));
  RETURN wynik;
END//

Now I want to test it. I run:

SELECT nowyRanking(1500, 1500, 0.0E0);

And I get a NULL, for reasons unknown to me.

And now the fun part. I run:

SELECT 1500 + ROUND(3.0E1 * (0.0E0 - spodziewanyWynik(1500, 1500)));

And I get: 1470. Whew, that’s quite an improvement from a NULL, isn’t it?

Why on Earth does this happen? How should I fix my stored function?

Thanks in advance.

1 Answer 1

2

Your problem is due to the same variable name is used for the parameter and local variable. i.e, wynik used as INT in the local variable as well as DOUBLE in the parameter of the nowyRanking function.

In this case changing the parameter name will solve the issue. In the below code I renamed the parameter name wynik DOUBLE to wynikDouble DOUBLE and handled the same in the query to solve the problem.

Answer for How should I fix my stored function?

CREATE FUNCTION nowyRanking(mój INT, przeciwnika INT, wynikDouble DOUBLE)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE wynik INT;
  SET wynik = 
    mój + ROUND(3.0E1 * (wynikDouble - spodziewanyWynik(mój, przeciwnika)));
  RETURN wynik;
END

Working fiddle: http://sqlfiddle.com/#!9/76b5b/3

Answer for Why on Earth does this happen?

Update: In your actual code you declared the wynik as INT and used in the SET wynik = mój + ROUND(3.0E1 * (wynik - spodziewanyWynik(mój, przeciwnika))); block, here the wynik value is NULL only.

So the query is execute as SELECT 1500 + ROUND(3.0E1 * (NULL - spodziewanyWynik(1500, 1500))); that's why you are getting NULL as result.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.