Backport of TIME->MYSQL_TIME / Y2K fixset

   
Made year 2000 handling more uniform
Removed year 2000 handling out from calc_days()
The above removes some bugs in date/datetimes with year between 0 and 200
Now we get a note when we insert a datetime value into a date column
For default values to CREATE, don't give errors for warning level NOTE
Fixed some compiler failures
Added library ws2_32 for windows compilation (needed if we want to compile with IOCP support)
Removed duplicate typedef TIME and replaced it with MYSQL_TIME

Better (more complete) fix for: Bug#21103 "DATE column not compared as DATE"
Fixed properly Bug#18997 "DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year value"
Fixed Bug#23093 "Implicit conversion of 9912101 to date does not match cast(9912101 as date)"
 


include/my_time.h:
  Removed not used define YY_MAGIC_BELOW
  Added prototype for year_2000_handling()
mysql-test/r/date_formats.result:
  Updated results (fixed bug in date_format() with year < 99
mysql-test/r/func_sapdb.result:
  Added more testing of make_date()
mysql-test/r/ps_2myisam.result:
  Now we get a note when we insert a datetime value into a date column
mysql-test/r/ps_3innodb.result:
  Now we get a note when we insert a datetime value into a date column
mysql-test/r/ps_4heap.result:
  Now we get a note when we insert a datetime value into a date column
mysql-test/r/ps_5merge.result:
  Now we get a note when we insert a datetime value into a date column
mysql-test/r/ps_7ndb.result:
  Now we get a note when we insert a datetime value into a date column
mysql-test/r/strict.result:
  zero-year in str_to_date() throws warning in strict
mysql-test/r/type_date.result:
  Added test for date conversions
mysql-test/r/type_datetime.result:
  Added testcase for datetime to date conversion.
mysql-test/t/date_formats.test:
  Added testing of dates < 200
mysql-test/t/func_sapdb.test:
  More testing of makedate()
mysql-test/t/type_date.test:
  Added test for date conversions
mysql-test/t/type_datetime.test:
  Added testcase for datetime to date conversion
sql/field.cc:
  Give note if we insert a datetime value in a date field
  Don't give notes if we are doing internal test conversions (like from convert_constant_item())
  More documentation (store functions can now return '3' to inform that the function did return a NOTE (not warning or error))
  Revert some changes in Field_newdate::store() to get more optimal code
  Field::set_warning() will now ignore notes if CHECK_FIELD_IGNORE is set.
  New parameters to make_truncated_value_warning()
sql/field.h:
  Give note if we insert a datetime value in a date field
  Don't give notes if we are doing internal test conversions (like from convert_constant_item())
  More documentation (store functions can now return '3' to inform that the function did return a NOTE (not warning or error))
  Revert some changes in Field_newdate::store() to get more optimal code
  Field::set_warning() will now ignore notes if CHECK_FIELD_IGNORE is set.
  New parameters to make_truncated_value_warning()
sql/item.cc:
  Give note if we insert a datetime value in a date field
  Don't give notes if we are doing internal test conversions (like from convert_constant_item())
  More documentation (store functions can now return '3' to inform that the function did return a NOTE (not warning or error))
  Revert some changes in Field_newdate::store() to get more optimal code
  Field::set_warning() will now ignore notes if CHECK_FIELD_IGNORE is set.
  New parameters to make_truncated_value_warning()
sql/item.h:
  TIME -> MYSQL_TIME
sql/item_cmpfunc.cc:
  Don't print notes in convert_constant_item()
sql/item_func.h:
  TIME -> MYSQL_TIME
sql/item_timefunc.cc:
  New parameters to make_truncated_value_warning()
  Moved year 2000 handling out from calc_days()
sql/item_timefunc.h:
  TIME -> MYSQL_TIME
sql/my_decimal.cc:
  TIME -> MYSQL_TIME
sql/my_decimal.h:
  TIME -> MYSQL_TIME
sql/mysql_priv.h:
  Added error level to make_truncated_value_warning()
sql/protocol.cc:
  TIME -> MYSQL_TIME
sql/protocol.h:
  TIME -> MYSQL_TIME
sql/sp.cc:
  TIME -> MYSQL_TIME
sql/sql_base.cc:
  Make testing of result value of save_in_field() uniform
sql/sql_class.h:
  TIME -> MYSQL_TIME
sql/sql_show.cc:
  TIME -> MYSQL_TIME
sql/structs.h:
  TIME -> MYSQL_TIME
sql/time.cc:
  Added error level to make_truncated_value_warning()
sql/tztime.cc:
  TIME -> MYSQL_TIME
sql/tztime.h:
  TIME -> MYSQL_TIME
sql/unireg.cc:
  For default values to CREATE, don't give errors for warning level NOTE
  (Fixed failed CREATE when we give a datetime value to a date field)
sql-common/my_time.c:
  Added year_2000_handling()
  Removed year 2000 handling from calc_daynr()

14 files changed, 169 insertions, 2 deletions

diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index f4ec6830cd5..d62c865bb3c 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -88,6 +88,8 @@ create table t1 (date char(30), format char(30) not null);
 insert into t1 values
 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
 ('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
+('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
+('03-01-02 8:11:2.123456',   '%Y-%m-%d %H:%i:%S.%#'),
 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
@@ -119,6 +121,8 @@ select date,format,str_to_date(date, format) as str_to_date from t1;
 date	format	str_to_date
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	2003-01-02 10:11:12
 03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	0003-01-02 08:11:02
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02 02:11:12.123450
@@ -150,6 +154,8 @@ select date,format,concat('',str_to_date(date, format)) as con from t1;
 date	format	con
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	2003-01-02 10:11:12
 03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	0003-01-02 08:11:02
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02 02:11:12.123450
@@ -181,6 +187,8 @@ select date,format,cast(str_to_date(date, format) as datetime) as datetime from
 date	format	datetime
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	2003-01-02 10:11:12
 03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	0003-01-02 08:11:02
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02 02:11:12.123450
@@ -212,6 +220,8 @@ select date,format,DATE(str_to_date(date, format)) as date2 from t1;
 date	format	date2
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	2003-01-02
 03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	0003-01-02
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	2003-01-02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02
@@ -243,6 +253,8 @@ select date,format,TIME(str_to_date(date, format)) as time from t1;
 date	format	time
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	10:11:12
 03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	02:11:12.123450
@@ -274,6 +286,8 @@ select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
 date	format	time2
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	10:11:12
 03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	02:11:12.123450
@@ -399,14 +413,14 @@ select date,format,str_to_date(date, format) as str_to_date from t1;
 date	format	str_to_date
 10:20:10AM	%h:%i:%s	0000-00-00 10:20:10
 2003-01-02 10:11:12	%Y-%m-%d %h:%i:%S	2003-01-02 10:11:12
-03-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	0003-01-02 22:11:12
+03-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
 Warnings:
 Warning	1292	Truncated incorrect datetime value: '10:20:10AM'
 select date,format,concat(str_to_date(date, format),'') as con from t1;
 date	format	con
 10:20:10AM	%h:%i:%s	0000-00-00 10:20:10
 2003-01-02 10:11:12	%Y-%m-%d %h:%i:%S	2003-01-02 10:11:12
-03-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	0003-01-02 22:11:12
+03-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
 Warnings:
 Warning	1292	Truncated incorrect datetime value: '10:20:10AM'
 drop table t1;
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index 4a6ec9a7ba0..dbae7e551e5 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -75,6 +75,12 @@ NULL
 select weekofyear("1997-11-30 23:59:59.000001");
 weekofyear("1997-11-30 23:59:59.000001")
 48
+select makedate(03,1);
+makedate(03,1)
+2003-01-01
+select makedate('0003',1);
+makedate('0003',1)
+2003-01-01
 select makedate(1997,1);
 makedate(1997,1)
 1997-01-01
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 2b2a29b2122..abd98067dae 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -2961,20 +2961,26 @@ delete from t9 ;
 test_sequence
 -- insert into date/time columns --
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
@@ -3005,6 +3011,7 @@ 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:
+Note	1265	Data truncated for column 'c13' 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 f30262ef219..b90cf996db4 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -2944,20 +2944,26 @@ delete from t9 ;
 test_sequence
 -- insert into date/time columns --
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
@@ -2988,6 +2994,7 @@ 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:
+Note	1265	Data truncated for column 'c13' 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 a012e516404..aed2fcea1ce 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -2945,20 +2945,26 @@ delete from t9 ;
 test_sequence
 -- insert into date/time columns --
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
@@ -2989,6 +2995,7 @@ 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:
+Note	1265	Data truncated for column 'c13' 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 ac9c8ae55ff..ed85e4411aa 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -2881,20 +2881,26 @@ delete from t9 ;
 test_sequence
 -- insert into date/time columns --
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
@@ -2925,6 +2931,7 @@ 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:
+Note	1265	Data truncated for column 'c13' 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
@@ -5895,20 +5902,26 @@ delete from t9 ;
 test_sequence
 -- insert into date/time columns --
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
@@ -5939,6 +5952,7 @@ 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:
+Note	1265	Data truncated for column 'c13' 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 2673448489a..0df2733aa7a 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -2944,20 +2944,26 @@ delete from t9 ;
 test_sequence
 -- insert into date/time columns --
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
+Note	1265	Data truncated for column 'c13' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1264	Out of range value adjusted for column 'c13' at row 1
@@ -2988,6 +2994,7 @@ 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:
+Note	1265	Data truncated for column 'c13' 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 eecdc545be7..e2e3dd67af5 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -206,6 +206,8 @@ INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));
 INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
 INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
+Warnings:
+Note	1265	Data truncated for column 'col1' at row 1
 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
 ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1
 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 644d4d971c6..9f51fc0371c 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -110,3 +110,29 @@ select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_dat
 1
 1
 drop table t1;
+select @d:=1111, year(@d), month(@d), day(@d), cast(@d as date);
+@d:=1111	year(@d)	month(@d)	day(@d)	cast(@d as date)
+1111	2000	11	11	2000-11-11
+select @d:=011111, year(@d), month(@d), day(@d), cast(@d as date);
+@d:=011111	year(@d)	month(@d)	day(@d)	cast(@d as date)
+11111	2001	11	11	2001-11-11
+select @d:=1311, year(@d), month(@d), day(@d), cast(@d as date);
+@d:=1311	year(@d)	month(@d)	day(@d)	cast(@d as date)
+1311	NULL	NULL	NULL	NULL
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '1311'
+Warning	1292	Truncated incorrect datetime value: '1311'
+Warning	1292	Truncated incorrect datetime value: '1311'
+Warning	1292	Truncated incorrect datetime value: '1311'
+create table t1 (d  date , dt datetime , ts timestamp);
+insert into t1 values (9912101,9912101,9912101);
+Warnings:
+Warning	1264	Out of range value adjusted for column 'd' at row 1
+Warning	1264	Out of range value adjusted for column 'dt' at row 1
+Warning	1265	Data truncated for column 'ts' at row 1
+insert into t1 values (11111,11111,11111);
+select * from t1;
+d	dt	ts
+0000-00-00	0000-00-00 00:00:00	0000-00-00 00:00:00
+2001-11-11	2001-11-11 00:00:00	2001-11-11 00:00:00
+drop table t1;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 3a28410b7dc..d86404aa97f 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -192,6 +192,41 @@ CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMA
 SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
 CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
 101112.098700
+set @org_mode=@@sql_mode;
+create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
+Warnings:
+Note	1265	Data truncated for column 'da' at row 1
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `da` date default '1962-03-03',
+  `dt` datetime default '1962-03-03 00:00:00'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values ();
+insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
+Warnings:
+Note	1265	Data truncated for column 'da' at row 1
+set @@sql_mode='ansi,traditional';
+insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
+Warnings:
+Note	1265	Data truncated for column 'da' at row 1
+insert into t1 set dt='2007-03-23 13:49:38',da=dt;
+Warnings:
+Note	1265	Data truncated for column 'da' at row 1
+insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
+ERROR 22007: Incorrect date value: '2007-03-32' for column 'da' at row 1
+select * from t1;
+da	dt
+1962-03-03	1962-03-03 00:00:00
+2007-03-23	2007-03-23 13:49:38
+2007-03-23	2007-03-23 13:49:38
+2007-03-23	2007-03-23 13:49:38
+drop table t1;
+create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
+ERROR 42000: Invalid default value for 'da'
+create table t1 (t time default '916:00:00 a');
+ERROR 42000: Invalid default value for 't'
+set @@sql_mode= @org_mode;
 create table t1 (f1 date, f2 datetime, f3 timestamp);
 insert into t1(f1) values(curdate());
 select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test
index 3054ec53faa..faa6d4242db 100644
--- a/mysql-test/t/date_formats.test
+++ b/mysql-test/t/date_formats.test
@@ -129,6 +129,8 @@ create table t1 (date char(30), format char(30) not null);
 insert into t1 values
 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
 ('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
+('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
+('03-01-02 8:11:2.123456',   '%Y-%m-%d %H:%i:%S.%#'),
 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test
index 77d7366afe6..bb65cbaa774 100644
--- a/mysql-test/t/func_sapdb.test
+++ b/mysql-test/t/func_sapdb.test
@@ -41,6 +41,8 @@ select datediff("1997-11-30 23:59:59.000001",null);
 
 select weekofyear("1997-11-30 23:59:59.000001");
 
+select makedate(03,1);
+select makedate('0003',1);
 select makedate(1997,1);
 select makedate(1997,0);
 select makedate(9999,365);
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index c6050753943..02cd07e3c16 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -123,3 +123,16 @@ insert into t1 values ('2000-01-01','2000-01-02');
 select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date;
 drop table t1;
 # End of 4.1 tests
+
+#
+# Bug #23093: Implicit conversion of 9912101 to date does not match
+# cast(9912101 as date)
+#
+select @d:=1111, year(@d), month(@d), day(@d), cast(@d as date);
+select @d:=011111, year(@d), month(@d), day(@d), cast(@d as date);
+select @d:=1311, year(@d), month(@d), day(@d), cast(@d as date);
+create table t1 (d  date , dt datetime , ts timestamp);
+insert into t1 values (9912101,9912101,9912101);
+insert into t1 values (11111,11111,11111);
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index fc7b20d77a4..4176cdb9d21 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -141,6 +141,31 @@ SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
 SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
 SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
 
+
+#
+# Test of storing datetime into date fields
+#
+
+set @org_mode=@@sql_mode;
+create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
+show create table t1;
+insert into t1 values ();
+insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
+set @@sql_mode='ansi,traditional';
+insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
+insert into t1 set dt='2007-03-23 13:49:38',da=dt;
+# Test error handling
+--error 1292
+insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
+select * from t1;
+drop table t1;
+--error 1067
+create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
+--error 1067
+create table t1 (t time default '916:00:00 a');
+set @@sql_mode= @org_mode;
+
+
 #
 # Bug#27590: Wrong DATE/DATETIME comparison.  
 #