该报错为递归表缺少列名导致，补充列名即可，可参考下方示例
创建测试表

 create  table tmp01 (id int, pid int,name varchar );
 create  table tmp02 (id int, pid int,name varchar );

执行报错语句

  select id, 
         name ,( with recursive tmp  as
               ( select a.id, 
                       a.pid, 
                       a.name 
                  from tmp01 a 
               union all
                   select a.id, 
                          a.pid, 
                          a.name 
                     from tmp01 a 
               inner join tmp t 
                       on t.id=a.pid 
                )
         select count(*) 
         from tmp02 
         where  pid in (select id from tmp) ) as cnt
    from tmp01 dt 
   where dt.pid=1 ;

修改处理语句，补充列名

  select id, 
         name ,( with recursive tmp(id, pid, name) as
               ( select a.id, 
                       a.pid, 
                       a.name 
                  from tmp01 a 
               union all
                   select a.id, 
                          a.pid, 
                          a.name 
                     from tmp01 a 
               inner join tmp t 
                       on t.id=a.pid 
                )
         select count(*) 
         from tmp02 
         where  pid in (select id from tmp) ) as cnt
    from tmp01 dt 
   where dt.pid=1 ;