BUG LATERAL VIEW explode_split 相关的BUG

报错的SQL 语句

SQL 错误 [1054] [42S22]: errCode = 2, detailMessage = Unknown column 'value' in 'lv1'

WITH dict AS (
SELECT null category_id -- 分类ID
      ,null category_code -- 分类编码
      ,null category_name -- 分类名称
      ,null dict_id -- 字典ID
      ,null dict_value -- 字典键值
      ,null dict_label -- 字典标签
      ,null dict_desc -- 字典描述
      ,null parent_id -- 父级ID
      ,null parent_ids -- 父级ID组
-- 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
)
,proj_info AS (
SELECT *
      ,left(etl_part,7) AS cycle_month
      ,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_const_proj_info_d
WHERE etl_part = '${etl_part}'
)
,fund_source AS (
SELECT t1.proj_code
      ,t1.proj_name
      ,t1.fund_source_code AS fund_source_code
      ,lv1.value
FROM proj_info t1
-- LATERAL VIEW explode(split_by_string(fund_source_code, ',')) lv1 as value
LATERAL VIEW explode_split(t1.fund_source_code, ',') lv1 AS value

-- SELECT t1.proj_code
--       ,t1.proj_name
--       ,t1.fund_source_code AS fund_source_code
--       ,concat_ws(',', collect_set(zjly.dict_label)) AS fund_source_name
-- FROM proj_info t1
-- LATERAL VIEW explode_split(t1.fund_source_code, ',') lv1 AS value
-- LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('XMZJLY_XMGZ')) zjly ON lv1.value = zjly.dict_value
-- GROUP BY t1.proj_code, t1.proj_name, t1.fund_source_code
)
,proj_stage AS (
SELECT proj_code
      ,CASE WHEN -- 判断条件简化理解 A AND B OR C
                 (achieve_date IS NOT NULL AND achieve_date != '')
                 AND (concat(left(achieve_date,4)+1,'-01-01') <= concat(left(cycle_month,4),'-01-01') AND left(achieve_date,4) < left(cycle_month,4))
                 OR (SUBSTRING(achieve_date,6,2) = '01' AND left(achieve_date,4) = left(cycle_month,4))
                 THEN '4' -- 归档阶段
            WHEN -- 判断条件简化理解 A AND B AND C
                 (handover_date IS NOT NULL AND handover_date != '')
                 AND (concat(left(handover_date,4)+1,'-01-01') <= concat(left(cycle_month,4),'-01-01'))
                 AND left(handover_date,4) < left(cycle_month,4)
                 THEN '3' -- 交工阶段
            WHEN -- 判断条件简化理解 A AND B AND C
                 (subject_finish_date IS NOT NULL AND subject_finish_date != '')
                 AND (concat(left(subject_finish_date,4)+1,'-01-01') <= concat(left(cycle_month,4),'-01-01'))
                 AND left(subject_finish_date,4) < left(cycle_month,4)
                 THEN '2' -- 主体完工阶段
            WHEN -- 判断条件简化理解 A AND B
                 (proj_entry_date IS NOT NULL AND proj_entry_date != '')
                 AND (proj_entry_date < months_add(concat(cycle_month,'-01'), 1)) 
                 THEN '1' -- 在建阶段
            ELSE '0' -- 其他阶段, 归属到在建
            END AS proj_stage_code_new -- 项目阶段
       ,proj_stage_code AS proj_stage_code_new11111 -- 项目阶段
FROM proj_info
)


-- select * from fund_source -- 可以执行成功(PS: 注释掉  select * from proj_stage)


select * from proj_stage -- 注释掉上一行, 运行就报错

可以执行成功的SQL

