Below is the list of changes that have just been committed into a local
5.0 repository of jimw. When jimw 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
1.1923 05/07/14 10:07:03 jimw@stripped +15 -0
Fix number to date conversion so it always honors the NO_ZERO_DATE,
NO_ZERO_IN_DATE, and INVALID_DATES bits of SQL_MODE. (Bug #5906)
sql/field.cc
1.272 05/07/14 10:07:00 jimw@stripped +92 -118
Update conversion of numbers to date, datetime, and timestamp to
use number_to_datetime() and report errors and warnings correctly
and consistently.
sql-common/my_time.c
1.14 05/07/14 10:07:00 jimw@stripped +49 -40
Expand check_date() to check NO_ZERO_DATE and NO_ZERO_IN_DATE, and
use it from number_to_datetime() as well as str_to_datetime(). Also,
make number_to_datetime() return -1 on error so we can distinguish
between a violation of NO_ZERO_DATE and other errors.
mysql-test/t/timezone2.test
1.8 05/07/14 10:07:00 jimw@stripped +5 -0
Add new test for checking DST gap handling for numbers converted
to timestamps.
mysql-test/t/strict.test
1.14 05/07/14 10:06:59 jimw@stripped +15 -0
Add new regression test
mysql-test/r/type_datetime.result
1.26 05/07/14 10:06:59 jimw@stripped +6 -6
Update results
mysql-test/r/timezone2.result
1.12 05/07/14 10:06:59 jimw@stripped +9 -0
Update results
mysql-test/r/strict.result
1.19 05/07/14 10:06:59 jimw@stripped +13 -0
Update results
mysql-test/r/ps_7ndb.result
1.33 05/07/14 10:06:59 jimw@stripped +12 -4
Update results
mysql-test/r/ps_6bdb.result
1.33 05/07/14 10:06:59 jimw@stripped +4 -4
Update results
mysql-test/r/ps_5merge.result
1.30 05/07/14 10:06:59 jimw@stripped +8 -8
Update results
mysql-test/r/ps_4heap.result
1.29 05/07/14 10:06:59 jimw@stripped +4 -4
Update results
mysql-test/r/ps_3innodb.result
1.33 05/07/14 10:06:59 jimw@stripped +4 -4
Update results
mysql-test/r/ps_2myisam.result
1.30 05/07/14 10:06:59 jimw@stripped +4 -4
Update results
libmysql/libmysql.c
1.223 05/07/14 10:06:59 jimw@stripped +3 -2
Enable fuzzy date handling when converting strings and numbers
to datetime fields.
include/my_time.h
1.10 05/07/14 10:06:59 jimw@stripped +1 -1
Pass flags to number_to_datetime() so it can check things
like NO_ZERO_DATE.
# 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: jimw
# Host: rama.(none)
# Root: /home/jimw/my/mysql-5.0-5906
--- 1.222/libmysql/libmysql.c 2005-07-12 01:58:14 -07:00
+++ 1.223/libmysql/libmysql.c 2005-07-14 10:06:59 -07:00
@@ -3600,7 +3600,7 @@
case MYSQL_TYPE_TIMESTAMP:
{
MYSQL_TIME *tm= (MYSQL_TIME *)buffer;
- (void) str_to_datetime(value, length, tm, 0, &err);
+ (void) str_to_datetime(value, length, tm, TIME_FUZZY_DATE, &err);
*param->error= test(err) && (param->buffer_type == MYSQL_TYPE_DATE &&
tm->time_type != MYSQL_TIMESTAMP_DATE);
break;
@@ -3718,7 +3718,8 @@
case MYSQL_TYPE_DATETIME:
{
int error;
- value= number_to_datetime(value, (MYSQL_TIME *) buffer, 1, &error);
+ value= number_to_datetime(value, (MYSQL_TIME *) buffer, TIME_FUZZY_DATE,
+ &error);
*param->error= test(error);
break;
}
--- 1.271/sql/field.cc 2005-07-03 17:44:30 -07:00
+++ 1.272/sql/field.cc 2005-07-14 10:07:00 -07:00
@@ -4469,13 +4469,12 @@
bool in_dst_time_gap;
THD *thd= table->in_use;
+ /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */
have_smth_to_conv= (str_to_datetime(from, len, &l_time,
- ((table->in_use->variables.sql_mode &
- MODE_NO_ZERO_DATE) |
- MODE_NO_ZERO_IN_DATE),
- &error) >
+ (table->in_use->variables.sql_mode &
+ MODE_NO_ZERO_DATE), &error) >
MYSQL_TIMESTAMP_ERROR);
-
+
if (error || !have_smth_to_conv)
{
error= 1;
@@ -4488,16 +4487,15 @@
{
if (!(tmp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap)))
{
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
ER_WARN_DATA_OUT_OF_RANGE,
from, len, MYSQL_TIMESTAMP_DATETIME, !error);
-
error= 1;
}
else if (in_dst_time_gap)
{
set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_INVALID_TIMESTAMP,
+ ER_WARN_INVALID_TIMESTAMP,
from, len, MYSQL_TIMESTAMP_DATETIME, !error);
error= 1;
}
@@ -4522,8 +4520,8 @@
int error= 0;
if (nr < 0 || nr > 99991231235959.0)
{
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE,
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_WARN_DATA_OUT_OF_RANGE,
nr, MYSQL_TIMESTAMP_DATETIME);
nr= 0; // Avoid overflow on buff
error= 1;
@@ -4541,35 +4539,37 @@
bool in_dst_time_gap;
THD *thd= table->in_use;
- if (number_to_datetime(nr, &l_time, 0, &error))
+ /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */
+ long tmp= number_to_datetime(nr, &l_time, MODE_NO_ZERO_IN_DATE, &error);
+ if (tmp < 0)
+ {
+ error= 2;
+ }
+ if (tmp == 0 && thd->variables.sql_mode & MODE_NO_ZERO_DATE)
+ {
+ error= 2;
+ }
+
+ if (!error && tmp)
{
if (!(timestamp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap)))
{
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE,
- nr, MYSQL_TIMESTAMP_DATETIME, 1);
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_WARN_DATA_OUT_OF_RANGE,
+ nr, MYSQL_TIMESTAMP_DATETIME, 1);
error= 1;
}
-
if (in_dst_time_gap)
{
set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_INVALID_TIMESTAMP,
- nr, MYSQL_TIMESTAMP_DATETIME, !error);
+ ER_WARN_INVALID_TIMESTAMP,
+ nr, MYSQL_TIMESTAMP_DATETIME, 1);
error= 1;
}
- }
- else if (error)
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- WARN_DATA_TRUNCATED,
- nr, MYSQL_TIMESTAMP_DATETIME, 1);
- if (!error && timestamp == 0 &&
- (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE))
- {
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ } else if (error)
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
WARN_DATA_TRUNCATED,
nr, MYSQL_TIMESTAMP_DATETIME, 1);
- }
#ifdef WORDS_BIGENDIAN
if (table->s->db_low_byte_first)
@@ -4579,7 +4579,7 @@
else
#endif
longstore(ptr,(uint32) timestamp);
-
+
return error;
}
@@ -5152,14 +5152,14 @@
TIME l_time;
uint32 tmp;
int error;
-
+
if (str_to_datetime(from, len, &l_time, TIME_FUZZY_DATE |
(table->in_use->variables.sql_mode &
(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
MODE_INVALID_DATES)),
&error) <= MYSQL_TIMESTAMP_ERROR)
{
- tmp=0;
+ tmp= 0;
error= 2;
}
else
@@ -5190,56 +5190,50 @@
if (nr < 0.0 || nr > 99991231.0)
{
tmp=0L;
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE,
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_WARN_DATA_OUT_OF_RANGE,
nr, MYSQL_TIMESTAMP_DATE);
error= 1;
}
else
tmp=(long) rint(nr);
- /*
- We don't need to check for zero dates here as this date type is only
- used in .frm tables from very old MySQL versions
- */
-
-#ifdef WORDS_BIGENDIAN
- if (table->s->db_low_byte_first)
- {
- int4store(ptr,tmp);
- }
- else
-#endif
- longstore(ptr,tmp);
- return error;
+ return Field_date::store(tmp);
}
int Field_date::store(longlong nr)
{
- long tmp;
- int error= 0;
- if (nr >= LL(19000000000000) && nr < LL(99991231235959))
- nr=nr/LL(1000000); // Timestamp to date
- if (nr < 0 || nr > LL(99991231))
+ TIME not_used;
+ int error;
+ longlong initial_nr= nr;
+
+ nr= number_to_datetime(nr, ¬_used, (TIME_FUZZY_DATE |
+ (table->in_use->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE |
+ MODE_NO_ZERO_DATE |
+ MODE_INVALID_DATES))), &error);
+
+ if (nr < 0)
{
- tmp=0L;
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE,
- nr, MYSQL_TIMESTAMP_DATE, 0);
- error= 1;
+ nr= 0;
+ error= 2;
}
- else
- tmp=(long) nr;
+
+ if (error)
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ error == 2 ? ER_WARN_DATA_OUT_OF_RANGE :
+ WARN_DATA_TRUNCATED, initial_nr,
+ MYSQL_TIMESTAMP_DATETIME, 1);
#ifdef WORDS_BIGENDIAN
if (table->s->db_low_byte_first)
{
- int4store(ptr,tmp);
+ int4store(ptr, nr);
}
else
#endif
- longstore(ptr,tmp);
+ longstore(ptr, nr);
return error;
}
@@ -5363,7 +5357,7 @@
MODE_INVALID_DATES))),
&error) <= MYSQL_TIMESTAMP_ERROR)
{
- tmp=0L;
+ tmp= 0L;
error= 2;
}
else
@@ -5372,7 +5366,7 @@
if (error)
set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
from, len, MYSQL_TIMESTAMP_DATE, 1);
-
+
int3store(ptr,tmp);
return error;
}
@@ -5383,7 +5377,7 @@
if (nr < 0.0 || nr > 99991231235959.0)
{
int3store(ptr,(int32) 0);
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
WARN_DATA_TRUNCATED, nr, MYSQL_TIMESTAMP_DATE);
return 1;
}
@@ -5393,52 +5387,28 @@
int Field_newdate::store(longlong nr)
{
- int32 tmp;
- int error= 0;
- if (nr >= LL(100000000) && nr <= LL(99991231235959))
- nr=nr/LL(1000000); // Timestamp to date
- if (nr < 0L || nr > 99991231L)
+ TIME l_time;
+ long tmp;
+ int error;
+ if ((tmp= number_to_datetime(nr, &l_time,
+ (TIME_FUZZY_DATE |
+ (table->in_use->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
+ MODE_INVALID_DATES))),
+ &error) < 0))
{
- tmp=0;
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE, nr,
- MYSQL_TIMESTAMP_DATE, 1);
- error= 1;
+ tmp= 0L;
+ error= 2;
}
else
- {
- uint month, day;
+ tmp= l_time.day + l_time.month*32 + l_time.year*16*32;
- tmp=(int32) nr;
- if (tmp)
- {
- if (tmp < YY_PART_YEAR*10000L) // Fix short dates
- tmp+= (uint32) 20000000L;
- else if (tmp < 999999L)
- tmp+= (uint32) 19000000L;
-
- month= (uint) ((tmp/100) % 100);
- day= (uint) (tmp%100);
- if (month > 12 || day > 31)
- {
- tmp=0L; // Don't allow date to change
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE, nr,
- MYSQL_TIMESTAMP_DATE, 1);
- error= 1;
- }
- else
- tmp= day + month*32 + (tmp/10000)*16*32;
- }
- else if (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE)
- {
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE,
- 0, MYSQL_TIMESTAMP_DATE);
- error= 1;
- }
- }
- int3store(ptr, tmp);
+ if (error)
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ error == 2 ? ER_WARN_DATA_OUT_OF_RANGE :
+ WARN_DATA_TRUNCATED,nr,MYSQL_TIMESTAMP_DATE, 1);
+
+ int3store(ptr,tmp);
return error;
}
@@ -5565,7 +5535,7 @@
int error;
ulonglong tmp= 0;
enum enum_mysql_timestamp_type func_res;
-
+
func_res= str_to_datetime(from, len, &time_tmp,
(TIME_FUZZY_DATE |
(table->in_use->variables.sql_mode &
@@ -5578,7 +5548,7 @@
error= 1; // Fix if invalid zero date
if (error)
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
ER_WARN_DATA_OUT_OF_RANGE,
from, len, MYSQL_TIMESTAMP_DATETIME, 1);
@@ -5615,20 +5585,24 @@
TIME not_used;
int error;
longlong initial_nr= nr;
-
- nr= number_to_datetime(nr, ¬_used, 1, &error);
- if (error)
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- WARN_DATA_TRUNCATED, initial_nr,
- MYSQL_TIMESTAMP_DATETIME, 1);
- else if (nr == 0 && table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE)
+ nr= number_to_datetime(nr, ¬_used, (TIME_FUZZY_DATE |
+ (table->in_use->variables.sql_mode &
+ (MODE_NO_ZERO_IN_DATE |
+ MODE_NO_ZERO_DATE |
+ MODE_INVALID_DATES))), &error);
+
+ if (nr < 0)
{
- set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_WARN_DATA_OUT_OF_RANGE,
- initial_nr, MYSQL_TIMESTAMP_DATE, 1);
- error= 1;
+ nr= 0;
+ error= 2;
}
+
+ if (error)
+ set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN,
+ error == 2 ? ER_WARN_DATA_OUT_OF_RANGE :
+ WARN_DATA_TRUNCATED, initial_nr,
+ MYSQL_TIMESTAMP_DATETIME, 1);
#ifdef WORDS_BIGENDIAN
if (table->s->db_low_byte_first)
--- 1.11/mysql-test/r/timezone2.result 2005-04-01 19:17:13 -08:00
+++ 1.12/mysql-test/r/timezone2.result 2005-07-14 10:06:59 -07:00
@@ -40,6 +40,12 @@
Warnings:
Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2
insert into t1 (i, ts) values
+(unix_timestamp(20030330015959),20030330015959),
+(unix_timestamp(20030330023000),20030330023000),
+(unix_timestamp(20030330030000),20030330030000);
+Warnings:
+Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2
+insert into t1 (i, ts) values
(unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
insert into t1 (i, ts) values
(unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'),
@@ -51,6 +57,9 @@
select * from t1;
i ts
1046473200 2003-02-28 23:00:00
+1048985999 2003-03-30 00:59:59
+1048986000 2003-03-30 01:00:00
+1048986000 2003-03-30 01:00:00
1048985999 2003-03-30 00:59:59
1048986000 2003-03-30 01:00:00
1048986000 2003-03-30 01:00:00
--- 1.7/mysql-test/t/timezone2.test 2005-04-01 19:17:13 -08:00
+++ 1.8/mysql-test/t/timezone2.test 2005-07-14 10:07:00 -07:00
@@ -48,6 +48,11 @@
(unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'),
(unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'),
(unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00');
+# Values around and in spring time-gap
+insert into t1 (i, ts) values
+ (unix_timestamp(20030330015959),20030330015959),
+ (unix_timestamp(20030330023000),20030330023000),
+ (unix_timestamp(20030330030000),20030330030000);
# Normal value with DST
insert into t1 (i, ts) values
(unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
--- 1.29/mysql-test/r/ps_2myisam.result 2005-06-27 04:12:07 -07:00
+++ 1.30/mysql-test/r/ps_2myisam.result 2005-07-14 10:06:59 -07:00
@@ -2976,25 +2976,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
--- 1.32/mysql-test/r/ps_3innodb.result 2005-06-27 04:12:07 -07:00
+++ 1.33/mysql-test/r/ps_3innodb.result 2005-07-14 10:06:59 -07:00
@@ -2959,25 +2959,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
--- 1.28/mysql-test/r/ps_4heap.result 2005-06-27 04:12:07 -07:00
+++ 1.29/mysql-test/r/ps_4heap.result 2005-07-14 10:06:59 -07:00
@@ -2960,25 +2960,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
--- 1.29/mysql-test/r/ps_5merge.result 2005-06-27 04:12:07 -07:00
+++ 1.30/mysql-test/r/ps_5merge.result 2005-07-14 10:06:59 -07:00
@@ -2896,25 +2896,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
@@ -5908,25 +5908,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
--- 1.32/mysql-test/r/ps_6bdb.result 2005-06-27 04:12:07 -07:00
+++ 1.33/mysql-test/r/ps_6bdb.result 2005-07-14 10:06:59 -07:00
@@ -2959,25 +2959,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
--- 1.9/include/my_time.h 2004-12-15 16:14:59 -08:00
+++ 1.10/include/my_time.h 2005-07-14 10:06:59 -07:00
@@ -53,7 +53,7 @@
str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
uint flags, int *was_cut);
longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res,
- my_bool fuzzy_date, int *was_cut);
+ uint flags, int *was_cut);
ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *time);
ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *time);
ulonglong TIME_to_ulonglong_time(const MYSQL_TIME *time);
--- 1.32/mysql-test/r/ps_7ndb.result 2005-06-27 04:12:07 -07:00
+++ 1.33/mysql-test/r/ps_7ndb.result 2005-07-14 10:06:59 -07:00
@@ -5,6 +5,8 @@
a int, b varchar(30),
primary key(a)
) engine = 'NDB' ;
+Warnings:
+Warning 1266 Using storage engine MyISAM for table 't1'
create table t9
(
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
@@ -18,6 +20,8 @@
c32 set('monday', 'tuesday', 'wednesday'),
primary key(c1)
) engine = 'NDB' ;
+Warnings:
+Warning 1266 Using storage engine MyISAM for table 't9'
delete from t1 ;
insert into t1 values (1,'one');
insert into t1 values (2,'two');
@@ -1334,6 +1338,8 @@
a int, b varchar(30),
primary key(a)
) engine = 'NDB' ;
+Warnings:
+Warning 1266 Using storage engine MyISAM for table 't2'
insert into t2(a,b) select a, b from t1 ;
prepare stmt1 from 'update t1 set a=? where b=?
and a in (select ? from t2
@@ -1524,6 +1530,8 @@
drop table if exists t2 ;
create table t2 (id int auto_increment primary key)
ENGINE= 'NDB' ;
+Warnings:
+Warning 1266 Using storage engine MyISAM for table 't2'
prepare stmt1 from ' select last_insert_id() ' ;
insert into t2 values (NULL) ;
execute stmt1 ;
@@ -2959,25 +2967,25 @@
Warning 1265 Data truncated for column 'c17' at row 1
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
Warnings:
Warning 1264 Out of range value adjusted for column 'c13' at row 1
-Warning 1265 Data truncated for column 'c14' at row 1
+Warning 1264 Out of range value adjusted for column 'c14' 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
--- 1.13/sql-common/my_time.c 2005-01-04 08:21:51 -08:00
+++ 1.14/sql-common/my_time.c 2005-07-14 10:07:00 -07:00
@@ -56,11 +56,14 @@
}
/*
- check date.
+ Check datetime value for validity according to flags.
- SYNOPOSIS
- bool check_date()
- time Date to check.
+ SYNOPSIS
+ check_date()
+ ltime - Date to check.
+ not_zero_date - ltime is not the zero date
+ flags - flags to check
+ was_cut - set to whether the value was truncated
NOTES
Here we assume that year and month is ok !
@@ -69,18 +72,35 @@
RETURN
0 ok
- 1 errro
+ 1 error
*/
-bool check_date(MYSQL_TIME *ltime)
+bool check_date(const MYSQL_TIME *ltime, bool not_zero_date, ulong flags,
+ int *was_cut)
{
- if (ltime->month && ltime->day > days_in_month[ltime->month-1])
+
+ if (not_zero_date)
{
- if (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
- ltime->day != 29)
- return 1;
+ if ((((flags & TIME_NO_ZERO_IN_DATE) || !(flags & TIME_FUZZY_DATE)) &&
+ (ltime->month == 0 || ltime->day == 0)) ||
+ (!(flags & TIME_INVALID_DATES) &&
+ ltime->month && ltime->day > days_in_month[ltime->month-1] &&
+ (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
+ ltime->day != 29)))
+ {
+ *was_cut= 2;
+ return TRUE;
+ }
}
- return 0;
+ else if (flags & TIME_NO_ZERO_DATE)
+ {
+ /*
+ We don't set *was_cut here to signal that the problem was a zero date
+ and not an invalid date
+ */
+ return TRUE;
+ }
+ return FALSE;
}
@@ -100,7 +120,7 @@
TIME_INVALID_DATES Allow 2000-02-31
was_cut 0 Value ok
1 If value was cut during conversion
- 2 Date part was withing ranges but date was wrong
+ 2 Date part was within ranges but date was wrong
DESCRIPTION
At least the following formats are recogniced (based on number of digits)
@@ -168,8 +188,6 @@
*was_cut= 1;
DBUG_RETURN(MYSQL_TIMESTAMP_NONE);
}
- if (flags & TIME_NO_ZERO_IN_DATE)
- flags&= ~TIME_FUZZY_DATE;
is_internal_format= 0;
/* This has to be changed if want to activate different timestamp formats */
@@ -385,22 +403,10 @@
if (year_length == 2 && not_zero_date)
l_time->year+= (l_time->year < YY_PART_YEAR ? 2000 : 1900);
- if (!not_zero_date && (flags & TIME_NO_ZERO_DATE))
- {
- /*
- We don't set *was_cut here to signal that the problem was a zero date
- and not an invalid date
- */
- goto err;
- }
-
if (number_of_fields < 3 ||
l_time->year > 9999 || l_time->month > 12 ||
l_time->day > 31 || l_time->hour > 23 ||
- l_time->minute > 59 || l_time->second > 59 ||
- (!(flags & TIME_FUZZY_DATE) && (l_time->month == 0 ||
- l_time->day == 0) &&
- not_zero_date))
+ l_time->minute > 59 || l_time->second > 59)
{
/* Only give warning for a zero date if there is some garbage after */
if (!not_zero_date) /* If zero date */
@@ -418,15 +424,12 @@
goto err;
}
+ if (check_date(l_time, not_zero_date, flags, was_cut))
+ goto err;
+
l_time->time_type= (number_of_fields <= 3 ?
MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
- if (not_zero_date && !(flags & TIME_INVALID_DATES) && check_date(l_time))
- {
- *was_cut= 2; /* Not correct date */
- goto err;
- }
-
for (; str != end ; str++)
{
if (!my_isspace(&my_charset_latin1,*str))
@@ -881,9 +884,10 @@
number_to_datetime()
nr - datetime value as number
time_res - pointer for structure for broken-down representation
- fuzzy_date - indicates whenever we allow fuzzy dates
- was_cut - set ot 1 if there was some kind of error during
- conversion or to 0 if everything was OK.
+ flags - flags to use in validating date, as in str_to_datetime()
+ was_cut 0 Value ok
+ 1 If value was cut during conversion
+ 2 Date part was within ranges but date was wrong
DESCRIPTION
Convert a datetime value of formats YYMMDD, YYYYMMDD, YYMMDDHHMSS,
@@ -893,12 +897,13 @@
This function also checks if datetime value fits in DATETIME range.
RETURN VALUE
+ -1 Timestamp with wrong values
+ anything else DATETIME as integer in YYYYMMDDHHMMSS format
Datetime value in YYYYMMDDHHMMSS format.
- If input value is not valid datetime value then 0 is returned.
*/
longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res,
- my_bool fuzzy_date, int *was_cut)
+ uint flags, int *was_cut)
{
long part1,part2;
@@ -952,13 +957,17 @@
if (time_res->year <= 9999 && time_res->month <= 12 &&
time_res->day <= 31 && time_res->hour <= 23 &&
time_res->minute <= 59 && time_res->second <= 59 &&
- (fuzzy_date || (time_res->month != 0 && time_res->day != 0) || nr==0))
+ !check_date(time_res, (nr != 0), flags, was_cut))
return nr;
+ /* Don't want to have was_cut get set if NO_ZERO_DATE was violated. */
+ if (!nr && flags & TIME_NO_ZERO_DATE)
+ return LL(-1);
+
err:
*was_cut= 1;
- return LL(0);
+ return LL(-1);
}
--- 1.25/mysql-test/r/type_datetime.result 2005-05-04 05:53:05 -07:00
+++ 1.26/mysql-test/r/type_datetime.result 2005-07-14 10:06:59 -07:00
@@ -100,12 +100,12 @@
insert into t1 values (20030102030460),(20030102036301),(20030102240401),
(20030132030401),(20031302030401),(100001202030401);
Warnings:
-Warning 1265 Data truncated for column 't' at row 1
-Warning 1265 Data truncated for column 't' at row 2
-Warning 1265 Data truncated for column 't' at row 3
-Warning 1265 Data truncated for column 't' at row 4
-Warning 1265 Data truncated for column 't' at row 5
-Warning 1265 Data truncated for column 't' at row 6
+Warning 1264 Out of range value adjusted for column 't' at row 1
+Warning 1264 Out of range value adjusted for column 't' at row 2
+Warning 1264 Out of range value adjusted for column 't' at row 3
+Warning 1264 Out of range value adjusted for column 't' at row 4
+Warning 1264 Out of range value adjusted for column 't' at row 5
+Warning 1264 Out of range value adjusted for column 't' at row 6
select * from t1;
t
0000-00-00 00:00:00
--- 1.18/mysql-test/r/strict.result 2005-05-13 01:22:21 -07:00
+++ 1.19/mysql-test/r/strict.result 2005-07-14 10:06:59 -07:00
@@ -1235,3 +1235,16 @@
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
create table t1(a varbinary(65537));
ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
+set @@sql_mode='traditional';
+create table t1 (d date);
+insert into t1 values ('2000-10-00');
+ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1
+insert into t1 values (1000);
+ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1
+insert into t1 values ('2000-10-01');
+update t1 set d = 1100;
+ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1
+select * from t1;
+d
+2000-10-01
+drop table t1;
--- 1.13/mysql-test/t/strict.test 2005-05-13 01:22:21 -07:00
+++ 1.14/mysql-test/t/strict.test 2005-07-14 10:06:59 -07:00
@@ -1093,3 +1093,18 @@
create table t1(a varchar(65537));
--error 1074
create table t1(a varbinary(65537));
+
+#
+# Bug #5906: handle invalid date due to conversion
+#
+set @@sql_mode='traditional';
+create table t1 (d date);
+--error 1292
+insert into t1 values ('2000-10-00');
+--error 1292
+insert into t1 values (1000);
+insert into t1 values ('2000-10-01');
+--error 1292
+update t1 set d = 1100;
+select * from t1;
+drop table t1;
| Thread |
|---|
| • bk commit into 5.0 tree (jimw:1.1923) BUG#5906 | Jim Winstead | 14 Jul |
