文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例：Type of Record in TargetList can not be shipped
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例：Type of Record in TargetList can not be shipped

问题根因

输出列中存在record类型，这种类型的不下推一般是不会体现在最外层的输出列上，一般这类报错有两个场景：

  1. SQL书写逻辑错误，导致输出列上出现了（...）形式的输出列。
  2. SQL业务逻辑正确， 这种场景需要了解业务含义，把record字段强转为text类型，再使用record字段的地方做特殊适配。

案例1：输出列书写错误，出现（...）形式的输出列

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT
    d.id,
    coalesce(d.period, 'snull') AS period,
    (d.plan_unit_code, 'snull') AS plan_unit_code,
    coalesce(d.product_type_model, 'snull') AS product_type_model,
    coalesce(d.revision, 'snull') AS revision,
    d.start_date
FROM (SELECT *
    FROM cdcscm.cdc_mp_d_forecast_t_6120 t
    WHERE t.cdc_timestamp > to_date('2023-07-06 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 7 
    AND t.cdc_timestamp < to_date('2023-08-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
) t1,
sdiscm.mp_d_forecast_t_6120 d
WHERE (t1.audit_op_type = 'delete' AND t1.audit_op_option = 'before')
AND d.id = t1.id

改写语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT
    d.id,
    coalesce(d.period, 'snull') AS period,
    coalesce(d.plan_unit_code, 'snull') AS plan_unit_code,
    coalesce(d.product_type_model, 'snull') AS product_type_model,
    coalesce(d.revision, 'snull') AS revision,
    d.start_date
FROM (SELECT *
    FROM cdcscm.cdc_mp_d_forecast_t_6120 t
    WHERE t.cdc_timestamp > to_date('2023-07-06 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 7 
    AND t.cdc_timestamp < to_date('2023-08-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
) t1,
sdiscm.mp_d_forecast_t_6120 d
WHERE (t1.audit_op_type = 'delete' AND t1.audit_op_option = 'before')
AND d.id = t1.id

改写前后的语句不等价，不等价的原因是因为原始SQL书写有问题，正确的写法是就是coalesce(d.plan_unit_code, 'snull') AS plan_unit_code

修改点对比