Conditions which always matches returns no result with CTE

I have the following scenario (in MySQL 8):

CREATE TABLE `steps` (
`id` int NOT NULL AUTO_INCREMENT,
`number` varchar(30) DEFAULT NULL,
`parent_number` varchar(30) DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`step` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
);

INSERT INTO `steps` (`number`, `parent_number`, `step`) VALUES 
    ('1', null, '1'), 
    (1, null, 'FINAL'), 
    (2, null, '1'), 
    (2, null, 'FINAL'), 
    (3, 1, '1'), 
    (3, 1, 'FINAL'), 
    (4, 1, '1'), 
    (4, 1, 'FINAL'), 
    (5, 3, '1'), 
    (5, 3, 'FINAL'), 
    (6, 4, '1'), 
    (6, 4, 'FINAL');

WITH RECURSIVE root_from_child(number, parent_number, timestamp, step) AS 
(
SELECT s1.number, s1.parent_number, s1.timestamp, s1.step FROM steps s1 
	WHERE s1.number = 5
UNION ALL 
SELECT s2.number, s2.parent_number, s2.timestamp, s2.step FROM root_from_child r 
	INNER JOIN steps s2 ON r.parent_number = s2.number 
//this line is commented	-- WHERE s2.number <> NULL
) 
SELECT DISTINCT r1.number, r1.parent_number, r1.timestamp, r1.step FROM root_from_child r1

My problem is that if I uncomment last line from the CTE (the one which I marked with //this line is commented), I get nothing but the rows returned by the pivot query (first query from CTE). Why this? I think the uncommenting of that line shouldn't have any impact on the result.

Any idea?

posted almost 3 years ago

CC BY-SA 4.0

3y ago by Alexei‭

artaxerxe‭

40 reputation 3 0 6 7