SQL Server Error Messages - Msg 253

Error Message

Server: Msg 253, Level 16, State 1, Line 1
Recursive member of a common table expression '<Common Table Expression>' has multiple recursive references.

Causes

A common table expression (CTE), introduced in SQL Server 2005, can be thought of as a temporary result set that is defined with the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE that references itself is referred to as a recursive common table expression.

To illustrate on how this error message is generated, given the following table structures:

CREATE TABLE [dbo].[User] (
    [UserID]            INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [Email]             VARCHAR(100),
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50)
)
GO

CREATE TABLE [dbo].[Friend] (
    [FriendID]          INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [User1ID]           INT NOT NULL,
    [User2ID]           INT NOT NULL,
    [IsAccepted]        BIT DEFAULT(0)
)
GO

CREATE UNIQUE INDEX [IX_Email] ON [dbo].[User] ( [Email] )
GO

ALTER TABLE [dbo].[Friend]
ADD CONSTRAINT [FK_User1ID] FOREIGN KEY ( [User1ID] )
REFERENCES [dbo].[User] ( [UserID] )
GO

ALTER TABLE [dbo].[Friend]
ADD CONSTRAINT [FK_User2ID] FOREIGN KEY ( [User2ID] )
REFERENCES [dbo].[User] ( [UserID] )
GO

INSERT INTO [dbo].[User] ( [Email], [FirstName], [LastName] )
VALUES ( 'mickeymouse@disney.com', 'Mickey', 'Mouse' ),
       ( 'minniemouse@disney.com', 'Minnie', 'Mouse' ),
       ( 'donaldduck@disney.com', 'Donald', 'Duck' ),
       ( 'daisyduck@disney.com', 'Daisy', 'Duck' )
GO

INSERT INTO [dbo].[Friend] ( [User1ID], [User2ID], [IsAccepted] )
VALUES ( 1, 2, 1 ),
       ( 1, 3, 1 ),
       ( 1, 4, 1 ),
       ( 2, 1, 1 ),
       ( 2, 3, 1 ),
       ( 2, 4, 1 ),
       ( 3, 1, 1 ),
       ( 3, 2, 1 ),
       ( 3, 4, 1 ),
       ( 4, 1, 1 ),
       ( 4, 2, 1 ),
       ( 4, 3, 1 )
GO

The following script tries to list the friends of a certain user as well as all friends of the user’s friends, and so on:

WITH [FacebookFriends]
AS (
    SELECT A.[Email] AS [User], C.[Email] AS [Friend],
           B.[User1ID], B.[User2ID]
    FROM [Facebook].[dbo].[User] A INNER JOIN [Facebook].[dbo].[Friend] B
                                           ON A.[UserID]   = B.[User1ID] AND
                                              B.[IsAccepted] = 1
                                   INNER JOIN [Facebook].[dbo].[User] C
                                           ON B.[User2ID] = C.[UserID]
    WHERE A.[Email] = 'mickeymouse@disney.com'
    UNION ALL
    SELECT A.[Friend] AS [User], C.[Email] AS [Friend],
           B.[User1ID], B.[User2ID]
    FROM [FacebookFriends] A INNER JOIN [Facebook].[dbo].[Friend] B
                                     ON A.[User2ID]  = B.[User1ID] AND
                                        B.[IsAccepted] = 1
                             INNER JOIN [Facebook].[dbo].[User] C
                                     ON B.[User2ID] = C.[UserID]
)
SELECT * FROM [FacebookFriends]
GO

Executing this script will generate the following error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The previous error occurred because of an infinite loop due to the fact that the user is also a friend of the user’s friend, creating a cycle. To better illustrate, in the case of Mickey Mouse, his friends are Minnie Mouse, Donald Duck and Daisy Duck. In turn, the friends of Minnie Mouse are Mickey Mouse, Donald Duck and Daisy Duck. Since Mickey Mouse is a friend of Minnie Mouse, all the friends of Mickey Mouse will be returned by the query again creating a cycle.

To avoid having an infinite loop, the script should make sure that the result set will not output the same user/friend record more than once. A condition is added to the previous script where it checks for the existence of the user/friend record before being added to the result output, as can be seen in the following script:

WITH [FacebookFriends]
AS (
    SELECT A.[Email] AS [User], C.[Email] AS [Friend],
           B.[User1ID], B.[User2ID]
    FROM [Facebook].[dbo].[User] A INNER JOIN [Facebook].[dbo].[Friend] B
                                           ON A.[UserID]   = B.[User1ID] AND
                                              B.[IsAccepted] = 1
                                   INNER JOIN [Facebook].[dbo].[User] C
                                           ON B.[User2ID] = C.[UserID]
    WHERE A.[Email] = 'mickeymouse@disney.com'
    UNION ALL
    SELECT A.[Friend] AS [User], C.[Email] AS [Friend],
           B.[User1ID], B.[User2ID]
    FROM [FacebookFriends] A INNER JOIN [Facebook].[dbo].[Friend] B
                                     ON A.[User2ID]  = B.[User1ID] AND
                                        B.[IsAccepted] = 1
                             INNER JOIN [Facebook].[dbo].[User] C
                                     ON B.[User2ID] = C.[UserID]
    WHERE NOT EXISTS (SELECT 'X' FROM [FacebookFriends] D
                      WHERE D.[User1ID] = B.[User1ID] AND
                            D.[User2ID] = B.[User2ID])
)
SELECT * FROM [FacebookFriends]
GO

