MySQL stored procedure returns wrong values

Question

Im new to MySQL stored procedures and I was following some tutorial on how to use them, but I ran into an interesting thing with the following:

DELIMITER $$
CREATE DEFINER=`user`@`%` PROCEDURE `CalculateScores`(IN ID INT, OUT test INT)
BEGIN
    SELECT COUNT(*)
    INTO test
    FROM myTable
    WHERE id = ID;
END$$
DELIMITER ;

I run it with this:

CALL CalculateScores(252, @test);

and then just:

SELECT @test;

The strange thing is that @test returns the total row count of the entire table not just for the id I sent as a parameter.

What am I missing here? The tutorial never mention this, and I can't find an answer to why this is happening, I might suck at searching..

Answer 1

It looks like MySQL cannot differentiate between id and ID:

SELECT COUNT(*)
INTO test
FROM myTable
WHERE id = ID;

And it treats it like 1 = 1 which is always true (if column is not nullable).

---

You could add alias to indicate that id is column and not parameter.

CREATE PROCEDURE `CalculateScores`(IN ID INT, OUT test INT)
BEGIN
    SELECT COUNT(*)
    INTO test
    FROM myTable t
    WHERE t.id = ID;
END

db<>fiddle demo