recursive CTE for family tree does not recurse

Question

I am trying with my limited SQL skills to construct a recursive CTE query. I have two tables that model a family tree:

CREATE TABLE "Relation" (
  "id" integer not null primary key autoincrement,
  "person1Id" integer,
  "person2Id" integer,
  foreign key("person1Id") references "Person"("id"),
  foreign key("person2Id") references "Person"("id"));

CREATE TABLE "Person" (
  "id" integer not null primary key autoincrement,
  "name" varchar(255),
  "gender" varchar(255),
  "bornToId" integer,
  foreign key("bornToId") references "Relation"("id"));

I have tried to make an sqlfiddle, but I keep getting 'Oops, something went wrong', so I'll paste the inserts here:

  INSERT INTO `Person` (id,name,gender,bornToId) VALUES
 (1,'William I','M',NULL),
 (2,'Matilde of Flanders','F',NULL),
 (3,'William Rufus','M',1),
 (4,'Henry I','M',1),
 (5,'Matilde of Scotland','F',NULL),
 (6,'William Adelin','M',2),
 (7,'Matilde Holy Roman Empress','F',2),
 (8,'Geoffrey of Anjou','M',NULL),
 (9,'Henry II','M',3),
 (10,'Eleanor of Aquitane','F',NULL),
 (11,'Richard I','M',4),
 (12,'John','M',4),
 (13,'Robert Curthose','M',1),
 (14,'Adeliza','F',NULL),
 (15,'Adela of Normandy','F',1),
 (16,'Stephen II Henry','M',NULL),
 (17,'Stephen of Blois','M',6);

INSERT INTO `Relation` (id,person1Id,person2Id) VALUES (1,1,2),
 (2,4,5),
 (3,7,8),
 (4,9,10),
 (5,4,14),
 (6,15,16);

I started by breaking down the problem into sub-queries. These all work as I expect them to (sqlite 3.14.0, DB Browser, on a Mac):

find a person with 0, 1 or many relations (partners)

select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName
from Person pers
join Relation
  on pers.id = Relation.person1Id
  or pers.id  = Relation.person2Id
join Person partner
  on (partner.id = Relation.person1Id and
     partner.id <> pers.id)
  or (partner.id = Relation.person2Id and
     partner.id <> pers.id)
where pers.id = 4

find a person's parents (if any)

select 
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId
from Person pers
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id
where pers.id = 4

Combining the above two also works, but I'll omit that query here.

find the children that belong to a relation

select pers.id, pers.name
from Person pers
where pers.bornToId = 1

Now, putting all this together in a recursive cte query is causing me problems. My attempts so far result in this monster of a query, but it just returns one record ("1" "William I" "2" "Matilde of Flanders" "NULL" "NULL" "NULL" "NULL"), so apparently it doesn't enter into the recursion.

WITH FamilyTree (
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name,
  parentsId
)
AS (
select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId
from Person pers
left join Relation
    on pers.id = Relation.person1Id
    or pers.id  = Relation.person2Id
left join Person partner
    on (partner.id = Relation.person1Id and
       partner.id <> pers.id)
    or (partner.id = Relation.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id
where pers.id = 1
union all


select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId
from Person pers
left join Relation
    on pers.id = Relation.person1Id
    or pers.id  = Relation.person2Id
left join Person partner
    on (partner.id = Relation.person1Id and
       partner.id <> pers.id)
    or (partner.id = Relation.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id

JOIN FamilyTree AS fam
     ON pers.bornToId = fam.parentsId
)


select
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name
from FamilyTree

I spent a lot of hours on this, so I think it's time to ask experts.

In case you're wondering, the end goal is to have nested javascript objects, like:

{
  ...
  relations: [{
    ...
    children: [{

so I can use it in a D3 tree.

Answer 1

Cool, I think I got it. The clue was in that one record it did return, which I overlooked. That record does not have a parentId, because it is a root person. But in the query I was matching the 'bornToId' of the next Person to this parentId with value NULL.

I should have matched against the id of the Relation of the root person, which I had not defined in the select.

In this revised cte query, it does enter into a recursion and the results look good. I still have to do some checks, but i'll post this anyway to save others the trouble of finding this particular bug.

WITH FamilyTree (
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name,
  parentsId,
  relationId
)
AS (
select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId,
  rel.id as relationId
from Person pers
left join Relation rel
    on pers.id = rel.person1Id
    or pers.id  = rel.person2Id
left join Person partner
    on (partner.id = rel.person1Id and
       partner.id <> pers.id)
    or (partner.id = rel.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id
where pers.id = 1
union all


select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId,
  rel.id as relationId
from Person pers
left join Relation rel
    on pers.id = rel.person1Id
    or pers.id  = rel.person2Id
left join Person partner
    on (partner.id = rel.person1Id and
       partner.id <> pers.id)
    or (partner.id = rel.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id

JOIN FamilyTree AS fam
     ON pers.bornToId = fam.relationId
)


select
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name
from FamilyTree