Prev: SP parameter optional
Next: DBTYP.NET Studio 2010 - Database Comparison Suite Released
From: tshad on 10 Mar 2010 13:12
I have a situation where you can have an infinite recusion.

There is an issue where editing a page could cause infinite recursion. This
would happen if a distribution folder were put into itself. It would also
happen if you put folder "A" into folder "B" where folder "B" is in any of
the folders contained in any of the folders found in folder "A". A little
convoluted sentence but that is the problem.

A is in B and B is in C

If you put A into C then C will eventually point back to itself and you the
recursion will continue on

YOu have to have a way to say that you have already seen this folder before
so ignore it.

Here is an example:

DECLARE @MultipleAnchors TABLE(
ParentID int NULL,
ID int NOT NULL );

INSERT INTO @MultipleAnchors
VALUES
(NULL, 1),
(NULL, 2),
(1, 3),
(3, 1), // Causes the infinite recursion
(1, 4),
(4, 5),
(2, 6),
(6, 7),
(7, 8);

WITH MultipleAnchorRowsCTE
AS
(
SELECT ParentID, ID, Level = 0
FROM @MultipleAnchors
WHERE ParentID IS NULL
UNION ALL
SELECT children.ParentID, children.ID, Level + 1
FROM @MultipleAnchors children
JOIN MultipleAnchorRowsCTE macte ON macte.ID = children.ParentID
)
SELECT ParentID, ID, Level
FROM MultipleAnchorRowsCTE;

Is there a way to handle this in the CTE?

Thanks,

Tom




From: tshad on 10 Mar 2010 13:23
"tshad" <t(a)dslextreme.com> wrote in message
news:%23O8X70HwKHA.4752(a)TK2MSFTNGP04.phx.gbl...
>I have a situation where you can have an infinite recusion.
>
> There is an issue where editing a page could cause infinite recursion.
> This
> would happen if a distribution folder were put into itself. It would also
> happen if you put folder "A" into folder "B" where folder "B" is in any of
> the folders contained in any of the folders found in folder "A". A
> little
> convoluted sentence but that is the problem.
>
> A is in B and B is in C
>
> If you put A into C then C will eventually point back to itself and you
> the
> recursion will continue on
>
> YOu have to have a way to say that you have already seen this folder
> before
> so ignore it.
>
> Here is an example:
>
> DECLARE @MultipleAnchors TABLE(
> ParentID int NULL,
> ID int NOT NULL );
>
> INSERT INTO @MultipleAnchors
> VALUES
> (NULL, 1),
> (NULL, 2),
> (1, 3),
> (3, 1), // Causes the infinite recursion
> (1, 4),
> (4, 5),
> (2, 6),
> (6, 7),
> (7, 8);
>
> WITH MultipleAnchorRowsCTE
> AS
> (
> SELECT ParentID, ID, Level = 0
> FROM @MultipleAnchors
> WHERE ParentID IS NULL
> UNION ALL
> SELECT children.ParentID, children.ID, Level + 1
> FROM @MultipleAnchors children
> JOIN MultipleAnchorRowsCTE macte ON macte.ID = children.ParentID
> )
> SELECT ParentID, ID, Level
> FROM MultipleAnchorRowsCTE;
>

Trying to get something where during the recursion it looks to see if this
parent has already been handled.

Something like:

WITH MultipleAnchorRowsCTE
AS
(
SELECT ParentID, ID, Level = 0
FROM @MultipleAnchors
WHERE ParentID IS NULL
UNION ALL
SELECT children.ParentID, children.ID, Level + 1
FROM @MultipleAnchors children
JOIN MultipleAnchorRowsCTE macte ON macte.ID = children.ParentID
WHERE children.ParentID not IN (MultipleAnchorRowsCTE.ParentID)
)
SELECT ParentID, ID, Level
FROM MultipleAnchorRowsCTE;

I know this is not the correct syntax but just trying to get the idea
across.

Thanks,

Tom


From: Plamen Ratchev on 10 Mar 2010 13:55
You can add path for the IDs and based on that define expression to detect cycles. Here is how it may look (the cycle
column expression is used to identify cycles and stop from exploring any paths with cycles):

