CODEWITHSUNDEEP
mysqlsqlstored-proceduressql-functionstored-functions
dalawh
dalawh

Reputation: 924

MySQL stored function isn't returning the correct value

Why do the SELECT statements in MySQL 1 return the correct results but the same SELECT statement in a stored function in MySQL 2 return the id of the latest row in table_a? I tried replacing the parameters in the stored function with the actual values used and it still returned the id of the latest row in table_a.

MySQL 1

USE `schema_a`;

SELECT `id`
FROM `table_a` 
WHERE `name` = 'AAA' 
AND `type_id` = 1 
AND `active` = 1 
ORDER BY `created_on` DESC
LIMIT 1;

SELECT `id`
FROM `table_a`
WHERE `name` = 'BBB'
AND `type_id` = 1
AND `active` = 1
ORDER BY `created_on` DESC
LIMIT 1;

SELECT `id`
FROM `table_a`
WHERE `name` = 'CCC'
AND `type_id` = 2
AND `active` = 1
ORDER BY `created_on` DESC
LIMIT 1;

MySQL 2

USE `schema_a`;

DROP FUNCTION IF EXISTS `test`;

DELIMITER //

CREATE FUNCTION `test`(name VARCHAR(255), type_id INT, active TINYINT)
    RETURNS INT
    NOT DETERMINISTIC
    READS SQL DATA
    BEGIN
        RETURN
        (SELECT `id`
        FROM `table_a`
        WHERE `name` = name
        AND `type_id` = type_id
        AND `active` = active
        ORDER BY `created_on` DESC
        LIMIT 1);
    END //

DELIMITER ;

SELECT `test`('AAA', 1, 1);
SELECT `test`('BBB', 1, 1);
SELECT `test`('CCC', 2, 1);

Upvotes: 0

Views: 124

Answers (1)

GMB
GMB

Reputation: 222482

Give the parameters values that are different from the column names, otherwise they are ambiguous.

DELIMITER //

CREATE FUNCTION `test`(p_name VARCHAR(255), p_type_id INT, p_active TINYINT)
    RETURNS INT
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
        RETURN
        (SELECT `id`
        FROM `table_a`
        WHERE `name` = p_name
        AND `type_id` = p_type_id
        AND `active` = p_active
        ORDER BY `created_on` DESC
        LIMIT 1);
END //

DELIMITER ;

Upvotes: 2

Related Questions