MySQL Bugs: #23616: Week() changed behaviour between 5.0.22 and 5.0.24

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

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.

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).

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.

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).

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).

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)

pushed to 4.1.23, 5.0.38, 5.1.17

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().