WITH MultipleAnchorRowsCTE
AS
(
SELECT ParentID, ID, 0 AS level, 'N' AS cycle,
CAST('.' + CAST(id AS VARCHAR(8)) + '.' AS VARCHAR(MAX)) AS cte_path
FROM @MultipleAnchors
WHERE ParentID IS NULL
UNION ALL
SELECT children.ParentID, children.ID, level + 1,
CASE WHEN macte.cte_path LIKE '%.' + CAST(children.id AS VARCHAR(8)) + '.%'
THEN 'Y'
ELSE 'N'
END,
CAST(macte.cte_path + CAST(children.id AS VARCHAR(8)) + '.' AS VARCHAR(MAX))
FROM @MultipleAnchors AS children
JOIN MultipleAnchorRowsCTE AS macte
ON macte.ID = children.ParentID
AND macte.cycle = 'N'
)
SELECT ParentID, ID, Level
FROM MultipleAnchorRowsCTE;

--
Plamen Ratchev
http://www.SQLStudio.com
From: alen teplitsky on 10 Mar 2010 15:32
On Mar 10, 1:55 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> You can add path for the IDs and based on that define expression to detect cycles. Here is how it may look (the cycle
> column expression is used to identify cycles and stop from exploring any paths with cycles):
>
> WITH MultipleAnchorRowsCTE
> AS
> (
> SELECT ParentID, ID, 0 AS level, 'N' AS cycle,
>         CAST('.' + CAST(id AS VARCHAR(8)) + '.' AS VARCHAR(MAX)) AS cte_path
> FROM @MultipleAnchors
> WHERE ParentID IS NULL
> UNION ALL
> SELECT children.ParentID, children.ID, level + 1,
>         CASE WHEN macte.cte_path LIKE '%.' + CAST(children.id AS VARCHAR(8)) + '.%'
>              THEN 'Y'
>              ELSE 'N'
>         END,
>         CAST(macte.cte_path + CAST(children.id AS VARCHAR(8)) + '..' AS VARCHAR(MAX))
> FROM @MultipleAnchors AS children
> JOIN MultipleAnchorRowsCTE AS macte
>    ON macte.ID = children.ParentID
>   AND macte.cycle = 'N'
> )
> SELECT ParentID, ID, Level
> FROM MultipleAnchorRowsCTE;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

i think i did something like this a year or so ago. made it faster
than the sp we're using in production for the last 10 years. you may
have to remind me to find it
From: tshad on 10 Mar 2010 17:09

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:E5qdnY9h_uqfdgrWnZ2dnUVZ_jSdnZ2d(a)speakeasy.net...
> You can add path for the IDs and based on that define expression to detect
> cycles. Here is how it may look (the cycle column expression is used to
> identify cycles and stop from exploring any paths with cycles):
>
> WITH MultipleAnchorRowsCTE
> AS
> (
> SELECT ParentID, ID, 0 AS level, 'N' AS cycle,
> CAST('.' + CAST(id AS VARCHAR(8)) + '.' AS VARCHAR(MAX)) AS
> cte_path
> FROM @MultipleAnchors
> WHERE ParentID IS NULL
> UNION ALL
> SELECT children.ParentID, children.ID, level + 1,
> CASE WHEN macte.cte_path LIKE '%.' + CAST(children.id AS
> VARCHAR(8)) + '.%'
> THEN 'Y'
> ELSE 'N'
> END,
> CAST(macte.cte_path + CAST(children.id AS VARCHAR(8)) + '.' AS
> VARCHAR(MAX))
> FROM @MultipleAnchors AS children
> JOIN MultipleAnchorRowsCTE AS macte
> ON macte.ID = children.ParentID
> AND macte.cycle = 'N'
> )
> SELECT ParentID, ID, Level
> FROM MultipleAnchorRowsCTE;
>

That worked really well.

And what you end up with is:

NULL 1 0 N .1.
NULL 2 0 N .2.
2 6 1 N .2.6.
6 7 2 N .2.6.7.
7 8 3 N .2.6.7.8.
1 3 1 N .1.3.
1 4 1 N .1.4.
4 5 2 N .1.4.5.
3 1 2 Y .1.3.1.

Thanks,

Tom
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 |  Next  |  Last
Pages: 1 2
Prev: SP parameter optional
Next: DBTYP.NET Studio 2010 - Database Comparison Suite Released