I have some questions about the queries used by phpbt. Specifically, the "Bugs assigned/reported to/by me" stock queries as well as those built from the Query page.
I am using phpbt 0.7.2.
We currently have about 100 bugs across 4 projects, and 12 users. My personal account ('jbecker', user_id=4) has 8 bugs assigned to it. The two numbers in the "bugs assigned to me" are 5 and 3, the query lists "1 - 8 of 8" below the table listing the bugs, and I can verify the number via this query (which also returns "8"):
select count(*) from bug,auth_user where bug.assigned_to=auth_user.user_id and auth_user.login='jbecker';
The problem is this: When I click on the (open bugs) "Bugs Assigned to me:" link (5 in this case), I only see 4 bugs. Furthermore, one of the bugs shown is actually marked as "Closed", and two others are marked "fixed". All bugs are assigned to me, and cover several severities, priorities, reporters, and projects.
This confused me, and so I did some nosing around in query.php, and found this SQL query that returns the results in question (I took the liberty of substituting the .TBL_AUTH_UER entries to see what actually gets sent):
select
b.*, reporter.login as reporter, owner.login as owner,
lastmodifier.login as lastmodifier, project_name, severity_name, severity_color,
status_name, os_name, version_name, component_name, resolution_name
from bug as b
left join auth_user owner on b.assigned_to = owner.user_id
left join auth_user reporter on b.created_by = reporter.user_id
left join auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id
left join resolution resolution on b.resolution_id = resolution.resolution_id,
severity severity, status status, os os, version version,
component component, project project
where
b.severity_id = severity.severity_id
and b.status_id = status.status_id
and b.os_id = os.os_id
and b.version_id = version.version_id
and b.component_id = component.component_id
and b.project_id = project.project_id
and assigned_to=4;
Now, before I found that query, I wrote this query on my own, which actually returns all 8 bugs assigned to me (I don't claim to be an SQL guru, so apologies if I'm doing something hideously wrong):
select
bug_id, status_name, resolution_name
from
project as p,
bug as b,
auth_user as u,
severity as sv,
status as st,
resolution as r
where
b.assigned_to=4
and u.login='jbecker'
and b.severity_id=sv.severity_id
and st.status_id=b.status_id
and p.project_id=b.project_id
and r.resolution_id=b.resolution_id
and b.resolution_id is not NULL
and b.resolution_id!=1;
I'm wondering if there's something I'm missing somewhere, or if I am just expecting the queries to do the wrong thing...
TIA!
--Jesse
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have some questions about the queries used by phpbt. Specifically, the "Bugs assigned/reported to/by me" stock queries as well as those built from the Query page.
I am using phpbt 0.7.2.
We currently have about 100 bugs across 4 projects, and 12 users. My personal account ('jbecker', user_id=4) has 8 bugs assigned to it. The two numbers in the "bugs assigned to me" are 5 and 3, the query lists "1 - 8 of 8" below the table listing the bugs, and I can verify the number via this query (which also returns "8"):
select count(*) from bug,auth_user where bug.assigned_to=auth_user.user_id and auth_user.login='jbecker';
The problem is this: When I click on the (open bugs) "Bugs Assigned to me:" link (5 in this case), I only see 4 bugs. Furthermore, one of the bugs shown is actually marked as "Closed", and two others are marked "fixed". All bugs are assigned to me, and cover several severities, priorities, reporters, and projects.
This confused me, and so I did some nosing around in query.php, and found this SQL query that returns the results in question (I took the liberty of substituting the .TBL_AUTH_UER entries to see what actually gets sent):
select
b.*, reporter.login as reporter, owner.login as owner,
lastmodifier.login as lastmodifier, project_name, severity_name, severity_color,
status_name, os_name, version_name, component_name, resolution_name
from bug as b
left join auth_user owner on b.assigned_to = owner.user_id
left join auth_user reporter on b.created_by = reporter.user_id
left join auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id
left join resolution resolution on b.resolution_id = resolution.resolution_id,
severity severity, status status, os os, version version,
component component, project project
where
b.severity_id = severity.severity_id
and b.status_id = status.status_id
and b.os_id = os.os_id
and b.version_id = version.version_id
and b.component_id = component.component_id
and b.project_id = project.project_id
and assigned_to=4;
Now, before I found that query, I wrote this query on my own, which actually returns all 8 bugs assigned to me (I don't claim to be an SQL guru, so apologies if I'm doing something hideously wrong):
select
bug_id, status_name, resolution_name
from
project as p,
bug as b,
auth_user as u,
severity as sv,
status as st,
resolution as r
where
b.assigned_to=4
and u.login='jbecker'
and b.severity_id=sv.severity_id
and st.status_id=b.status_id
and p.project_id=b.project_id
and r.resolution_id=b.resolution_id
and b.resolution_id is not NULL
and b.resolution_id!=1;
I'm wondering if there's something I'm missing somewhere, or if I am just expecting the queries to do the wrong thing...
TIA!
--Jesse
Are you still seeing this problem in the latest version?