MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE task_categories ( 
id INTEGER ,
parent_id INTEGER ,
name VARCHAR(20) ,
is_public INTEGER );

INSERT INTO  task_categories  VALUES
(1, NULL,'category 1', 1),
(2, 1, 'category 2', 0),
(3, 2, 'category 3', 0),
(4, 3, 'category 4', 0),
(5, 4, 'category 5', 1);

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM task_categories;


id
parent_id
name
is_public


1
null
category 1
1

2
1
category 2
0

3
2
category 3
0

4
3
category 4
0

5
4
category 5
1


WITH RECURSIVE recursiveTree AS (
          SELECT task_categories.id, task_categories.name, task_categories.is_public
          FROM task_categories WHERE task_categories.is_public = 0

          UNION ALL
          SELECT tc.id, tc.name, tc.is_public
          FROM recursiveTree
          INNER JOIN task_categories tc ON recursiveTree.id = tc.parent_id
          WHERE tc.is_public = 0
        )
        SELECT DISTINCT * FROM recursiveTree

-- in shouldn't return any row but returns 3


id
name
is_public


2
category 2
0

3
category 3
0

4
category 4
0

id	parent_id	name	is_public
1	null	category 1	1
2	1	category 2	0
3	2	category 3	0
4	3	category 4	0
5	4	category 5	1