MDEV-4667 DATE('string') incompability between mysql and mariadb

Cleanup: remove TIME_FUZZY_DATE.

Introduce TIME_FUZZY_DATES which means "very fuzzy, the resulting
value is only used for comparison. It can be invalid date, fine, as long as it can be
compared".

Updated many tests results (they're better now).

24 files changed, 125 insertions, 111 deletions

diff --git a/include/my_time.h b/include/my_time.h
index dbef712a038..4d8ea0c2eda 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -68,14 +68,17 @@ typedef long my_time_t;
 #endif
 
 /* Flags to str_to_datetime */
-#define TIME_FUZZY_DATE		1
+
+/*
+  TIME_FUZZY_DATES is used for the result will only be used for comparison
+  purposes. Conversion is as relaxed as possible.
+*/
+#define TIME_FUZZY_DATES        1
 #define TIME_DATETIME_ONLY	2
 #define TIME_TIME_ONLY	        4
-/* Must be same as MODE_NO_ZERO_IN_DATE */
-#define TIME_NO_ZERO_IN_DATE    (65536L*2*2*2*2*2*2*2)
-/* Must be same as MODE_NO_ZERO_DATE */
-#define TIME_NO_ZERO_DATE	(TIME_NO_ZERO_IN_DATE*2)
-#define TIME_INVALID_DATES	(TIME_NO_ZERO_DATE*2)
+#define TIME_NO_ZERO_IN_DATE    (1UL << 23) /* == MODE_NO_ZERO_IN_DATE */
+#define TIME_NO_ZERO_DATE	(1UL << 24) /* == MODE_NO_ZERO_DATE    */
+#define TIME_INVALID_DATES	(1UL << 25) /* == MODE_INVALID_DATES   */
 
 #define MYSQL_TIME_WARN_TRUNCATED    1
 #define MYSQL_TIME_WARN_OUT_OF_RANGE 2
diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c
index e8887b81e68..2d64aa99bfa 100644
--- a/libmysql/libmysql.c
+++ b/libmysql/libmysql.c
@@ -3549,7 +3549,7 @@ static void fetch_string_with_conversion(MYSQL_BIND *param, char *value,
   case MYSQL_TYPE_TIME:
   {
     MYSQL_TIME *tm= (MYSQL_TIME *)buffer;
-    str_to_time(value, length, tm, TIME_FUZZY_DATE, &err);
+    str_to_time(value, length, tm, 0, &err);
     *param->error= test(err);
     break;
   }
@@ -3558,7 +3558,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, TIME_FUZZY_DATE, &err);
+    (void) str_to_datetime(value, length, tm, 0, &err);
     *param->error= test(err) && (param->buffer_type == MYSQL_TYPE_DATE &&
                                  tm->time_type != MYSQL_TIMESTAMP_DATE);
     break;
@@ -3681,9 +3681,7 @@ static void fetch_long_with_conversion(MYSQL_BIND *param, MYSQL_FIELD *field,
   case MYSQL_TYPE_DATETIME:
   {
     int error;
-    value= number_to_datetime(value, 0,
-                              (MYSQL_TIME *) buffer, TIME_FUZZY_DATE,
-                              &error);
+    value= number_to_datetime(value, 0, (MYSQL_TIME *) buffer, 0, &error);
     *param->error= test(error);
     break;
   }
diff --git a/mysql-test/r/adddate_454.result b/mysql-test/r/adddate_454.result
index 0993cdce32c..8b7c17cd47e 100644
--- a/mysql-test/r/adddate_454.result
+++ b/mysql-test/r/adddate_454.result
@@ -4,6 +4,8 @@ select * from t1;
 d
 2012-00-00
 update t1 set d = adddate(d, interval 1 day);
+Warnings:
+Warning	1292	Incorrect datetime value: '2012-00-00'
 select * from t1;
 d
 NULL
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 72e6dca8890..548a7bfee57 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -268,37 +268,37 @@ cast(010203101112.121314 as datetime)
 0001-02-03 10:11:12
 select cast(120010203101112.121314 as datetime);
 cast(120010203101112.121314 as datetime)
-0000-00-00 00:00:00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '120010203101112.121314'
 select cast(cast(1.1 as decimal) as datetime);
 cast(cast(1.1 as decimal) as datetime)
-0000-00-00 00:00:00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '1'
 select cast(cast(-1.1 as decimal) as datetime);
 cast(cast(-1.1 as decimal) as datetime)
-0000-00-00 00:00:00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '-1'
 select cast('0' as date);
 cast('0' as date)
-0000-00-00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '0'
 select cast('' as date);
 cast('' as date)
-0000-00-00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: ''
 select cast('0' as datetime);
 cast('0' as datetime)
-0000-00-00 00:00:00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '0'
 select cast('' as datetime);
 cast('' as datetime)
-0000-00-00 00:00:00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: ''
 select cast('0' as time);
@@ -306,7 +306,7 @@ cast('0' as time)
 00:00:00
 select cast('' as time);
 cast('' as time)
-00:00:00
+NULL
 Warnings:
 Warning	1292	Truncated incorrect time value: ''
 select cast(NULL as DATE);
@@ -323,13 +323,13 @@ cast(NULL as BINARY)
 NULL
 select cast(cast(120010203101112.121314 as double) as datetime);
 cast(cast(120010203101112.121314 as double) as datetime)
-0000-00-00 00:00:00
+NULL
 select cast(cast(1.1 as double) as datetime);
 cast(cast(1.1 as double) as datetime)
 0000-00-00 00:00:01
 select cast(cast(-1.1 as double) as datetime);
 cast(cast(-1.1 as double) as datetime)
-0000-00-00 00:00:00
+NULL
 explain extended select cast(10 as double(5,2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
@@ -764,4 +764,10 @@ SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY;
 CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY
 NULL
 Warnings:
-Warning	1292	Truncated incorrect date value: '0000-00-00'
+Warning	1292	Incorrect datetime value: '0000-00-00'
+SET SQL_MODE=ALLOW_INVALID_DATES;
+SELECT DATE("foo");
+DATE("foo")
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: 'foo'
diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index 044338e98b8..dd346cb94dc 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -586,7 +586,7 @@ TIME_FORMAT("25:00:00", '%l %p')
 1 AM
 SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896);
 DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896)
-1151414896
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '%Y-%m-%d %H:%i:%s'
 select str_to_date('04 /30/2004', '%m /%d/%Y');
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index 5bd3b3f8fda..feb92da3321 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -168,7 +168,7 @@ date("1997-12-31 23:59:59.000001")
 1997-12-31
 select date("1997-13-31 23:59:59.000001");
 date("1997-13-31 23:59:59.000001")
-0000-00-00
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '1997-13-31 23:59:59.000001'
 select time("1997-12-31 23:59:59.000001");
@@ -176,7 +176,7 @@ time("1997-12-31 23:59:59.000001")
 23:59:59.000001
 select time("1997-12-31 25:59:59.000001");
 time("1997-12-31 25:59:59.000001")
-00:00:00
+NULL
 Warnings:
 Warning	1292	Truncated incorrect time value: '1997-12-31 25:59:59.000001'
 select microsecond("1997-12-31 23:59:59.000001");
@@ -250,8 +250,6 @@ a
 select microsecond(19971231235959.01) as a;
 a
 10000
-Warnings:
-Warning	1292	Truncated incorrect time value: '19971231235959.01'
 select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
 a
 1997-12-31 00:00:10.090000
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 97fedf81647..88599b79a1f 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1239,14 +1239,13 @@ DROP TABLE t1,t2;
 set time_zone= @@global.time_zone;
 select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
 str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
-NULL
-Warnings:
-Error	1411	Incorrect datetime value: '10:00 PM' for function str_to_date
+22:10:00
 select str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute;
 str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute
 NULL
 Warnings:
-Error	1411	Incorrect datetime value: '1997-00-04 22:23:00' for function str_to_date
+Warning	1292	Truncated incorrect date value: '1997-00-04 22:23:00'
+Warning	1292	Incorrect datetime value: '1997-00-04'
 create table t1 (field DATE);
 insert into t1 values ('2006-11-06');
 select * from t1 where field < '2006-11-06 04:08:36.0';
@@ -1452,13 +1451,15 @@ MAKEDATE(11111111,1)
 NULL
 SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1);
 WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1)
