版本升级
报错依然存在
关闭参数: enable_nereids_planner
链接地址: https://doris.apache.org/zh-CN/docs/releasenotes/v2.1/release-2.1.7?_highlight=enable_nereids_planner#行为变更
我专门测试了一下 SET GLOBAL enable_nereids_planner = false;
当设置这个参数关闭之后, 会导致 LATERAL VIEW explode_split, explode_map 类似的操作报错
示例SQL
WITH dict AS (
SELECT t1.category_id -- 分类ID
,t1.category_code -- 分类编码
,t1.category_name -- 分类名称
,t2.dict_id -- 字典ID
,t2.dict_value -- 字典键值
,t2.dict_label -- 字典标签
,t2.dict_desc -- 字典描述
,t2.parent_id -- 父级ID
,t2.parent_ids -- 父级ID组
FROM (SELECT * FROM dim.dim_csp_sys_dict_category_d WHERE etl_part = '${etl_part}') t1
LEFT JOIN (SELECT * FROM dim.dim_csp_sys_dict_d WHERE etl_part = '${etl_part}') t2
ON t1.category_id = t2.category_id
)
,province_city_county AS (
SELECT t1.dict_value AS province_code
,t1.dict_label AS province_name
,t2.dict_value AS city_code
,t2.dict_label AS city_name
,t3.dict_value AS county_code
,t3.dict_label AS county_name
FROM (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('TA') AND ((LENGTH(dict_value) = 4 AND RIGHT(dict_value,2) = '00') OR (LENGTH(dict_value) = 3))) t1
LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('TA') AND LENGTH(dict_value) = 4 AND RIGHT(dict_value,2) != '00') t2
ON t1.dict_id = t2.parent_id
LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('TA') AND LENGTH(dict_value) > 5) t3
ON t2.dict_id = t3.parent_id
)
,area AS (
SELECT t1.dict_value as area_code -- 片区编码
,t1.dict_label as area_name -- 片区名称
,t2.*
FROM (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('TA')) t1
JOIN (SELECT distinct *, right(left(parent_ids, 65), 32) as area_id FROM dict WHERE UPPER(category_code) = UPPER('TA')) t2
ON t1.dict_id = t2.area_id
)
,office AS (
SELECT t1.office_id
,t1.office_code
,t1.office_name
,t1.office_shortname
,t1.parent_id
,t1.parent_ids
,t1.type AS org_type_code
,jglx.dict_label AS org_type_name
,CASE WHEN t1.type = 'C20' AND t1.parent_id IN ('87c7835fecbe4ad195c219400655f0c2', '483a36471aac48c6b017cd17bdab7e3d') THEN '直属投资公司' END AS direct_proj -- 直属项目
,CASE WHEN substring(concat(t1.parent_ids,t1.office_id),34,32) = '483a36471aac48c6b017cd17bdab7e3d'
THEN -- 隧道局的判断方式
CASE WHEN t1.office_id = '483a36471aac48c6b017cd17bdab7e3d' THEN t1.office_id
WHEN t1.parent_id = substring(t1.parent_ids,34,32) THEN t1.office_id
WHEN substring(concat(t1.parent_ids,t1.office_id),67,32) = t3.office_id THEN t3.office_id
END
ELSE -- 非隧道局的判断方式
CASE WHEN t1.parent_ids is null OR length(t1.parent_ids) = 0 THEN t1.office_id
WHEN t1.parent_id = left(t1.parent_ids,32) THEN t1.office_id
WHEN substring(t1.parent_ids,34,32) = t2.office_id THEN t2.office_id
END
END AS owner_office_code -- 所属机构编码
,CASE WHEN substring(concat(t1.parent_ids,t1.office_id),34,32) = '483a36471aac48c6b017cd17bdab7e3d'
THEN -- 隧道局的判断方式
CASE WHEN t1.office_id = '483a36471aac48c6b017cd17bdab7e3d' THEN t1.office_name
WHEN t1.parent_id = substring(t1.parent_ids,34,32) THEN t1.office_name
WHEN substring(concat(t1.parent_ids,t1.office_id),67,32) = t3.office_id THEN t3.office_name
END
ELSE -- 非隧道局的判断方式
CASE WHEN t1.parent_ids is null OR length(t1.parent_ids) = 0 THEN t1.office_name
WHEN t1.parent_id = left(t1.parent_ids,32) THEN t1.office_name
WHEN substring(t1.parent_ids,34,32) = t2.office_id THEN t2.office_name
END
END AS owner_office_name -- 所属机构名称
,CASE WHEN substring(concat(t1.parent_ids,t1.office_id),34,32) = '483a36471aac48c6b017cd17bdab7e3d'
THEN -- 隧道局的判断方式
CASE WHEN t1.office_id = '483a36471aac48c6b017cd17bdab7e3d' THEN t1.office_shortname
WHEN t1.parent_id = substring(t1.parent_ids,34,32) THEN t1.office_shortname
WHEN substring(concat(t1.parent_ids,t1.office_id),67,32) = t3.office_id THEN t3.office_shortname
END
ELSE -- 非隧道局的判断方式
CASE WHEN t1.parent_ids is null OR length(t1.parent_ids) = 0 THEN t1.office_shortname
WHEN t1.parent_id = left(t1.parent_ids,32) THEN t1.office_shortname
WHEN substring(t1.parent_ids,34,32) = t2.office_id THEN t2.office_shortname
END
END AS owner_office_shortname -- 所属机构简称
/*
,CASE WHEN t1.parent_ids is null OR length(t1.parent_ids) = 0 THEN t1.office_name
WHEN t1.parent_id = left(t1.parent_ids,32) THEN t1.office_name
WHEN substring(t1.parent_ids,34,32) = t2.office_id THEN t2.office_name
END AS 测试_非隧道局的判断方式
*/
FROM (SELECT * FROM dim.dim_csp_sys_office_d WHERE etl_part = '${etl_part}') t1
LEFT JOIN (SELECT * FROM dim.dim_csp_sys_office_d WHERE etl_part = '${etl_part}') t2
ON substring(t1.parent_ids,34,32) = t2.office_id
LEFT JOIN (SELECT * FROM dim.dim_csp_sys_office_d WHERE etl_part = '${etl_part}') t3
ON substring(concat(t1.parent_ids,t1.office_id),67,32) = t3.office_id
LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('organtype.ccccltd')) jglx
ON t1.type = jglx.dict_value
)
,country_area AS (
SELECT CODE -- 编码
,ZCOUNTRYNAME -- 中文名称
,ZCOUNTRYENAME -- 英文名称
,ZGBCHAR -- 国标三字符代码
,ZGBNUM -- 国标数字代码
,ZCONTINENTCODE -- 大洲代码
,ZCRCCODE -- 中交区域中心代码
FROM dim.dim_mdm_country_area_code_d
WHERE etl_part = '${etl_part}'
)
,proj_info AS (
SELECT *
,CASE WHEN length(proj_loc_code) = 3 THEN proj_loc_code
WHEN length(proj_loc_code) = 4 AND right(proj_loc_code, 2) = '00' THEN proj_loc_code
END AS province_code
,CASE WHEN length(proj_loc_code) = 4 AND right(proj_loc_code, 2) != '00' THEN proj_loc_code END AS city_code
,CASE WHEN length(proj_loc_code) > 5 THEN proj_loc_code END AS county_code
,CASE WHEN proj_loc_code in ('110', '121', '143') AND length(proj_loc_code) = 3 THEN proj_loc_code -- 110-香港, 121-澳门, 143-台湾, 142-中国
WHEN proj_loc_code not in ('110', '121', '143') AND length(proj_loc_code) > 3 THEN '142' -- 142-中国
WHEN proj_loc_code not in ('110', '121', '143') AND length(proj_loc_code) = 3 THEN proj_loc_code
END AS loc_country_area_code
FROM dwd.dwd_inv_proj_info_d
WHERE etl_part = '${etl_part}'
)
,proj_fund_source AS (
SELECT t1.proj_code
,t1.proj_name
,t1.proj_fund_source AS proj_fund_source_code
,t1.invest_proj_fund_source AS invest_proj_fund_source_code
,concat_ws(',', collect_set(xmzjly.dict_label)) AS proj_fund_source_name
,concat_ws(',', collect_set(tzxmzjly.dict_label)) AS invest_proj_fund_source_name
FROM proj_info t1
LATERAL VIEW explode_split(t1.proj_fund_source, ',') lv1 AS value
LATERAL VIEW explode_split(t1.invest_proj_fund_source, ',') lv2 AS value
LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('XMZJLY_XMGZ')) xmzjly ON lv1.value = xmzjly.dict_value
LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('TZXMZJLY')) tzxmzjly ON lv2.value = tzxmzjly.dict_value
GROUP BY t1.proj_code, t1.proj_name, t1.proj_fund_source, t1.invest_proj_fund_source
)
select * from proj_fund_source
