bk commit into 5.1 tree (tnurnberg:1.2390) BUG#23093

[Tatjana A Nuernberg]

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-01-16 18:21:29+01:00, tnurnberg@stripped +3 -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). Have the
  second group also throw an error on those dates.

  mysql-test/r/func_time.result@stripped, 2007-01-16 18:21:23+01:00, tnurnberg@stripped +18
-0
    Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
    
    Show that we throw on dates with a odd number of digits
    (not counting 'T' in ISO-datetime) not just on INSERT, but on YEAR(),
    CAST() and everything else that goes through str_to_datetime() rather
    than number_to_datetime() as well.

  mysql-test/t/func_time.test@stripped, 2007-01-16 18:21:23+01:00, tnurnberg@stripped +16 -0
    Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date)
    
    Show that we throw on dates with a odd number of digits
    (not counting 'T' in ISO-datetime) not just on INSERT, but on YEAR(),
    CAST() and everything else that goes through str_to_datetime() rather
    than number_to_datetime() as well.

  sql-common/my_time.c@stripped, 2007-01-16 18:21:23+01:00, tnurnberg@stripped +11 -5
    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 while CAST(), YEAR() etc. go through
    string_to_datetime(). Have string_to_datetime() reject numeric dates
    (those with no separators except, optionally, T for ISO-datetimes)
    with an odd number of digits.

# 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.azundris.com
# Root:	/home/tnurnberg/work/mysql-5.1-maint-23093

--- 1.38/sql-common/my_time.c	2006-12-31 01:06:37 +01:00
+++ 1.39/sql-common/my_time.c	2007-01-16 18:21:23 +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)
@@ -169,7 +169,7 @@ str_to_datetime(const char *str, uint le
   const char *pos, *last_field_pos;
   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, found_t= 0;
   uint frac_pos, frac_len;
   DBUG_ENTER("str_to_datetime");
   DBUG_PRINT("ENTER",("str: %.*s",length,str));
@@ -199,7 +199,8 @@ 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') && (found_t= 1)));
        pos++)
     ;
 
@@ -213,9 +214,14 @@ str_to_datetime(const char *str, uint le
     field_length= year_length;
     is_internal_format= 1;
     format_position= internal_format_positions;
+
+    if((digits + found_t) % 2)
+      DBUG_RETURN(MYSQL_TIMESTAMP_ERROR);       /* odd number of digits, can't be a valid
date */
   }
   else
   {
+    found_t= 0;
+
     if (format_position[0] >= 3)                /* If year is after HHMMDD */
     {
       /*
@@ -320,7 +326,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 +349,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 */

--- 1.92/mysql-test/r/func_time.result	2006-11-29 21:30:58 +01:00
+++ 1.93/mysql-test/r/func_time.result	2007-01-16 18:21:23 +01:00
@@ -1217,4 +1217,22 @@ 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'
+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
+select * from t1;
+d	dt	ts
+0000-00-00	0000-00-00 00:00:00	0000-00-00 00:00:00
+drop table t1;
 End of 5.1 tests

--- 1.77/mysql-test/t/func_time.test	2006-11-29 21:30:58 +01:00
+++ 1.78/mysql-test/t/func_time.test	2007-01-16 18:21:23 +01:00
@@ -710,5 +710,21 @@ 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:="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) ;
+select * from t1;
+drop table t1;
 
 --echo End of 5.1 tests