chaz6chez opened a new issue, #17040: URL: https://github.com/apache/dolphinscheduler/issues/17040
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues. ### What happened When I select the PostgreSQL data source in my workflow and use the non-query type to execute the following PL/pgSQL script: ``` DO $DO$ DECLARE offset_value INTEGER := 0; fetch_size INTEGER := 10000; cur RECORD; BEGIN LOOP FOR cur IN SELECT (adsp::json ->> 'merchantid') as merchantid, COUNT(DISTINCT CASE WHEN event_code = 'merchant_pvv' THEN utdid END) AS exposure_num, COUNT(CASE WHEN event_code = 'merchant_pvv' THEN __time__ END) AS exposure_cnt, COUNT(DISTINCT CASE WHEN event_code = 'merchant_click' THEN utdid END) AS visit_num, COUNT(CASE WHEN event_code = 'merchant_click' THEN __time__ END) AS visit_cnt, COUNT(DISTINCT CASE WHEN event_code = 'merchant_collect' THEN utdid END) AS collect_num, COUNT(DISTINCT CASE WHEN event_code = 'merchant_intention' THEN utdid END) AS intention_num, DATE_TRUNC('day', TO_TIMESTAMP(__time__)) AS tag, 'daily' AS type FROM public.djdj_qt_log_holo WHERE TO_TIMESTAMP(__time__) >= TO_TIMESTAMP(EXTRACT(EPOCH FROM CURRENT_DATE - INTERVAL '1 day')) AND TO_TIMESTAMP(__time__) < TO_TIMESTAMP(EXTRACT(EPOCH FROM CURRENT_DATE)) GROUP BY merchantid, tag ORDER BY merchantid, tag LIMIT fetch_size OFFSET offset_value LOOP IF cur.merchantid IS NOT NULL THEN INSERT INTO public.multi_app_statistics ( merchantid, exposure_num, exposure_cnt, visit_num, visit_cnt, collect_num, intention_num, tag, type ) VALUES ( cur.merchantid, cur.exposure_num, cur.exposure_cnt, cur.visit_num, cur.visit_cnt, cur.collect_num, cur.intention_num, cur.tag, cur.type ) ON CONFLICT (merchantid, type, tag) DO UPDATE SET exposure_num = EXCLUDED.exposure_num, exposure_cnt = EXCLUDED.exposure_cnt, visit_num = EXCLUDED.visit_num, visit_cnt = EXCLUDED.visit_cnt, collect_num = EXCLUDED.collect_num, intention_num = EXCLUDED.intention_num, type = EXCLUDED.type; END IF; END LOOP; offset_value := offset_value + fetch_size; IF NOT FOUND THEN EXIT; END IF; END LOOP; END $DO$; ``` It fails to run. ### What you expected to happen According to the logs, I think the issue was caused by splitting the complete PL/pgSQL error during SQL parsing. ``` [INFO] 2025-03-03 13:27:12.221 +0800 - sql type : POSTGRESQL, datasource : 2, sql : DO $DO$ DECLARE offset_value INTEGER := 0; fetch_size INTEGER := 10000; cur RECORD; BEGIN LOOP FOR cur IN SELECT (adsp::json ->> 'merchantid') as merchantid, COUNT(DISTINCT CASE WHEN event_code = 'merchant_pvv' THEN utdid END) AS exposure_num, COUNT(CASE WHEN event_code = 'merchant_pvv' THEN __time__ END) AS exposure_cnt, COUNT(DISTINCT CASE WHEN event_code = 'merchant_click' THEN utdid END) AS visit_num, COUNT(CASE WHEN event_code = 'merchant_click' THEN __time__ END) AS visit_cnt, COUNT(DISTINCT CASE WHEN event_code = 'merchant_collect' THEN utdid END) AS collect_num, COUNT(DISTINCT CASE WHEN event_code = 'merchant_intention' THEN utdid END) AS intention_num, DATE_TRUNC('day', TO_TIMESTAMP(__time__)) AS tag, 'daily' AS type FROM public.djdj_qt_log_holo GROUP BY merchantid, tag ORDER BY merchantid, tag LIMIT fetch_size OFFSET offset_value LOOP IF cur.merchantid IS NOT NULL THEN INSERT INTO public.multi_app_statistics (merchantid, exposure_num, exposure_cnt, visit_num, visit_cnt, collect_num, intention_num, tag, type) VALUES (cur.merchantid, cur.exposure_num, cur.exposure_cnt, cur.visit_num, cur.visit_cnt, cur.collect_num, cur.intention_num, cur.tag, cur.type) ON CONFLICT (merchantid, type, tag) DO UPDATE SET exposure_num = EXCLUDED.exposure_num, exposure_cnt = EXCLUDED.exposure_cnt, visit_num = EXCLUDED.visit_num, visit_cnt = EXCLUDED.visit_cnt, collect_num = EXCLUDED.collect_num, intention_num = EXCLUDED.intention_num, type = EXCLUDED.type; END IF; END LOOP; offset_value := offset_value + fetch_size; IF NOT FOUND THEN EXIT; END IF; END LOOP; END $DO$; , localParams : [],udfs : null,showType : null,connParams : null,varPool : [] ,query max result limit 0 [INFO] 2025-03-03 13:27:12.222 +0800 - after replace sql , preparing : DO $DO$ DECLARE offset_value INTEGER := 0 [INFO] 2025-03-03 13:27:12.222 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.222 +0800 - after replace sql , preparing : fetch_size INTEGER := 10000 [INFO] 2025-03-03 13:27:12.222 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.222 +0800 - after replace sql , preparing : cur RECORD [INFO] 2025-03-03 13:27:12.222 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.223 +0800 - after replace sql , preparing : BEGIN LOOP FOR cur IN SELECT (adsp::json ->> 'merchantid') as merchantid, COUNT(DISTINCT CASE WHEN event_code = 'merchant_pvv' THEN utdid END) AS exposure_num, COUNT(CASE WHEN event_code = 'merchant_pvv' THEN __time__ END) AS exposure_cnt, COUNT(DISTINCT CASE WHEN event_code = 'merchant_click' THEN utdid END) AS visit_num, COUNT(CASE WHEN event_code = 'merchant_click' THEN __time__ END) AS visit_cnt, COUNT(DISTINCT CASE WHEN event_code = 'merchant_collect' THEN utdid END) AS collect_num, COUNT(DISTINCT CASE WHEN event_code = 'merchant_intention' THEN utdid END) AS intention_num, DATE_TRUNC('day', TO_TIMESTAMP(__time__)) AS tag, 'daily' AS type FROM public.djdj_qt_log_holo GROUP BY merchantid, tag ORDER BY merchantid, tag LIMIT fetch_size OFFSET offset_value LOOP IF cur.merchantid IS NOT NULL THEN INSERT INTO public.multi_app_statistics (merchantid, exposure_num, exposure_cnt, visit_num, visit_cnt, collect_num, intention_num, tag, type) VALUES (cur.merchantid, cur.exposure_num, cur.exposure_cnt, cur.visit_num, cur.visit_cnt, cur.collect_num, cur.intention_num, cur.tag, cur.type) ON CONFLICT (merchantid, type, tag) DO UPDATE SET exposure_num = EXCLUDED.exposure_num, exposure_cnt = EXCLUDED.exposure_cnt, visit_num = EXCLUDED.visit_num, visit_cnt = EXCLUDED.visit_cnt, collect_num = EXCLUDED.collect_num, intention_num = EXCLUDED.intention_num, type = EXCLUDED.type [INFO] 2025-03-03 13:27:12.223 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.223 +0800 - after replace sql , preparing : END IF [INFO] 2025-03-03 13:27:12.223 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.223 +0800 - after replace sql , preparing : END LOOP [INFO] 2025-03-03 13:27:12.224 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.224 +0800 - after replace sql , preparing : offset_value := offset_value + fetch_size [INFO] 2025-03-03 13:27:12.224 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.224 +0800 - after replace sql , preparing : IF NOT FOUND THEN EXIT [INFO] 2025-03-03 13:27:12.224 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.224 +0800 - after replace sql , preparing : END IF [INFO] 2025-03-03 13:27:12.224 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.224 +0800 - after replace sql , preparing : END LOOP [INFO] 2025-03-03 13:27:12.224 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.224 +0800 - after replace sql , preparing : END $DO$ [INFO] 2025-03-03 13:27:12.224 +0800 - Sql Params are replaced sql , parameters: [INFO] 2025-03-03 13:27:12.224 +0800 - can't find udf function resource [WARN] 2025-03-03 13:27:12.225 +0800 - Connect strings must start with jdbc:snowflake:// [ERROR] 2025-03-03 13:27:12.325 +0800 - execute sql error: SQL task prepareStatementAndBind error [ERROR] 2025-03-03 13:27:12.325 +0800 - sql task error org.apache.dolphinscheduler.plugin.task.api.TaskException: SQL task prepareStatementAndBind error at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.prepareStatementAndBind(SqlTask.java:396) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeQuery(SqlTask.java:315) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:205) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:159) at org.apache.dolphinscheduler.server.worker.runner.DefaultWorkerTaskExecutor.executeTask(DefaultWorkerTaskExecutor.java:54) at org.apache.dolphinscheduler.server.worker.runner.WorkerTaskExecutor.run(WorkerTaskExecutor.java:175) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 4 in SQL DO $DO$ DECLARE offset_value INTEGER := 0. Expected terminating $$ at org.postgresql.core.Parser.checkParsePosition(Parser.java:1380) at org.postgresql.core.Parser.parseSql(Parser.java:1279) at org.postgresql.core.Parser.replaceProcessing(Parser.java:1231) at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43) at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19) at org.postgresql.util.LruCache.borrow(LruCache.java:123) at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:296) at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:196) at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:88) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1325) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1779) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:454) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.prepareStatementAndBind(SqlTask.java:380) ... 8 common frames omitted [ERROR] 2025-03-03 13:27:12.325 +0800 - Task execute failed, due to meet an exception org.apache.dolphinscheduler.plugin.task.api.TaskException: Execute sql task failed at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:166) at org.apache.dolphinscheduler.server.worker.runner.DefaultWorkerTaskExecutor.executeTask(DefaultWorkerTaskExecutor.java:54) at org.apache.dolphinscheduler.server.worker.runner.WorkerTaskExecutor.run(WorkerTaskExecutor.java:175) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.dolphinscheduler.plugin.task.api.TaskException: SQL task prepareStatementAndBind error at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.prepareStatementAndBind(SqlTask.java:396) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeQuery(SqlTask.java:315) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:205) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:159) ... 5 common frames omitted Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 4 in SQL DO $DO$ DECLARE offset_value INTEGER := 0. Expected terminating $$ at org.postgresql.core.Parser.checkParsePosition(Parser.java:1380) at org.postgresql.core.Parser.parseSql(Parser.java:1279) at org.postgresql.core.Parser.replaceProcessing(Parser.java:1231) at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43) at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19) at org.postgresql.util.LruCache.borrow(LruCache.java:123) at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:296) at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:196) at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:88) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1325) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1779) at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:454) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.prepareStatementAndBind(SqlTask.java:380) ... 8 common frames omitted [INFO] 2025-03-03 13:27:12.325 +0800 - Get appIds from worker dolphinscheduler-worker-2.dolphinscheduler-worker-headless:1234, taskLogPath: /opt/dolphinscheduler/logs/20250303/16708144210528/8/31/43.log [INFO] 2025-03-03 13:27:12.326 +0800 - Start finding appId in /opt/dolphinscheduler/logs/20250303/16708144210528/8/31/43.log, fetch way: log [INFO] 2025-03-03 13:27:12.326 +0800 - The appId is empty [INFO] 2025-03-03 13:27:12.326 +0800 - Cancel the task successfully [INFO] 2025-03-03 13:27:12.327 +0800 - Get a exception when execute the task, will send the task status: FAILURE to master: dolphinscheduler-worker-2.dolphinscheduler-worker-headless:1234 [INFO] 2025-03-03 13:27:12.327 +0800 - FINALIZE_SESSION ``` ### How to reproduce The issue can be reproduced simply by having multiple semicolons in the non-query SQL that gets executed. ### Anything else _No response_ ### Version 3.2.x ### 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
