Fix for bug #4756 "STR_TO_DATE() returning bad results with AM/PM".

Added support of converion specifiers mentioned in manual but missing in code.


mysql-test/r/date_formats.result:
  Added tests of str_to_date() with new %T, %r and %V, %v conversion specifiers, and also 
  some other specifiers for which tests were missing previously.
mysql-test/t/date_formats.test:
  Added tests of str_to_date() and new %T, %r and %V, %v conversion specifiers, and also 
  some other specifiers for which tests were missing previously.
sql/item_timefunc.cc:
  Added support for %T, %r, %V, %v, %X, %x conversion specifiers to extract_date_time()
  function. Also simplified a bit calculation of dates from week number.

1 files changed, 65 insertions, 7 deletions

diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index 6a4935ef3f8..23da99f38bb 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -90,16 +90,23 @@ insert into t1 values
 ('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'),
 ('10:20:10', '%H:%i:%s'),
 ('10:20:10', '%h:%i:%s.%f'),
+('10:20:10', '%T'),
 ('10:20:10AM', '%h:%i:%s%p'),
+('10:20:10AM', '%r'),
 ('10:20:10.44AM', '%h:%i:%s.%f%p'),
 ('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'),
 ('15 September 2001', '%d %M %Y'),
 ('15 SEPTEMB 2001', '%d %M %Y'),
 ('15 MAY 2001', '%d %b %Y'),
+('15th May 2001', '%D %b %Y'),
 ('Sunday 15 MAY 2001', '%W %d %b %Y'),
 ('Sund 15 MAY 2001', '%W %d %b %Y'),
 ('Tuesday 00 2002', '%W %U %Y'),
 ('Thursday 53 1998', '%W %u %Y'),
+('Sunday 01 2001', '%W %v %x'),
+('Tuesday 52 2001', '%W %V %X'),
+('060 2004', '%j %Y'),
+('4 53 1998', '%w %u %Y'),
 ('15-01-2001', '%d-%m-%Y %H:%i:%S'),
 ('15-01-20', '%d-%m-%y'),
 ('15-2001-1', '%d-%Y-%c');
@@ -114,16 +121,23 @@ date	format	str_to_date
 2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	2003-01-02 23:11:12
 10:20:10	%H:%i:%s	0000-00-00 10:20:10
 10:20:10	%h:%i:%s.%f	0000-00-00 10:20:10
+10:20:10	%T	0000-00-00 10:20:10
 10:20:10AM	%h:%i:%s%p	0000-00-00 10:20:10
+10:20:10AM	%r	0000-00-00 10:20:10
 10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	2001-01-15 12:59:58
 15 September 2001	%d %M %Y	2001-09-15 00:00:00
 15 SEPTEMB 2001	%d %M %Y	2001-09-15 00:00:00
 15 MAY 2001	%d %b %Y	2001-05-15 00:00:00
+15th May 2001	%D %b %Y	2001-05-15 00:00:00
 Sunday 15 MAY 2001	%W %d %b %Y	2001-05-15 00:00:00
 Sund 15 MAY 2001	%W %d %b %Y	2001-05-15 00:00:00
 Tuesday 00 2002	%W %U %Y	2002-01-01 00:00:00
 Thursday 53 1998	%W %u %Y	1998-12-31 00:00:00
+Sunday 01 2001	%W %v %x	2001-01-07 00:00:00
+Tuesday 52 2001	%W %V %X	2002-01-01 00:00:00
+060 2004	%j %Y	2004-02-29 00:00:00
+4 53 1998	%w %u %Y	1998-12-31 00:00:00
 15-01-2001	%d-%m-%Y %H:%i:%S	2001-01-15 00:00:00
 15-01-20	%d-%m-%y	2020-01-15 00:00:00
 15-2001-1	%d-%Y-%c	2001-01-15 00:00:00
@@ -138,16 +152,23 @@ date	format	con
 2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	2003-01-02 23:11:12
 10:20:10	%H:%i:%s	0000-00-00 10:20:10
 10:20:10	%h:%i:%s.%f	0000-00-00 10:20:10
+10:20:10	%T	0000-00-00 10:20:10
 10:20:10AM	%h:%i:%s%p	0000-00-00 10:20:10
+10:20:10AM	%r	0000-00-00 10:20:10
 10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	2001-01-15 12:59:58
 15 September 2001	%d %M %Y	2001-09-15 00:00:00
 15 SEPTEMB 2001	%d %M %Y	2001-09-15 00:00:00
 15 MAY 2001	%d %b %Y	2001-05-15 00:00:00
