327348 - Whining fails on MySQL 4.0 due to multiple date addition

Status: RESOLVED FIXED

(Bugzilla :: Whining, defect, P1)

Description

[Rob Siklos [:robzilla]]

In whine.pl, near line 185 (and other places), we have sql like the following:

"UPDATE whine_schedules " .
   "SET run_next = CURRENT_DATE + " .
        $dbh->sql_interval('?', 'DAY') . " + " .
        $dbh->sql_interval('?', 'HOUR') .
" WHERE id = ?"

When I run the whine script on MySQL 4.0.20, I get a syntax error from this statement.

It seems that MySQL doesn't like the addition of more than two dates in a single statement using standard arithmetic operators.  It probably wants us to use the Date and Time arithmetic functions instead.

For example, if I change it to the following, it works:

"UPDATE whine_schedules " .
   "SET run_next = DATE_ADD(CURRENT_DATE + " .
        $dbh->sql_interval('?', 'DAY') . ", " .
        $dbh->sql_interval('?', 'HOUR') . ") " . 
" WHERE id = ?"

Comment 1

[Frédéric Buclin]

I haven't MySQL 4.0.x installed, so I cannot check this.

Comment 2

[Vlad Dascalu]

Conforming with MySQL 4.0.26:

[Sun Feb 26 04:10:38 2006] whine.pl: DBD::mysql::st execute failed: You have an
error in your SQL syntax.  Check the manual that corresponds to your MySQL serve
r version for the right syntax to use near 'WHERE id = '1'' at line 1 [for State
ment "UPDATE whine_schedules SET run_next = CURRENT_DATE + INTERVAL ? DAY + INTE
RVAL ? HOUR WHERE id = ?"] at whine.pl line 182

Comment 3

[Frédéric Buclin]

We support MySQL 4.0.14, so that's a blocker.

Comment 4

[Vlad Dascalu]

s/Conforming/Confirming/ (in my previous comment)

Reference: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

It says:

>>  As of MySQL 3.23, date arithmetic also can be performed using INTERVAL together with the + or - operator:

date + INTERVAL expr type
date - INTERVAL expr type

INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. <<

So according to documentation, at least the first addition should work. We can use DATE_ADD for the 2nd ones.

Comment 5

[Vlad Dascalu]

Attachment: Version 1, while.pl tested successfully

Comment 6

[Vlad Dascalu]

Comment on attachment 213209 [details] [diff] [review]
Version 1, while.pl tested successfully

Since it works on 4.0, I thought LpSolit could test it on PgSQL and other MySQL releases to make sure it keeps running ok on them.

Comment 7

[Frédéric Buclin]

Comment on attachment 213209 [details] [diff] [review]
Version 1, while.pl tested successfully

>+                                 "SET run_next = DATE_ADD(CURRENT_DATE + " .
>+                                 $dbh->sql_interval('?', 'DAY') . ", " .
>+                                 $dbh->sql_interval('?', 'HOUR') . ") " .

DATE_ADD() is not supported on PostgreSQL:

http://www.postgresql.org/docs/8.1/static/functions-datetime.html

I tested on my Pg 8.0.7 installation and it indeed fails.

Comment 8

[Frédéric Buclin]

Couldn't you write "(A + B) + C" instead of "A + B + C" ?

Comment 9

[Vlad Dascalu]

Attachment: Version 2, while.pl tested successfully

Yeap, ( .. ) works as well.

Comment 10

[Frédéric Buclin]

Comment on attachment 213251 [details] [diff] [review]
Version 2, while.pl tested successfully

Tested successfully on MySQL 4.1.x, 5.0.x and PostgreSQL 7.4.x and 8.0.x.

Comment 11

[Frédéric Buclin]

*** Bug 325813 has been marked as a duplicate of this bug. ***

Comment 12

[Jeffrey Forman]

works here on bugs.gentoo.org. mysql 4.0.24.

thanks guys!

Comment 13

[Vlad Dascalu]

Checking in whine.pl;
/cvsroot/mozilla/webtools/bugzilla/whine.pl,v  <--  whine.pl
new revision: 1.23; previous revision: 1.22
done

Checking in whine.pl;
/cvsroot/mozilla/webtools/bugzilla/whine.pl,v  <--  whine.pl
new revision: 1.22.2.1; previous revision: 1.22
done

Checking in whine.pl;
/cvsroot/mozilla/webtools/bugzilla/whine.pl,v  <--  whine.pl
new revision: 1.13.2.6; previous revision: 1.13.2.5
done