Stored function returns NULL Mysql

Question

i make this simple STORED FUNCTION in MySql that returns emails sparated with comma:

CREATE FUNCTION get_participantes (id INT) RETURNS VARCHAR(50)
BEGIN
 DECLARE par VARCHAR(50) DEFAULT "";
 (select GROUP_CONCAT(mail SEPARATOR ',') INTO @par from users where id IN (
select user_id from meeting_participants where meeting_id = id));
 RETURN @par;
END

But when i call it using SELECT, i got NULL everytime. The SQL sentence works perfectly but itself.

+----------------------+
| get_participantes(5) |
+----------------------+
| NULL                 |
+----------------------+

Please help

Well what happens when you run the raw `select` query in MySQL, with the `id` value of 5 hard coded? Do you get anything back? — Tim Biegeleisen, Jun 28 '18 at 15:44
Have you tried to run `select GROUP_CONCAT(mail SEPARATOR ',') from users where id IN ( select user_id from meeting_participants where meeting_id = 5)`??? — Eric, Jun 28 '18 at 15:47
YES, it worked. I solve it. Was the parameter that was ambiguous — , Jun 28 '18 at 15:52

Bill Karwin · Accepted Answer · 2018-06-28T16:27:15.963

The name of your function parameter is id, but you also have a column id in your users table, and maybe there's an id column in your meeting_participants table as well.

This creates an ambiguity. The expression doesn't know whether you mean the column id or the function parameter id.

You should give your function parameter a distinct name, to resolve the ambiguity.

CREATE FUNCTION get_participantes (_id INT) RETURNS VARCHAR(50)
READS SQL DATA
BEGIN
 DECLARE par VARCHAR(50) DEFAULT "";
 (select GROUP_CONCAT(mail SEPARATOR ',') INTO par from users where id IN (
select user_id from meeting_participants where meeting_id = _id));
 RETURN par;
END

A couple of other issues that you should know about, even though they are not related to your question:

In MySQL routines (unlike SQL Server for example), your par local variable is not the same as the @var session variable. Your code in this specific case will work, but it's possible some other function you write will be confused. See also my answer to "@" symbol in stored procedure?
You should add the clause READS SQL DATA to your function, or else you may get this error (as I did when I tested your function):

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

Stored function returns NULL Mysql

1 Answers1