[Bug] The WITH subquery is abnormal. Procedure. WITH 子查询异常

[xqg1316]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Version

Doris-2.1.3

What's Wrong?

WITH 语法异常

有以下SQL语句

ALTER TABLE dwd.dwd_inv_proj_corp_d DROP PARTITION IF EXISTS p20240928; ALTER TABLE dwd.dwd_inv_proj_corp_d ADD PARTITION IF NOT EXISTS p20240928 VALUES IN ('2024-09-28');

INSERT INTO dwd.dwd_inv_proj_corp_d PARTITION (p20240928) WITH t1 AS ( SELECT * FROM ods.ods_cfhec_pro_inv_com_inf_f_d PARTITION (p20240928) WHERE del_flag = '0' ) ,t2 AS ( SELECT pro_com_id ,sum(case when gd_category = '01' then (cgbl / 100) else 0 end) AS sum_cg FROM ods.ods_cfhec_pro_inv_com_inf_gd_f_d PARTITION (p20240928) GROUP BY pro_com_id ) SELECT NULL AS proj_md_code ,t1.inv_pro_id AS proj_code --项目编码 ,t1.id AS proj_corp_code --项目公司编码 ,t1.pro_com_name AS proj_corp_name --项目公司名称(唯一性) ,round(t1.zczj, 2) AS reg_cap --注册资金 ,t1.register_area AS reg_location --注册地 ,t1.fddbr AS lpr --法定代表人 ,t1.register_date AS reg_date --注册时间 ,t1.com_zjl AS corp_gm --公司总经理 ,t1.zjl_tel AS gm_tel --总经理联系方式 ,t1.com_cwfzr AS corp_fin_prin --公司财务负责人 ,t1.cw_tel AS fin_tel --财务联系方式 ,round(t2.sum_cg / 100, 4) AS cfhec_group_shr_right --一公局集团所占股权(%) ,round(t1.zgjjszgq / 100, 4) AS cccc_consortium_shr_right --中交内联合体所占股权(%) ,t1.yyzz AS busi_license --营业执照 ,t1.yyzzh AS busi_license_num --营业执照号 ,t1.gszc AS articles_of_corp --公司章程 ,t1.wfgddbmc AS our_side_shrhd_rept_name --我方股东代表名称 ,t1.create_date AS create_date ,t1.update_date AS update_date ,t1.del_flag AS del_flag --删除标记 ,current_timestamp() AS etl_time --etl加载时间 ,'' AS source_system --来源系统 ,'ods_cfhec_pro_inv_com_inf_f_d,ods_cfhec_pro_inv_com_inf_gd_f_d' AS source_table --来源表名 ,'2024-09-28' AS etl_part --分区字段 FROM t1 LEFT JOIN t2 ON t1.id = t2.pro_com_id ;

---

当执行 INSERT INTO WITH SELECT 的时候, 居然报错 SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 5: WITH t1 AS ( ^ Encountered: IDENTIFIER Expected

What You Expected?

无

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• [X] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[SoyMeng]

设置变量 enable_unique_key_partial_update = true之后， INSERT 不支持CTE，不确定是否是同一个问题

[xqg1316]

好像不是，已反馈给社区这个问题了

[ChubChen]

使用With 语句insert into 的时候， 必须使用 labelName INSERT INTO dwd.dwd_inv_proj_corp_d WITH LABEL labelName WITH t1 AS()

[ScalaFirst]

使用With 语句insert into 的时候， 必须使用 labelName INSERT INTO dwd.dwd_inv_proj_corp_d WITH LABEL labelName WITH t1 AS()

你说的是对的，确实使用了 with label xxx 语法后，g4就能正常解析报错了。

[github-actions[bot]]

We're closing this issue because it hasn't been updated in a while. If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!