Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
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` ti...
#3: Post edited
- 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
-- WHERE s2.number <> NULL- )
- SELECT DISTINCT r1.number, r1.parent_number, r1.timestamp, r1.step FROM root_from_child r1
My problem is that I uncomment last line from the CTE, 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?
- 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?
#2: Post edited
- 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
- -- WHERE s2.number <> NULL
- )
- SELECT DISTINCT r1.number, r1.parent_number, r1.timestamp, r1.step FROM root_from_child r1
- My problem is that I uncomment last line from the CTE, 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?
- 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
- -- WHERE s2.number <> NULL
- )
- SELECT DISTINCT r1.number, r1.parent_number, r1.timestamp, r1.step FROM root_from_child r1
- My problem is that I uncomment last line from the CTE, 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?
#1: Initial revision
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 -- WHERE s2.number <> NULL ) SELECT DISTINCT r1.number, r1.parent_number, r1.timestamp, r1.step FROM root_from_child r1 My problem is that I uncomment last line from the CTE, 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?