Why MySQL is giving error "Not allowed to return a result set from a function"?

Question

I am trying to create a MySQL function using phpMyAdmin and getting this error.

#1415 - Not allowed to return a result set from a function

The function code is as below:

DELIMITER $$
    
CREATE FUNCTION get_binary_count(a INT, c INT)
RETURNS INT
DETERMINISTIC 

BEGIN
    DECLARE c1, c2 INT;
    SET c1 = 0;
    SET c2 = 0;

    SELECT left_id  AS c1 FROM mlm_user_mst WHERE parent_id = a AND left_id > 0;
    SELECT right_id AS c2 FROM mlm_user_mst WHERE parent_id = a AND right_id > 0;

    IF (c1 > 0 AND c2 > 0) THEN
        SET c = c + 1;
        SET c = c + get_binary_count(c1, 0);
        SET c = c + get_binary_count(c2, 0);
    END IF;

    RETURN c;
END$$
 
DELIMITER ;

Any suggestions?

Thanks in advance.

Answer 1

Because

SELECT left_id AS c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0; 

doesn't set the variable c1, it returns a set with a column named c1

You want

SELECT left_id INTO c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0; 

Similarly for c2.