Hi, please look at the code below. If I run this I get the following error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion
I do understand what the error is and in this case it is due to the value of (0,0) both in the ID as well as in the ParentID column. But my question is should not Cross Apply be passed the values of the Outer table which in this case is filtering & values remain are only 1,2,3. Why is Cross Apply being passed a 0 and the error is due to this 0 value record, until I am missing something.
--1. CREATE A TABLE WITH PARENT-CHILD RELATION
CREATE TABLE T1(ID INT, PARENTID INT NULL)
INSERT INTO T1 VALUES(1,0),(2,0),(3,0),(0,0),(4,1),(5,2)
--2.CREATE A RECURSIVE FUNCTION BASED ON THE ABOVE TABLE
CREATE FUNCTION fn_Test(@ID Int)
RETURNS @Table table( ID int )
AS
BEGIN
;WITH CTE (ID) AS
(
SELECT ID
FROM T1 With (Nolock)
WHERE ID = @ID
UNION ALL
SELECT cat.ID
FROM T1 AS CAT With (NolOck) INNER JOIN CTE C ON cat.PARENTID = C.ID
)
INSERT INTO @Table(ID)
SELECT * FROM CTE
RETURN
END
GO
--3.
BEGIN
DECLARE @TBL1 TABLE(ID INT)
INSERT INTO @TBL1 VALUES(1),(2),(3)
SELECT CG.ID
FROM @TBL1 TMP
INNER JOIN T1 CG WITH(NOLOCK) ON TMP.ID = CG.ID
CROSS APPLY dbo.fn_Test(CG.ID) AS FN
Alter your second part of the CTE : to exclude the 0
WHERE cat.ID <> 0
ALTER FUNCTION dbo.fn_Test(@ID Int)
RETURNS @Table table( ID int )
AS
BEGIN
;WITH CTE (ID) AS
(
SELECT ID
FROM T1 With (Nolock)
WHERE ID = @ID
UNION ALL
SELECT cat.ID
FROM T1 AS CAT With (NolOck)
INNER JOIN CTE C
ON cat.PARENTID = C.ID
WHERE
cat.ID <> 0
)
INSERT INTO @Table(ID)
SELECT * FROM CTE
RETURN
END
GO
DECLARE @TBL1 TABLE(ID INT)
INSERT INTO @TBL1 VALUES
(1),
(2),
(3)
SELECT *
FROM
@TBL1 AS TMP
INNER JOIN
(SELECT * FROM dbo.T11 AS T WHERE T.ID >=1 AND T.ID<=3) AS T
ON TMP.ID= T.ID
CROSS APPLY dbo.FN_Test(T.id) AS FM
The key here is : (SELECT * FROM dbo.T11 AS T WHERE T.ID >=1 AND T.ID<=3) AS T
And why it is behaving like this:
the way the engine works in steps:
step 1 = FROM / JOIN phase
step 2 = ON phase
step 3= WHERE phase
and so one
So, your query was stuck on step1/phase 1 = FROM / JOIN - generating all the records.(cartesian)
and this is why I used :
(SELECT * FROM dbo.T11 AS T WHERE T.ID >=1 AND T.ID<=3) AS T
I was under the impression that Apply would simply pick up the previous Join's final resultset and you have proven that it does not work that way. So this means whatever table is used before the Apply operator, All the values will be passed.
You can take a look at the actual execution plan.
On my station, the engine is , first, joining T1 with fn_Test (for all records in the initial phase).
Then is doing the join with @tbl1 as TMP.
This line CROSS APPLY dbo.fn_Test(CG.ID) AS FN could be changed
to : CROSS APPLY dbo.fn_Test(TMP.ID) AS FN.
In this way , the join is between @tbl1 as TMP and fn_test, and it will work.
Yes even I got that solution. The whole point of this topic was to understand why Apply operator was being passed the 0th row even though the previous join filters only on 1,2,3. There is another interesting thing that I came across, maybe you can shed some light on this...
The scenario I created is exactly similar to where I am facing this issue and the data has this kind of 0th record. As we know now that we can filter out the 0th record in multiple ways or simply pass TMP.ID and our issue will be resolved.
Also, the issue has been occurring on production but on on dev., which was strange. So I did few things to check if both the versions\tables were same or not:
SQL 2014 in both with compatiblity level of 100 (=2008)
In Prod., the table in question (which in our case is T1) was reindexed an hour before the actual call of the procedure and the procedure failed with the same recursion error. This reindexing has been happening for some time now and the procedure also has been running smoothly till now. Only once before it failed with the same recursion error but the subsequent runs were successful.
On Dev., the reindexing job never happens and this procedure does not fail (it has failed only a few times in the past months with the same recursion error but has been continuing fine on its own in subsequent runs). Yesterday I did one more thing, I added this query hint OPTION (QUERYTRACEON 2312) so that it uses the new CE of sql 2014 and voila, the procedure failed on Dev with the same recursion error. When I switch back to old CE prior to Sql 2014 using OPTION (QUERYTRACEON 9481) which is also the default here, it works fine. So I am stumped now.
Another thing, when I replace the table in production with a newly created copy of the table (I did a Select * INTO) it starts working in production. This may mean the PK which was reindexed and not copied over to the new table (due to INTO) did not hinder in any way with the query in question (which is exactly similar to the sample Cross Apply query).
So does this also mean that the reindexed PK has a role to play in the failure?
Please let me know your thoughts on the CE thing on Dev. and the reason the copy of table might be working in production.
Rebuild the index will update statistics of it. and if there are changes this will trigger a recompile for it and may get a different plan.
Also changes in objects also trigger a recompile -> maybe a new plan.
You have to look at execution plan.
How big is your T1 ?
Maybe is less expensive to do a full scan of T1 , instead of a seek.
Hi, rebuild has made the query work now.
So this means it was picking up a bad plan all this while. But then how did the query fail following the index rebuild earlier, the same flow happened, index rebuilt and procedure executed and failed.
How did it start working after another rebuild?
This is a weekly maintenance activity, how should this be controlled now so that it does not fail again. We can't do a manual rebuild every time or wait for a week for the rebuild to run.
I missed taking the estimated plan earlier. So now will have to wait up until the next rebuild it might fail again if I don't put any query hints, will make sure to get the estimated plan this time. I will check if I can do a manual rebuild.
