Issue Details (XML | Word | Printable)

Key: CORE-1373
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Vlad Khorsun
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Incorrect result of recursive CTE query when recursive member's SELECT list contains expression using self-referenced fields

Created: 21/Jul/07 05:20 PM   Updated: 26/Jan/09 08:24 AM
Component/s: None
Affects Version/s: 2.1 Alpha 1
Fix Version/s: 2.1 Beta 2

Time Tracking:
Not Specified

Issue Links:
Relate
 


 Description  « Hide
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>


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 21/Jul/07 05:35 PM
Solution is to create separate contexts (records) for current and next levels of recursive select.
This leads, unfortunately, to change in blr_recursive verb. Now it contains additional new parameter :

blr_recursive, <main context number>, <secondary context number>, <number of union's members>

Therefore any stored objects (stored procedures, triggers, views, etc) with recursive CTE's must be recreated
else blr parsing error will be raised

Philippe Makowski added a comment - 08/Dec/08 09:30 AM
Q/A test ok