dosubot[bot] commented on issue #8834: URL: https://github.com/apache/incubator-devlake/issues/8834#issuecomment-4238262168
<!-- Greeting --> Hi @ciaramulligan! I'm [Dosu](https://go.dosu.dev/dosubot) and Iām helping the incubator-devlake team. <!-- Answer --> Good investigation ā I was able to trace this to the converter's query logic. The root cause is in [`issue_changelog_convertor.go`](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/jira/tasks/issue_changelog_convertor.go#L90-L102). The query uses a **LEFT JOIN** to `_tool_jira_board_issues`, but then the **WHERE clause** filters on `_tool_jira_board_issues.board_id = ?`: ```sql LEFT JOIN _tool_jira_board_issues ON ( _tool_jira_board_issues.connection_id = _tool_jira_issue_changelogs.connection_id AND _tool_jira_board_issues.issue_id = _tool_jira_issue_changelogs.issue_id ) WHERE _tool_jira_issue_changelog_items.connection_id = ? AND _tool_jira_board_issues.board_id = ? ``` This effectively turns the LEFT JOIN into an **INNER JOIN** ā any changelog item whose issue is not present in `_tool_jira_board_issues` for the specific board being processed is silently dropped. No error, no warning. So the ~21% of changelog items being skipped likely belong to issues that were **collected** (their changelogs exist in the tool layer) but are **not associated with the board** in `_tool_jira_board_issues` at conversion time. This can happen when: 1. The board's JQL filter doesn't match all issues that have changelogs in the tool layer 2. Issues moved between boards or lost board membership between collection runs 3. Changelogs were collected via a different path (e.g., epic-sourced issues) than the board-scoped issue list This is not isolated to changelogs ā the same LEFT JOIN + WHERE anti-pattern appears in at least 8 other converter files across the Jira, DORA, and Linker plugins [[1]](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/linker/tasks/link_pr_and_issue.go#L72-L75) [[2]](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/dora/tasks/incident_from_issue_generator.go#L85-L93), meaning the same class of silent data loss can affect other domain tables too. A minimal fix for changelogs would be to either: - Change the LEFT JOIN to an explicit `INNER JOIN` (making the filtering intentional and obvious), or - Move the `board_id` condition into the JOIN's ON clause so the LEFT JOIN semantics are preserved and non-board issues are still converted And adding a log/warning when items are filtered by board scope would address the "silent" part of the problem. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=brand-link).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-great_response#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-irrelevant_answer#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-incorrect_sources#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-too_verbose#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=too_verbose) | [Hallucination](https:// app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-hallucination#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=hallucination) | [Report š](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-bug_report#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-other#message_id=6f0e55c3-fd43-4b88-846d-d39842717fed&feedback_type=other)</sup> [](https://app.dosu.dev/b4e8e847-d479-4541-83a8-d88d83fea5c9/ask?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=chat-badge) [](https://cursor.com/link/prompt?text=Good%20investigation%20%E2%80%94%20I%20was%20able%20to%20trace%20this%20to%20the%20converter%27s%20query%20logic.%0A%0AThe%20root%20cause%20is%20in%20%5B%60issue_changelog_convertor.go%60%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/jira/tasks/issue_changelog_convertor.go%23L90-L102%29.%20The%20query%20uses%20a%20%2A%2ALEFT%20JOIN%2A%2A%20to%20%60_tool_jira_board_issues%60%2C%20but%20then%20the%20%2A%2AWHERE%20clause%2A%2A%20filters%20on%20%60_tool_jira_board_issues.board_id%20%3D%20%3F%60%3A%0A%0A%60%60%60sql%0ALEFT%20JOIN%20_tool_jira_board_issues%20ON%20%28%0A%20%20%20%20_tool_jira_board_issues.connectio n_id%20%3D%20_tool_jira_issue_changelogs.connection_id%0A%20%20%20%20AND%20_tool_jira_board_issues.issue_id%20%3D%20_tool_jira_issue_changelogs.issue_id%0A%29%0AWHERE%20_tool_jira_issue_changelog_items.connection_id%20%3D%20%3F%0A%20%20AND%20_tool_jira_board_issues.board_id%20%3D%20%3F%0A%60%60%60%0A%0AThis%20effectively%20turns%20the%20LEFT%20JOIN%20into%20an%20%2A%2AINNER%20JOIN%2A%2A%20%E2%80%94%20any%20changelog%20item%20whose%20issue%20is%20not%20present%20in%20%60_tool_jira_board_issues%60%20for%20the%20specific%20board%20being%20processed%20is%20silently%20dropped.%20No%20error%2C%20no%20warning.%0A%0ASo%20the%20~21%25%20of%20changelog%20items%20being%20skipped%20likely%20belong%20to%20issues%20that%20were%20%2A%2Acollected%2A%2A%20%28their%20changelogs%20exist%20in%20the%20tool%20layer%29%20but%20are%20%2A%2Anot%20associated%20with%20the%20board%2A%2A%20in%20%60_tool_jira_board_issues%60%20at%20conversion%20time.%20This%20can%20happen%20when%3A%0A%0A1.%20The%20board%27s%20JQ L%20filter%20doesn%27t%20match%20all%20issues%20that%20have%20changelogs%20in%20the%20tool%20layer%0A2.%20Issues%20moved%20between%20boards%20or%20lost%20board%20membership%20between%20collection%20runs%0A3.%20Changelogs%20were%20collected%20via%20a%20different%20path%20%28e.g.%2C%20epic-sourced%20issues%29%20than%20the%20board-scoped%20issue%20list%0A%0AThis%20is%20not%20isolated%20to%20changelogs%20%E2%80%94%20the%20same%20LEFT%20JOIN%20%2B%20WHERE%20anti-pattern%20appears%20in%20at%20least%208%20other%20converter%20files%20across%20the%20Jira%2C%20DORA%2C%20and%20Linker%20plugins%20%5B%5B1%5D%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/linker/tasks/link_pr_and_issue.go%23L72-L75%29%20%5B%5B2%5D%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/dora/tasks/incident_from_issue_generator.go%23L85-L93%29%2C%20meaning%20the%20same%20class%20of%20silent%20d ata%20loss%20can%20affect%20other%20domain%20tables%20too.%0A%0AA%20minimal%20fix%20for%20changelogs%20would%20be%20to%20either%3A%0A%0A-%20Change%20the%20LEFT%20JOIN%20to%20an%20explicit%20%60INNER%20JOIN%60%20%28making%20the%20filtering%20intentional%20and%20obvious%29%2C%20or%0A-%20Move%20the%20%60board_id%60%20condition%20into%20the%20JOIN%27s%20ON%20clause%20so%20the%20LEFT%20JOIN%20semantics%20are%20preserved%20and%20non-board%20issues%20are%20still%20converted%0A%0AAnd%20adding%20a%20log/warning%20when%20items%20are%20filtered%20by%20board%20scope%20would%20address%20the%20%22silent%22%20part%20of%20the%20problem.) [](https://go.dosu.dev/discord-bot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=join-discord) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%2 0this%20issue!&url=https%3A//github.com/apache/incubator-devlake/issues/8834) -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
