CTE, why is an empty table returned?-CodePudding

I'm using a Postgresql database and I'm trying to get a tree using a CTE:

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail

Doesn't output any errors. There is nothing in the output, where did I go wrong?

Database

CREATE TABLE org(
  name TEXT PRIMARY KEY,
  boss TEXT REFERENCES org
);

INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');

My request:

WITH RECURSIVE under_alice AS (select name, boss, 0 AS level FROM org 
     where boss = null
    UNION ALL
    SELECT org.name, org.boss, under_alice.level 1
      FROM org JOIN under_alice ON org.boss=under_alice.name
  )
SELECT * FROM under_alice;

CodePudding user response：

WITH RECURSIVE under_alice AS
(
  select name, boss, 0 AS level FROM org 
     where boss IS NULL 
 UNION ALL
  SELECT org.name, org.boss, under_alice.level 1
  FROM org JOIN under_alice ON org.boss=under_alice.name
)
SELECT * FROM under_alice

Please take a look on where boss IS NULL. Here is the issue in your query