559539 - [Oracle] whine.pl sets run_next incorrectly due to CURRENT_DATE

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[David Taylor]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3
Build Identifier: Bugzilla 3.6.0

whine.pl assumes that CURRENT_DATE returns, well, the current date.  Oracle's DATE datatype is actually a date+time.

As a result when whine.pl runs at 2010-04-15 08:04:30 and performs the calculation (pseudo-SQL) 'CURRENT_DATE + 11 hours', rather than getting the answer 2010-04-15 11:00:00, it instead obtains 2010-04-15 19:04:30.

As a result whines run at completely the wrong time.  The simple fix is to use TRUNC(CURRENT_DATE) to ensure the datetime is truncated to simply the date.

Reproducible: Always

Steps to Reproduce:
1. Install bugzilla using an Oracle DB
2. At 10 am, configure a whine to run at 13:00
3. Run whine.pl
Actual Results:  
run_next will be set to 10am + 13 hours = 23:00

Expected Results:  
run_next should be set to 13:00

Comment 1

[David Taylor]

Attachment: wrap CURRENT_DATE in TRUNC()

Comment 2

[Frédéric Buclin]

Comment on attachment 439207 [details] [diff] [review]
wrap CURRENT_DATE in TRUNC()

TRUNC doesn't exist on MySQL. It must be an Oracle thing only, if required.

Comment 3

[David Taylor]

Attachment: wrap CURRENT_DATE in TRUNC() for Oracle only

Oops.  It seems Bugzilla/DB/Oracle.pm is already translating CURRENT_DATE() into CURRENT_DATE.  The attached patch will now translate them both into TRUNC(CURRENT_DATE).

Comment 4

[Max Kanat-Alexander]

Hey David. Thanks for the patch! If you'd like to get it reviewed, see our development process here for details on how:

  http://wiki.mozilla.org/Bugzilla:Developers

Comment 5

[Frédéric Buclin]

Comment on attachment 440196 [details] [diff] [review]
wrap CURRENT_DATE in TRUNC() for Oracle only

This code has been removed, see bug 675603.

Comment 6

[David Taylor]

As noted in bug 675603 comment 11, CURRENT_DATE is still used in whine.pl.

The fix for that bug has had no effect on whine.pl and this bug remains.

Comment 7

[Frédéric Buclin]

Attachment: patch for 4.4, v1

Tested with current 4.3.2+ code.

Comment 8

[Frédéric Buclin]

Attachment: patch for 4.4, v1.1

Oops, there was an incorrect indentation in my previous patch. Actually, I'm going to r+ this patch myself, because it's mostly a port of David's patch to 4.4.

Comment 9

[Frédéric Buclin]

Comment on attachment 440196 [details] [diff] [review]
wrap CURRENT_DATE in TRUNC() for Oracle only

>-    $part =~ s/\bCURRENT_DATE\b\(\)/CURRENT_DATE/io;
>+    $part =~ s/\bCURRENT_DATE(?:\(\)|\b)/TRUNC(CURRENT_DATE)/io;

Ok, this change is fine. I'm going to write \bCURRENT_DATE\b(?:\(\))? though, which seems cleaner to me. r=LpSolit for 4.2.3.

Comment 10

[Frédéric Buclin]

Thanks for your patch, David! :)

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/DB/Oracle.pm
Committed revision 8357.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.2/
modified Bugzilla/DB/Oracle.pm
Committed revision 8122.