No target tables in UPDATE statement using CTE #453
Description
SQL statement (pure UPDATE) (see below). After parsing in parser_result target_tables are empty!
WITH data_table AS (
SELECT
id,
user_id,
recommended_vacancies,
push_dtm,
push_check_datetime
FROM dap_pushs_multyrec_sent
WHERE
status = 'CHECKED'
AND push_dtm > NOW() - INTERVAL '2 months'
AND push_success = 0
AND (push_success_notimeframe_total IS NULL OR push_success_notimeframe_total = 0)
),
-----
unnest_vacs AS (
SELECT user_id, UNNEST (recommended_vacancies) AS vacancy_id FROM data_table
),
----
activities_count_table AS (
SELECT * FROM unnest_vacs
LEFT JOIN LATERAL(
SELECT
count (*) AS num_activities_vacancy,
min(ewa_created_on) AS reaction_dtm
FROM dap_ewa
WHERE ewa_executor_id = unnest_vacs.user_id
AND ewa_vacancy_id = unnest_vacs.vacancy_id
) activities ON TRUE),
-----
data_with_actcount_unnested AS (
SELECT
id, data_table.user_id, recommended_vacancies, vacancy_id, num_activities_vacancy, reaction_dtm,
data_table.push_dtm, data_table.push_check_datetime
FROM data_table
LEFT JOIN activities_count_table ON activities_count_table.vacancy_id = ANY (data_table.recommended_vacancies) AND data_table.user_id = activities_count_table.user_id
),
----
result_table AS (
SELECT
id, user_id,
recommended_vacancies,
array_agg((num_activities_vacancy>0)::integer) AS push_success_notimeframe,
sum((num_activities_vacancy>0)::integer) push_success_notimeframe_total,
array_agg (reaction_dtm AT time ZONE 'UTC-3' - push_dtm) AS reaction_time_notimeframe
FROM data_with_actcount_unnested
GROUP BY id, user_id, recommended_vacancies)
----
UPDATE dap_pushs_multyrec_sent
SET
push_success_notimeframe = result_table.push_success_notimeframe,
push_success_notimeframe_total = result_table.push_success_notimeframe_total,
reaction_time_notimeframe = result_table.reaction_time_notimeframe
FROM result_table
WHERE result_table.id = dap_pushs_multyrec_sent.id;
parser_result = LineageRunner(sql_query, dialect = 'postgres')