[Firebird-devel] [FB-Tracker] Created: (CORE-1373) Incorrect result of recursive CTE query when recursive member's SELECT list contains expression using self-referenced fields

[Vlad H. (JIRA) <tr...@fi...>]

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