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
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.