-0
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '0000-00-00'
 #
 # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0,
 #
 DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5);
 Warnings:
-Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0000-00-00'
 #
 # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY
 # SIMILAR TO '2009-10-00' 
@@ -1755,7 +1756,7 @@ Warnings:
 Warning	1441	Datetime function: time field overflow
 select cast('131415.123e0' as time);
 cast('131415.123e0' as time)
-00:00:00
+NULL
 Warnings:
 Warning	1292	Truncated incorrect time value: '131415.123e0'
 select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04';
@@ -1775,12 +1776,12 @@ unix_timestamp(null)
 NULL
 select truncate(date('2010-40-10'), 6);
 truncate(date('2010-40-10'), 6)
-0.000000
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '2010-40-10'
 select extract(month from '2010-40-50');
 extract(month from '2010-40-50')
-0
+NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '2010-40-50'
 select subtime('0000-00-10 10:10:10', '30 10:00:00');
@@ -1858,6 +1859,8 @@ insert into t1 values ('0000-00-00');
 select timestampadd(week, 1, f1) from t1;
 timestampadd(week, 1, f1)
 NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '0000-00-00'
 select timestampadd(week, 1, date("0000-00-00"));
 timestampadd(week, 1, date("0000-00-00"))
 NULL
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 1f5b359199d..6af68b8c938 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -555,14 +555,10 @@ ERROR 42000: Incorrect parameters in the call to native function 'atan'
 DROP TABLE IF EXISTS t1;
 SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
 STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
