Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

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‭

#4: Post edited by user avatar Alexei‭ · 2022-02-24T18:45:26Z (over 2 years ago)
using existing tag
#3: Post edited by user avatar artaxerxe‭ · 2022-02-24T13:42:38Z (over 2 years ago)
  • 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 user avatar artaxerxe‭ · 2022-02-24T13:23:11Z (over 2 years ago)
  • 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 user avatar artaxerxe‭ · 2022-02-24T13:21:45Z (over 2 years ago)
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?