-2

When I run the following code, it always returns a NULL value for the two variables inside the loop.

SET @c = '';
SET @i = 4;
REPEAT
SET @c = CONCAT(
        ' -> ',
        (
            SELECT name
            FROM c
            WHERE id = @i
        ),
        @c
    );
SET @i = (
        SELECT pid
        FROM c
        WHERE id = @i
    );
UNTIL TRIM(COALESCE(@i, '')) = ''
END REPEAT;

The loop must be run at least 4 times; But in the second run, the run of the loop ends.
The value is set for variables in the first time that the loop is executed; But in the second run of the loop, the value of both changes to NULL and the loop stops. However, inside table c there are related values for the execution of the loop.

  • The problem occurs when the value of the variable @i in the loop changes.
    For example, the first time the loop is executed, the value of @i becomes 3, and there is an id with a value of 3 in table c in addition to the initial value of 4.

Structure of Table c

CREATE TABLE c (
    id int UNSIGNED NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    pid int UNSIGNED DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB,
AUTO_INCREMENT = 1,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_unicode_ci;
ALTER TABLE c
ADD CONSTRAINT c_pid_foreign FOREIGN KEY (pid) REFERENCES c (id);

INSERT INTO c(id, name, pid)
VALUES (1, '1st', NULL),
    (2, '2nd', 1),
    (3, '3rd', 2),
    (4, '4th', 3);

Thank you in advance for the good cooperation of the people to solve this problem.

New contributor
MRS1367 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
-2

I was finally able to solve my question after about more than a day; Of course, I did not understand the cause of this problem and I do not know why MySQL changes the return value of the result of the SELECT statement to NULL when using the @i variable after changing its value inside the loop?!!!
The solution is as follows:

SET @d = '';
SET @j = 4;
REPEAT
SELECT @c := name
FROM c
WHERE id = @j;
SET @d = CONCAT(' -> ', @c, @d);
SELECT @i := pid
FROM c
WHERE id = @j;
SET @j = @i;
UNTIL TRIM(COALESCE(@j, '')) = ''
END REPEAT;

Here are the best solution to this problem:

SET @d = '';
SET @j = 4;
REPEAT
SELECT name INTO @c
FROM c
WHERE id = @j;
SET @d = CONCAT(' -> ', @c, @d);
SELECT pid INTO @i
FROM c
WHERE id = @j;
SET @j = @i;
UNTIL TRIM(COALESCE(@j, '')) = ''
END REPEAT;

I hope it is useful for others as well.

New contributor
MRS1367 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.