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.

Answer 2

that is because you are using SELECT queries whose output is not stored into variables or temporary inside FUNCTION which must. Function can return only one single value. So your code should be something like this:

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

or

SELECT left_id AS c1 INTO @c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0 LIMIT 1; 

SELECT right_id AS c2 INTO @c2 FROM mlm_user_mst WHERE parent_id=a AND right_id>0 LIMIT 1;