Fix number to date conversion so it always honors the NO_ZERO_DATE,

NO_ZERO_IN_DATE, and INVALID_DATES bits of SQL_MODE. (Bug #5906)


include/my_time.h:
  Pass flags to number_to_datetime() so it can check things
  like NO_ZERO_DATE.
libmysql/libmysql.c:
  Enable fuzzy date handling when converting strings and numbers
  to datetime fields.
mysql-test/r/ps_2myisam.result:
  Update results
mysql-test/r/ps_3innodb.result:
  Update results
mysql-test/r/ps_4heap.result:
  Update results
mysql-test/r/ps_5merge.result:
  Update results
mysql-test/r/ps_6bdb.result:
  Update results
mysql-test/r/ps_7ndb.result:
  Update results
mysql-test/r/strict.result:
  Update results
mysql-test/r/timezone2.result:
  Update results
mysql-test/r/type_datetime.result:
  Update results
mysql-test/t/strict.test:
  Add new regression test
mysql-test/t/timezone2.test:
  Add new test of timestamp values in DST gap
sql-common/my_time.c:
  Expand check_date() to check NO_ZERO_DATE and NO_ZERO_IN_DATE, and
  use it from number_to_datetime() as well as str_to_datetime(). Also,
  make number_to_datetime() return -1 on error so we can distinguish
  between a violation of NO_ZERO_DATE and other errors.
sql/field.cc:
  Update conversion of numbers to date, datetime, and timestamp to
  use number_to_datetime() and report errors and warnings correctly
  and consistently.

15 files changed, 221 insertions, 196 deletions

diff --git a/include/my_time.h b/include/my_time.h
index 8058df8fe4e..aa68a6f0bbd 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -53,7 +53,7 @@ enum enum_mysql_timestamp_type
 str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
                 uint flags, int *was_cut);
 longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res,
-                               my_bool fuzzy_date, int *was_cut);
+                            uint flags, int *was_cut);
 ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *time);
 ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *time);
 ulonglong TIME_to_ulonglong_time(const MYSQL_TIME *time);
diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c
index 2074abd0f85..4127600babc 100644
--- a/libmysql/libmysql.c
+++ b/libmysql/libmysql.c
@@ -3600,7 +3600,7 @@ static void fetch_string_with_conversion(MYSQL_BIND *param, char *value,
   case MYSQL_TYPE_TIMESTAMP:
   {
     MYSQL_TIME *tm= (MYSQL_TIME *)buffer;
-    (void) str_to_datetime(value, length, tm, 0, &err);
+    (void) str_to_datetime(value, length, tm, TIME_FUZZY_DATE, &err);
     *param->error= test(err) && (param->buffer_type == MYSQL_TYPE_DATE &&
                                  tm->time_type != MYSQL_TIMESTAMP_DATE);
     break;
@@ -3718,7 +3718,8 @@ static void fetch_long_with_conversion(MYSQL_BIND *param, MYSQL_FIELD *field,
   case MYSQL_TYPE_DATETIME:
   {
     int error;
-    value= number_to_datetime(value, (MYSQL_TIME *) buffer, 1, &error);
+    value= number_to_datetime(value, (MYSQL_TIME *) buffer, TIME_FUZZY_DATE,
+                              &error);
     *param->error= test(error);
     break;
   }
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index a9342636349..4f512f08961 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -2976,25 +2976,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 3a470d218d1..457e19eca5e 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -2959,25 +2959,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index beeaff05ece..614ad09515a 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -2960,25 +2960,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index fada983a561..d07b84a08a3 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -2896,25 +2896,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
@@ -5908,25 +5908,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index 404c25c2c37..a8bc8649f6a 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -2959,25 +2959,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index ffddec6d5c2..b507064b702 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -2959,25 +2959,25 @@ Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
-Warning	1265	Data truncated for column 'c14' at row 1
+Warning	1264	Out of range value adjusted for column 'c14' at row 1
 Warning	1265	Data truncated for column 'c15' at row 1
 Warning	1264	Out of range value adjusted for column 'c16' at row 1
 Warning	1264	Out of range value adjusted for column 'c17' at row 1
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index d7ad803b828..fa7b86388ef 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -1235,3 +1235,16 @@ create table t1(a varchar(65537));
 ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
 create table t1(a varbinary(65537));
 ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
+set @@sql_mode='traditional';
+create table t1 (d date);
+insert into t1 values ('2000-10-00');
+ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
+insert into t1 values (1000);
+ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
+insert into t1 values ('2000-10-01');
+update t1 set d = 1100;
+ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
+select * from t1;
+d
+2000-10-01
+drop table t1;
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index a90bdf9ad5b..df51a6aac9b 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -40,6 +40,12 @@ insert into t1 (i, ts) values
 Warnings:
 Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
 insert into t1 (i, ts) values
+(unix_timestamp(20030330015959),20030330015959),
+(unix_timestamp(20030330023000),20030330023000),
+(unix_timestamp(20030330030000),20030330030000);
+Warnings:
+Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
+insert into t1 (i, ts) values
 (unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
 insert into t1 (i, ts) values
 (unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'),
@@ -54,6 +60,9 @@ i	ts
 1048985999	2003-03-30 00:59:59
 1048986000	2003-03-30 01:00:00
 1048986000	2003-03-30 01:00:00
+1048985999	2003-03-30 00:59:59
+1048986000	2003-03-30 01:00:00
+1048986000	2003-03-30 01:00:00
 1051740000	2003-04-30 22:00:00
 1067122800	2003-10-25 23:00:00
 1067126400	2003-10-26 00:00:00
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 98f7829ca1c..33c7e837997 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -100,12 +100,12 @@ create table t1 (t datetime);
 insert into t1 values (20030102030460),(20030102036301),(20030102240401),
 (20030132030401),(20031302030401),(100001202030401);
 Warnings:
-Warning	1265	Data truncated for column 't' at row 1
-Warning	1265	Data truncated for column 't' at row 2
-Warning	1265	Data truncated for column 't' at row 3
-Warning	1265	Data truncated for column 't' at row 4
-Warning	1265	Data truncated for column 't' at row 5
-Warning	1265	Data truncated for column 't' at row 6
+Warning	1264	Out of range value adjusted for column 't' at row 1
+Warning	1264	Out of range value adjusted for column 't' at row 2
+Warning	1264	Out of range value adjusted for column 't' at row 3
+Warning	1264	Out of range value adjusted for column 't' at row 4
+Warning	1264	Out of range value adjusted for column 't' at row 5
+Warning	1264	Out of range value adjusted for column 't' at row 6
 select * from t1;
 t
 0000-00-00 00:00:00
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index 302acc9bef2..74c27c488db 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -1093,3 +1093,18 @@ set @@sql_mode='traditional';
 create table t1(a varchar(65537));
 --error 1074
 create table t1(a varbinary(65537));
+
+#
+# Bug #5906: handle invalid date due to conversion
+#
+set @@sql_mode='traditional';
+create table t1 (d date);
+--error 1292
+insert into t1 values ('2000-10-00');
+--error 1292
+insert into t1 values (1000);
+insert into t1 values ('2000-10-01');
+--error 1292
+update t1 set d = 1100;
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 0b5aaed5d30..40fcd153877 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -48,6 +48,11 @@ insert into t1 (i, ts) values
   (unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'),
   (unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'),
   (unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00');
+# Values around and in spring time-gap
+insert into t1 (i, ts) values
+  (unix_timestamp(20030330015959),20030330015959),
+  (unix_timestamp(20030330023000),20030330023000),
+  (unix_timestamp(20030330030000),20030330030000);
 # Normal value with DST
 insert into t1 (i, ts) values
   (unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index 1078259f15d..c00c0e7be83 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -56,11 +56,14 @@ uint calc_days_in_year(uint year)
 }
 
 /*
-  check date.
+  Check datetime value for validity according to flags.
 
-  SYNOPOSIS
-    bool check_date()
-    time	Date to check.
+  SYNOPSIS
+    check_date()
+      ltime         - Date to check.
+      not_zero_date - ltime is not the zero date
+      flags         - flags to check
+      was_cut       - set to whether the value was truncated
 
   NOTES
     Here we assume that year and month is ok !
@@ -69,18 +72,35 @@ uint calc_days_in_year(uint year)
 
   RETURN
     0  ok
-    1  errro
+    1  error
 */
 
-bool check_date(MYSQL_TIME *ltime)
+bool check_date(const MYSQL_TIME *ltime, bool not_zero_date, ulong flags,
+                int *was_cut)
 {
-  if (ltime->month && ltime->day > days_in_month[ltime->month-1])
+
+  if (not_zero_date)
   {
-    if (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
-        ltime->day != 29)
-      return 1;
+    if ((((flags & TIME_NO_ZERO_IN_DATE) || !(flags & TIME_FUZZY_DATE)) &&
+         (ltime->month == 0 || ltime->day == 0)) ||
+        (!(flags & TIME_INVALID_DATES) &&
+         ltime->month && ltime->day > days_in_month[ltime->month-1] &&
+         (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
+          ltime->day != 29)))
+    {
+      *was_cut= 2;
+      return TRUE;
+    }
   }
-  return 0;
+  else if (flags & TIME_NO_ZERO_DATE)
+  {
+    /*
+      We don't set *was_cut here to signal that the problem was a zero date
+      and not an invalid date
+    */
+    return TRUE;
+  }
+  return FALSE;
 }
 
 
@@ -100,7 +120,7 @@ bool check_date(MYSQL_TIME *ltime)
                         TIME_INVALID_DATES	Allow 2000-02-31
     was_cut             0	Value ok
 			1       If value was cut during conversion
-			2	Date part was withing ranges but date was wrong
+			2	Date part was within ranges but date was wrong
 
   DESCRIPTION
     At least the following formats are recogniced (based on number of digits)
@@ -168,8 +188,6 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
     *was_cut= 1;
     DBUG_RETURN(MYSQL_TIMESTAMP_NONE);
   }
-  if (flags & TIME_NO_ZERO_IN_DATE)
-    flags&= ~TIME_FUZZY_DATE;
 
   is_internal_format= 0;
   /* This has to be changed if want to activate different timestamp formats */
@@ -385,22 +403,10 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
   if (year_length == 2 && not_zero_date)
     l_time->year+= (l_time->year < YY_PART_YEAR ? 2000 : 1900);
 
-  if (!not_zero_date && (flags & TIME_NO_ZERO_DATE))
-  {
-    /*
-      We don't set *was_cut here to signal that the problem was a zero date
-      and not an invalid date
-    */
-    goto err;
-  }
-
   if (number_of_fields < 3 ||
       l_time->year > 9999 || l_time->month > 12 ||
       l_time->day > 31 || l_time->hour > 23 ||
-      l_time->minute > 59 || l_time->second > 59 ||
-      (!(flags & TIME_FUZZY_DATE) && (l_time->month == 0 ||
-                                      l_time->day == 0) &&
-       not_zero_date))
+      l_time->minute > 59 || l_time->second > 59)
   {
     /* Only give warning for a zero date if there is some garbage after */
     if (!not_zero_date)                         /* If zero date */
@@ -418,15 +424,12 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
     goto err;
   }
 
+  if (check_date(l_time, not_zero_date, flags, was_cut))
+    goto err;
+
   l_time->time_type= (number_of_fields <= 3 ?
                       MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
 
-  if (not_zero_date && !(flags & TIME_INVALID_DATES) && check_date(l_time))
-  {
-    *was_cut= 2;                                /* Not correct date */
-    goto err;
-  }
-
   for (; str != end ; str++)
   {
     if (!my_isspace(&my_charset_latin1,*str))
@@ -881,9 +884,10 @@ int my_TIME_to_str(const MYSQL_TIME *l_time, char *to)
     number_to_datetime()
       nr         - datetime value as number
       time_res   - pointer for structure for broken-down representation
-      fuzzy_date - indicates whenever we allow fuzzy dates
-      was_cut    - set ot 1 if there was some kind of error during
-                   conversion or to 0 if everything was OK.
+      flags      - flags to use in validating date, as in str_to_datetime()
+      was_cut    0      Value ok
+                 1      If value was cut during conversion
+                 2      Date part was within ranges but date was wrong
 
   DESCRIPTION
     Convert a datetime value of formats YYMMDD, YYYYMMDD, YYMMDDHHMSS,
@@ -893,12 +897,13 @@ int my_TIME_to_str(const MYSQL_TIME *l_time, char *to)
     This function also checks if datetime value fits in DATETIME range.
 
   RETURN VALUE
+    -1              Timestamp with wrong values
+    anything else   DATETIME as integer in YYYYMMDDHHMMSS format
     Datetime value in YYYYMMDDHHMMSS format.
-    If input value is not valid datetime value then 0 is returned.
 */
 
 longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res,
-                               my_bool fuzzy_date, int *was_cut)
+                            uint flags, int *was_cut)
 {
   long part1,part2;
 
@@ -952,13 +957,17 @@ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res,
   if (time_res->year <= 9999 && time_res->month <= 12 &&
       time_res->day <= 31 && time_res->hour <= 23 &&
       time_res->minute <= 59 && time_res->second <= 59 &&
-      (fuzzy_date || (time_res->month != 0 && time_res->day != 0) || nr==0))
+      !check_date(time_res, (nr != 0), flags, was_cut))
     return nr;
 
+  /* Don't want to have was_cut get set if NO_ZERO_DATE was violated. */
+  if (!nr && flags & TIME_NO_ZERO_DATE)
+    return LL(-1);
+
  err:
 
   *was_cut= 1;
-  return LL(0);
+  return LL(-1);
 }
 
 
diff --git a/sql/field.cc b/sql/field.cc
index 925fca8ac43..7a818fa481d 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4469,13 +4469,13 @@ int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs)
   bool in_dst_time_gap;
   THD *thd= table->in_use;
 
+  /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */
   have_smth_to_conv= (str_to_datetime(from, len, &l_time,
-                                      ((table->in_use->variables.sql_mode &
-                                        MODE_NO_ZERO_DATE) |
-                                       MODE_NO_ZERO_IN_DATE),
-                                      &error) >
+                                      (table->in_use->variables.sql_mode &
+                                       MODE_NO_ZERO_DATE) |
+                                      MODE_NO_ZERO_IN_DATE, &error) >
                       MYSQL_TIMESTAMP_ERROR);
-  
+
   if (error || !have_smth_to_conv)
   {
     error= 1;
@@ -4488,16 +4488,15 @@ int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs)
   {
     if (!(tmp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap)))
     {
-      set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
+      set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
                            ER_WARN_DATA_OUT_OF_RANGE,
                            from, len, MYSQL_TIMESTAMP_DATETIME, !error);
-      
       error= 1;
     }
     else if (in_dst_time_gap)
     {
       set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
-                           ER_WARN_INVALID_TIMESTAMP, 
+                           ER_WARN_INVALID_TIMESTAMP,
                            from, len, MYSQL_TIMESTAMP_DATETIME, !error);
       error= 1;
     }
@@ -4522,8 +4521,8 @@ int Field_timestamp::store(double nr)
   int error= 0;
   if (nr < 0 || nr > 99991231235959.0)
   {
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         ER_WARN_DATA_OUT_OF_RANGE, 
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                         ER_WARN_DATA_OUT_OF_RANGE,
                          nr, MYSQL_TIMESTAMP_DATETIME);
     nr= 0;					// Avoid overflow on buff
     error= 1;
@@ -4541,35 +4540,35 @@ int Field_timestamp::store(longlong nr)
   bool in_dst_time_gap;
   THD *thd= table->in_use;
 
-  if (number_to_datetime(nr, &l_time, 0, &error))
+  /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */
+  long tmp= number_to_datetime(nr, &l_time, (thd->variables.sql_mode &
+                                             MODE_NO_ZERO_DATE) |
+                               MODE_NO_ZERO_IN_DATE, &error);
+  if (tmp < 0)
+  {
+    error= 2;
+  }
+
+  if (!error && tmp)
   {
     if (!(timestamp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap)))
     {
-      set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                          ER_WARN_DATA_OUT_OF_RANGE,
-                          nr, MYSQL_TIMESTAMP_DATETIME, 1);
+      set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                           ER_WARN_DATA_OUT_OF_RANGE,
+                           nr, MYSQL_TIMESTAMP_DATETIME, 1);
       error= 1;
     }
