Bug #71414 DATE_FORMAT('0000-00-00', '%U') overflows, gives '613566757'
Submitted: 17 Jan 2014 20:41 Modified: 18 Jan 2014 5:31
Reporter: Arthur O'Dwyer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.31, 5.5.35, 5.6.15, 5.1.73 OS:Any
Assigned to: CPU Architecture:Any

[17 Jan 2014 20:41] Arthur O'Dwyer 
Description:
The calc_week() function doesn't properly handle an input of "0000-00-00"; instead of returning a sensible value such as "week 0 of year 0", it returns "week 613566757 of year 0". (That's UINT_MAX/7 + 1.)

The DATE_FORMAT() builtin exposes this behavior to the user.

How to repeat:
select date_format('0000-00-00', '%U');

  +---------------------------------+
  | date_format('0000-00-00', '%U') |
  +---------------------------------+
  | 613566757                       |
  +---------------------------------+

Suggested fix:
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 8abe7cb..d326582 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -120,6 +120,8 @@ uint calc_week(MYSQL_TIME *l_time, uint week_behaviour, uint *year)
   uint weekday=calc_weekday(first_daynr, !monday_first);
   *year=l_time->year;
 
+  if (daynr == 0) return 0;
+
   if (l_time->month == 1 && l_time->day <= 7-weekday)
   {
     if (!week_year &&
[17 Jan 2014 20:46] Arthur O'Dwyer 
Actually, my suggested fix wasn't general enough; it needs to be

    if (l_time->year == 0 || l_time->month == 0 || l_time->day == 0) return 0;

in order to handle the other problematic user-inputs, such as

select yearweek("0000-01-01", 1);
-> 4294967248
select extract(week from "2000-00-00");
-> 613566752

After the new and improved patch, these inputs also yield "0" outputs.
[18 Jan 2014 5:31] MySQL Verification Team 
Hello Arthur,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh