P_Ferreira P_Ferreira - 13 days ago 9
MySQL Question

Different results for the same query but inside a function

I have a table with Pontuation(Pontuacao) and an unique number for Accomodation(Estadia) and i want to calculate the average pontuation of each accomodation.
This is the table:

Estadia | Pontuacao
-------------------
5 | 5
-------------------
5 | 5


So i made this funcion:

delimiter $$
create function mediapontuacao(estadia int)
returns float
begin
declare media float;
select sum(Pontuacao)/count(*) into media
from EstadiaUtilizador
where Estadia = estadia;
return media;
end $$


If i do this

select mediapontuacao(5); //calculate average pontuation of the accomodation which number is 5


This query gives me the value of 3.965.
But if i do this

select sum(Pontuacao)/count(*)
from EstadiaUtilizador
where Estadia = 5;


In other words calculate average pontuation of the accomodation which number is 5, the exact same thing the function i wrote should do and this query gives me the value of 5.00 which is the correct answer.
I am puzzled why i get different values when it should give the same value, i think.

Answer

The problem is here:

where Estadia = estadia

which is the same as, say,

where 1 = 1

Your parameter and column should have different names, so the DBMS knows what you are talking about.