-  
     if (in_dst_time_gap)
     {
       set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
-                           ER_WARN_INVALID_TIMESTAMP, 
-                           nr, MYSQL_TIMESTAMP_DATETIME, !error);
+                           ER_WARN_INVALID_TIMESTAMP,
+                           nr, MYSQL_TIMESTAMP_DATETIME, 1);
       error= 1;
     }
-  }
-  else if (error)
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
+  } else if (error)
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
                          WARN_DATA_TRUNCATED,
                          nr, MYSQL_TIMESTAMP_DATETIME, 1);
-  if (!error && timestamp == 0 &&
-      (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE))
-  {
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         WARN_DATA_TRUNCATED,
-                         nr, MYSQL_TIMESTAMP_DATETIME, 1);
-  }
 
 #ifdef WORDS_BIGENDIAN
   if (table->s->db_low_byte_first)
@@ -4579,7 +4578,7 @@ int Field_timestamp::store(longlong nr)
   else
 #endif
     longstore(ptr,(uint32) timestamp);
-    
+
   return error;
 }
 
@@ -5152,14 +5151,14 @@ int Field_date::store(const char *from, uint len,CHARSET_INFO *cs)
   TIME l_time;
   uint32 tmp;
   int error;
-  
+
   if (str_to_datetime(from, len, &l_time, TIME_FUZZY_DATE |
                       (table->in_use->variables.sql_mode &
                        (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
                         MODE_INVALID_DATES)),
                       &error) <= MYSQL_TIMESTAMP_ERROR)
   {
-    tmp=0;
+    tmp= 0;
     error= 2;
   }
   else
