Mixing
Simple recursive CTE bombs out when changing operator in the WHERE clause
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.
CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)
INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')
;WITH
Parent AS
( SELECT parent
, child
, 1 as sort
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.sort + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, sort
FROM Parent
ORDER BY parent, sort desc
OPTION (maxrecursion 0);
DROP TABLE #Recursion
If I leave the where clause as such, it works
WHERE s.TopDate < d.TopDate
As soon as I change it to this, it bombs and will not complete
WHERE s.TopDate <= d.TopDate
I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?
Here is some example data. Note how the date field can be identical
parent child sort TopDate
00003137 04536347 2 2017-02-28
00003137 00003137 1 2018-08-31
00269705 00269705 2 9999-12-31
00269705 05458040 1 9999-12-31
I can get this to work on all data except data that has identical TopDates
sql-server sql-server-2012 t-sql
asked Sep 4 at 13:48
jdids
877
add a comment |Â
up vote
1
down vote
favorite
As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.
CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)
INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')
;WITH
Parent AS
( SELECT parent
, child
, 1 as sort
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.sort + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, sort
FROM Parent
ORDER BY parent, sort desc
OPTION (maxrecursion 0);
DROP TABLE #Recursion
If I leave the where clause as such, it works
WHERE s.TopDate < d.TopDate
As soon as I change it to this, it bombs and will not complete
WHERE s.TopDate <= d.TopDate
I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?
Here is some example data. Note how the date field can be identical
parent child sort TopDate
00003137 04536347 2 2017-02-28
00003137 00003137 1 2018-08-31
00269705 00269705 2 9999-12-31
00269705 05458040 1 9999-12-31
I can get this to work on all data except data that has identical TopDates
sql-server sql-server-2012 t-sql
asked Sep 4 at 13:48
jdids
877
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.
CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)
INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')
;WITH
Parent AS
( SELECT parent
, child
, 1 as sort
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.sort + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, sort
FROM Parent
ORDER BY parent, sort desc
OPTION (maxrecursion 0);
DROP TABLE #Recursion
If I leave the where clause as such, it works
WHERE s.TopDate < d.TopDate
As soon as I change it to this, it bombs and will not complete
WHERE s.TopDate <= d.TopDate
I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?
Here is some example data. Note how the date field can be identical
parent child sort TopDate
00003137 04536347 2 2017-02-28
00003137 00003137 1 2018-08-31
00269705 00269705 2 9999-12-31
00269705 05458040 1 9999-12-31
I can get this to work on all data except data that has identical TopDates
sql-server sql-server-2012 t-sql
asked Sep 4 at 13:48
jdids
877
As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.
CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)
INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')
;WITH
Parent AS
( SELECT parent
, child
, 1 as sort
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.sort + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, sort
FROM Parent
ORDER BY parent, sort desc
OPTION (maxrecursion 0);
DROP TABLE #Recursion
If I leave the where clause as such, it works
WHERE s.TopDate < d.TopDate
As soon as I change it to this, it bombs and will not complete
WHERE s.TopDate <= d.TopDate
I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?
Here is some example data. Note how the date field can be identical
parent child sort TopDate
00003137 04536347 2 2017-02-28
00003137 00003137 1 2018-08-31
00269705 00269705 2 9999-12-31
00269705 05458040 1 9999-12-31
I can get this to work on all data except data that has identical TopDates
sql-server sql-server-2012 t-sql
asked Sep 4 at 13:48
jdids
877
edited Sep 4 at 14:21
asked Sep 4 at 13:48
jdids
877
asked Sep 4 at 13:48
jdids
877
asked Sep 4 at 13:48
jdids
877
877
add a comment |Â
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
5
down vote
accepted
If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop. Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.
EG, if you change < to <= the query will enter an infinite loop. Note this is why (maxrecursion 0) is perhaps not a good idea.
drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());
;WITH
Parent AS
( SELECT parent
, child
, 1 as [order]
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.[order] + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child = d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, [order]
FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);
Also
ON s.child = d.parent
should probably be
ON d.child = s.parent
As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
5
down vote
accepted
If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop. Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.
EG, if you change < to <= the query will enter an infinite loop. Note this is why (maxrecursion 0) is perhaps not a good idea.
drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());
;WITH
Parent AS
( SELECT parent
, child
, 1 as [order]
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.[order] + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child = d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, [order]
FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);
Also
ON s.child = d.parent
should probably be
ON d.child = s.parent
As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
add a comment |Â
up vote
5
down vote
accepted
If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop. Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.
EG, if you change < to <= the query will enter an infinite loop. Note this is why (maxrecursion 0) is perhaps not a good idea.
drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());
;WITH
Parent AS
( SELECT parent
, child
, 1 as [order]
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.[order] + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child = d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, [order]
FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);
Also
ON s.child = d.parent
should probably be
ON d.child = s.parent
As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
add a comment |Â
up vote
5
down vote
accepted
up vote
5
down vote
accepted
If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop. Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.
EG, if you change < to <= the query will enter an infinite loop. Note this is why (maxrecursion 0) is perhaps not a good idea.
drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());
;WITH
Parent AS
( SELECT parent
, child
, 1 as [order]
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.[order] + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child = d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, [order]
FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);
Also
ON s.child = d.parent
should probably be
ON d.child = s.parent
As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop. Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.
EG, if you change < to <= the query will enter an infinite loop. Note this is why (maxrecursion 0) is perhaps not a good idea.
drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());
;WITH
Parent AS
( SELECT parent
, child
, 1 as [order]
, TopDate
FROM #Recursion
WHERE parent = child
UNION ALL
SELECT s.parent
, d.child
, d.[order] + 1
, s.TopDate
FROM Parent as d
JOIN #Recursion s
ON s.child = d.parent
WHERE s.TopDate < d.TopDate
)
SELECT parent
, child
, [order]
FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);
Also
ON s.child = d.parent
should probably be
ON d.child = s.parent
As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
answered Sep 4 at 14:09
David Browne - Microsoft
8,195622
8,195622
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
add a comment |Â
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
Thank you for the detailed explanation. It now makes sense
– jdids
Sep 4 at 15:08
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216672%2fsimple-recursive-cte-bombs-out-when-changing-operator-in-the-where-clause%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