-NULL
-Warnings:
-Error	1411	Incorrect datetime value: '10:00 PM' for function str_to_date
+22:10:00
 SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE;
 STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE
-NULL
-Warnings:
-Error	1411	Incorrect datetime value: '10:00 PM' for function str_to_date
+22:01:00
 SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
 "1997-12-31 23:59:59" + INTERVAL 1 SECOND
 1998-01-01 00:00:00
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index f60c87aa351..d5594c7453e 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -1906,10 +1906,9 @@ INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL);
 # test with an invalid date, which lead to item->null_value is set.
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p20090401	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Warning	1292	Incorrect datetime value: '2009-04-99'
-Warning	1292	Incorrect datetime value: '2009-04-99'
 DROP TABLE t1;
 CREATE TABLE t1
 (a INT NOT NULL AUTO_INCREMENT,
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index ca87f430c5d..da38f8d9ef8 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -136,7 +136,7 @@ select @d:=1311;
 1311
 select year(@d), month(@d), day(@d), cast(@d as date);
 year(@d)	month(@d)	day(@d)	cast(@d as date)
-0	0	0	0000-00-00
+NULL	NULL	NULL	NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '1311'
 Warning	1292	Incorrect datetime value: '1311'
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index b835eacac95..286a84c56e7 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -657,8 +657,8 @@ create table t1 (d date, t time) engine=myisam;
 insert into t1 values ('2000-12-03','22:55:23'),('2008-05-03','10:19:31');
 select case when d = '2012-12-12' then d else t end as cond, group_concat( d ) from t1 group by cond;
 cond	group_concat( d )
-0000-00-00 00:00:00	2000-12-03
-0000-00-00 00:00:00	2008-05-03
+NULL	2000-12-03
+NULL	2008-05-03
 Warnings:
 Warning	1292	Incorrect datetime value: '22:55:23'
 Warning	1292	Incorrect datetime value: '10:19:31'
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index 14467b2d630..4929eabb2e9 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -187,7 +187,11 @@ ts TIMESTAMP,
 tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
 ) ENGINE=MyISAM;
 INSERT INTO t1 (tsv) VALUES (DEFAULT);
+Warnings:
+Warning	1292	Incorrect datetime value: '0000-00-00'
 INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
+Warnings:
+Warning	1292	Incorrect datetime value: '0000-00-00'
 FLUSH TABLES;
 SELECT COUNT(*) FROM t1;
 COUNT(*)
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index b3bda315673..8f4035e6070 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -439,3 +439,6 @@ drop table t1;
 #
 SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY;
 