@@ -5190,56 +5189,50 @@ int Field_date::store(double nr)
   if (nr < 0.0 || nr > 99991231.0)
   {
     tmp=0L;
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         ER_WARN_DATA_OUT_OF_RANGE, 
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                         ER_WARN_DATA_OUT_OF_RANGE,
                          nr, MYSQL_TIMESTAMP_DATE);
     error= 1;
   }
   else
     tmp=(long) rint(nr);
 
-  /*
-    We don't need to check for zero dates here as this date type is only
-    used in .frm tables from very old MySQL versions
-  */
-
-#ifdef WORDS_BIGENDIAN
-  if (table->s->db_low_byte_first)
-  {
-    int4store(ptr,tmp);
-  }
-  else
-#endif
-    longstore(ptr,tmp);
-  return error;
+  return Field_date::store(tmp);
 }
 
 
 int Field_date::store(longlong nr)
 {
-  long tmp;
-  int error= 0;
-  if (nr >= LL(19000000000000) && nr < LL(99991231235959))
-    nr=nr/LL(1000000);			// Timestamp to date
-  if (nr < 0 || nr > LL(99991231))
+  TIME not_used;
+  int error;
+  longlong initial_nr= nr;
+
+  nr= number_to_datetime(nr, &not_used, (TIME_FUZZY_DATE |
+                                         (table->in_use->variables.sql_mode &
+                                          (MODE_NO_ZERO_IN_DATE |
+                                           MODE_NO_ZERO_DATE |
+                                           MODE_INVALID_DATES))), &error);
+
+  if (nr < 0)
   {
-    tmp=0L;
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         ER_WARN_DATA_OUT_OF_RANGE,
-                         nr, MYSQL_TIMESTAMP_DATE, 0);
-    error= 1;
+    nr= 0;
+    error= 2;
   }
