Bug #23616 Week() changed behaviour between 5.0.22 and 5.0.24
Submitted: 25 Oct 2006 6:58 Modified: 30 Mar 2007 19:27
Reporter: Stephan Amann Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26, 5.0.24, 5.1.15-BK OS:
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: regression

[25 Oct 2006 6:58] Stephan Amann
Description:
mySQL 5.0.22:

mysql> select WEEK(NOW()+0,1);
+-----------------+
| WEEK(NOW()+0,1) |
+-----------------+
|              43 |
+-----------------+

mySQL 5.0.24:

mysql> select WEEK(NOW()+0,2);
+-----------------+
| WEEK(NOW()+0,2) |
+-----------------+
|            NULL |
+-----------------+

This change is not documented and as it seems up to now not reported as a bug.

How to repeat:
Statemenst above from mysql command line

Suggested fix:
Change behaviour of week(now()+0) back as it was in 5.0.22 and before.
[25 Oct 2006 8:44] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.26. The problem is that now()+0 now is of type double(23,6), and even with explict cast:

SELECT cast(now()+0 as datetime);

it gives NULL. If this is intended behaviour, it should be noted somewhere (on http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html, for example).
[25 Oct 2006 9:05] Stephan Amann
I only agree partially. It probably has to do with the different type of now()+0 since Version 4.1.13/5.0.8.

However - in 5.0.22 now()+0 was already double(23,6), but week(now()+0) still worked, but since 5.0.24 doesn't anymore.

So one reason is for sure the different type of now()+0, but unrelated (?) to that, the behaviour of week(double(23,6)) has changed.
[9 Nov 2006 13:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15086

ChangeSet@1.2544, 2006-11-09 17:02:44+04:00, ramil@mysql.com +4 -0
  Fix for bug #23616: Week() changed behaviour between 5.0.22 and 5.0.24
  
  Consider double values as legal date{time} function's arguments
  (i.e. allow dates in internal format YYYYMMDDHHMMSS.XXXXXX).
[26 Dec 2006 11:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17385

ChangeSet@1.2597, 2006-12-26 15:08:41+04:00, ramil@mysql.com +4 -0
  Fix for bug #23616: Week() changed behaviour between 5.0.22 and 5.0.24
  
  Consider double values as legal date{time} function's arguments
  (i.e. allow dates in internal format YYYYMMDDHHMMSS.XXXXXX).
[3 Feb 2007 8:36] Valeriy Kravchuk
This bug should be fixed in 5.1 also. In 5.1.15-BK I still have:

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.1.15-beta |
+-------------+
1 row in set (0.00 sec)

mysql> select week(now()+0,1);
+-----------------+
| week(now()+0,1) |
+-----------------+
|            NULL |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '20070202191048.000000'
1 row in set (0.00 sec)
[8 Mar 2007 20:17] Timothy Smith
pushed to 4.1.23, 5.0.38, 5.1.17
[30 Mar 2007 19:27] Paul Dubois
Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.

DOUBLE values such as 20070202191048.000000 were being treated as
illegal arguments by WEEK().