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 mytable ( 
id INTEGER ,
name VARCHAR(20) ,
parent_id INTEGER ,
is_public INTEGER );

INSERT INTO  mytable  VALUES
('1','Task 1',null,'1'),
('2','Task 1.1','1','0'),
('3','Task 1.1.1','2','1'),
('4','Task 1.2','1','1'),
('5','Task 1.2.1','4','1');

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM mytable;


id
name
parent_id
is_public


1
Task 1
null
1

2
Task 1.1
1
0

3
Task 1.1.1
2
1

4
Task 1.2
1
1

5
Task 1.2.1
4
1


-- not accounting `is_public`
WITH RECURSIVE cte AS (
  SELECT id,name,parent_id,is_public
  FROM mytable where parent_id is null

  union all

  SELECT mytable.id, mytable.name, mytable.parent_id, mytable.is_public
  FROM cte
  INNER JOIN mytable ON cte.id = mytable.parent_id 
)
SELECT * FROM cte;


id
name
parent_id
is_public


1
Task 1
null
1

2
Task 1.1
1
0

4
Task 1.2
1
1

3
Task 1.1.1
2
1

5
Task 1.2.1
4
1


-- including `Task 1.1`
WITH RECURSIVE cte AS (
  SELECT id,name,parent_id,is_public
  FROM mytable where parent_id is null

  union all

  SELECT mytable.id, mytable.name, mytable.parent_id, mytable.is_public
  FROM cte
  INNER JOIN mytable ON cte.id = mytable.parent_id and cte.is_public=1
)
SELECT * FROM cte;


id
name
parent_id
is_public


1
Task 1
null
1

2
Task 1.1
1
0

4
Task 1.2
1
1

5
Task 1.2.1
4
1


-- not  including `Task 1.1`
WITH RECURSIVE cte AS (
  SELECT id,name,parent_id,is_public
  FROM mytable where parent_id is null

  union all

  SELECT mytable.id, mytable.name, mytable.parent_id, mytable.is_public
  FROM cte
  INNER JOIN mytable ON cte.id = mytable.parent_id and mytable.is_public=1
)
SELECT * FROM cte;


id
name
parent_id
is_public


1
Task 1
null
1

4
Task 1.2
1
1

5
Task 1.2.1
4
1
id	name	parent_id	is_public
1	Task 1	null	1
2	Task 1.1	1	0
3	Task 1.1.1	2	1
4	Task 1.2	1	1
5	Task 1.2.1	4	1