Matillion操作Redshift遇关联子查询内部错误求助

这个错误和你的Redshift集群本身无关，核心原因是Redshift查询引擎对特定模式的相关子查询支持有限——你在SELECT列表里用的(select case ... end)这种单行相关子查询嵌套，正好触发了Redshift优化器的不支持场景。

问题分析 #

你的查询里，order_date_time和ship_date_time字段的定义都用了嵌套子查询，但这些子查询并没有关联外部数据集，只是单纯引用当前行的sap表字段，完全不需要用子查询包裹。Redshift对这种无意义的子查询嵌套的处理逻辑存在限制，所以抛出了内部错误。

修复后的SQL #

把嵌套的子查询去掉，直接将CASE逻辑放在SELECT列表中即可，修改后的查询如下：

SELECT DISTINCT 
    "sap"."tracking_no" AS "tracking_no",
    -- 直接写CASE逻辑，去掉外层的select
    CASE 
        WHEN len("sap"."order_create_time")=6 
             AND regexp_instr("sap"."order_create_date",'[a-zA-Z]')=0 
             AND regexp_instr("sap"."order_create_time",'[a-zA-Z]')=0 
        THEN CAST(
            CONCAT(
                CONCAT(cast(isnull("sap"."order_create_date",'1900-01-01') as VARCHAR(10)), ' '),
                CONCAT(
                    CONCAT(
                        CONCAT(
                            SUBSTRING(isnull("sap"."order_create_time",'000000'), 1,2), ':'
                        ), SUBSTRING(isnull("sap"."order_create_time",'000000'), 3,2)
                    ),':'
                ), SUBSTRING(isnull("sap"."order_create_time",'000000'), 5,2)
            ) as timestamp
        )
        WHEN len("sap"."order_create_time")=5 
             AND regexp_instr("sap"."order_create_date",'[a-zA-Z]')=0 
             AND regexp_instr("sap"."order_create_time",'[a-zA-Z]')=0 
        THEN CAST(
            CONCAT(
                CONCAT(cast(isnull("sap"."order_create_date",'1900-01-01') as VARCHAR(10)), ' '),
                CONCAT(
                    CONCAT(
                        CONCAT(
                            concat('0',SUBSTRING(isnull("sap"."order_create_time",'000000'), 1,1)), ':'
                        ), SUBSTRING(isnull("sap"."order_create_time",'000000'), 2,2)
                    ),':'
                ), SUBSTRING(isnull("sap"."order_create_time",'000000'), 4,2)
            ) as timestamp
        )
        ELSE cast('1900-01-01 00:00:00' as timestamp) 
    END AS "order_date_time",
    -- 同样处理ship_date_time字段
    CASE 
        WHEN len("sap"."ship_time")=6 
             AND regexp_instr("sap"."ship_date",'[a-zA-Z]')=0 
             AND regexp_instr("sap"."ship_time",'[a-zA-Z]')=0 
        THEN CAST(
            CONCAT(
                CONCAT(cast(isnull("sap"."ship_date",'1900-01-01') as VARCHAR(10)), ' '),
                CONCAT(
                    CONCAT(
                        CONCAT(
                            SUBSTRING(isnull("sap"."ship_time",'000000'), 1,2), ':'
                        ), SUBSTRING(isnull("sap"."ship_time",'000000'), 3,2)
                    ),':'
                ), SUBSTRING(isnull("sap"."ship_time",'000000'), 5,2)
            ) as timestamp
        )
        WHEN len("sap"."ship_time")=5 
             AND regexp_instr("sap"."ship_date",'[a-zA-Z]')=0 
             AND regexp_instr("sap"."ship_time",'[a-zA-Z]')=0 
        THEN CAST(
            CONCAT(
                CONCAT(cast(isnull("sap"."ship_date",'1900-01-01') as VARCHAR(10)), ' '),
                CONCAT(
                    CONCAT(
                        CONCAT(
                            concat('0',SUBSTRING(isnull("sap"."ship_time",'000000'), 1,1)), ':'
                        ), SUBSTRING(isnull("sap"."ship_time",'000000'), 2,2)
                    ),':'
                ), SUBSTRING(isnull("sap"."ship_time",'000000'), 4,2)
            ) as timestamp
        )
        ELSE cast('1900-01-01 00:00:00' as timestamp) 
    END AS "ship_date_time",
    "fedex"."TRACKING_NUMBER" AS "TRACKING_NUMBER"
FROM fed_ex_feed AS "fedex"
LEFT OUTER JOIN sap_delivery AS "sap" 
    ON "fedex"."TRACKING_NUMBER" = "sap"."tracking_no"
WHERE sap.order_create_date is not null 
  AND fedex.DELIVERY_DATE is not null

额外优化提示 #

1. 你原查询里的默认时间字符串'1900-01-01 000000'格式不符合Redshift的timestamp要求，我已经修正为'1900-01-01 00:00:00'。
2. 原查询中处理5位时间的截取索引存在错误，我已经调整了SUBSTRING的参数，确保时分秒的拼接逻辑正确。
3. Redshift对复杂字符串操作的性能不算最优，如果这类转换操作频繁执行，建议考虑在数据加载阶段就把日期时间字段转换为标准timestamp格式，减少查询时的计算开销。

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