Recursive query in mysql

Question

I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Here is my working theory: Because you have the table data in InnoDB it may subjected to MVCC and Transaction Isolation in the midst of the InnoDB Infrastructure.

Please notice in my original post that I used MyISAM tables, which are not subject to ACID compliance.

Make a copy of the table, convert it to MyISAM, and run GetAncestry against it. You should find better performance.

Give it a Try !!!

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

The problem here is your use of SELECT ... INTO.

As I illustrated in my answer to this question, there are two ways to place the value returned from a query into a variable: SELECT ... INTO (as you're doing) or by using a scalar subquery as a scalar operand -- that is, assigning the value returned from a scalar subquery to the variable, using SET.

The two techniques do not have the same result when no records are found.

Specifically, SELECT ... INTO will never return the NULL you are looking for in a no-record-found situation, because when no records are found, it not only doesn't return anything... it's as if the statement had not been executed and the value of your variable remains what it was before you ran the query.

So, you're in an infinite loop. As soon as you stop matching rows, your variable's value stops changing and persists at its previous value.

Your workaround is to use the scalar subquery directly inside the IFNULL(), because this does return NULL (as you appear to be expecting) when no records are matched:

SET ch = IFNULL((SELECT parent_id FROM pctable WHERE id = ch),-1);

From the documentation for SELECT ... INTO:

If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged.

Recursive query in mysql

2 Answers2

Linked