Below is the list of changes that have just been committed into a local
5.1 repository of tnurnberg. When tnurnberg does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-03-05 16:50:34+01:00, tnurnberg@stripped +11 -0
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
For dates given as integers, INSERT and friends parse as integers
(meaning, dates with an odd number of digits are parsed right to left,
zero-padding from the left), while CAST(), YEAR() etc. parse as strings
(left-to-right). This can lead to counter-intuitive results for invalid
dates (dates with no separators and an odd number of digits). Try our
best to parse dates that have only digits (and, optionally, a T) as
integers to get consistent behaviour.
---
Merge tnurnberg@stripped:/home/bk/mysql-5.1-maint
into mysql.com:/home/tnurnberg/work/mysql-5.1-maint-23093
mysql-test/r/func_time.result@stripped, 2007-03-05 16:50:16+01:00, tnurnberg@stripped +33 -0
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Show that we now process dates consisting only of digits (not
counting 'T' in ISO-datetime) the same way in YEAR(), CAST() etc.
as we do in INSERT.
mysql-test/r/loaddata.result@stripped, 2007-03-05 16:50:17+01:00, tnurnberg@stripped +0 -3
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/ps_2myisam.result@stripped, 2007-03-05 16:50:18+01:00, tnurnberg@stripped +1 -1
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/ps_3innodb.result@stripped, 2007-03-05 16:50:20+01:00, tnurnberg@stripped +1 -1
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/ps_4heap.result@stripped, 2007-03-05 16:50:21+01:00, tnurnberg@stripped +1 -1
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/ps_5merge.result@stripped, 2007-03-05 16:50:22+01:00, tnurnberg@stripped +2 -2
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/ps_7ndb.result@stripped, 2007-03-05 16:50:23+01:00, tnurnberg@stripped +1 -1
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/rpl_rewrt_db.result@stripped, 2007-03-05 16:50:24+01:00, tnurnberg@stripped +0 -3
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/r/type_datetime.result@stripped, 2007-03-05 16:50:25+01:00, tnurnberg@stripped +1 -1
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Adjust test/results now that we process dates consisting only of
digits (not counting 'T' in ISO-datetime) the same way in YEAR(),
CAST() etc. as we do in INSERT and friends.
mysql-test/t/func_time.test@stripped, 2007-03-05 16:50:26+01:00, tnurnberg@stripped +21 -0
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
Show that we now process dates consisting only of digits (not
counting 'T' in ISO-datetime) the same way in YEAR(), CAST() etc.
as we do in INSERT.
sql-common/my_time.c@stripped, 2007-03-05 16:50:27+01:00, tnurnberg@stripped +78 -8
Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
For dates given as integers, INSERT and friends go through
number_to_datetime(), while CAST(), YEAR() etc. go through
string_to_datetime(). Have string_to_datetime() parse numeric dates
(those with no separators except, optionally, T for ISO-datetimes)
using number_to_datetime() for consistency.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: tnurnberg
# Host: sin.mysql.com
# Root: /home/tnurnberg/work/mysql-5.1-maint-23093
--- 1.52/mysql-test/r/ps_2myisam.result 2007-03-02 15:25:00 +01:00
+++ 1.53/mysql-test/r/ps_2myisam.result 2007-03-05 16:50:18 +01:00
@@ -3031,7 +3031,7 @@ c1 c13 c14 c15 c16 c17
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
-51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
+51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
--- 1.55/mysql-test/r/ps_3innodb.result 2007-03-02 15:25:00 +01:00
+++ 1.56/mysql-test/r/ps_3innodb.result 2007-03-05 16:50:20 +01:00
@@ -3014,7 +3014,7 @@ c1 c13 c14 c15 c16 c17
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
-51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
+51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
--- 1.51/mysql-test/r/ps_4heap.result 2007-03-02 15:25:01 +01:00
+++ 1.52/mysql-test/r/ps_4heap.result 2007-03-05 16:50:21 +01:00
@@ -3015,7 +3015,7 @@ c1 c13 c14 c15 c16 c17
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
-51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
+51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
--- 1.52/mysql-test/r/ps_5merge.result 2007-03-02 15:25:01 +01:00
+++ 1.53/mysql-test/r/ps_5merge.result 2007-03-05 16:50:22 +01:00
@@ -2951,7 +2951,7 @@ c1 c13 c14 c15 c16 c17
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
-51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
+51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
@@ -5965,7 +5965,7 @@ c1 c13 c14 c15 c16 c17
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
-51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
+51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
--- 1.9/mysql-test/r/rpl_rewrt_db.result 2006-10-03 13:00:03 +02:00
+++ 1.10/mysql-test/r/rpl_rewrt_db.result 2007-03-05 16:50:24 +01:00
@@ -29,8 +29,6 @@ Warnings:
Warning 1265 Data truncated for column 'a' at row 1
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
-Warning 1265 Data truncated for column 'a' at row 2
-Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
select * from rewrite.t1;
@@ -44,7 +42,6 @@ load data infile '../std_data_ln/loaddat
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
-Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
select * from rewrite.t1;
a b c d
--- 1.51/mysql-test/r/ps_7ndb.result 2007-03-02 15:25:01 +01:00
+++ 1.52/mysql-test/r/ps_7ndb.result 2007-03-05 16:50:23 +01:00
@@ -3014,7 +3014,7 @@ c1 c13 c14 c15 c16 c17
42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
-51 2010-00-00 2010-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
+51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000
60 NULL NULL 1991-01-01 01:01:01 NULL NULL
--- 1.41/sql-common/my_time.c 2007-03-05 08:57:46 +01:00
+++ 1.42/sql-common/my_time.c 2007-03-05 16:50:27 +01:00
@@ -123,7 +123,7 @@ my_bool check_date(const MYSQL_TIME *lti
2 Date part was within ranges but date was wrong
DESCRIPTION
- At least the following formats are recogniced (based on number of digits)
+ At least the following formats are recognised (based on number of digits)
YYMMDD, YYYYMMDD, YYMMDDHHMMSS, YYYYMMDDHHMMSS
YY-MM-DD, YYYY-MM-DD, YY-MM-DD HH.MM.SS
YYYYMMDDTHHMMSS where T is a the character T (ISO8601)
@@ -166,10 +166,10 @@ str_to_datetime(const char *str, uint le
uint add_hours= 0, start_loop;
ulong not_zero_date, allow_space;
my_bool is_internal_format;
- const char *pos, *last_field_pos;
+ const char *pos, *last_field_pos, *pos_t= NULL;
const char *end=str+length;
const uchar *format_position;
- my_bool found_delimitier= 0, found_space= 0;
+ my_bool found_delimiter= 0, found_space= 0;
uint frac_pos, frac_len;
DBUG_ENTER("str_to_datetime");
DBUG_PRINT("ENTER",("str: %.*s",length,str));
@@ -199,14 +199,16 @@ str_to_datetime(const char *str, uint le
(YYYY-MM-DD, YYYYMMDD, YYYYYMMDDHHMMSS)
*/
for (pos=str;
- pos != end && (my_isdigit(&my_charset_latin1,*pos) || *pos == 'T');
+ pos != end && (my_isdigit(&my_charset_latin1,*pos) ||
+ ((*pos == 'T') && (pos_t= pos)));
pos++)
;
digits= (uint) (pos-str);
start_loop= 0; /* Start of scan loop */
date_len[format_position[0]]= 0; /* Length of year field */
- if (pos == end || *pos == '.')
+
+ if (*pos == '.')
{
/* Found date in internal format (only numbers like YYYYMMDD) */
year_length= (digits == 4 || digits == 8 || digits >= 14) ? 4 : 2;
@@ -214,6 +216,74 @@ str_to_datetime(const char *str, uint le
is_internal_format= 1;
format_position= internal_format_positions;
}
+ else if (pos == end)
+ {
+ longlong value;
+
+ for (value= 0, pos= str; pos != end; pos++)
+ {
+ if (pos != pos_t)
+ value=value*10L + (long) (*pos - '0');
+ }
+
+ /*
+ if using T-format (19991210T1234), time component might not
+ be a complete 6 digits as required by number_to_datetime()
+ */
+
+ if (pos_t != NULL)
+ {
+ long len= 6 - (end - pos_t - 1);
+ if (len < 0)
+ {
+ *was_cut= 1;
+ goto err;
+ }
+ while (len--)
+ {
+ value*= 10L;
+ digits++; /* count in 'virtual' digits */
+ }
+
+ digits--; /* subtract T, it's not a digit */
+ }
+
+ l_time->neg= 0;
+
+ if (number_to_datetime(value, l_time, flags, was_cut) == LL(-1))
+ {
+ *was_cut= 1;
+ goto err;
+ }
+
+ /*
+ Undo it. This is so phenomenally retarded there's not even a word
+ for it. Fix later either by not do the Y2K in the lowest-level
+ routines, or better, only do it if a certain flag is given. Fugleee!
+ */
+
+ if ((str[0] == '0') &&
+ (digits == 4 || digits == 8 || digits >= 14))
+ {
+ l_time->year -= (l_time->year >= 2000) ? 2000 : 1900;
+
+ if(check_date(l_time, (value != 0), flags, was_cut))
+ DBUG_RETURN(MYSQL_TIMESTAMP_NONE);
+ }
+
+ if ((end - str) < 10)
+ {
+ if (flags & TIME_DATETIME_ONLY)
+ {
+ *was_cut= 1;
+ DBUG_RETURN(MYSQL_TIMESTAMP_NONE); /* Can't be a full datetime */
+ }
+ DBUG_RETURN(MYSQL_TIMESTAMP_DATE);
+ }
+
+ DBUG_RETURN(MYSQL_TIMESTAMP_DATETIME);
+ }
else
{
if (format_position[0] >= 3) /* If year is after HHMMDD */
@@ -320,7 +390,7 @@ str_to_datetime(const char *str, uint le
found_space= 1;
}
str++;
- found_delimitier= 1; /* Should be a 'normal' date */
+ found_delimiter= 1; /* Should be a 'normal' date */
}
/* Check if next position is AM/PM */
if (i == format_position[6]) /* Seconds, time for AM/PM */
@@ -343,7 +413,7 @@ str_to_datetime(const char *str, uint le
}
last_field_pos= str;
}
- if (found_delimitier && !found_space && (flags & TIME_DATETIME_ONLY))
+ if (found_delimiter && !found_space && (flags & TIME_DATETIME_ONLY))
{
*was_cut= 1;
DBUG_RETURN(MYSQL_TIMESTAMP_NONE); /* Can't be a datetime */
@@ -1066,7 +1136,7 @@ int my_TIME_to_str(const MYSQL_TIME *l_t
2 Date part was within ranges but date was wrong
DESCRIPTION
- Convert a datetime value of formats YYMMDD, YYYYMMDD, YYMMDDHHMSS,
+ Convert a datetime value of formats YYMMDD, YYYYMMDD, YYMMDDHHMMSS,
YYYYMMDDHHMMSS to broken-down TIME representation. Return value in
YYYYMMDDHHMMSS format as side-effect.
--- 1.95/mysql-test/r/func_time.result 2007-03-05 08:57:46 +01:00
+++ 1.96/mysql-test/r/func_time.result 2007-03-05 16:50:16 +01:00
@@ -1252,4 +1252,37 @@ NULL
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)
NULL
+select @d:=9912101, year(@d), month(@d), day(@d), cast(@d as date);
+@d:=9912101 year(@d) month(@d) day(@d) cast(@d as date)
+9912101 NULL NULL NULL NULL
+Warnings:
+Warning 1292 Incorrect datetime value: '9912101'
+Warning 1292 Incorrect datetime value: '9912101'
+Warning 1292 Incorrect datetime value: '9912101'
+Warning 1292 Incorrect datetime value: '9912101'
+select @d:='19991210T101112', year(@d), month(@d), day(@d), cast(@d as date);
+@d:='19991210T101112' year(@d) month(@d) day(@d) cast(@d as date)
+19991210T101112 1999 12 10 1999-12-10
+select @d:='991210T101112', year(@d), month(@d), day(@d), cast(@d as date);
+@d:='991210T101112' year(@d) month(@d) day(@d) cast(@d as date)
+991210T101112 1999 12 10 1999-12-10
+select @d:="991210T1011", year(@d), month(@d), day(@d), cast(@d as date);
+@d:="991210T1011" year(@d) month(@d) day(@d) cast(@d as date)
+991210T1011 1999 12 10 1999-12-10
+create table t1 (d date, dt datetime, ts timestamp);
+insert into t1 values (9912101,9912101,9912101) ;
+Warnings:
+Warning 1264 Out of range value for column 'd' at row 1
+Warning 1264 Out of range value for column 'dt' at row 1
+Warning 1265 Data truncated for column 'ts' at row 1
+insert into t1 values ("19991210T","19991210T","19991210T") ;
+insert into t1 values ("19991210T1012","19991210T1012","19991210T1012") ;
+insert into t1 values ("19991210T123456","19991210T123456","19991210T123456") ;
+select * from t1;
+d dt ts
+0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00
+1999-12-10 1999-12-10 00:00:00 1999-12-10 00:00:00
+1999-12-10 1999-12-10 10:12:00 1999-12-10 10:12:00
+1999-12-10 1999-12-10 12:34:56 1999-12-10 12:34:56
+drop table t1;
End of 5.1 tests
--- 1.36/mysql-test/r/type_datetime.result 2006-11-08 15:51:40 +01:00
+++ 1.37/mysql-test/r/type_datetime.result 2007-03-05 16:50:25 +01:00
@@ -39,7 +39,7 @@ t
0000-00-00 00:00:00
0001-01-01 00:00:00
9999-12-31 00:00:00
-2000-10-10 00:00:00
+2000-01-01 00:00:00
2069-12-31 00:00:00
1970-01-01 00:00:00
1999-12-31 23:59:59
--- 1.80/mysql-test/t/func_time.test 2007-03-05 08:57:46 +01:00
+++ 1.81/mysql-test/t/func_time.test 2007-03-05 16:50:26 +01:00
@@ -742,5 +742,26 @@ select date_sub("90-01-01 00:00:01",INTE
select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
+#
+# Bug #23093 - Implicit conversion of 9912101 to date
+# does not match cast(9912101 as date)
+#
+
+# show that we throw error for odd number of digits in string dates without separator
+select @d:=9912101, year(@d), month(@d), day(@d), cast(@d as date);
+
+# show that ISO dates with 'T' delimiter still work
+select @d:='19991210T101112', year(@d), month(@d), day(@d), cast(@d as date);
+select @d:='991210T101112', year(@d), month(@d), day(@d), cast(@d as date);
+select @d:="991210T1011", year(@d), month(@d), day(@d), cast(@d as date);
+
+# show that we throw for INSERT (integer date parsed by number_to_datetime())
+create table t1 (d date, dt datetime, ts timestamp);
+insert into t1 values (9912101,9912101,9912101) ;
+insert into t1 values ("19991210T","19991210T","19991210T") ;
+insert into t1 values ("19991210T1012","19991210T1012","19991210T1012") ;
+insert into t1 values ("19991210T123456","19991210T123456","19991210T123456") ;
+select * from t1;
+drop table t1;
--echo End of 5.1 tests
--- 1.28/mysql-test/r/loaddata.result 2007-02-14 14:46:09 +01:00
+++ 1.29/mysql-test/r/loaddata.result 2007-03-05 16:50:17 +01:00
@@ -5,8 +5,6 @@ Warnings:
Warning 1265 Data truncated for column 'a' at row 1
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
-Warning 1265 Data truncated for column 'a' at row 2
-Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
SELECT * from t1;
@@ -20,7 +18,6 @@ load data infile '../std_data_ln/loaddat
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
-Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
SELECT * from t1;
a b c d
| Thread |
|---|
| • bk commit into 5.1 tree (tnurnberg:1.2490) BUG#23093 | Tatjana A Nuernberg | 5 Mar |
