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

71%

+3 −0

Q&A 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` ti...

1 answer · posted 2y ago by artaxerxe‭ · edited 2y ago by Alexei‭

Question mysql common-table-expression

#4: Post edited by

Alexei‭ · 2022-02-24T18:45:26Z (about 2 years ago)
using existing tag

Copy Link

Raw

Markdown

#3: Post edited by

artaxerxe‭ · 2022-02-24T13:42:38Z (about 2 years ago)

Copy Link

Raw

Markdown

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 by

artaxerxe‭ · 2022-02-24T13:23:11Z (about 2 years ago)

Copy Link

Raw

Markdown

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 by

artaxerxe‭ · 2022-02-24T13:21:45Z (about 2 years ago)

Copy Link

Raw

Markdown

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?

mysql common-table-expression

Communities

Post History