-  else
-    tmp=(long) nr;
+
+  if (error)
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                         error == 2 ? ER_WARN_DATA_OUT_OF_RANGE :
+                         WARN_DATA_TRUNCATED, initial_nr,
+                         MYSQL_TIMESTAMP_DATETIME, 1);
 
 #ifdef WORDS_BIGENDIAN
   if (table->s->db_low_byte_first)
   {
-    int4store(ptr,tmp);
+    int4store(ptr, nr);
   }
   else
 #endif
-    longstore(ptr,tmp);
+    longstore(ptr, nr);
   return error;
 }
 
@@ -5363,7 +5356,7 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs)
                          MODE_INVALID_DATES))),
                       &error) <= MYSQL_TIMESTAMP_ERROR)
   {
-    tmp=0L;
+    tmp= 0L;
     error= 2;
   }
   else
@@ -5372,7 +5365,7 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs)
   if (error)
     set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
                          from, len, MYSQL_TIMESTAMP_DATE, 1);
-    
+
   int3store(ptr,tmp);
   return error;
 }
@@ -5383,7 +5376,7 @@ int Field_newdate::store(double nr)
   if (nr < 0.0 || nr > 99991231235959.0)
   {
     int3store(ptr,(int32) 0);
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
                          WARN_DATA_TRUNCATED, nr, MYSQL_TIMESTAMP_DATE);
     return 1;
   }
