DATE_SUB and DATE_ADD are not ANSI SQL

RESOLVED FIXED in Bugzilla 2.20

Status

()

Bugzilla
Bugzilla-General
RESOLVED FIXED
13 years ago
13 years ago

People

(Reporter: Tomas Kopal, Assigned: Tomas Kopal)

Tracking

unspecified
Bugzilla 2.20
Bug Flags:
approval +

Details

Attachments

(1 attachment)

(Assignee)

Description

13 years ago
We are using DATE_SUB and DATE_ADD MySQL functions on couple of places. These
are not cross DB compatible. Fortunatelly, as of 3.23, MySQL support using plus
and minus operators for date computations and they are functionally equivalent
so we can easily replace all occurences.
(Assignee)

Comment 1

13 years ago
Created attachment 175931 [details] [diff] [review]
V1

Patch replacing DATE_SUB and DATE_ADD SQL functions.
Attachment #175931 - Flags: review?

Comment 2

13 years ago
Comment on attachment 175931 [details] [diff] [review]
V1

Hrm, let's have the guy who keeps finding regressions in these patches look it
over... :-)
Attachment #175931 - Flags: review? → review?(wicked)
Comment on attachment 175931 [details] [diff] [review]
V1

uhh, ohh.. the pressure.. the pressure of a review. ok, here goes nothing:

>RCS file: /cvsroot/mozilla/webtools/bugzilla/sanitycheck.cgi,v
>--- sanitycheck.cgi	26 Feb 2005 06:34:20 -0000	1.84
>+++ sanitycheck.cgi	1 Mar 2005 12:39:13 -0000
>@@ -177,8 +177,8 @@
> 
>     Status("OK, now attempting to send unsent mail");
>     SendSQL("SELECT bug_id FROM bugs WHERE lastdiffed < delta_ts AND 
>-             delta_ts < date_sub(now(), " . $dbh->sql_interval('30 minute') .
>-            ") ORDER BY bug_id");
>+             delta_ts < now() - " . $dbh->sql_interval('30 minute') .
>+            " ORDER BY bug_id");

Nit: Capitalize NOW().

>@@ -736,8 +736,8 @@
> 
> SendSQL("SELECT bug_id " .
>         "FROM bugs WHERE lastdiffed < delta_ts AND ".
>-        "delta_ts < date_sub(now(), " . $dbh->sql_interval('30 minute') .
>-        ") ORDER BY bug_id");
>+        "delta_ts < now() - " . $dbh->sql_interval('30 minute') .
>+        " ORDER BY bug_id");

Nit: Ditto.

>RCS file: /cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v
>--- Bugzilla/Search.pm	25 Feb 2005 03:40:03 -0000	1.84
>+++ Bugzilla/Search.pm	1 Mar 2005 12:39:28 -0000
>@@ -922,7 +922,7 @@
>-                my $cutoff = "DATE_SUB(NOW(), " .
>+                my $cutoff = "NOW() - " .
>                              $dbh->sql_interval("$quantity $unitinterval");

This actually FIXES a previous regression.. Now Time Since Owner Touched
searches work. :)

>RCS file: /cvsroot/mozilla/webtools/bugzilla/contrib/sendunsentbugmail.pl,v
>--- contrib/sendunsentbugmail.pl	20 Feb 2005 08:02:07 -0000	1.2
>+++ contrib/sendunsentbugmail.pl	1 Mar 2005 12:39:29 -0000
>@@ -31,7 +31,7 @@
> 
> my $dbh = Bugzilla->dbh;
> SendSQL("SELECT bug_id FROM bugs WHERE lastdiffed < delta_ts AND 
>-         delta_ts < date_sub(now(), " . $dbh->sql_interval('30 minute') .
>+         delta_ts < NOW() - " . $dbh->sql_interval('30 minute') .
>         " ORDER BY bug_id");

Ditto here. Now this script works or atleast doesn't crash immediately. :)

So, this patch looks and functions good in my eyes.
Attachment #175931 - Flags: review?(wicked) → review+

Updated

13 years ago
Flags: approval?
Flags: approval? → approval+

Comment 4

13 years ago
Checking in sanitycheck.cgi;
/cvsroot/mozilla/webtools/bugzilla/sanitycheck.cgi,v  <--  sanitycheck.cgi
new revision: 1.85; previous revision: 1.84
done
Checking in whine.pl;
/cvsroot/mozilla/webtools/bugzilla/whine.pl,v  <--  whine.pl
new revision: 1.10; previous revision: 1.9
done
Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.86; previous revision: 1.85
done
Checking in Bugzilla/Token.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Token.pm,v  <--  Token.pm
new revision: 1.29; previous revision: 1.28
done
Checking in contrib/sendunsentbugmail.pl;
/cvsroot/mozilla/webtools/bugzilla/contrib/sendunsentbugmail.pl,v  <-- 
sendunsentbugmail.pl
new revision: 1.3; previous revision: 1.2
done
Status: NEW → RESOLVED
Last Resolved: 13 years ago
Resolution: --- → FIXED
Target Milestone: --- → Bugzilla 2.20
You need to log in before you can comment on or make changes to this bug.