Bug #71378 FROM_DAYS(3652499) yields invalid unprintable date
Submitted: 14 Jan 2014 18:03 Modified: 15 Jan 2014 5:41
Reporter: Arthur O'Dwyer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.31,5.5.34, 5.5.35, 5.6.15 OS:Any
Assigned to: CPU Architecture:Any

[14 Jan 2014 18:03] Arthur O'Dwyer
Description:
FROM_DAYS(3652425) through FROM_DAYS(3652499) yield dates in the year 10000, which is out of range. Out-of-range dates can't be printed properly, and may have other bugs.

This appears to be a straightforward thinko in get_date_from_daynr().

Amusingly, one recent tutorial on MySQL gives examples of FROM_DAYS() usage, skipping straight from 3652424 to 3652500 as if the intervening seventy-five numbers didn't exist!
http://books.google.com/books?id=c5G42OHT96cC&lpg=PT1025&ots=6baHczaGTZ&dq=mysql%203652500...

How to repeat:
CREATE TABLE foo (d DATE, dt DATETIME);
INSERT INTO foo VALUES (FROM_DAYS(3652499), FROM_DAYS(3652499));
SELECT FROM_DAYS(3652499), d, dt, year(d), year(dt) FROM foo;

  +--------------------+------------+---------------------+---------+----------+
  | FROM_DAYS(3652499) | d          | dt                  | year(d) | year(dt) |
  +--------------------+------------+---------------------+---------+----------+
  | 10000-03-15        | :000-03-15 | :000-03-15 00:00:00 |   10000 |    10000 |
  +--------------------+------------+---------------------+---------+----------+

The wacky ":000" looks scary, but it's just a natural consequence of the "open coding" used in Field_newdate::val_str() and Field_datetime::val_str(). This glitch does mean that the invalid date can't be round-tripped through TEXT:

CREATE TABLE bar (t TEXT);
INSERT INTO bar SELECT d FROM foo;
SELECT * FROM bar;

  +------------+
  | t          |
  +------------+
  | :000-03-15 |
  +------------+

INSERT INTO foo(d) SELECT t FROM bar;

  ERROR 1292 (22007): Incorrect date value: ':000-03-15' for column 'd' at row 1

Suggested fix:
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 8abe7cb..6febda0 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -161,7 +161,7 @@ void get_date_from_daynr(long daynr,uint *ret_year,uint *ret_month,
   uchar *month_pos;
   DBUG_ENTER("get_date_from_daynr");
 
-  if (daynr <= 365L || daynr >= 3652500)
+  if (daynr <= 365L || daynr >= 3652425)
   {                                            /* Fix if wrong daynr */
     *ret_year= *ret_month = *ret_day =0;
   }
[15 Jan 2014 5:41] Umesh Shastry
Hello Arthur,

Thank you for the report and contribution.
Verified as described.

Thanks,
Umesh