@@ -5393,52 +5386,28 @@ int Field_newdate::store(double nr)
 
 int Field_newdate::store(longlong nr)
 {
-  int32 tmp;
-  int error= 0;
-  if (nr >= LL(100000000) && nr <= LL(99991231235959))
-    nr=nr/LL(1000000);			// Timestamp to date
-  if (nr < 0L || nr > 99991231L)
-  {
-    tmp=0;
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         ER_WARN_DATA_OUT_OF_RANGE, nr,
-                         MYSQL_TIMESTAMP_DATE, 1);
-    error= 1;
+  TIME l_time;
+  long tmp;
+  int error;
+  if ((tmp= number_to_datetime(nr, &l_time,
+                               (TIME_FUZZY_DATE |
+                                (table->in_use->variables.sql_mode &
+                                 (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
+                                  MODE_INVALID_DATES))),
+                               &error) < 0))
+  {
+    tmp= 0L;
+    error= 2;
   }
   else
-  {
-    uint month, day;
+    tmp= l_time.day + l_time.month*32 + l_time.year*16*32;
 
-    tmp=(int32) nr;
-    if (tmp)
-    {
-      if (tmp < YY_PART_YEAR*10000L)			// Fix short dates
-	tmp+= (uint32) 20000000L;
-      else if (tmp < 999999L)
-	tmp+= (uint32) 19000000L;
-
-      month= (uint) ((tmp/100) % 100);
-      day=   (uint) (tmp%100);
-      if (month > 12 || day > 31)
-      {
-        tmp=0L;					// Don't allow date to change
-        set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                             ER_WARN_DATA_OUT_OF_RANGE, nr,
-                             MYSQL_TIMESTAMP_DATE, 1);
-        error= 1;
-      }
-      else
-        tmp= day + month*32 + (tmp/10000)*16*32;
-    }
-    else if (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE)
-    {
-      set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                           ER_WARN_DATA_OUT_OF_RANGE, 
-                           0, MYSQL_TIMESTAMP_DATE);
-      error= 1;
-    }
-  }
-  int3store(ptr, tmp);
+  if (error)
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                         error == 2 ? ER_WARN_DATA_OUT_OF_RANGE :
+                         WARN_DATA_TRUNCATED,nr,MYSQL_TIMESTAMP_DATE, 1);
+
+  int3store(ptr,tmp);
   return error;
 }
 
@@ -5565,7 +5534,7 @@ int Field_datetime::store(const char *from,uint len,CHARSET_INFO *cs)
   int error;
   ulonglong tmp= 0;
   enum enum_mysql_timestamp_type func_res;
-  
+
   func_res= str_to_datetime(from, len, &time_tmp,
                             (TIME_FUZZY_DATE |
                              (table->in_use->variables.sql_mode &
@@ -5578,7 +5547,7 @@ int Field_datetime::store(const char *from,uint len,CHARSET_INFO *cs)
     error= 1;                                 // Fix if invalid zero date
 
   if (error)
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
                          ER_WARN_DATA_OUT_OF_RANGE,
                          from, len, MYSQL_TIMESTAMP_DATETIME, 1);
 
@@ -5615,21 +5584,25 @@ int Field_datetime::store(longlong nr)
   TIME not_used;
   int error;
   longlong initial_nr= nr;
-  
-  nr= number_to_datetime(nr, &not_used, 1, &error);
 
-  if (error)
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         WARN_DATA_TRUNCATED, initial_nr, 
-                         MYSQL_TIMESTAMP_DATETIME, 1);
-  else if (nr == 0 && table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE)
+  nr= number_to_datetime(nr, &not_used, (TIME_FUZZY_DATE |
+                                         (table->in_use->variables.sql_mode &
+                                          (MODE_NO_ZERO_IN_DATE |
+                                           MODE_NO_ZERO_DATE |
+                                           MODE_INVALID_DATES))), &error);
+
+  if (nr < 0)
   {
-    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, 
-                         ER_WARN_DATA_OUT_OF_RANGE, 
-                         initial_nr, MYSQL_TIMESTAMP_DATE, 1);
-    error= 1;
+    nr= 0;
+    error= 2;
   }
 
+  if (error)
+    set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+                         error == 2 ? ER_WARN_DATA_OUT_OF_RANGE :
+                         WARN_DATA_TRUNCATED, initial_nr,
+                         MYSQL_TIMESTAMP_DATETIME, 1);
+
 #ifdef WORDS_BIGENDIAN
   if (table->s->db_low_byte_first)
   {