SQLite TCL自定义函数-deterministic标志的缓存及相关问题

1. -deterministic标志对:startIdx=7的缓存生命周期 #

当给TCL自定义函数添加-deterministic标志后，SQLite会将相同输入参数的函数调用结果缓存至当前SQL语句的整个执行周期内。也就是说，在同一条SELECT语句（包含其内部的CTE、子查询等所有执行阶段）中，无论多少次调用get_key('a',7)，只会执行一次函数逻辑，后续直接返回缓存值。语句执行完毕后，该缓存会被销毁，下一条语句即使调用相同参数的函数，也会重新执行。

2. 在此场景下使用-deterministic标志是否安全 #

安全的核心前提是你的get_key函数是真正的确定性函数：相同的输入参数，无论何时、何种上下文调用，返回的结果完全一致。
从你的描述来看，get_key('a', :startIdx)在:startIdx=7时返回固定值7，get_key('a',18)也返回固定值，这种场景下添加-deterministic标志完全安全，还能避免不必要的重复调用，提升性能。
但如果get_key内部依赖了外部可变状态（比如全局变量、数据库数据的实时变化、系统时间等），则绝对不能添加该标志——否则SQLite会缓存错误的结果，导致查询输出异常。

3. 递归CTE的两部分对get_key()的处理方式差异原因 #

这是由SQLite的函数缓存逻辑和递归CTE的执行机制共同决定的：

• 非递归（锚点）部分：get_key('a',18)的参数是固定值，但因为你没有添加-deterministic标志，SQLite无法确认该函数的返回值不会随外部状态改变。当这个函数调用与doc_pointers表的扫描绑定（比如作为WHERE条件、SELECT列或JOIN条件）时，SQLite会在每一行的扫描上下文里重新执行函数，而非跨行缓存结果，因此会出现“每一行执行一次”的现象。
• 递归部分：get_key('b', p.order_idx + :loopDir)的参数依赖于递归行的order_idx值，每次调用的输入参数几乎都是唯一的——即使添加了-deterministic标志，不同参数的调用也会重新执行。你观察到的“不会”重复执行，本质是因为参数没有重复，自然不会出现相同参数的重复调用；而非递归部分是相同参数却重复执行，核心差异就在于是否添加了-deterministic标志让SQLite识别到可以缓存固定参数的结果。

内容的提问来源于stack exchange，提问作者Gary