chenwei791129 opened a new issue, #9225: URL: https://github.com/apache/devlake/issues/9225
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/devlake/issues?q=is%3Aissue) and found no similar issues. ### What happened The PR time panels of the **Engineering Throughput and Cycle Time** dashboards treat a merged PR that has no row in `project_pr_metrics` as a PR that took **0 hours**. The PR should be left out of the average instead. Affected panels (MySQL and PostgreSQL variants of each dashboard): | Dashboard | Panels | | --- | --- | | `engineering-throughput-and-cycle-time.json` | PR Cycle Time, PR Coding Time, PR Pickup Time, PR Review Time, PR Deploy Time | | `engineering-throughput-and-cycle-time-team-view.json` | Total Cycle Time(h), PR Coding Time(h), PR Pickup Time(h), PR Review Time(h), PR Deploy Time(h) | All ten queries have the same shape (MySQL version of *PR Cycle Time*, trimmed): ```sql with _prs as ( SELECT pr.id, pr.merged_date as pr_merged_date, coalesce(prm.pr_cycle_time/60, 0) as cycle_time -- a missing row becomes 0 FROM pull_requests pr left join project_pr_metrics prm on pr.id = prm.id join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' WHERE $__timeFilter(pr.merged_date) and pm.project_name in (${project}) GROUP BY 1,2,3 ) SELECT ..., avg(cycle_time) FROM _prs GROUP BY ... ``` The set of PRs and the time axis come from `pull_requests`, while the metric values come from `project_pr_metrics`. Because of `LEFT JOIN` + `coalesce(..., 0)`, a merged PR without a `project_pr_metrics` row enters `avg()` as `0` and pulls the average down: ```text pull_requests project_pr_metrics value used by the panel ───────────── ────────────────── ─────────────────────── PR A (merged) ──▶ pr_cycle_time = 14h ──▶ 14h PR B (merged) ──▶ pr_cycle_time = 10h ──▶ 10h PR C (merged) ──▶ (no row) ──▶ 0h ◀ should be excluded avg = 8h (expected: 12h) ``` `project_pr_metrics` is written only by the DORA plugin (`calculateChangeLeadTime` in `backend/plugins/dora/tasks/change_lead_time_calculator.go`). The most common way to hit this bug is a project with DORA metrics disabled. In that case `pull_requests` has merged PRs, but `project_pr_metrics` has no rows for them, so all five panels show a flat **0 h**. It looks as if every PR was finished instantly, when in fact no PR time data is available. The dashboard's "Data Sources Required" text does not mention DORA, so users have no hint about the cause. The bug depends only on whether a PR has a matching `project_pr_metrics` row. The reason the row is missing does not matter. ### What do you expect to happen PRs without a `project_pr_metrics` row should be left out of these averages (removed from the denominator) instead of being counted as 0. If no PR in the selected range has a row (e.g. DORA is not enabled for the project), the panels should show "No data" instead of 0 h. ### How to reproduce 1. Create a project with a GitHub or GitLab repository and keep **DORA metrics disabled** in the project settings. 2. Collect data so that `pull_requests` contains merged PRs. 3. Open **Engineering Throughput and Cycle Time** (or its Team View) and select the project. 4. PR Cycle / Coding / Pickup / Review / Deploy Time show 0 h in every period, even though there are merged PRs. To count the merged PRs that these panels average in as 0: ```sql SELECT count(*) FROM pull_requests pr JOIN project_mapping pm ON pr.base_repo_id = pm.row_id AND pm.`table` = 'repos' LEFT JOIN project_pr_metrics prm ON pr.id = prm.id WHERE pr.merged_date IS NOT NULL AND pm.project_name = '<your project>' AND prm.id IS NULL; ``` ### Anything else **Proposed fix** (open to discussion) - In the 20 affected queries (2 dashboards × 5 panels × MySQL/PostgreSQL), change `LEFT JOIN project_pr_metrics` to an inner `JOIN`. - Keep the existing `coalesce(..., 0)` on the per-row values. The comment above it says the goal is to "make sure cycle_time equals the sum of the four metrics". A row can still have a NULL coding, pickup, review or deploy time, and those values are still treated as 0. All five panels still average over the same set of PRs (those that have a row), so this intent is preserved. The only change is that PRs with no row at all are dropped. - When DORA is enabled, every merged PR in the project gets a row, so those projects see no change in values. Only the "no row" case changes, from 0 h to excluded (or "No data"). **Not affected** (no change needed) - `dora-details-lead-timefor-changes.json` uses the same `LEFT JOIN` + `coalesce` pattern, but filters on `$__timeFilter(prm.pr_deployed_date)`, which already drops PRs without a row. - `DORA.json`, `dora-by-team.json`, `kiro-dora.json`, `kiro-credits-dora.json`, `github-copilot-dora-correlation.json` and `monorepo-subprojects.json` either inner-join or select directly from `project_pr_metrics`, or `AVG()` the nullable column without `coalesce` (NULLs are skipped). **Related** - #7398 reported PR Cycle Time showing 0 when data is incomplete. This issue is a different case: the PR has no `project_pr_metrics` row at all. ### Version v1.0.3-beta15 (the same queries are still on `main` at 4ad2f66df) ### 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
