[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #9320: [Bug] [task sql] execute a sql with dynamic table name

[GitBox <gi...@apache.org>]

github-actions[bot] commented on issue #9320:
URL: https://github.com/apache/dolphinscheduler/issues/9320#issuecomment-1085829598


   ### 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
   
   Hi. I have a problem when I submit a SQL task. My SQL have a dynamic table name. Because it's a partitioned table by postgresql.
   
   The task definition like below:
   ![image](https://user-images.githubusercontent.com/12369169/161258187-13c7eaa7-b407-4f3b-89f4-4fbdec22d64a.png)
   
   The declare SQL is:
   ```sql
   
   WITH 
   r00 AS (
    SELECT coalesce(rb_build_agg(cast(id as int)), rb_build('{}')) AS id_bitmap
    FROM ads.t_dmp_test_user
    WHERE (id not in (1,2,3) AND (part_day BETWEEN TO_CHAR(CURRENT_DATE-3000, 'YYYY-MM-DD') AND '2022-03-30' OR jkx_userid is not null))
   )
    INSERT INTO ads.t_dmp_test_tag_${abc} (tag_name,tag_id,tag_value,bit_list,etl_time,part_day) 
    SELECT '付费用户',123,'七日内付费用户',r00.id_bitmap,CURRENT_TIMESTAMP,TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') FROM r00
    ON CONFLICT(tag_name, tag_value, part_day)
    DO UPDATE SET (tag_name,tag_id,tag_value,bit_list,etl_time,part_day)=ROW(excluded.*)
   ```
   
   But I got a error when the task executed. The error info like below:
   ![image](https://user-images.githubusercontent.com/12369169/161259409-96634b17-a8af-45a3-8f18-e69690c316d0.png)
   
   The error message:
   ```
   [ERROR] 2022-04-01 19:27:05.672 [TaskLogInfo- - [taskAppId=TASK-5037179531040_7-267-584]]  - execute sql error: ERROR: relation "ads.t_dmp_test_tag_$1" does not exist
     Position: 269
   [ERROR] 2022-04-01 19:27:05.672 [TaskLogInfo- - [taskAppId=TASK-5037179531040_7-267-584]]  - sql task error: org.postgresql.util.PSQLException: ERROR: relation "ads.t_dmp_test_tag_$1" does not exist
     Position: 269
   ```
   
   ### What you expected to happen
   
   I hope SQL task can support execute a sql with dynamic table name.
   
   ### How to reproduce
   
   It can distinguish the different SQL part, such as table name, where conditions etc.
   
   ### Anything else
   
   _No response_
   
   ### Version
   
   2.0.3
   
   ### 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](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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org