+15th May 2001	%D %b %Y	2001-05-15 00:00:00
 Sunday 15 MAY 2001	%W %d %b %Y	2001-05-15 00:00:00
 Sund 15 MAY 2001	%W %d %b %Y	2001-05-15 00:00:00
 Tuesday 00 2002	%W %U %Y	2002-01-01 00:00:00
 Thursday 53 1998	%W %u %Y	1998-12-31 00:00:00
+Sunday 01 2001	%W %v %x	2001-01-07 00:00:00
+Tuesday 52 2001	%W %V %X	2002-01-01 00:00:00
+060 2004	%j %Y	2004-02-29 00:00:00
+4 53 1998	%w %u %Y	1998-12-31 00:00:00
 15-01-2001	%d-%m-%Y %H:%i:%S	2001-01-15 00:00:00
 15-01-20	%d-%m-%y	2020-01-15 00:00:00
 15-2001-1	%d-%Y-%c	2001-01-15 00:00:00
@@ -162,16 +183,23 @@ date	format	datetime
 2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	2003-01-02 23:11:12
 10:20:10	%H:%i:%s	0000-00-00 10:20:10
 10:20:10	%h:%i:%s.%f	0000-00-00 10:20:10
+10:20:10	%T	0000-00-00 10:20:10
 10:20:10AM	%h:%i:%s%p	0000-00-00 10:20:10
+10:20:10AM	%r	0000-00-00 10:20:10
 10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	2001-01-15 12:59:58
 15 September 2001	%d %M %Y	2001-09-15 00:00:00
 15 SEPTEMB 2001	%d %M %Y	2001-09-15 00:00:00
 15 MAY 2001	%d %b %Y	2001-05-15 00:00:00
+15th May 2001	%D %b %Y	2001-05-15 00:00:00
 Sunday 15 MAY 2001	%W %d %b %Y	2001-05-15 00:00:00
 Sund 15 MAY 2001	%W %d %b %Y	2001-05-15 00:00:00
 Tuesday 00 2002	%W %U %Y	2002-01-01 00:00:00
 Thursday 53 1998	%W %u %Y	1998-12-31 00:00:00
+Sunday 01 2001	%W %v %x	2001-01-07 00:00:00
+Tuesday 52 2001	%W %V %X	2002-01-01 00:00:00
+060 2004	%j %Y	2004-02-29 00:00:00
+4 53 1998	%w %u %Y	1998-12-31 00:00:00
 15-01-2001	%d-%m-%Y %H:%i:%S	2001-01-15 00:00:00
 15-01-20	%d-%m-%y	2020-01-15 00:00:00
 15-2001-1	%d-%Y-%c	2001-01-15 00:00:00
@@ -186,16 +214,23 @@ date	format	date2
 2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	2003-01-02
 10:20:10	%H:%i:%s	0000-00-00
 10:20:10	%h:%i:%s.%f	0000-00-00
+10:20:10	%T	0000-00-00
 10:20:10AM	%h:%i:%s%p	0000-00-00
+10:20:10AM	%r	0000-00-00
 10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	2001-01-15
 15 September 2001	%d %M %Y	2001-09-15
 15 SEPTEMB 2001	%d %M %Y	2001-09-15
 15 MAY 2001	%d %b %Y	2001-05-15
+15th May 2001	%D %b %Y	2001-05-15
 Sunday 15 MAY 2001	%W %d %b %Y	2001-05-15
 Sund 15 MAY 2001	%W %d %b %Y	2001-05-15
 Tuesday 00 2002	%W %U %Y	2002-01-01
 Thursday 53 1998	%W %u %Y	1998-12-31
+Sunday 01 2001	%W %v %x	2001-01-07
+Tuesday 52 2001	%W %V %X	2002-01-01
+060 2004	%j %Y	2004-02-29
+4 53 1998	%w %u %Y	1998-12-31
 15-01-2001	%d-%m-%Y %H:%i:%S	2001-01-15
 15-01-20	%d-%m-%y	2020-01-15
 15-2001-1	%d-%Y-%c	2001-01-15
@@ -210,16 +245,23 @@ date	format	time
 2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12
 10:20:10	%H:%i:%s	10:20:10
 10:20:10	%h:%i:%s.%f	10:20:10
+10:20:10	%T	10:20:10
 10:20:10AM	%h:%i:%s%p	10:20:10
+10:20:10AM	%r	10:20:10
 10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
 15 September 2001	%d %M %Y	00:00:00
 15 SEPTEMB 2001	%d %M %Y	00:00:00
 15 MAY 2001	%d %b %Y	00:00:00
+15th May 2001	%D %b %Y	00:00:00
 Sunday 15 MAY 2001	%W %d %b %Y	00:00:00
 Sund 15 MAY 2001	%W %d %b %Y	00:00:00
 Tuesday 00 2002	%W %U %Y	00:00:00
 Thursday 53 1998	%W %u %Y	00:00:00
+Sunday 01 2001	%W %v %x	00:00:00
+Tuesday 52 2001	%W %V %X	00:00:00
+060 2004	%j %Y	00:00:00
+4 53 1998	%w %u %Y	00:00:00
 15-01-2001	%d-%m-%Y %H:%i:%S	00:00:00
 15-01-20	%d-%m-%y	00:00:00
 15-2001-1	%d-%Y-%c	00:00:00
@@ -234,16 +276,23 @@ date	format	time2
 2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12
 10:20:10	%H:%i:%s	10:20:10
 10:20:10	%h:%i:%s.%f	10:20:10
+10:20:10	%T	10:20:10
 10:20:10AM	%h:%i:%s%p	10:20:10
+10:20:10AM	%r	10:20:10
 10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
 15 September 2001	%d %M %Y	00:00:00
 15 SEPTEMB 2001	%d %M %Y	00:00:00
 15 MAY 2001	%d %b %Y	00:00:00
+15th May 2001	%D %b %Y	00:00:00
 Sunday 15 MAY 2001	%W %d %b %Y	00:00:00
 Sund 15 MAY 2001	%W %d %b %Y	00:00:00
 Tuesday 00 2002	%W %U %Y	00:00:00
 Thursday 53 1998	%W %u %Y	00:00:00
+Sunday 01 2001	%W %v %x	00:00:00
+Tuesday 52 2001	%W %V %X	00:00:00
+060 2004	%j %Y	00:00:00
+4 53 1998	%w %u %Y	00:00:00
 15-01-2001	%d-%m-%Y %H:%i:%S	00:00:00
 15-01-20	%d-%m-%y	00:00:00
 15-2001-1	%d-%Y-%c	00:00:00
@@ -258,10 +307,13 @@ insert into t1 values
 ('15 Septembei 2001', '%d %M %Y'),
 ('15 Ju 2001', '%d %M %Y'),
 ('Sund 15 MA', '%W %d %b %Y'),
-('Sunday 01 2001', '%W %V %X'),
 ('Thursdai 12 1998', '%W %u %Y'),
-(NULL, get_format(DATE,'USA')),
-('Tuesday 52 2001', '%W %V %X');
+('Sunday 01 2001', '%W %v %X'),
+('Tuesday 52 2001', '%W %V %x'),
+('Tuesday 52 2001', '%W %V %Y'),
+('Tuesday 52 2001', '%W %u %x'),
+('7 53 1998', '%w %u %Y'),
+(NULL, get_format(DATE,'USA'));
 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 PM	%Y-%m-%d %H:%i:%S %p	NULL
@@ -273,10 +325,13 @@ date	format	str_to_date
 15 Septembei 2001	%d %M %Y	NULL
 15 Ju 2001	%d %M %Y	NULL
 Sund 15 MA	%W %d %b %Y	NULL
-Sunday 01 2001	%W %V %X	NULL
 Thursdai 12 1998	%W %u %Y	NULL
+Sunday 01 2001	%W %v %X	NULL
+Tuesday 52 2001	%W %V %x	NULL
+Tuesday 52 2001	%W %V %Y	NULL
+Tuesday 52 2001	%W %u %x	NULL
+7 53 1998	%w %u %Y	NULL
 NULL	%m.%d.%Y	NULL
-Tuesday 52 2001	%W %V %X	NULL
 select date,format,concat(str_to_date(date, format),'') as con from t1;
 date	format	con
 2003-01-02 10:11:12 PM	%Y-%m-%d %H:%i:%S %p	NULL
@@ -288,10 +343,13 @@ date	format	con
 15 Septembei 2001	%d %M %Y	NULL
 15 Ju 2001	%d %M %Y	NULL
 Sund 15 MA	%W %d %b %Y	NULL
-Sunday 01 2001	%W %V %X	NULL
 Thursdai 12 1998	%W %u %Y	NULL
+Sunday 01 2001	%W %v %X	NULL
+Tuesday 52 2001	%W %V %x	NULL
+Tuesday 52 2001	%W %V %Y	NULL
+Tuesday 52 2001	%W %u %x	NULL
+7 53 1998	%w %u %Y	NULL
 NULL	%m.%d.%Y	NULL
-Tuesday 52 2001	%W %V %X	NULL
 truncate table t1;
 insert into t1 values
 ('10:20:10AM', '%h:%i:%s'),