284244 - DATE_SUB and DATE_ADD are not ANSI SQL

Status: RESOLVED FIXED

(Bugzilla :: Bugzilla-General, defect)

Description

[Tomas Kopal]

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.

Comment 1

[Tomas Kopal]

Attachment: V1

Patch replacing DATE_SUB and DATE_ADD SQL functions.

Comment 2

[Max Kanat-Alexander]

Comment on attachment 175931 [details] [diff] [review]
V1

Hrm, let's have the guy who keeps finding regressions in these patches look it
over... :-)

Comment 3

[Teemu Mannermaa (: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.

Comment 4

[Max Kanat-Alexander]

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