PG recusive语法下, 发现一些场景可能有bug

[forrestzhu]

    with recursive up_path as (
    select 1 as level, array_agg(distinct source_task_id) as path from vdm_link
    where
    target_task_id = #{targetTaskId}
    group by level
    union all
    select t1.level, t1.path
    from
    (select * from up_path) t,
    LATERAL (
    select t.level+1 as level, array_cat(t.path, array_agg(distinct e.source_task_id)) as path
    from vdm_link e
    where e.source_task_id in ( select 't_abc' union all select e2.source_task_id from vdm_link e2 where e2.target_task_id in
    ( select * from unnest(t.path) ) )
    and e.source_task_id not in (select * from unnest(t.path))
    and t.level < #{depth}
    group by (t.level+1) ) t1
    )
    select unnest(path)
    from up_path where level = (select max(level) from up_path);

如果上面的sql子语句: where e.source_task_id in ( select e2.source_task_id from vdm_link e2 where e2.target_task_id in ( select from unnest(t.path) ) ) and e.source_task_id not in (select from unnest(t.path))

这里: ( select e2.source_task_id from vdm_link e2 where e2.target_task_id in ( select * from unnest(t.path) ) ) 如果没有结果, 发现在PG9.5版本下面还会走not in 的查询, 而不是直接过滤掉. 实测这种in and not in 类型的sql, 在常规语法下是可以正常被过滤掉的

[digoal]

    select 1 as level, array_agg(distinct source_task_id) as path from vdm_link
    where
    target_task_id = #{targetTaskId}
    group by level
    union all

    select 
      t.level+1, 
      (select array_cat(t.path, array_agg(distinct source_task_id) filter (where not t.path @> array[source_task_id])) from vdm_link where target_task_id=any(t.path)) as path
    from up_path t 
      where t.level < #{depth}
      group by (t.level+1)
)
select unnest(path)
    from up_path where level = (select max(level) from up_path);```