+SET SQL_MODE=ALLOW_INVALID_DATES;
+SELECT DATE("foo");
+
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index 5ad037ab6c9..d3a968ec53f 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -83,7 +83,7 @@ my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date,
 {
   if (not_zero_date)
   {
-    if ((((flags & TIME_NO_ZERO_IN_DATE) || !(flags & TIME_FUZZY_DATE)) &&
+    if (((flags & TIME_NO_ZERO_IN_DATE) &&
          (ltime->month == 0 || ltime->day == 0)) || ltime->neg ||
         (!(flags & TIME_INVALID_DATES) &&
          ltime->month && ltime->day > days_in_month[ltime->month-1] &&
@@ -115,7 +115,7 @@ my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date,
     length              Length of string
     l_time              Date is stored here
     flags               Bitmap of following items
-                        TIME_FUZZY_DATE    Set if we should allow partial dates
+                        TIME_FUZZY_DATE
                         TIME_DATETIME_ONLY Set if we only allow full datetimes.
                         TIME_NO_ZERO_IN_DATE	Don't allow partial dates
                         TIME_NO_ZERO_DATE	Don't allow 0000-00-00 date
@@ -1324,7 +1324,7 @@ int number_to_time(my_bool neg, longlong nr, ulong sec_part,
   if (nr > 9999999 && neg == 0)
   {
     if (number_to_datetime(nr, sec_part, ltime,
-                           TIME_FUZZY_DATE |  TIME_INVALID_DATES, was_cut) < 0)
+                           TIME_INVALID_DATES, was_cut) < 0)
       return -1;
 
     ltime->year= ltime->month= ltime->day= 0;
diff --git a/sql/field.cc b/sql/field.cc
index fa8ed5fdda9..60ec8fcea57 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -5127,10 +5127,9 @@ int Field_temporal::store(const char *from,uint len,CHARSET_INFO *cs)
   Lazy_string_str str(from, len);
 
   func_res= str_to_datetime(from, len, &ltime,
-                            (TIME_FUZZY_DATE |
-                             (thd->variables.sql_mode &
+                            (thd->variables.sql_mode &
                               (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
-                               MODE_INVALID_DATES))),
+                               MODE_INVALID_DATES)),
                             &error);
   return store_TIME_with_warning(&ltime, &str, error, func_res > MYSQL_TIMESTAMP_ERROR);
 }
@@ -5144,11 +5143,10 @@ int Field_temporal::store(double nr)
   Lazy_string_double str(nr);
 
   longlong tmp= double_to_datetime(nr, &ltime,
-                                    (TIME_FUZZY_DATE |
-                                       (thd->variables.sql_mode &
+                                    (thd->variables.sql_mode &
                                         (MODE_NO_ZERO_IN_DATE |
                                          MODE_NO_ZERO_DATE |
-                                         MODE_INVALID_DATES))), &error);
+                                         MODE_INVALID_DATES)), &error);
   return store_TIME_with_warning(&ltime, &str, error, tmp != -1);
 }
 
@@ -5161,11 +5159,10 @@ int Field_temporal::store(longlong nr, bool unsigned_val)
   THD *thd= table->in_use;
   Lazy_string_num str(nr);
 
-  tmp= number_to_datetime(nr, 0, &ltime, (TIME_FUZZY_DATE |
-                                      (thd->variables.sql_mode &
+  tmp= number_to_datetime(nr, 0, &ltime, (thd->variables.sql_mode &
                                        (MODE_NO_ZERO_IN_DATE |
                                         MODE_NO_ZERO_DATE |
-                                        MODE_INVALID_DATES))), &error);
+                                        MODE_INVALID_DATES)), &error);
 
   return store_TIME_with_warning(&ltime, &str, error, tmp != -1);
 }
@@ -5181,17 +5178,16 @@ int Field_temporal::store_time_dec(MYSQL_TIME *ltime, uint dec)
     structure always fit into DATETIME range.
   */
   have_smth_to_conv= !check_date(&l_time, pack_time(&l_time) != 0,
-                                 (TIME_FUZZY_DATE |
-                                  (current_thd->variables.sql_mode &
+                                 (current_thd->variables.sql_mode &
                                    (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
-                                    MODE_INVALID_DATES))), &error);
+                                    MODE_INVALID_DATES)), &error);
   return store_TIME_with_warning(&l_time, &str, error, have_smth_to_conv);
 }
 
 my_decimal *Field_temporal::val_decimal(my_decimal *d)
 {
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE))
+  if (get_date(&ltime, 0))
   {
     bzero(&ltime, sizeof(ltime));
     ltime.time_type= mysql_type_to_time_type(type());
@@ -5330,7 +5326,8 @@ String *Field_time::val_str(String *val_buffer,
 bool Field_time::get_date(MYSQL_TIME *ltime, uint fuzzydate)
 {
   THD *thd= table->in_use;
-  if (!(fuzzydate & (TIME_FUZZY_DATE|TIME_TIME_ONLY)))
+  if (!(fuzzydate & TIME_TIME_ONLY) &&
+      (fuzzydate & TIME_NO_ZERO_IN_DATE))
   {
     push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
                         ER_WARN_DATA_OUT_OF_RANGE,
@@ -5459,7 +5456,7 @@ bool Field_time_hires::get_date(MYSQL_TIME *ltime, uint fuzzydate)
   ltime->time_type= MYSQL_TIMESTAMP_TIME;
   ltime->hour+= (ltime->month*32+ltime->day)*24;
   ltime->month= ltime->day= 0;
-  return fuzzydate & (TIME_FUZZY_DATE | TIME_TIME_ONLY) ? 0 : 1;
+  return !(fuzzydate & TIME_TIME_ONLY) && (fuzzydate & TIME_NO_ZERO_IN_DATE);
 }
 
 
@@ -5848,7 +5845,7 @@ void Field_datetime::store_TIME(MYSQL_TIME *ltime)
 bool Field_datetime::send_binary(Protocol *protocol)
 {
   MYSQL_TIME tm;
-  Field_datetime::get_date(&tm, TIME_FUZZY_DATE);
+  Field_datetime::get_date(&tm, 0);
   return protocol->store(&tm, 0);
 }
   
@@ -5931,7 +5928,7 @@ bool Field_datetime::get_date(MYSQL_TIME *ltime, uint fuzzydate)
   if (!tmp)
     return (fuzzydate & TIME_NO_ZERO_DATE) != 0;
   if (!ltime->month || !ltime->day)
-    return !(fuzzydate & TIME_FUZZY_DATE);
+    return fuzzydate & TIME_NO_ZERO_IN_DATE;
   return 0;
 }
 
@@ -5984,11 +5981,10 @@ int Field_datetime_hires::store_decimal(const my_decimal *d)
     error= 2;
   }
   else
-    tmp= number_to_datetime(nr, sec_part, &ltime, (TIME_FUZZY_DATE |
-                                          (thd->variables.sql_mode &
+    tmp= number_to_datetime(nr, sec_part, &ltime, (thd->variables.sql_mode &
                                            (MODE_NO_ZERO_IN_DATE |
                                             MODE_NO_ZERO_DATE |
-                                            MODE_INVALID_DATES))), &error);
+                                            MODE_INVALID_DATES)), &error);
 
   return store_TIME_with_warning(&ltime, &str, error, tmp != -1);
 }
@@ -5996,7 +5992,7 @@ int Field_datetime_hires::store_decimal(const my_decimal *d)
 bool Field_datetime_hires::send_binary(Protocol *protocol)
 {
   MYSQL_TIME ltime;
-  Field_datetime_hires::get_date(&ltime, TIME_FUZZY_DATE);
+  Field_datetime_hires::get_date(&ltime, 0);
   return protocol->store(&ltime, dec);
 }
 
@@ -6004,14 +6000,14 @@ bool Field_datetime_hires::send_binary(Protocol *protocol)
 double Field_datetime_hires::val_real(void)
 {
   MYSQL_TIME ltime;
-  Field_datetime_hires::get_date(&ltime, TIME_FUZZY_DATE);
+  Field_datetime_hires::get_date(&ltime, 0);
   return TIME_to_double(&ltime);
 }
 
 longlong Field_datetime_hires::val_int(void)
 {
   MYSQL_TIME ltime;
-  Field_datetime_hires::get_date(&ltime, TIME_FUZZY_DATE);
+  Field_datetime_hires::get_date(&ltime, 0);
   return TIME_to_ulonglong_datetime(&ltime);
 }
 
@@ -6020,7 +6016,7 @@ String *Field_datetime_hires::val_str(String *str,
                                       String *unused __attribute__((unused)))
 {
   MYSQL_TIME ltime;
-  Field_datetime_hires::get_date(&ltime, TIME_FUZZY_DATE);
+  Field_datetime_hires::get_date(&ltime, 0);
   str->alloc(field_length+1);
   str->length(field_length);
   my_datetime_to_str(&ltime, (char*) str->ptr(), dec);
@@ -6035,7 +6031,7 @@ bool Field_datetime_hires::get_date(MYSQL_TIME *ltime, uint fuzzydate)
   if (!packed)
     return fuzzydate & TIME_NO_ZERO_DATE;
   if (!ltime->month || !ltime->day)
-    return !(fuzzydate & TIME_FUZZY_DATE);
+    return fuzzydate & TIME_NO_ZERO_IN_DATE;
   return 0;
 }
 
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index a64b77bf500..7ec3fe5ea7b 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -413,7 +413,7 @@ static void do_field_decimal(Copy_field *copy)
 static void do_field_temporal(Copy_field *copy)
 {
   MYSQL_TIME ltime;
-  copy->from_field->get_date(&ltime, TIME_FUZZY_DATE);
+  copy->from_field->get_date(&ltime, 0);
   copy->to_field->store_time_dec(&ltime, copy->from_field->decimals());
 }
 
