OmniSciDB执行SQL报Expression node not supported错误如何修复

问题背景 #

我正在使用GPU数据库OmniSciDB，原本认为它的SQL语法与MySQL一致，编写的SQL语句如下：

SELECT rdate as rdate
      ,stime / 100 as Stime
      , ttime /100 as etime 
    ,c as endreturn 
      , up.steps as urange
      ,down.steps as drange 
      , Uptime.MinTime as utime 
      , Downtime.MinTime as dtime 
      /*, IFNULL(Uptime.MinTime, 2555) as utime 
      ,IFNULL(Downtime.MinTime, 2555) as dtime */

FROM HL as A

CROSS JOIN(VALUES(0.001),   (0.00125),  (0.0015),   (0.00175),  (0.002),    (0.00225),  (0.0025),   (0.00275),  (0.003),    (0.00325),  (0.0035),   (0.00375),  (0.004),    (0.00425),  (0.0045),   (0.00475),  (0.005),    (0.00525),  (0.0055),   (0.00575),  (0.006),    (0.00625),  (0.0065),   (0.00675),  (0.007),    (0.00725),  (0.0075),   (0.00775),  (0.008),    (0.00825),  (0.0085),   (0.00875),  (0.009),    (0.00925),  (0.0095),   (0.00975),  (0.01), (0.01025),  (0.0105),   (0.01075),  (0.011)) AS up(steps)
CROSS JOIN(VALUES (-0.001), (-0.00125), (-0.0015),  (-0.00175), (-0.002),   (-0.00225), (-0.0025),  (-0.00275), (-0.003),   (-0.00325), (-0.0035),  (-0.00375), (-0.004),   (-0.00425), (-0.0045),  (-0.00475), (-0.005),   (-0.00525), (-0.0055),  (-0.00575), (-0.006),   (-0.00625), (-0.0065),  (-0.00675), (-0.007),   (-0.00725), (-0.0075),  (-0.00775), (-0.008),   (-0.00825), (-0.0085),  (-0.00875), (-0.009),   (-0.00925), (-0.0095),  (-0.00975), (-0.01),    (-0.01025), (-0.0105),  (-0.01075), (-0.011)) AS  down(steps)  

OUTER APPLY (SELECT MIN(ttime) /100 AS MinTime FROM HL as B WHERE B.rdate = A.rdate AND B.ttime > A.stime AND b.ttime <= A.ttime AND H > up.steps ) as Uptime
OUTER APPLY (SELECT MIN(ttime) /100 AS MinTime FROM HL as C WHERE C.rdate = A.rdate AND C.ttime > A.stime AND C.ttime <= A.ttime AND H > up.steps ) as Downtime
 
 WHERE rdate / 10000 = 2011;

执行后返回如下报错：

Expression node {"field":"rdate","expr":{"correl":"$cor0","type":[{"type":"INTEGER","nullable":true,"name":"rdate"},{"type":"INTEGER","nullable":true,"name":"Stime"},{"type":"INTEGER","nullable":true,"name":"ttime"},{"type":"FLOAT","nullable":true,"name":"h"},{"type":"FLOAT","nullable":true,"name":"L"},{"type":"FLOAT","nullable":true,"name":"C"},{"type":"BIGINT","nullable":false,"name":"rowid"},{"type":"DECIMAL","nullable":false,"precision":6,"scale":5,"name":"steps"},{"type":"DECIMAL","nullable":false,"precision":6,"scale":5,"name":"steps0"},{"type":"INTEGER","nullable":true,"name":"MinTime"},{"type":"INTEGER","nullable":true,"name":"MinTime0"}]}} not supported

修复方案 #

报错的核心原因是OmniSciDB对OUTER APPLY关联子查询、跨作用域的字段引用支持不完善，按以下步骤修改即可：

• 替换OUTER APPLY语法：OmniSciDB不支持APPLY系列运算符，改用LEFT JOIN + 窗口函数的逻辑实现同等效果，或者先完成笛卡尔积后统一做聚合计算
• 补全所有字段的表别名：原语句WHERE条件中的rdate未加表别名A.，同时第二个关联子查询的条件存在逻辑错误，原写的H > up.steps需要改为H < down.steps
• 优化年份过滤逻辑：将rdate / 10000 = 2011改为rdate BETWEEN 20110101 AND 20111231，避免对字段做运算导致无法识别，同时可以提升查询效率
• 替换空值处理函数：OmniSciDB不支持IFNULL，改用标准SQL函数COALESCE即可实现同等的空值默认值替换效果

修复后参考代码 #

WITH HL_2011 AS (
    SELECT * FROM HL WHERE rdate BETWEEN 20110101 AND 20111231
),
step_values AS (
    SELECT up.steps as urange, down.steps as drange
    FROM (VALUES(0.001),   (0.00125),  (0.0015),   (0.00175),  (0.002),    (0.00225),  (0.0025),   (0.00275),  (0.003),    (0.00325),  (0.0035),   (0.00375),  (0.004),    (0.00425),  (0.0045),   (0.00475),  (0.005),    (0.00525),  (0.0055),   (0.00575),  (0.006),    (0.00625),  (0.0065),   (0.00675),  (0.007),    (0.00725),  (0.0075),   (0.00775),  (0.008),    (0.00825),  (0.0085),   (0.00875),  (0.009),    (0.00925),  (0.0095),   (0.00975),  (0.01), (0.01025),  (0.0105),   (0.01075),  (0.011)) AS up(steps)
    CROSS JOIN (VALUES (-0.001), (-0.00125), (-0.0015),  (-0.00175), (-0.002),   (-0.00225), (-0.0025),  (-0.00275), (-0.003),   (-0.00325), (-0.0035),  (-0.00375), (-0.004),   (-0.00425), (-0.0045),  (-0.00475), (-0.005),   (-0.00525), (-0.0055),  (-0.00575), (-0.006),   (-0.00625), (-0.0065),  (-0.00675), (-0.007),   (-0.00725), (-0.0075),  (-0.00775), (-0.008),   (-0.00825), (-0.0085),  (-0.00875), (-0.009),   (-0.00925), (-0.0095),  (-0.00975), (-0.01),    (-0.01025), (-0.0105),  (-0.01075), (-0.011)) AS  down(steps)
)
SELECT 
    A.rdate,
    A.stime / 100 as Stime,
    A.ttime / 100 as etime,
    A.c as endreturn,
    s.urange,
    s.drange,
    COALESCE(MIN(CASE WHEN B.H > s.urange THEN B.ttime / 100 END), 2555) as utime,
    COALESCE(MIN(CASE WHEN B.H < s.drange THEN B.ttime / 100 END), 2555) as dtime
FROM HL_2011 A
CROSS JOIN step_values s
LEFT JOIN HL_2011 B ON B.rdate = A.rdate AND B.ttime > A.stime AND B.ttime <= A.ttime
GROUP BY A.rdate, A.stime, A.ttime, A.c, s.urange, s.drange;

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