Mysql error 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

Источник

Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.

Why? That’s the purpose of a function to perform something and return a single reply.

That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.

If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$

It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:

ERROR 1415 (0A000): Not allowed to return a result set from a function

You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.

Session Variable Debugging ↓

Expand this section to see the steps for debugging functions with session variables.

Create two session level variables, like these:

SET @counter := 0;
SET @msg := '';

Create a function that uses the SELECT-INTO statement to collect and store debugging information during function execution.

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$

Run the function and then query the session variable for results

SELECT debugger();
SELECT @msg;

You’ll see the following text:

+------------+
| @msg       |
+------------+
| [Debug #1] |
+------------+

Temporary Table Debugging ↓

Expand this section to see the steps for debugging functions with session variables.

Only when you want a counter, create one session level variables.

Create an in-memory table to store debugging information from function execution.

CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;

Create a function that supports inserts into the in-memory table. Naturally, you may need to make the columns larger when your debugging results are large. I’ve found that 80 characters is generally adequate for most debugging exercises.

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$

Call the function and query the debugging results.

SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

You’ll see the following text:

+---------------+
| debug_comment |
+---------------+
| [Debug #1]    |
| [Debug #2]    |
| [Debug #3]    |
+---------------+

Complete Code Sample ↓

Expand this section to see the sample working code for all examples.

This script creates, runs, and tests the code from the above discussions.

-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that returns 1.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Declare session level variables.
SET @counter := 0;
SET @msg := '';
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to local session variables.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT @msg;
 
-- Declare a session level variable.
SET @counter := 0;
 
-- Conditionally drop the function when it exists.
DROP TABLE IF EXISTS debugger;
 
-- Create a temporary (in-memory) table to record debugging information.
CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to a debugging table.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

Источник

Я пытаюсь внести условное изменение в параметр для оператора обновления. Я получаю следующую ошибку, когда пытаюсь выполнить следующую функцию

/home/y/bin/mysql -u root < testpri.sql > out
ERROR 1415 (0A000) at line 4: Not allowed to return a result set from a function

Содержимое testpri.sql следующее:

use `zestdb`;
DROP FUNCTION IF EXISTS UPDATEPASSWD;
DELIMITER //
CREATE FUNCTION UPDATEPASSWD(n INT) RETURNS varchar(255) DETERMINISTIC 
BEGIN
    DECLARE mypasswd varchar(255);
    IF (n = 1) THEN
       SET mypasswd = '12ccc1e5c3c9203af7752f937fca4ea6263f07a5';
       SELECT 'n is 1' AS ' ';
    ELSE
       SET mypasswd = '1a7bc371cc108075cf8115918547c3019bf97e5d';
       SELECT 'n is 0' AS ' ';
    END IF;>
    SELECT CONCAT('mypasswd is ', mypasswd) AS ' ';
    RETURN mypasswd;
END //
DELIMITER ;
CALL UPDATEPASSWD(0);

Что я упускаю?

2 ответы

Я думаю, что это на самом деле ваша отладка SELECT звонки.

Из Документы:

Операторы, возвращающие набор результатов, можно использовать в хранимой процедуре, но не в хранимой функции. Этот запрет распространяется на операторы SELECT, которые не содержат предложения INTO var_list…

ответ дан 11 мая ’13, 02:05

Я прибыл в поисках ответов на тот же вопрос и нашел другой способ обойти проблему, чтобы я мог использовать оператор SELECT, который является сердцем и душой функции MySQL, вызвавшей предупреждение.

Рассмотрим следующий фрагмент.

SET intNMatches = ( SELECT COUNT(*) …

SET заставляет оператор SELECT возвращать свой единственный столбец, количество строк, в intNMatches, локальную переменную, приведенную к BIGINT. Поскольку он содержит коммерческую тайну, я не могу показать остальную часть запроса. Достаточно сказать, что запрос устанавливается, не заставляя движок MySQL выдавать предупреждение.

ответ дан 04 мар ’18, в 19:03

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

mysql
sql
function
stored-functions
mysql-error-1415

or задайте свой вопрос.

Источник