Unfortunately, executing this script generates the following error message:

Msg 253, Level 16, State 1, Line 3
Recursive member of a common table expression 'FacebookFriends' has multiple recursive references.

Alternatively, a possible way of overcoming the issue of the infinite loop is to make use of a UNION operation instead of a UNION ALL operation. The UNION/UNION ALL operation combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. If ALL is not specified with the UNION, the UNION operation removes duplicate rows. Here’s what the script will look like changing the UNION ALL to a UNION operation:

WITH [FacebookFriends]
AS (
    SELECT A.[Email] AS [User], C.[Email] AS [Friend],
           B.[User1ID], B.[User2ID]
    FROM [Facebook].[dbo].[User] A INNER JOIN [Facebook].[dbo].[Friend] B
                                           ON A.[UserID]   = B.[User1ID] AND
                                              B.[IsAccepted] = 1
                                   INNER JOIN [Facebook].[dbo].[User] C
                                           ON B.[User2ID] = C.[UserID]
    WHERE A.[Email] = 'mickeymouse@disney.com'
    UNION 
    SELECT A.[Friend] AS [User], C.[Email] AS [Friend],
           B.[User1ID], B.[User2ID]
    FROM [FacebookFriends] A INNER JOIN [Facebook].[dbo].[Friend] B
                                     ON A.[User2ID]  = B.[User1ID] AND
                                        B.[IsAccepted] = 1
                             INNER JOIN [Facebook].[dbo].[User] C
                                     ON B.[User2ID] = C.[UserID]
)
SELECT * FROM [FacebookFriends]
GO

Unfortunately, changing the UNION ALL operator to a simple UNION operator generates a different error:

Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'FacebookFriends' does not contain a top-level UNION ALL operator.

Solution / Work Around

Instead of using a recursive common table expression, one way of working around this error (Msg 254 – Recursive member of a common table expression has multiple recursive references) is with the use of a WHILE loop together with a temporary table that will hold the resulting output.

Here’s a script that will return the list of friends of a user as well as the friends of the user’s friends, and so on, up to 10 levels of friends deep. This script avoids both issues encountered earlier with the use of a recursive common table expression, namely the error with the maximum recursion (Error Msg 530) as well as the error with the multiple recursive references (Error Msg 253).

DECLARE @FacebookFriends TABLE (
    [User]              VARCHAR(100),
    [Friend]            VARCHAR(100),
    [User1ID]           INT,
    [User2ID]           INT, 
    [Level]             INT
)

DECLARE @Depth          INT
DECLARE @MaxDepth       INT
DECLARE @Email          VARCHAR(50)

SET @Depth = 1
SET @MaxDepth = 10
SET @Email = 'mickeymouse@disney.com'
WHILE @Depth <= @MaxDepth
BEGIN
    IF NOT EXISTS (SELECT 'X' FROM @FacebookFriends
                   WHERE [User] = @Email)
    BEGIN
        INSERT INTO @FacebookFriends ( [User], [Friend], [User1ID], [User2ID], [Level] )
        SELECT A.[Email], C.[Email], B.[User1ID], B.[User2ID], @Depth
        FROM [Facebook].[dbo].[User] A INNER JOIN [Facebook].[dbo].[Friend] B
                                               ON A.[Email] = @Email AND
                                                  A.[UserID] = B.[User1ID]
                                       INNER JOIN [Facebook].[dbo].[User] C
                                               ON B.[User2ID] = C.[UserID]
    END
    ELSE
    BEGIN
        INSERT INTO @FacebookFriends ( [User], [Friend], [User1ID], [User2ID], [Level] )
        SELECT A.[Friend], C.[Email], B.[User1ID], B.[User2ID], @Depth
        FROM @FacebookFriends A INNER JOIN [Facebook].[dbo].[Friend] B
                                        ON A.[User2ID] = B.User1ID
                                INNER JOIN [Facebook].[dbo].[User] C
                                        ON B.[User2ID] = C.[UserID]
        WHERE NOT EXISTS (SELECT 'X' FROM @FacebookFriends D
                          WHERE D.[User1ID] = B.[User1ID] AND
                                D.[User2ID] = B.[User2ID])
    END

    SET @Depth = @Depth + 1
END

SELECT * FROM @FacebookFriends
ORDER BY [User]
GO