dosubot[bot] commented on issue #8449:
URL:
https://github.com/apache/incubator-devlake/issues/8449#issuecomment-2908931285
<!-- Answer -->
You are correct in your analysis: the current SQL DELETE statement in
`link_pr_and_issue.go` is likely causing the issue where collecting data for
one project deletes all `pull_request_issues` entries for other projects. The
relevant code is as follows:
```sql
DELETE FROM pull_request_issues
WHERE pull_request_id IN (
SELECT pr.id
FROM pull_requests pr
LEFT JOIN project_mapping pm
ON pm.table = 'repos'
AND pm.row_id = pr.base_repo_id
AND pm.project_name = ?
)
```
This query uses a `LEFT JOIN` and filters by `pm.project_name = ?`, but
because it's a `LEFT JOIN` without a corresponding `WHERE` clause to ensure
only the intended project's records are affected, it can result in deleting
entries from other projects as well. This matches your observation and the
described behavior.
No similar issues or discussions were found in the repository, and there are
no recent changes addressing this in the codebase since v1.0.1.
**Suggested approach:**
To fix this, the query should ensure that only records associated with the
specific project are deleted. You can achieve this by using an `INNER JOIN` or
by adding a `WHERE` clause to the subquery to strictly filter by the project.
For example:
```sql
DELETE FROM pull_request_issues
WHERE pull_request_id IN (
SELECT pr.id
FROM pull_requests pr
INNER JOIN project_mapping pm
ON pm.table = 'repos'
AND pm.row_id = pr.base_repo_id
WHERE pm.project_name = ?
)
```
This change ensures that only `pull_request_issues` linked to pull requests
belonging to the specified project are deleted, preventing data loss from other
projects.
You can find the relevant code and logic in the [`clearHistoryData` function
in
link_pr_and_issue.go](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/linker/tasks/link_pr_and_issue.go).
Let me know if you need further details or guidance on implementing this
change.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://go.dosu.dev/dosubot), mention
@dosu-bot.*
---
<sup>Help Dosu learn! Give it feedback: [Great
Response](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=great_response)
| [Irrelevant
Answer](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=irrelevant_answer)
| [Incorrect
Sources](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=incorrect_sources)
| [Too
Verbose](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=too_verbose)
|
[Hallucination](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=hallucination)
| [Bug
Report](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=bug_report)
|
[Other](https://app.dosu.dev/response-feedback/e8965d7b-7170-4fd5-a902-29d383535fff?feedback_type=other)</sup>
--
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: dev-unsubscr...@devlake.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
