SonnetDB CTE 与集合运算：有界递归、UNION、INTERSECT 和 EXCEPT

SonnetDB CTE 与集合运算：有界递归、UNION、INTERSECT 和 EXCEPT

SonnetDB 支持用普通 CTE 组织查询、用一个受限的递归 CTE 遍历关系表，并组合 UNION、INTERSECT 与 EXCEPT；本文给出可检查的 SQL 形状和结果解释，同时明确这些上限是查询准入与语义边界，不是进程 CLR 堆峰值或生产容量承诺。

SonnetDB CTE 与集合运算：有界递归、UNION、INTERSECT 和 EXCEPT

AI 主题示意图，并非实测截图。

先按实际部署版本选择语法

本文的 CTE 源码链接固定到已复核的提交 7255f0104a1176fd15b883814222dd0f23591d8d。在 2026-11-03 对照当前已提交源码基线时，本文引用的 CTE 实现、SQL 参考和测试合同没有变更。公开 GitHub 最新正式发行仍为 v4.0.0；公开 NuGet 目录包含 4.0.0，也列有 4.5.0-preview.ef266.5。实际 Server、SDK 或嵌入式应用需要按自己安装的版本与发布说明复核，不能把当前源码的查询合同自动宣传为所有已发布包都具备的能力。

以下 SQL 是根据源码、SQL 参考和已有测试合同整理的可复核示例，本文没有在生产服务器、固定硬件或长期负载上执行它们。执行前请在隔离测试库确认表结构、参数和超时配置。

可在空的隔离测试库准备下面的最小数据；每条语句分别执行，避免依赖客户端是否支持多语句批处理：

CREATE TABLE devices (id INT PRIMARY KEY, parent_id INT, region STRING);
INSERT INTO devices (id, parent_id, region) VALUES (100, NULL, 'west');
INSERT INTO devices (id, parent_id, region) VALUES (101, 100, 'west');
INSERT INTO devices (id, parent_id, region) VALUES (102, 100, 'east');
INSERT INTO devices (id, parent_id, region) VALUES (200, NULL, 'west');
CREATE TABLE active_devices (id INT PRIMARY KEY);
INSERT INTO active_devices (id) VALUES (100), (101);
CREATE TABLE recently_seen (id INT PRIMARY KEY);
INSERT INTO recently_seen (id) VALUES (101), (102);
CREATE TABLE managed_devices (id INT PRIMARY KEY);
INSERT INTO managed_devices (id) VALUES (101), (200);

这些表名只用于本篇练习。若名称已经存在，请使用新的隔离数据库，不要为复现示例删除业务表。

普通 CTE：按声明顺序拆解查询

普通 WITH ... AS (...) 可以定义一个或多个非递归 CTE，后面的 CTE 能引用前面的定义。下面先筛选西区设备，再在第二层继续筛选：

WITH west (device_id) AS (
    SELECT id FROM devices WHERE region = 'west'
), selected (device_id) AS (
    SELECT device_id FROM west WHERE device_id > 100
)
SELECT device_id FROM selected ORDER BY device_id;

按上面的样例数据，结果应为 101、200，输出列名为 device_id。

这不是新的独立 planner。SonnetDB 将 CTE 展开到既有派生表、关系 JOIN、IN / EXISTS 子查询执行路径，因此原有来源与查询组合的限制仍然生效。CTE 名称按声明顺序解析：重复名称会拒绝，前向引用也不成立。WITH name (column, ...) AS (...) 可以按位置重命名输出列；列数即使在空结果下也必须吻合，列名保留拼写，普通引用忽略大小写，双引号引用精确匹配。

普通 CTE 可以使用 measurement 或文档 SELECT 作为来源，但这不等价于宣称所有跨服务端 REST/Frame 组合都完成了 parity 验证。需要远程路径时，应单独验证目标版本和传输方式。

递归 CTE：先设计能收敛的关系表遍历

WITH RECURSIVE 当前允许一个 query-local 递归定义，其形状是 anchor UNION [ALL] recursive_member。若数据可能有环，使用不含深度列的 UNION 让已见行按整行去重，避免仅靠层数继续生成不同的行：

WITH RECURSIVE reachable (id) AS (
    SELECT id FROM devices WHERE id = @rootId
    UNION
    SELECT child.id
    FROM devices AS child
    JOIN reachable ON child.parent_id = reachable.id
)
SELECT id FROM reachable ORDER BY id;

通过支持命名参数的调用入口把 @rootId 绑定为整数 100；在只执行字面量 SQL 的交互工具中，可先将这个占位符改成 100。样例应返回 100、101、102，独立根节点 200 不可达。

