liujie-yt opened a new issue, #48295: URL: https://github.com/apache/doris/issues/48295
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 2.1.4 ### What's Wrong? SQL Error ``` with tmp0 as ( select a.*, case when b.parent_sfc is null then b.SFC else b.parent_sfc end as parent_sfc_tmp, b.parent_sfc as parent_sfc_tmp0 from ads.ads_mes_ppm_nc_data_qty_stat_i_rt a left join dim.dim_mes_cn_sfc_t b on split_part(a.NC_CONTEXT_GBO, ',', 2) = b.SFC where substr(a.DATE_TIME, 1, 10) between DATE_SUB('2025-02-25', INTERVAL 20 DAY) and '2025-02-25' ), tmp2 as (select b.line_name, a.nc_type, substr(a.DATE_TIME, 1, 10) as CALC_DATE, a.parent_sfc_tmp, sum(a.qty) as nc_num from tmp0 a, dim.dim_config_ppm_line_mapping_f_d b where a.resource = b.resource_id and a.operation = b.operation and split_part(NC_CONTEXT_GBO, ',', 2) not in (select distinct parent_sfc_tmp0 from tmp0 where parent_sfc_tmp0 is not null) group by b.line_name, a.nc_type, a.parent_sfc_tmp, substr(a.DATE_TIME, 1, 10) ), tmp3 as (select t1.p_day, t1.resource, t1.operation, t1.item, t1.s * t2.ts as total_num from (select a.*, split_part(split_part(b.BOM_BO, ':', 2), ',', 2) as BOM_INFO2, split_part(split_part(b.BOM_BO, ':', 2), ',', 4) as BOM_INFO4 from (select a.p_day, a.resource, a.operation, a.shop_order, a.item, sum(a.qty) as s from ads.ads_mes_ppm_activtiy_log_qty_stat_i_rt a where p_day between DATE_SUB('2025-02-25', INTERVAL 20 DAY) and '2025-02-25' group by a.p_day, a.resource, a.operation, a.shop_order, a.item) a left join dwd.dwd_mes_shop_order_detail_i_rt b on a.shop_order = b.shop_order) t1, (select b.BOM_INFO2, b.BOM_INFO4, b.OPERATION, sum(b.qty) as ts from ads.ads_mes_ppm_bom_oeration_qty_stat_i_rt b group by b.BOM_INFO2, b.BOM_INFO4, b.OPERATION) t2 where t1.BOM_INFO2 = t2.BOM_INFO2 and t1.BOM_INFO4 = t2.BOM_INFO4 and t1.OPERATION = t2.OPERATION ) select n.line_name, n.item, n.p_day, m.nc_num , n.total_num , now() as ETL_INSERT_TIME , now() as ETL_UPDATE_TIME from ( select t3.p_day, t4.line_name, t3.item, sum(t3.total_num) as total_num from tmp3 t3, dim.dim_config_ppm_line_mapping_f_d t4 where t3.resource = t4.resource_id and t3.operation = t4.operation group by t3.p_day, t4.line_name, t3.item ) n left join ( select a.line_name, b.ITEM as ITEM, a.CALC_DATE, sum(a.nc_num) nc_num from tmp2 a left join ( select split_part(b.NC_CONTEXT_GBO, ',', 2) sfc, item from ads.ads_mes_ppm_nc_data_qty_stat_i_rt group by split_part(b.NC_CONTEXT_GBO, ',', 2), item ) b on a.parent_sfc_tmp = b.sfc group by a.line_name, b.ITEM, a.CALC_DATE ) m on n.line_name = m.line_name and n.item = m.item and n.p_day = m.CALC_DATE ``` ``` SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 2: with tmp0 as ( ^ Encountered: IDENTIFIER Expected org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 2: with tmp0 as ( ^ Encountered: IDENTIFIER Expected at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:615) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:506) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:528) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:977) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4155) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:194) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5147) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:117) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLException: errCode = 2, detailMessage = Syntax error in line 2: with tmp0 as ( ^ Encountered: IDENTIFIER Expected at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:767) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:652) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) ... 11 more ``` ### What You Expected? execution sucess ### How to Reproduce? _No response_ ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
