Incorrect result of recursive CTE query when recursive member's SELECT list contains expression using self-referenced fields
----------------------------------------------------------------------------------------------------------------------------
Key: CORE-1373
URL: http://tracker.firebirdsql.org/browse/CORE-1373
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.1 Alpha 1
Reporter: Vlad Horsun
Assignee: Vlad Horsun
Fix For: 2.1 Beta 2
RECREATE TABLE Phases
(Id INT NOT NULL PRIMARY KEY, ParentPhaseId INT);
CREATE GENERATOR GenPhases;
COMMIT;
INSERT INTO Phases VALUES(491, NULL);
INSERT INTO Phases VALUES(494, 491);
INSERT INTO Phases VALUES(495, 491);
INSERT INTO Phases VALUES(497, 494);
INSERT INTO Phases VALUES(498, NULL);
-- below i want to renumber Phases table and keep parent-child relation
SET GENERATOR GenPhases to 0;
COMMIT;
WITH RECURSIVE
Tree (OldPhaseId, OldParentPhaseId, NewPhaseId, NewParentPhaseId) AS
(
SELECT P.Id, P.ParentPhaseId, GEN_ID(GenPhases, 1), CAST(NULL AS INTEGER)
FROM Phases P
WHERE P.ParentPhaseId IS NULL
UNION ALL
SELECT P.Id, P.ParentPhaseId, GEN_ID(GenPhases, 1), T.NewPhaseId
FROM Phases P, Tree T
WHERE P.ParentPhaseId = T.OldPhaseId
)
SELECT * FROM Tree;
-- results is
OLDPHASEID OLDPARENTPHASEID NEWPHASEID NEWPARENTPHASEID
491 <null> 1 <null>
494 491 2 2
497 494 3 3
495 491 4 4
498 <null> 5 <null>
-- while correct results must be
OLDPHASEID OLDPARENTPHASEID NEWPHASEID NEWPARENTPHASEID
491 <null> 1 <null>
494 491 2 1
497 494 3 2
495 491 4 1
498 <null> 5 <null>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