WITH dict AS (
SELECT null category_id -- 分类ID
      ,null category_code -- 分类编码
      ,null category_name -- 分类名称
      ,null dict_id -- 字典ID
      ,null dict_value -- 字典键值
      ,null dict_label -- 字典标签
      ,null dict_desc -- 字典描述
      ,null parent_id -- 父级ID
      ,null parent_ids -- 父级ID组
-- 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
)
,proj_info AS (
SELECT *
      ,left(etl_part,7) AS cycle_month
      ,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_const_proj_info_d
WHERE etl_part = '${etl_part}'
)
,fund_source AS (
SELECT t1.proj_code
      ,t1.proj_name
      ,t1.fund_source_code AS fund_source_code
      ,lv1.value
FROM proj_info t1
-- LATERAL VIEW explode(split_by_string(fund_source_code, ',')) lv1 as value
LATERAL VIEW explode_split(t1.fund_source_code, ',') lv1 AS value

-- SELECT t1.proj_code
--       ,t1.proj_name
--       ,t1.fund_source_code AS fund_source_code
--       ,concat_ws(',', collect_set(zjly.dict_label)) AS fund_source_name
-- FROM proj_info t1
-- LATERAL VIEW explode_split(t1.fund_source_code, ',') lv1 AS value
-- LEFT JOIN (SELECT * FROM dict WHERE UPPER(category_code) = UPPER('XMZJLY_XMGZ')) zjly ON lv1.value = zjly.dict_value
-- GROUP BY t1.proj_code, t1.proj_name, t1.fund_source_code
)
,proj_stage AS (
SELECT proj_code
      ,CASE WHEN -- 判断条件简化理解 A AND B OR C
                 (achieve_date IS NOT NULL AND achieve_date != '')
                 AND (concat(left(achieve_date,4)+1,'-01-01') <= concat(left(cycle_month,4),'-01-01') AND left(achieve_date,4) < left(cycle_month,4))
                 OR (SUBSTRING(achieve_date,6,2) = '01' AND left(achieve_date,4) = left(cycle_month,4))
                 THEN '4' -- 归档阶段
            WHEN -- 判断条件简化理解 A AND B AND C
                 (handover_date IS NOT NULL AND handover_date != '')
                 AND (concat(left(handover_date,4)+1,'-01-01') <= concat(left(cycle_month,4),'-01-01'))
                 AND left(handover_date,4) < left(cycle_month,4)
                 THEN '3' -- 交工阶段
            WHEN -- 判断条件简化理解 A AND B AND C
                 (subject_finish_date IS NOT NULL AND subject_finish_date != '')
                 AND (concat(left(subject_finish_date,4)+1,'-01-01') <= concat(left(cycle_month,4),'-01-01'))
                 AND left(subject_finish_date,4) < left(cycle_month,4)
                 THEN '2' -- 主体完工阶段
            WHEN -- 判断条件简化理解 A AND B
                 (proj_entry_date IS NOT NULL AND proj_entry_date != '')
                 AND (proj_entry_date < months_add(concat(cycle_month,'-01'), 1)) 
                 THEN '1' -- 在建阶段
            ELSE '0' -- 其他阶段, 归属到在建
            END AS proj_stage_code_new -- 项目阶段
       ,proj_stage_code AS proj_stage_code_new11111 -- 项目阶段
FROM proj_info
)


select * from fund_source -- 可以执行成功(PS: 注释掉  select * from proj_stage)


-- select * from proj_stage -- 注释掉上一行, 运行就报错

DWD 建表语句