结果解释如下：anchor 给出起点；递归成员在每一轮只读取上一层 frontier；最终 SELECT 读取累计结果。UNION 按全体已见行去重，适合上述可达性查询。UNION ALL 保留重复行，若遍历存在环，必须额外限制访问条件，否则它会达到资源上限并报错。若把 depth 放进投影，(id, depth) 在每一层可能仍是不同的整行，不能简单把 UNION 当成通用的环检测替代品。

当前上限是最多 64 层、累计保留 100,000 行、约 32 MiB 的估算保留行数据，以及每轮最多 100,000 个候选输出。超限会明确报错且不返回部分结果；EXPLAIN 会报告递归 worktable 与这些上限。取消会在逐层和逐行处理中检查。

以下形状仍是明确不支持，而不是“可能慢一些”：互递归、多个直接自引用、递归成员内的子查询、聚合、DISTINCT、ORDER BY 或分页；递归定义内部也不能排序或分页，应把排序和分页放在最终 SELECT。

集合运算：让去重、优先级和尾部排序可预测

关系查询可使用 UNION、UNION ALL、INTERSECT 和 EXCEPT。下面先对“最近上报”和“被托管”求交集，再用 UNION ALL 与活跃设备组合，最后由统一的尾部排序控制展示顺序：

SELECT id FROM active_devices
UNION ALL
SELECT id FROM recently_seen
INTERSECT
SELECT id FROM managed_devices
ORDER BY id;

样例中 recently_seen INTERSECT managed_devices 只有 101；与活跃集合组合后，排序结果为 100、101、101。重复的 101 正是 UNION ALL 保留的结果，不能把本句解释成先 UNION 再对整个结果求交集。

解释时应记住四条规则：

  1. UNION、INTERSECT 和 EXCEPT 按集合语义去重；UNION ALL 保留重复行。
  2. INTERSECT 的优先级高于 UNION 与 EXCEPT；同优先级运算按从左到右应用。
  3. 结果列名取第一个分支；每个分支必须返回相同列数，已知声明类型或可推断的非 NULL 类型必须逐列兼容。需要转换时显式使用 CAST，不要依赖可能损失精度的隐式数值转换。
  4. 最终的 ORDER BY、LIMIT、OFFSET/FETCH 只写在复合查询末尾。INTERSECT ALL 与 EXCEPT ALL 不在当前合同内。

两个 NULL 在集合去重时视为相等。UNION ALL 没有最终 ORDER BY 时按分支书写顺序保留各自行顺序；一旦添加最终排序，就应只依赖排序结果。

预算错误要按“拒绝”处理，而不是把它当作结果

递归 CTE 与集合运算都会受阻塞算子内存预算约束。集合去重/交/差所需的保留行和哈希集合超过查询或数据库预算时，SonnetDB 拒绝执行且不返回部分结果；当前路径不使用 spill。递归 CTE 的行字节是保守估算，并会在保留关系投影、JOIN 构建行、普通 CTE 副本等位置进行准入检查。

这不等于进程的 CLR 堆峰值上限。表读取、UTF-8 解码、表达式求值、临时 JOIN 输出和非关系 SELECT 分发可能在准入点之前分配对象。已有审计明确记录了一个 64 KiB 预算下仍观察到约 3 MiB 分配的反例，因此不能把 32 MiB 或查询预算写成“绝不会超过的物理内存”。生产容量、固定硬件延迟、Native AOT 和完整远程验收仍应单独获取证据。

建议的验证顺序

  1. 在隔离数据库创建最小关系表，先运行普通 CTE，确认列名和结果集。
  2. 对递归查询从空 anchor、无子节点和小分支开始；含环数据优先使用能按整行收敛的 UNION 形状。
  3. 对集合查询分别断言重复保留、重复去除、NULL、列数不等和类型不等的行为。
  4. 为递归/集合查询运行 EXPLAIN，记录 worktable、预算和调用方超时。
  5. 远程 REST、HTTP/2 Frame、固定硬件或长期负载需要各自的专门验证；本地单元测试与文档示例不能替代这些门禁。

可核查来源

关注我们，扫码进群沟通

欢迎关注我们，扫码加入微信或 QQ 交流群，一起交流技术实践，分享使用经验和问题反馈。

微信交流二维码

微信交流二维码

QQ 交流群：IoTSharp，群号 63631741

IoTSharp QQ 交流群二维码，群号 63631741

请扫码并关注我们，进群沟通。若二维码暂时无法使用，可通过 QQ 群号 63631741 搜索加入。

posted @ 2026-11-04 02:10  IoTSharp  阅读(4)  评论(0)    收藏  举报