思源 sql 查询结果与 sqlitestudio 结果不一致,后者符合预期

日记系统采用默认配置,使用周报模板如下:

{{select * from blocks where id in (select id from (select id,type,subtype,parent_id as id2 from blocks)as a left join (select id as id2,type as type2,parent_id as id3 from blocks)as b on a.id2=b.id2 left join (select id as id3,type as type3,content as content3 from blocks)as c on b.id3=c.id3 where a.type='i' and a.subtype='t' and c.type3='h' and c.content3 like '%版本开发%')and markdown like '* [_] %' and markdown not like '* [_] ~~%~~' and path like '/20211025140243-y1q7v8d/%/%-%-%' and  (case when updated>created then updated else created end) > (select max(substr(hpath,21,4)||substr(hpath,26,2)||substr(hpath,29,2)||'9') from blocks where markdown = '# 周报' and path like '/20211025140243-y1q7v8d/%/%-%-%' and root_id!='.action{.id}') and hpath <= (select hpath from blocks where id= '.action{.id}')}}

在日记页使用模板后,sql 如下:

select * from blocks where id in (select id from (select id,type,subtype,parent_id as id2 from blocks)as a left join (select id as id2,type as type2,parent_id as id3 from blocks)as b on a.id2=b.id2 left join (select id as id3,type as type3,content as content3 from blocks)as c on b.id3=c.id3 where a.type='i' and a.subtype='t' and c.type3='h' and c.content3 like '%版本开发%')and markdown like '* [_] %' and markdown not like '* [_] ~~%~~' and path like '/20211025140243-y1q7v8d/%/%-%-%' and  (case when updated>created then updated else created end) > (select max(substr(hpath,21,4)||substr(hpath,26,2)||substr(hpath,29,2)||'9') from blocks where markdown = '# 周报' and path like '/20211025140243-y1q7v8d/%/%-%-%' and root_id!='20211101100623-359fk4i') and hpath <= (select hpath from blocks where id= '20211101100623-359fk4i')

主要逻辑是筛选出上次周报所在日记日期之后及本日记日期之前(包括本日期)创建或变更的版本开发任务,并排除掉子任务。

在 sqlitestudio 中使用符合预期,在思源中使用,没有排除掉上次周报已经覆盖的任务内容。

怀疑是以下逻辑有问题:

(case when updated>created then updated else created end) > (select max(substr(hpath,21,4)||substr(hpath,26,2)||substr(hpath,29,2)||'9') from blocks where markdown = '# 周报' and path like '/20211025140243-y1q7v8d/%/%-%-%' and root_id!='.action{.id}')

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...
  • 88250
    订阅者

    可能是实参不一致?

    4 回复
  • duyujie
    订阅者 作者

    一致的,我是直接拿了嵌入模块里的 sql,来通过另一个引擎访问 temp 目录下的数据库。一样的 sql 一样的 siyuan.db,没道理不一样的结果。

  • duyujie
    订阅者 作者

    可能 substr 和 || 两个操作没支持好,其他操作都验证成的

  • duyujie
    订阅者 作者

    || 不支持,测出来了,你那边试试?

  • duyujie
    订阅者 作者

    你们用的是 sqlite 吗?

  • 88250
    订阅者
  • duyujie
    订阅者 作者

    替代方案:

    {{select * from blocks where id in (select id from (select id,type,subtype,parent_id as id2 from blocks)as a left join (select id as id2,type as type2,parent_id as id3 from blocks)as b on a.id2=b.id2 left join (select id as id3,type as type3,content as content3 from blocks)as c on b.id3=c.id3 where a.type='i' and a.subtype='t' and c.type3='h' and c.content3 like '%版本开发%')and markdown like '* [_] %' and markdown not like '* [_] ~~%~~' and path like '/20211025140243-y1q7v8d/%/%/%' and  substr((case when updated>created then updated else created end),1,8) > (select max(replace(substr(hpath,21,10),'-','')) from blocks where markdown = '# 周报' and path like '/20211025140243-y1q7v8d/%/%/%' and root_id!='.action{.id}') and hpath <= (select hpath from blocks where id= '.action{.id}')}}
    

    也就是把

    (case when updated>created then updated else created end) > (select max(substr(hpath,21,4)||substr(hpath,26,2)||substr(hpath,29,2)||'9') from blocks where markdown = '# 周报' and path like '/20211025140243-y1q7v8d/%/%-%-%' and root_id!='.action{.id}')
    

    替换为

    substr((case when updated>created then updated else created end),1,8) > (select max(replace(substr(hpath,21,10),'-','')) from blocks where markdown = '# 周报' and path like '/20211025140243-y1q7v8d/%/%/%' and root_id!='.action{.id}')
    
请输入回帖内容 ...