Mysql ошибка 1415 - Исправление ошибок и поиск оптимальных решений проблем

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.

informatik01

15.8k10 gold badges74 silver badges103 bronze badges

asked Aug 9, 2012 at 9:22

aslamdoctoraslamdoctor

3,61511 gold badges52 silver badges93 bronze badges

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.

answered Aug 9, 2012 at 9:24

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;

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;

answered Aug 9, 2012 at 9:24

OmeshOmesh

27.2k6 gold badges41 silver badges50 bronze badges

Источник

I am not trying to return a result set and I have no idea what I’m doing wrong here.
MySQL 5.5

delimiter $$

CREATE FUNCTION CheckAccount(
    i_username varchar(50)
) RETURNS integer

BEGIN

    DECLARE v_validUserId int;
    DECLARE v_validMembership int;
    DECLARE o_Status integer;

    SELECT vvalidUserId = u.UserId
    FROM Users u
    WHERE u.Username = i_username;

    IF( v_validUserId IS NULL ) THEN
        SET o_Status = 2; -- Invalid username
    ELSE
        SET o_Status = 1; -- Good
    END IF;


    IF(o_Status != 2 ) THEN
            SELECT v_validMembership = 1
            FROM Users u
            JOIN UserMemberships um on um.UserId = u.userId
            JOIN Memberships m on m.MembershipId = um.MembershipId
            WHERE um.MembershipExpireDateTime > CURDATE()
            AND u.UserId = v_validUserId;

            IF( v_validMembership IS NULL ) THEN 
                SET o_Status = 3; -- Invalid membership
            END IF;
    END IF;

    RETURN o_status;

END $$
DELIMITER ;

Any help will be greatly appreciated!

Trinimon

13.8k9 gold badges43 silver badges60 bronze badges

asked Apr 23, 2013 at 19:06

I’m not sure if you can assign variables that way, try use INTO statement for your selects. For example:

SELECT 
    u.UserId INTO vvalidUserId 
FROM 
    Users u
WHERE 
    u.Username = i_username;

answered Apr 24, 2013 at 1:48

b.b3rn4rdb.b3rn4rd

8,2842 gold badges41 silver badges55 bronze badges

Источник

Error Code: 1415. Not allowed to return a result set from a trigger

Discussion in ‘MySQL’ started by ying7690, Jul 4, 2011.

ying7690
New Member

Joined:

Jul 4, 2011

Messages:

3

Likes Received:

0

Trophy Points:

0
I have the following code for trigger
the syntax message was

«Error Code: 1415. Not allowed to return a result set from a trigger»
delimiter $$ CREATE TRIGGER New_User AFTER INSERT ON tblAdmin FOR EACH ROW BEGIN DECLARE _AdminRECID char(36); DECLARE _CreateUser varchar(50); DECLARE _CreateDate datetime; DECLARE _AuditUser varchar(50); DECLARE _AuditDate datetime; DECLARE _MenuRECID CHAR(36); SELECT RECID = _AdminRECID, CreateUser = _CreateUser, CreateDate = _CreateDate, AuditUser = _AuditUser, AuditDate = _AuditDate FROM tblAdmin; SELECT RECID = _MenuRECID FROM tblMenu; INSERT INTO tblAdmin_Access(RECID,AdminRECID,MenuRECID,Status, CreateUser,CreateDate,AuditUser,AuditDate) VALUES (UUID(),_AdminRECID,_MenuRECID,'A', _CreateUser,CURDATE(),_AuditUser,CURDATE()); END;

ying7690
New Member

Joined:: Jul 4, 2011

Messages:: 3

Likes Received:: 0

Trophy Points:: 0

Not allowed to return a result set from a trigger

I have the following code for trigger
the syntax message was

«Error Code: 1415. Not allowed to return a result set from a trigger»

delimiter $$

CREATE TRIGGER  New_User AFTER INSERT ON  tblAdmin

FOR EACH ROW
BEGIN
DECLARE _AdminRECID char(36);
DECLARE _CreateUser varchar(50);
DECLARE _CreateDate datetime;
DECLARE _AuditUser varchar(50);
DECLARE _AuditDate datetime;
DECLARE _MenuRECID CHAR(36);

SELECT RECID = _AdminRECID,
       CreateUser = _CreateUser,
       CreateDate = _CreateDate,
       AuditUser  = _AuditUser,
       AuditDate  = _AuditDate
FROM tblAdmin;

SELECT RECID = _MenuRECID
FROM tblMenu;



    INSERT INTO tblAdmin_Access(RECID,AdminRECID,MenuRECID,Status,CreateUser,CreateDate,AuditUser,AuditDate)
    VALUES (UUID(),_AdminRECID,_MenuRECID,'A',_CreateUser,CURDATE(),_AuditUser,CURDATE());


END;

shabbir
Administrator
Staff Member

Joined:

Jul 12, 2004

Messages:

15,370

Likes Received:

387

Trophy Points:

83

Please don’t create two separate threads and I have merged both of them for you.
ying7690
New Member

Joined:

Jul 4, 2011

Messages:

3

Likes Received:

0

Trophy Points:

0

thanks.because i cant remove the previous thread…

Share This Page

Источник

Error Code: 1415. Not allowed to return a result set from a trigger

ying7690 New Member

shabbir Administrator Staff Member

Share This Page

ying7690
New Member

shabbir
Administrator
Staff Member