[Bug] [task sql] execute a sql with dynamic table name #9320
Description
Search before asking
- I had searched in the issues 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:
The declare SQL is:
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:
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?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct