Red Hat Bugzilla – Bug 111830
SQL bug for "days since bug changed" boolean chart
Last modified: 2007-03-27 00:13:00 EDT
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
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
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...
Sorry, make that bugs.delta_ts in the new line...
It needs to be changed in the fielddefs table, I believe. (Changed the summary to be more accurate.) -Max
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
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
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.