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

[firebird-automations]

Submitted by: @hvlad

Is related to QA219

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 http://P.Id, P.ParentPhaseId, GEN_ID(GenPhases, 1), CAST(NULL AS INTEGER)
FROM Phases P
WHERE P.ParentPhaseId IS NULL

UNION ALL

SELECT <http://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>

Commits: 671e59e

[firebird-automations]

Commented by: @hvlad

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

[firebird-automations]

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 12640 ] => Firebird [ 14230 ]

[firebird-automations]

Modified by: @pmakowski

Link: This issue block progress on QA219 [ QA219 ]

[firebird-automations]

Commented by: @pmakowski

Q/A test ok

[firebird-automations]

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA219 [ QA219 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue block progress on QA219 [ QA219 ] =>

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully