1200182 - Relative dates involving months are not translated into absolute dates correctly

Status: NEW

(Bugzilla :: Query/Bug List, defect)

Description

[Ben Smith]

User Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0
Build ID: 20150826023504

Steps to reproduce:

Doing advanced search: search for bugs changed in the last 4 month, i.e. between -4m and now (querying on 2015-08-31 !)

http://bugzilla.somewhere.tld/report.cgi?x_axis_field=bug_status&y_axis_field=product&z_axis_field=&no_redirect=1&query_format=report-table&short_desc_type=allwordssubstr&short_desc=&bug_status=UNCONFIRMED&bug_status=CONFIRMED&bug_status=IN_PROGRESS&longdesc_type=allwordssubstr&longdesc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&bug_id=&bug_id_type=anyexact&emailassigned_to1=1&emailtype1=substring&email1=bschmitt&emailtype2=substring&email2=&emailtype3=substring&email3=&chfieldvalue=&chfieldfrom=-4m&chfieldto=Now&j_top=AND&f1=noop&o1=noop&v1=&format=table&action=wrap

Bugzilla Version 4.4.9 running on Oracle Database


Actual results:

Software error:

DBD::Oracle::db selectcol_arrayref failed: ORA-01839: date not valid for month specified (DBD ERROR: OCIStmtFetch) [for Statement "SELECT bugs.bug_id  bug_id
  FROM bugs
LEFT JOIN bug_group_map  security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (12,10,9,8,6,47,44,52,36,50,46,16,31,53,21,15,22,23,17,49,48,51,19,35,20,30,55,40,24,37,25,32,29,26,34,33,43,39,41,27,18,54,28,14) )
LEFT JOIN cc  security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 2
INNER JOIN profiles  name_assigned_to_1 ON bugs.assigned_to = name_assigned_to_1.userid
 WHERE bugs.creation_ts IS NOT NULL
   AND (security_map.group_id IS NULL
        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 2)
        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
        OR bugs.assigned_to = 2
)
   AND  bugs.bug_status IN ('UNCONFIRMED','CONFIRMED','IN_PROGRESS')  AND INSTR(LOWER(name_assigned_to_1.login_name), LOWER('bschmitt')) > 0 AND bugs.delta_ts >= '2015-02-31 14:03:40'
GROUP BY bugs.bug_id
"] at Bugzilla/DB/Oracle.pm line 466.
	Bugzilla::DB::Oracle::selectcol_arrayref(undef, 'SELECT bugs.bug_id  bug_id\x{a}  FROM bugs\x{a}LEFT JOIN bug_group_ma...') called at Bugzilla/Search.pm line 725
	Bugzilla::Search::data('Bugzilla::Search=HASH(0x43f6070)') called at /var/www/bugzilla/report.cgi line 171

For help, please send mail to the webmaster ([e-mail]), giving this error message and the time and date of the error. 


Expected results:

Correct filtered bug result list should have appereared.

The error seems to be in wrong date calculation in SQL:
2015-08-31    -4m => 2015-02-31 !!!  
leading to ORA-01839: date not valid for month specified

Since bugs.delta is a DATE Field in ORACLE-DB.
It is checked against a wrong calculated date string. 
obviously there is an implicit oracle datatype cast on wrong date string to date datatype that leads to the error ORA-01839: date not valid for month specified

Possible solution:
correctly calculate the date string -4m to consider end of month.
same problem with februar (shorter month, possible leap year)

Comment 1

[Ben Smith]

error message in description was for quering changes between -6m and now (having trouble in Febraury)

the correct error for -4m (as mentioned in description) is the following:

DBD::Oracle::db selectcol_arrayref failed: ORA-01839: date not valid for month specified (DBD ERROR: OCIStmtFetch) [for Statement "SELECT bugs.bug_id  bug_id
  FROM bugs
LEFT JOIN bug_group_map  security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (12,10,9,8,6,47,44,52,36,50,46,16,31,53,21,15,22,23,17,49,48,51,19,35,20,30,55,40,24,37,25,32,29,26,34,33,43,39,41,27,18,54,28,14) )
LEFT JOIN cc  security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 2
INNER JOIN profiles  name_assigned_to_1 ON bugs.assigned_to = name_assigned_to_1.userid
 WHERE bugs.creation_ts IS NOT NULL
   AND (security_map.group_id IS NULL
        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 2)
        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
        OR bugs.assigned_to = 2
)
   AND  bugs.bug_status IN ('UNCONFIRMED','CONFIRMED','IN_PROGRESS')  AND INSTR(LOWER(name_assigned_to_1.login_name), LOWER('bschmitt')) > 0 AND bugs.delta_ts >= '2015-04-31 14:39:22'
GROUP BY bugs.bug_id
"] at Bugzilla/DB/Oracle.pm line 466.
	Bugzilla::DB::Oracle::selectcol_arrayref(undef, 'SELECT bugs.bug_id  bug_id\x{a}  FROM bugs\x{a}LEFT JOIN bug_group_ma...') called at Bugzilla/Search.pm line 725
	Bugzilla::Search::data('Bugzilla::Search=HASH(0x2d0f2c8)') called at /var/www/bugzilla/report.cgi line 171

Comment 2

[Frédéric Buclin]

Confirmed! PostgreSQL is also affected by this problem.

Comment 3

[Frédéric Buclin]

Attachment: patch, v1

Comment 4

[Gervase Markham [:gerv]]

Comment on attachment 8654908 [details] [diff] [review]
patch, v1

Review of attachment 8654908 [details] [diff] [review]:
-----------------------------------------------------------------

::: Bugzilla/Search.pm
@@ +2262,5 @@
>              }
>              else {
> +                # Make sure we don't exceed the number of days in a month.
> +                # If we do, we take the last day of the month.
> +                if ($month == 1 && $mday > 28 + !($year % 4)) {

No, this doesn't work for all leap years :-) Seriously, surely there are Perl modules and APIs and suchlike to do this sort of calculation, so we don't have to roll our own? It would also make the code much clearer and less full of magic numbers.

Comment 5

[Gervase Markham [:gerv]]

Specifically, we already require both TimeDate and DateTime, so there is undoubtedly a function in one of those modules to enable you to get the last day of the month, or to do date subtraction.

Gerv