CREATE TABLE dwd.`dwd_const_proj_info_d` (
  `proj_md_code` varchar(96) NULL COMMENT '项目主数据编码',
  `proj_code` varchar(96) NULL COMMENT '项目编码',
  `proj_num` varchar(150) NULL COMMENT '项目编号',
  `proj_name` varchar(300) NULL COMMENT '项目名称',
  `proj_short_name` varchar(765) NULL COMMENT '项目简称',
  `proj_name_lang` varchar(900) NULL COMMENT '项目名称外文',
  `bid_proj_code` varchar(96) NULL COMMENT '中标项目编码',
  `cny_code` varchar(300) NULL COMMENT '币种编码',
  `org_code` varchar(96) NULL COMMENT '组织机构编码',
  `bu_code` varchar(30) NULL COMMENT '所属事业部编码',
  `proj_owner_corp_code` varchar(300) NULL COMMENT '项目所属单位编码',
  `proj_manage_corp_code` varchar(300) NULL COMMENT '项目管理单位编码',
  `proj_manage_mode_code` varchar(300) NULL COMMENT '项目管理方式编码',
  `gain_mode_code` varchar(300) NULL COMMENT '获取方式编码',
  `proj_loc_code` varchar(300) NULL COMMENT '项目所在地编码',
  `proj_detail_addr` varchar(765) NULL COMMENT '项目详细地址',
  `proj_stage_code` varchar(300) NULL COMMENT '项目阶段编码',
  `engin_class_code` varchar(300) NULL COMMENT '工程类别编码',
  `proj_type_code` varchar(300) NULL COMMENT '项目类型编码',
  `proj_control_mode` varchar(765) NULL COMMENT '项目管控模式',
  `proj_coop_mode_code` varchar(300) NULL COMMENT '项目合作方式编码',
  `busi_dept_proj_type_code` varchar(300) NULL COMMENT '商务部项目类型编码',
  `country_ind_class_code` varchar(300) NULL COMMENT '国家行业分类编码',
  `relevance_invest_proj_code` varchar(96) NULL COMMENT '关联投资项目编码',
  `invest_source_code` varchar(300) NULL COMMENT '投资来源编码',
  `fund_source_code` varchar(300) NULL COMMENT '资金来源编码',
  `output_tax_rate` decimal(18,4) NULL COMMENT '销项税率(%)',
  `fund_source_remark` varchar(765) NULL COMMENT '资金来源备注',
  `fund_source_class_code` varchar(300) NULL COMMENT '按资金来源分类编码',
  `proj_size_corp_code` varchar(300) NULL COMMENT '项目规模单位编码',
  `is_unsign_proj_code` varchar(15) NULL COMMENT '是否未签约项目编码(1-是,0-否)',
  `is_general_subcon_proj_code` varchar(300) NULL COMMENT '是否总分包项目编码(1-是,0-否)',
  `list_is_tax_included_code` varchar(300) NULL COMMENT '清单是否含税编码(1-是,0-否)',
  `is_include_tunnel_code` varchar(300) NULL COMMENT '是否包含隧道编码(1-是,0-否)',
  `is_new3_proj_code` varchar(30) NULL COMMENT '是否三新项目编码(1-是,0-否)',
  `is_work_acct_code` varchar(6) NULL COMMENT '是否工区核算编码(1-是,0-否)',
  `proj_type_main_tech_index` varchar(765) NULL COMMENT '项目类型和主要技术指标',
  `owner_code` varchar(96) NULL COMMENT '业主编码',
  `design_corp_code` varchar(1500) NULL COMMENT '设计单位编码',
  `supervision_corp_code` varchar(96) NULL COMMENT '监理单位编码',
  `create_excellence_type` varchar(300) NULL COMMENT '创优类型',
  `proj_size` decimal(18,2) NULL COMMENT '项目规模',
  `proj_detail_situ` text NULL COMMENT '项目详细情况',
  `proj_feature` varchar(3000) NULL COMMENT '项目特色',
  `proj_overview_size` varchar(3000) NULL COMMENT '项目概况及规模',
  `proj_entry_date` datetime NULL COMMENT '项目进场日期',
  `proj_act_start_date` datetime NULL COMMENT '项目实际开工日期',
  `proj_work_order_date` datetime NULL COMMENT '项目开工令日期',
  `is_get_const_lice` varchar(30) NULL COMMENT '是否取得施工许可证',
  `const_lice_get_date` datetime NULL COMMENT '施工许可证取得日期',
  `not_get_const_lice_reason_desc` varchar(2400) NULL COMMENT '未取得施工许可证特殊原因说明',
  `subject_finish_date` datetime NULL COMMENT '主体完工日期',
  `finish_rpt_date` datetime NULL COMMENT '完工上报日期',
  `handover_date` datetime NULL COMMENT '交工日期',
  `open_to_traffic_date` datetime NULL COMMENT '通车日期',
  `handover_cert_get_date` datetime NULL COMMENT '交工证书取得日期',
  `complete_cert_get_date` datetime NULL COMMENT '竣工证书取得日期',
  `achieve_date` datetime NULL COMMENT '归档日期',
  `complete_date` datetime NULL COMMENT '竣工日期',
  `entire_line_lot_num` bigint NULL COMMENT '全线标段数',
  `engin_entity_center_loc_long` varchar(300) NULL COMMENT '工程实体中心位置经度',
  `engin_entity_center_loc_lat` varchar(300) NULL COMMENT '工程实体中心位置纬度',
  `tunnel_const_method` varchar(300) NULL COMMENT '隧道施工方法(01-矿山法、掘进机法、盾构法、明挖法、盖挖法、浅埋暗挖法、沉管法)',
  `parent_id` varchar(96) NULL COMMENT '父级编号',
  `parent_ids` varchar(1920) NULL COMMENT '父级所有ID',
  `create_date` datetime NULL COMMENT '创建时间',
  `update_date` datetime NULL COMMENT '更新时间',
  `del_flag` varchar(24) NULL COMMENT '删除标记(1-删除,0-正常)',
  `etl_time` datetime NULL COMMENT 'ETL加载时间',
  `source_system` varchar(2000) NULL COMMENT '来源系统',
  `source_table` varchar(2000) NULL COMMENT '来源表名',
  `etl_part` varchar(100) NULL COMMENT '分区字段'
) ENGINE=OLAP
COMMENT '施工项目基本信息'
PARTITION BY LIST(`etl_part`)
(PARTITION p20241217 VALUES IN ("2024-12-17"))
DISTRIBUTED BY HASH(`proj_code`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_duplicate_without_keys_by_default" = "true"
);