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

11 files changed, 47 insertions, 36 deletions

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");
+