MariaDB Error creating Function when it worked on MySQL

Question

Please consider the following function defination. I created and set it up on MySQL 5.1 but it's failing in MariaDB 5.5

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `weighted_mean_by_kpi`(`KPIID` INT, `employee_id` INT, `date` DATE)
    RETURNS decimal(6,3)
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE rating_number INT DEFAULT 0;
    DECLARE rating_count INT DEFAULT 0;
    DECLARE rating_total INT DEFAULT 0;
    DECLARE weighted_total DOUBLE DEFAULT 0;

    DECLARE cur CURSOR FOR
            SELECT COUNT(rating), rating FROM employees_KPIs WHERE kpi_id = KPIID AND employee_id = employee_id AND employees_KPIs.created_at LIKE CONCAT("%",DATE_FORMAT(date,'%Y-%m'),"%") GROUP BY rating;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    RATING: LOOP
            FETCH cur INTO rating_count, rating_number;
            IF done = 1 THEN
                    LEAVE RATING;
            END IF;
            SET weighted_total =  weighted_total + (rating_number * rating_count);
            SET rating_total = rating_total + rating_count;
    END LOOP RATING;
    return (weighted_total/rating_total);
    #return (weighted_total);
    CLOSE cur;
END

I get the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 8

Thanks

not aware of mariaDB....but,i strongly feel error is there because done would be a reserved keyword in mariaDB for line DECLARE done INT DEFAULT 0; — NoobEditor
– NoobEditor, Commented Mar 21, 2014 at 7:13
Did you execute this after redefining the value for delimiter, like DELIMITER // or something else? — Ravinder Reddy
– Ravinder Reddy, Commented Mar 21, 2014 at 7:19

mc0e · Accepted Answer · 2014-12-22 16:48:35Z

3

Mysql sees the ';' delimiters in the function and breaks your CREATE FUNCTION statement.

To avoid this, change the delimiter before you define the function, and then change it back afterward:

Like:

DELIMITER //

-- your create function definition statement here

//
DELIMITER ;

As in your code the first ; semicolon was found at line 8, it tried to execute it the code up to the ';', and the syntax was invalid because it was incomplete (BEGIN without END).

edited Dec 22, 2014 at 16:48

mc0e

2,88031 silver badges25 bronze badges

answered Mar 21, 2014 at 7:20

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MariaDB Error creating Function when it worked on MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related