You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by "chaz6chez (via GitHub)" <gi...@apache.org> on 2025/03/04 02:36:19 UTC
[I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
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: commits-unsubscribe@dolphinscheduler.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "chaz6chez (via GitHub)" <gi...@apache.org>.
chaz6chez commented on issue #17040:
URL: https://github.com/apache/dolphinscheduler/issues/17040#issuecomment-2696040407
The query type SQL too
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "SbloodyS (via GitHub)" <gi...@apache.org>.
SbloodyS commented on issue #17040:
URL: https://github.com/apache/dolphinscheduler/issues/17040#issuecomment-2777452424
> Hi [@SbloodyS](https://github.com/SbloodyS) i can help fix this issue
I've assigned to you. Looking forward to your contribution.
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "sdhzwc (via GitHub)" <gi...@apache.org>.
sdhzwc commented on issue #17040:
URL: https://github.com/apache/dolphinscheduler/issues/17040#issuecomment-2921055086
> Can we close this issue [@SbloodyS](https://github.com/SbloodyS) ?
cc
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "SbloodyS (via GitHub)" <gi...@apache.org>.
SbloodyS closed issue #17040: [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly.
URL: https://github.com/apache/dolphinscheduler/issues/17040
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "DhiyaneshwaranR (via GitHub)" <gi...@apache.org>.
DhiyaneshwaranR commented on issue #17040:
URL: https://github.com/apache/dolphinscheduler/issues/17040#issuecomment-2774867125
Hi @SbloodyS i can help fix this issue
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "chaz6chez (via GitHub)" <gi...@apache.org>.
chaz6chez commented on issue #17040:
URL: https://github.com/apache/dolphinscheduler/issues/17040#issuecomment-2709799827
3.1.9 Running Properly
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [SQL] PL/pgSQL scripts in version 3.x were incorrectly parsed, causing them to not run properly. [dolphinscheduler]
Posted by "DhiyaneshwaranR (via GitHub)" <gi...@apache.org>.
DhiyaneshwaranR commented on issue #17040:
URL: https://github.com/apache/dolphinscheduler/issues/17040#issuecomment-2839781681
Can we close this issue @SbloodyS ?
--
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-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org