Bug 111830 - SQL bug for "days since bug changed" boolean chart
Summary: SQL bug for "days since bug changed" boolean chart
Status: CLOSED NOTABUG
Alias: None
Product: Bugzilla
Classification: Community
Component: Bugzilla General   
(Show other bugs)
Version: 2.17
Hardware: All Linux
high
medium vote
Target Milestone: ---
Assignee: David Lawrence
QA Contact: David Lawrence
URL:
Whiteboard:
Keywords:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2003-12-10 18:19 UTC by Maxwell Kanat-Alexander
Modified: 2007-03-27 04:13 UTC (History)
1 user (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2006-02-14 20:03:27 UTC
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

Description Maxwell Kanat-Alexander 2003-12-10 18:19:57 UTC
Sometimes buglist.cgi, when queried, is using some mysql SQL instead
of the Pg SQL. The symptom is that I get an error saying that
"to_days(timestamp with time zone) does not exist."

The only place that has to_days in it is Search.pm, and only if the DB
is MySQL.

Here's the detailed error:

[Wed Dec 10 14:09:22 2003] [error] [client 192.168.48.132] [Wed Dec 10
14:09:22 2003] buglist.cgi: SELECT bugs.bug_id, map_products.name,
bugs.bug_severity, bugs.op_sys, map_assigned_to.login_name,
bugs.bug_status, bugs.resolution, bugs.short_desc, bugs.priority FROM
bugs LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id 
LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 20   , profiles
AS map_assigned_to, products AS map_products WHERE bugs.assigned_to =
map_assigned_to.userid AND bugs.product_id = map_products.id AND
(bugs.product_id IN (7)) AND (bugs.component_id IN (20)) AND
(bugs.bug_status IN
('UNCONFIRMED','NEW','NEEDINFO','ASSIGNED','VERIFIED','REOPENED')) AND
((to_days(now()) - to_days(bugs.delta_ts)) > '1') AND
((bug_group_map.group_id IS NULL )  OR (bugs.reporter_accessible = 1
AND bugs.reporter = 20)  OR (bugs.cclist_accessible = 1 AND cc.who IS
NOT NULL)  OR (bugs.assigned_to = 20) ) GROUP BY bugs.bug_id,
bugs.bug_id,map_products.name,bugs.bug_severity,bugs.op_sys,map_assigned_to.login_name,bugs.bug_status,bugs.resolution,bugs.short_desc,bugs.priority
ORDER BY bugs.bug_status, bugs.priority, map_assigned_to.login_name,
bugs.bug_id : ERROR:  Function to_days(timestamp with time zone) does
not exist at globals.pl line 331., referer:
http://bugzilla.kerio.com/query.cgi?format=advanced

From the SQL, I'd guess that this is happening through quicksearch.

-M

Comment 1 David Lawrence 2003-12-10 18:33:37 UTC
to_days will have to be conditioned out in the code and a sql
statement with the equivalent PG function be added when used with PG.
The PG date function age() or interval() is probably what is needed.
Will look into fixing this. 

Thanks

Comment 2 Jason Hollinden 2003-12-18 13:43:21 UTC
This can be fixed by changing:

to_days(now()) - to_days(bugs.delta_ts)

to this:

(now() - '0000-01-01'::date) - (delta_ts - '0000-01-01'::date)

Hope that helps...

Comment 3 Jason Hollinden 2003-12-18 13:50:51 UTC
Sorry, make that bugs.delta_ts in the new line...

Comment 4 Maxwell Kanat-Alexander 2004-01-10 02:49:08 UTC
It needs to be changed in the fielddefs table, I believe.

(Changed the summary to be more accurate.)

-Max

Comment 5 Maxwell Kanat-Alexander 2004-01-10 03:16:18 UTC
Actually, this seems to work properly:

to_char(now() - bugs.delta_ts, 'DD')::int

The problem is that otherwise (in the code from comment 2) PostgreSQL
returns it as a string with "x days y minutes..." and so on.

-Max

Comment 6 Maxwell Kanat-Alexander 2004-01-10 03:24:45 UTC
Wait, thinking about it a bit more, what users expect (and what the
MySQL code is actually doing) is this:

to_char(now(), 'J')::int - to_char(bugs.delta_ts, 'J')::int

-Max

Comment 7 Rahul Sundaram 2006-02-14 20:02:33 UTC

I am closing this bug report as there has not been any activity on this for a
while and there has been various changes made to bugzilla in the meantime. If
the issue still exists, kindly reopen it. Thank you.


Note You need to log in before you can comment on or make changes to this bug.