@@ -884,7 +884,7 @@ int field_conv(Field *to,Field *from)
   if (from->cmp_type() == TIME_RESULT)
   {
     MYSQL_TIME ltime;
-    if (from->get_date(&ltime, TIME_FUZZY_DATE))
+    if (from->get_date(&ltime, 0))
       return to->reset();
     else
       return to->store_time_dec(&ltime, from->decimals());
diff --git a/sql/filesort.cc b/sql/filesort.cc
index 6619989c1ea..d72e1c83355 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -868,7 +868,7 @@ static void make_sortkey(register SORTPARAM *param,
           else
           {
             MYSQL_TIME buf;
-            if (item->get_date_result(&buf, TIME_FUZZY_DATE | TIME_INVALID_DATES))
+            if (item->get_date_result(&buf, TIME_INVALID_DATES))
             {
               DBUG_ASSERT(maybe_null);
               DBUG_ASSERT(item->null_value);
diff --git a/sql/item.cc b/sql/item.cc
index cb60d6fb812..f5687f18cb3 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -252,7 +252,7 @@ String *Item::val_string_from_decimal(String *str)
 String *Item::val_string_from_date(String *str)
 {
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE) ||
+  if (get_date(&ltime, 0) ||
       str->alloc(MAX_DATE_STRING_REP_LENGTH))
   {
     null_value= 1;
@@ -308,7 +308,7 @@ my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE))
+  if (get_date(&ltime, 0))
   {
     my_decimal_set_zero(decimal_value);
     null_value= 1;                               // set NULL, stop processing
@@ -367,7 +367,7 @@ int Item::save_time_in_field(Field *field)
 int Item::save_date_in_field(Field *field)
 {
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE))
+  if (get_date(&ltime, 0))
     return set_field_to_null_with_conversions(field, 0);
   field->set_notnull();
   return field->store_time_dec(&ltime, decimals);
@@ -1205,7 +1205,7 @@ err:
     if allowed, otherwise - null.
   */
   bzero((char*) ltime,sizeof(*ltime));
-  return null_value|= (fuzzydate & (TIME_NO_ZERO_DATE|TIME_NO_ZERO_IN_DATE));
+  return null_value|= !(fuzzydate & TIME_FUZZY_DATES);
 }
 
 bool Item::get_seconds(ulonglong *sec, ulong *sec_part)
@@ -6032,7 +6032,7 @@ bool Item::send(Protocol *protocol, String *buffer)
   case MYSQL_TYPE_TIMESTAMP:
   {
     MYSQL_TIME tm;
-    get_date(&tm, TIME_FUZZY_DATE | sql_mode_for_dates());
+    get_date(&tm, sql_mode_for_dates());
     if (!null_value)
     {
       if (f_type == MYSQL_TYPE_DATE)
@@ -8161,8 +8161,8 @@ int stored_field_cmp_to_item(THD *thd, Field *field, Item *item)
     }
     else
     {
-      field->get_date(&field_time, TIME_FUZZY_DATE | TIME_INVALID_DATES);
-      item->get_date(&item_time, TIME_FUZZY_DATE | TIME_INVALID_DATES);
+      field->get_date(&field_time, TIME_INVALID_DATES);
+      item->get_date(&item_time, TIME_INVALID_DATES);
     }
     return my_time_compare(&field_time, &item_time);
   }
@@ -8343,7 +8343,7 @@ bool  Item_cache_temporal::cache_value()
   value_cached= true;
  
   MYSQL_TIME ltime;
-  if (example->get_date_result(&ltime, TIME_FUZZY_DATE))
+  if (example->get_date_result(&ltime, 0))
     value=0;
   else
     value= pack_time(&ltime);
diff --git a/sql/item.h b/sql/item.h
index 1938273c261..c66573fc0ef 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -936,7 +936,7 @@ public:
                        Item **ref, bool skip_registered);
   virtual bool get_date(MYSQL_TIME *ltime,uint fuzzydate);
   bool get_time(MYSQL_TIME *ltime)
-  { return get_date(ltime, TIME_TIME_ONLY | TIME_FUZZY_DATE); }
+  { return get_date(ltime, TIME_TIME_ONLY); }
   bool get_seconds(ulonglong *sec, ulong *sec_part);
   virtual bool get_date_result(MYSQL_TIME *ltime,uint fuzzydate)
   { return get_date(ltime,fuzzydate); }
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 1aaedf3a6ad..b2a91c8ec1e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -713,7 +713,7 @@ bool get_mysql_time_from_str(THD *thd, String *str, timestamp_type warn_type,
   bool value;
   int error;
   enum_mysql_timestamp_type timestamp_type;
-  int flags= TIME_FUZZY_DATE | MODE_INVALID_DATES;
+  int flags= TIME_FUZZY_DATES | MODE_INVALID_DATES;
 
   if (warn_type == MYSQL_TIMESTAMP_TIME)
     flags|= TIME_TIME_ONLY;
@@ -885,7 +885,7 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
   else
   {
     MYSQL_TIME ltime;
-    uint fuzzydate= TIME_FUZZY_DATE | TIME_INVALID_DATES;
+    uint fuzzydate= TIME_FUZZY_DATES | TIME_INVALID_DATES;
     if (f_type == MYSQL_TYPE_TIME)
       fuzzydate|= TIME_TIME_ONLY;
     if (item->get_date(&ltime, fuzzydate))
diff --git a/sql/item_func.cc b/sql/item_func.cc
index ccbdabe0a90..e1a2bd44c34 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2538,7 +2538,7 @@ double Item_func_min_max::val_real()
   if (compare_as_dates)
   {
     MYSQL_TIME ltime;
-    if (get_date(&ltime, TIME_FUZZY_DATE))
+    if (get_date(&ltime, 0))
       return 0;
 
     return TIME_to_double(&ltime);
@@ -2567,7 +2567,7 @@ longlong Item_func_min_max::val_int()
   if (compare_as_dates)
   {
     MYSQL_TIME ltime;
-    if (get_date(&ltime, TIME_FUZZY_DATE))
+    if (get_date(&ltime, 0))
       return 0;
 
     return TIME_to_ulonglong(&ltime);
@@ -2597,7 +2597,7 @@ my_decimal *Item_func_min_max::val_decimal(my_decimal *dec)
   if (compare_as_dates)
   {
     MYSQL_TIME ltime;
-    if (get_date(&ltime, TIME_FUZZY_DATE))
+    if (get_date(&ltime, 0))
       return 0;
 
     return date2my_decimal(&ltime, dec);
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 024b743fc32..50ca7d91a53 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -3618,10 +3618,10 @@ void Item_func_dyncol_create::prepare_arguments()
       }
       break;
     case DYN_COL_DATETIME:
-      args[valpos]->get_date(&vals[i].x.time_value, TIME_FUZZY_DATE);
+      args[valpos]->get_date(&vals[i].x.time_value, 0);
       break;
     case DYN_COL_DATE:
-      args[valpos]->get_date(&vals[i].x.time_value, TIME_FUZZY_DATE);
+      args[valpos]->get_date(&vals[i].x.time_value, 0);
       break;
     case DYN_COL_TIME:
       args[valpos]->get_time(&vals[i].x.time_value);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index e044441b5d1..3322863a76d 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -791,7 +791,7 @@ longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
   MYSQL_TIME ltime;
   longlong res;
   int dummy;                                /* unused */
-  if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
+  if (get_arg0_date(&ltime, 0))
   {
     /* got NULL, leave the incl_endp intact */
     return LONGLONG_MIN;
@@ -855,14 +855,14 @@ longlong Item_func_dayofmonth::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  return get_arg0_date(&ltime, TIME_FUZZY_DATE) ? 0 : (longlong) ltime.day;
+  return get_arg0_date(&ltime, 0) ? 0 : (longlong) ltime.day;
 }
 
 longlong Item_func_month::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  return get_arg0_date(&ltime, TIME_FUZZY_DATE) ? 0 : (longlong) ltime.month;
+  return get_arg0_date(&ltime, 0) ? 0 : (longlong) ltime.month;
 }
 
 
@@ -907,7 +907,7 @@ longlong Item_func_quarter::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
+  if (get_arg0_date(&ltime, 0))
     return 0;
   return (longlong) ((ltime.month+2)/3);
 }
@@ -981,7 +981,7 @@ longlong Item_func_week::val_int()
   DBUG_ASSERT(fixed == 1);
   uint year;
   MYSQL_TIME ltime;
-  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
+  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))
     return 0;
   return (longlong) calc_week(&ltime,
 			      week_mode((uint) args[1]->val_int()),
@@ -994,7 +994,7 @@ longlong Item_func_yearweek::val_int()
   DBUG_ASSERT(fixed == 1);
   uint year,week;
   MYSQL_TIME ltime;
-  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
+  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))
     return 0;
   week= calc_week(&ltime, 
 		  (week_mode((uint) args[1]->val_int()) | WEEK_YEAR),
@@ -1008,7 +1008,7 @@ longlong Item_func_weekday::val_int()
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
   
-  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
+  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE))
     return 0;
 
   return (longlong) calc_weekday(calc_daynr(ltime.year, ltime.month,
@@ -1050,7 +1050,7 @@ longlong Item_func_year::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  return get_arg0_date(&ltime, TIME_FUZZY_DATE) ? 0 : (longlong) ltime.year;
+  return get_arg0_date(&ltime, 0) ? 0 : (longlong) ltime.year;
 }
 
 
@@ -1082,7 +1082,7 @@ longlong Item_func_year::val_int_endpoint(bool left_endp, bool *incl_endp)
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
+  if (get_arg0_date(&ltime, 0))
   {
     /* got NULL, leave the incl_endp intact */
     return LONGLONG_MIN;
@@ -1354,7 +1354,7 @@ longlong Item_temporal_func::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE))
+  if (get_date(&ltime, 0))
     return 0;
   longlong v= TIME_to_ulonglong(&ltime);
   return ltime.neg ? -v : v;
@@ -1365,7 +1365,7 @@ double Item_temporal_func::val_real()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE))
+  if (get_date(&ltime, 0))
     return 0;
   return TIME_to_double(&ltime);
 }
@@ -1744,7 +1744,7 @@ String *Item_func_date_format::val_str(String *str)
   int is_time_flag = is_time_format ? TIME_TIME_ONLY : 0;
   DBUG_ASSERT(fixed == 1);
   
-  if (get_arg0_date(&l_time, TIME_FUZZY_DATE | is_time_flag))
+  if (get_arg0_date(&l_time, is_time_flag))
     return 0;
   
   if (!(format = args[1]->val_str(str)) || !format->length())
@@ -1923,10 +1923,15 @@ bool Item_date_add_interval::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
 {
   INTERVAL interval;
 
-  if (args[0]->get_date(ltime, TIME_NO_ZERO_DATE | TIME_FUZZY_DATE | TIME_NO_ZERO_IN_DATE) ||
+  if (args[0]->get_date(ltime, 0) ||
       get_interval_value(args[1], int_type, &value, &interval))
     return (null_value=1);
 
+  if (ltime->time_type != MYSQL_TIMESTAMP_TIME &&
+      check_date_with_warn(ltime, TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE,
+                           MYSQL_TIMESTAMP_ERROR))
+    return (null_value=1);
+
   if (date_sub_interval)
     interval.neg = !interval.neg;
 
@@ -2019,7 +2024,7 @@ longlong Item_extract::val_int()
   long neg;
   int is_time_flag = date_value ? 0 : TIME_TIME_ONLY;
 
-  if (get_arg0_date(&ltime, TIME_FUZZY_DATE | is_time_flag))
+  if (get_arg0_date(&ltime, is_time_flag))
     return 0;
   neg= ltime.neg ? -1 : 1;
 
@@ -2430,7 +2435,7 @@ bool Item_func_add_time::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
 
   if (is_date)                        // TIMESTAMP function
   {
-    if (get_arg0_date(&l_time1, TIME_FUZZY_DATE) || 
+    if (get_arg0_date(&l_time1, 0) || 
         args[1]->get_time(&l_time2) ||
         l_time1.time_type == MYSQL_TIMESTAMP_TIME || 
         l_time2.time_type != MYSQL_TIMESTAMP_TIME)
diff --git a/sql/time.cc b/sql/time.cc
index 42e33eaf8e0..f106bf1bb5f 100644
--- a/sql/time.cc
+++ b/sql/time.cc
@@ -263,7 +263,7 @@ str_to_datetime_with_warn(const char *str, uint length, MYSQL_TIME *l_time,
   @param nr            integer part of the number to convert
   @param sec_part      microsecond part of the number
   @param ltime         converted value will be written here
-  @param fuzzydate     conversion flags (TIME_FUZZY_DATE, etc)
+  @param fuzzydate     conversion flags (TIME_INVALID_DATE, etc)
   @param str           original number, as a Lazy_string. For the warning
   @param field_name    field name or NULL if not a field. For the warning
   
@@ -280,6 +280,7 @@ static bool number_to_time_with_warn(bool neg, ulonglong nr, ulong sec_part,
 
   if (fuzzydate & TIME_TIME_ONLY)
   {
+    fuzzydate= TIME_TIME_ONLY; // clear other flags
     f_type= MYSQL_TYPE_TIME;
     res= number_to_time(neg, nr, sec_part, ltime, &was_cut);
   }
@@ -289,7 +290,7 @@ static bool number_to_time_with_warn(bool neg, ulonglong nr, ulong sec_part,
     res= neg ? -1 : number_to_datetime(nr, sec_part, ltime, fuzzydate, &was_cut);
   }
 
-  if (res < 0 || (was_cut && !(fuzzydate & TIME_FUZZY_DATE)))
+  if (res < 0 || (was_cut && (fuzzydate & TIME_NO_ZERO_IN_DATE)))
   {
     make_truncated_value_warning(current_thd,
                                  MYSQL_ERROR::WARN_LEVEL_WARN, str,