从Postgres迁移至Apache Ignite时如何重写递归SQL解决执行报错
重构方案说明
报错根因定位
Column "S.TAGID" not found:Ignite底层依赖的H2引擎对递归CTE的列推导存在限制,JOIN后的CTE别名列无法被自动解析,同时UNION的去重逻辑会进一步加剧解析失败。Subquery is not a single column query:此前修改方向错误,将递归分支关联CTE的逻辑改为关联原表,破坏了递归CTE的语法结构,同时旧版H2对多列IN子查询的支持不完善,触发了解析异常。
适配Ignite/H2环境的修正查询
完全规避H2递归查询限制的修正版代码如下:
WITH RECURSIVE reportIdList(reportId, tagId, owner) AS ( -- 递归锚点:初始tag-group根节点 SELECT reportId, tagId, owner FROM "MyReportPojoCache".MyReportPojo WHERE id = ANY ( SELECT id FROM "MyOtherReportPojoCache".MyOtherReportPojo WHERE owner IS NOT NULL AND isManage IS TRUE AND type = 'tag-group' ) UNION ALL -- 替换UNION为UNION ALL,适配H2递归语法要求 -- 递归分支:恢复关联上一层CTE结果,避免关联原表 SELECT m.reportId, m.tagId, m.owner FROM "MyReportPojoCache".MyReportPojo m INNER JOIN reportIdList s ON s.tagId = m.reportId AND s.owner IS NOT NULL AND s.owner != 'admin' AND s.owner = m.owner ) -- 多列IN替换为EXISTS,规避H2多列子查询解析限制 SELECT r.qpIntId FROM "MyReportPojoCache".MyReportPojo r WHERE EXISTS ( SELECT 1 FROM reportIdList s WHERE s.reportId = r.reportId AND s.owner = r.owner )
补充落地注意事项
- 列名对齐:如果Ignite缓存的SQL列名采用下划线映射规则(如
report_id、qp_int_id),需将查询中的驼峰列名替换为对应下划线格式,避免列找不到的异常。 - 分布式JOIN适配:跨缓存JOIN的场景需确保两个缓存的
owner字段为亲和列,或开启分布式JOIN配置,避免查询路由失败。 - 递归层级配置:H2默认递归层级上限为1000,若业务递归深度超过该值,需调整Ignite SQL配置中的递归深度参数;若递归层级较浅(如不超过3层),也可直接用多层显式JOIN替代递归,性能更优。
内容的提问来源于stack exchange,提问作者RichardFeynman
相关产品推荐
相关产品推荐

