apache / apache/devlake

[Bug][gh-copilot] PostgreSQL adoption dashboard compares boolean columns to integer, panels render "No data"

Closed Beginner friendly
#9,126 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
3.1k
Forks
808
Avg merge
1d 8h
Merged PRs (30d)
49

Description

## Search before asking

- [x] I had searched in the issues and found no similar issues.

---

## What happened

On PostgreSQL, three panels in the **GitHub Copilot Adoption** dashboard
(`grafana/dashboards/postgresql/github-copilot-adoption.json`) always render
**"No data"**, even when the underlying tables are fully populated:

- Panel 12 — *Agent Mode Adopters*
- Panel 13 — *Chat Adopters*
- Panel 30 — *Agent Users vs Chat Users Trend*

All three filter on `used_agent` / `used_chat` using an integer comparison:

```sql
-- panel 12
SELECT COUNT(DISTINCT user_login) AS "Agent Users"
FROM _tool_copilot_user_daily_metrics
WHERE $__timeFilter(day) AND ... AND used_agent = 1

-- panel 13
... AND used_chat = 1

-- panel 30
COUNT(DISTINCT CASE WHEN used_agent = 1 THEN user_login END) AS "Agent Users",
COUNT(DISTINCT CASE WHEN used_chat = 1 THEN user_login END) AS "Chat Users"
```

But the PostgreSQL migration declares these columns as native `boolean`:

```
\d _tool_copilot_user_daily_metrics

used_agent | boolean
used_chat | boolean
```

So the query aborts before returning rows:

```
ERROR: operator does not exist: boolean = integer
LINE 1: ... AND used_agent = 1;
^
HINT: No operator matches the given name and argument types.
You might need to add explicit type casts.
```

Grafana surfaces this as an empty panel rather than a visible error, so it
looks like a collection or credentials problem rather than a dashboard defect.
Neighbouring panels that don't touch these two columns (e.g. panel 11
*Unique Users (Period)*) render correctly against the same data, which makes
the failure easy to misattribute.

This appears to date back to the MySQL -> PostgreSQL dashboard port in
`31ea5303` ("Grafana postgresql support", #8870), where `tinyint(1) = 1` was
carried over literally. It is still present on `main` today:

```console
$ gh api "repos/apache/devlake/contents/grafana/dashboards/postgresql/github-copilot-adoption.json?ref=main" \
--jq '.content' | base64 -d | grep -o "used_[a-z_]* = 1" | sort | uniq -c
2 used_agent = 1
2 used_chat = 1
```

Only this one file is affected; no other provisioned PostgreSQL dashboard
references these columns.

---

## What do you expect to happen

*Agent Mode Adopters*, *Chat Adopters*, and *Agent Users vs Chat Users Trend*
should show distinct user counts, consistent with the other adoption panels
sourced from the same table.

On a populated dataset the panels render blank, while the equivalent
boolean-safe query returns non-zero counts:

```sql
SELECT COUNT(DISTINCT user_login) FILTER (WHERE used_agent) AS agent_users,
COUNT(DISTINCT user_login) FILTER (WHERE used_chat) AS chat_users,
COUNT(DISTINCT user_login) AS unique_users
FROM _tool_copilot_user_daily_metrics
WHERE day > now() - interval '90 day'
AND connection_id = 1
AND scope_id = '';
```

```
agent_users | chat_users | unique_users
-------------+------------+--------------
| |
```

Expected: the same non-zero counts the boolean-safe query returns.
Actual: "No data" for all three panels.

---

## How to reproduce

1. Deploy DevLake with a **PostgreSQL** backend (`DB_URL=postgres://...`).
2. Configure a `gh-copilot` connection and run a full blueprint so that
`_tool_copilot_user_daily_metrics` is populated with at least one row where
`used_agent = true` and one where `used_chat = true`.
3. Open the **GitHub Copilot Adoption** dashboard in Grafana and select the
matching `connection_id` / `scope_id` template variables.
4. Panels *Agent Mode Adopters*, *Chat Adopters* and *Agent Users vs Chat Users
Trend* show "No data", while *Unique Users (Period)* on the same table
returns a non-zero count.
5. Running either panel's SQL directly against the database reproduces the
error: `operator does not exist: boolean = integer`.

---

## Anything else

Happens every time on PostgreSQL — it is a static type error in the SQL, not
data-dependent. MySQL deployments are unaffected, since `tinyint(1) = 1` is
valid there.

A cross-dialect-safe fix would be `used_agent IS TRUE` / `used_chat IS TRUE`,
which is valid in both PostgreSQL and MySQL, so the MySQL and PostgreSQL
dashboard variants can stay in sync. Dropping the comparison entirely
(`AND used_agent`) also works on PostgreSQL. Four occurrences in the one file.

It may be worth grepping the other ported PostgreSQL dashboards for the same
` = 1` pattern, given the shared origin in #8870. Related
past MySQL-ism ports: #8778 and #8835.

---

## Version

v1.0.3-beta17 (also reproduced on `main`)

---

## Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

---

## Code of Conduct

- [x] I agree to follow this project's Code of Conduct

Contributor guide

No contributing guide indexed for this repository

Research direction

Open grafana/dashboards/postgresql/github-copilot-adoption.json and inspect the queries for panels 12, 13, and 30, starting with the used_agent and used_chat filters. Compare the PostgreSQL boolean columns with the existing SQL and verify the affected panels against a populated PostgreSQL dataset. Done means all four integer comparisons are boolean-safe and the three panels show the expected user counts without breaking MySQL.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
data-visualization, databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
88/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.