MySQL function does not return correct result

Question

The function below always returns ELSE value (which is 6) even if the SELECT query itself returns value 30.00 for example. In that case, the function should return number 4. Can anyone help me please?

DELIMITER $$
CREATE FUNCTION znamka(id_testu INT, id_studenta INT)
    RETURNS TINYINT
BEGIN
    DECLARE percenta FLOAT;
    SET percenta=
    (
        SELECT ROUND(tv.pocet_bodov/t.max_body*100, 2)
        FROM test t JOIN test_vysledky tv ON (tv.fk_test=t.id_test)
        WHERE tv.fk_test=id_testu AND tv.fk_student=id_studenta
    );
    RETURN CASE
        WHEN percenta>=90 THEN 1
        WHEN percenta<90 >=75 THEN 2
        WHEN percenta<75 >=50 THEN 3
        WHEN percenta<50 >=30 THEN 4
        WHEN percenta<30 THEN 5
        ELSE 6
    END;
END;
$$
DELIMITER ;

"WHEN percenta<90 >=75 THEN 2" etc is invalid syntax. Try "WHEN percenta between 75 and 89.9999 THEN 2" etc, or "WHEN percenta <90 and percenta >=75 THEN 2". — jarlh
– jarlh, Commented Sep 25, 2015 at 6:35

Tim Biegeleisen · Accepted Answer · 2015-09-25 06:38:45Z

2

Change your CASE statement to this:

RETURN CASE
    WHEN percenta >= 90 THEN 1
    WHEN (percenta < 90 AND percenta >=75) THEN 2
    WHEN (percenta < 75 AND percenta >=50) THEN 3
    WHEN (percenta < 50 AND percenta >=30) THEN 4
    WHEN percenta < 30 THEN 5
    ELSE 6
END;

Comments:

As @Drew mentioned in his comment, you will never hit your ELSE condition. In addition, I'm surprised that MySQL was executing this function at all without any error.

edited Sep 25, 2015 at 6:38

answered Sep 25, 2015 at 6:36

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Drew Over a year ago

Tim to the rescue. Too bad his ELSE makes no sense

Tim Biegeleisen Over a year ago

Are you certain your query is working as expected? What happens if you hard code percenta = 90 in your function? Does the appropriate condition fire?

Tim Biegeleisen Over a year ago

There is something going on in your query I believe, in addition to the problem you had with your CASE statement.

dnoeth Over a year ago

@Lopfest: try to return percenta instead of the CASE to see the actual value

Lopfest Over a year ago

I figured out that there is actually problem with the INSERT statement, not the function. INSERT INTO test_vysledky VALUES (10, 99, 9, znamka(10, 99)); this will insert always the ELSE value from function. But when i put it like below, with function stored in variable: SET @VAR=(SELECT znamka(10, 99)); INSERT INTO test_vysledky VALUES (10, 99, 9, @VAR); then it chooses the correct CASE. Although this doesn't suit me, because there will be different function variables in each INSERT. Any ideas?

|

Tim Biegeleisen · Accepted Answer · 2015-09-25 07:02:20Z

0

What's the datatype of those pocet_bodov and max_body columns? If it's INT you might get a truncated result. Try multiplication first:

SELECT ROUND(100*tv.pocet_bodov/t.max_body, 2)

Edit: Previous can't cause a problem in MySQL because it's not doing INTEGER calculation.

The SQL CASE statement is not the same as a C SWITCH; it stops at the first matching condition, there is no need to use BETWEEN:

RETURN CASE
    WHEN percenta >= 90 THEN 1
    WHEN percenta >=75) THEN 2
    WHEN percenta >=50) THEN 3
    WHEN percenta >=30) THEN 4
    WHEN percenta < 30 THEN 5
    ELSE 6 -- this will only be returned for NULLs
END;

edited Sep 25, 2015 at 7:02

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Sep 25, 2015 at 6:43

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

1 Comment

Lopfest Over a year ago

those columns are both TINYINT UNSIGNED.

Collectives™ on Stack Overflow

MySQL function does not return correct result

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related