DATE_SUB and DATE_ADD are not ANSI SQL

RESOLVED FIXED in Bugzilla 2.20

Status

()

RESOLVED FIXED
14 years ago
14 years ago

People

(Reporter: Tomas.Kopal, Assigned: Tomas.Kopal)

Tracking

unspecified
Bugzilla 2.20
Bug Flags:
approval +

Details

Attachments

(1 attachment)

(Assignee)

Description

14 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

14 years ago
Posted patch V1Splinter Review
Patch replacing DATE_SUB and DATE_ADD SQL functions.
Attachment #175931 - Flags: review?
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

14 years ago
Flags: approval?
Flags: approval? → approval+
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: 14 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.