diff options
| author | unknown <monty@mishka.local> | 2004-09-28 20:08:00 +0300 |
|---|---|---|
| committer | unknown <monty@mishka.local> | 2004-09-28 20:08:00 +0300 |
| commit | 2a49121590930ad9ef69718caad67304e94b9c00 (patch) | |
| tree | 7f0311baaca32e6242f23dcf0a89ed38e39c9ac0 | |
| parent | e74b00bbc98c1082086422dc9c8578dade9d4a11 (diff) | |
| download | mariadb-git-2a49121590930ad9ef69718caad67304e94b9c00.tar.gz | |
Strict mode & better warnings
Under strict mode MySQL will generate an error message if there was any conversion when assigning data to a field.
Added checking of date/datetime fields.
If strict mode, give error if we have not given value to field without a default value (for INSERT)
client/mysqltest.c:
Added --exit as an option to abort a test in a middle (good for debugging)
include/my_time.h:
Added flags to allow checking of dates in strict mode
include/mysql_com.h:
Added flag to check if field has a default value or not
include/mysqld_error.h:
New error messages for strict mode
include/sql_state.h:
Fixed SQL states (for strict mode tests)
mysql-test/r/auto_increment.result:
Updated error messages
mysql-test/r/func_sapdb.result:
Added test for ALLOW_INVALID_DATES
mysql-test/r/func_str.result:
Updated error messages
mysql-test/r/func_time.result:
Updated error messages
mysql-test/r/insert.result:
Updated error messages
mysql-test/r/loaddata.result:
Updated error messages
mysql-test/r/select.result:
Updated error messages
mysql-test/r/sp.result:
Updated error messages
mysql-test/r/timezone2.result:
Updated error messages
mysql-test/r/type_datetime.result:
Updated error messages
mysql-test/r/type_decimal.result:
Updated error messages
mysql-test/r/type_float.result:
Updated error messages
mysql-test/r/type_ranges.result:
Updated error messages
mysql-test/r/type_time.result:
Updated error messages
mysql-test/r/type_uint.result:
Updated error messages
mysql-test/r/warnings.result:
Updated error messages
mysql-test/t/func_sapdb.test:
Aded test
sql-common/my_time.c:
Added checking of dates
sql/field.cc:
Better error messages
Optimization of warning handling (by introducing of check_int())
Changed to use my_strtoll10()
sql/field.h:
Added check_int()
sql/item_func.cc:
Warnings when dividing by NULL
sql/item_func.h:
Warnings when dividing by NULL
sql/item_timefunc.cc:
Testing of date/datetime
Use macros instead of constants
sql/mysql_priv.h:
New modes (part of strict mode)
sql/mysqld.cc:
New modes (part of strict mode)
sql/opt_range.cc:
Simple optimizations
sql/protocol.cc:
Add note/warning level to find_handler()
sql/set_var.cc:
Added mode 'traditional'
sql/share/czech/errmsg.txt:
New error messages for strict mode
sql/share/danish/errmsg.txt:
New error messages for strict mode
sql/share/dutch/errmsg.txt:
New error messages for strict mode
sql/share/english/errmsg.txt:
New error messages for strict mode
sql/share/estonian/errmsg.txt:
New error messages for strict mode
sql/share/french/errmsg.txt:
New error messages for strict mode
sql/share/german/errmsg.txt:
New error messages for strict mode
sql/share/greek/errmsg.txt:
New error messages for strict mode
sql/share/hungarian/errmsg.txt:
New error messages for strict mode
sql/share/italian/errmsg.txt:
New error messages for strict mode
sql/share/japanese/errmsg.txt:
New error messages for strict mode
sql/share/korean/errmsg.txt:
New error messages for strict mode
sql/share/norwegian-ny/errmsg.txt:
New error messages for strict mode
sql/share/norwegian/errmsg.txt:
New error messages for strict mode
sql/share/polish/errmsg.txt:
New error messages for strict mode
sql/share/portuguese/errmsg.txt:
New error messages for strict mode
sql/share/romanian/errmsg.txt:
New error messages for strict mode
sql/share/russian/errmsg.txt:
New error messages for strict mode
sql/share/serbian/errmsg.txt:
New error messages for strict mode
sql/share/slovak/errmsg.txt:
New error messages for strict mode
sql/share/spanish/errmsg.txt:
New error messages for strict mode
sql/share/swedish/errmsg.txt:
New error messages for strict mode
sql/share/ukrainian/errmsg.txt:
New error messages for strict mode
sql/sp_rcontext.cc:
Add note/warning level to find_handler()
sql/sp_rcontext.h:
Add note/warning level to find_handler()
sql/sql_base.cc:
Fix bug for detecting crashed table
sql/sql_class.cc:
Variables for strct mode
sql/sql_class.h:
Variables for strct mode
sql/sql_error.cc:
In strict mode, convert warnings to errors
sql/sql_insert.cc:
Strict mode
If strict mode, give error if we have not given value to field without a default value
sql/sql_load.cc:
Strict mode
sql/sql_parse.cc:
Strict mode.
Add flag to field if it doesn't have a default value
sql/sql_select.cc:
Added comment
Prepare for upper level handling of table->status
sql/sql_union.cc:
Added THD to write_record()
sql/sql_update.cc:
Strict mode
sql/table.cc:
Handling of default values
sql/time.cc:
Checking of dates
72 files changed, 1964 insertions, 445 deletions
diff --git a/client/mysqltest.c b/client/mysqltest.c index 3287c9738d3..02982e8e37f 100644 --- a/client/mysqltest.c +++ b/client/mysqltest.c @@ -229,11 +229,11 @@ Q_ENABLE_INFO, Q_DISABLE_INFO, Q_ENABLE_METADATA, Q_DISABLE_METADATA, Q_EXEC, Q_DELIMITER, Q_DISPLAY_VERTICAL_RESULTS, Q_DISPLAY_HORIZONTAL_RESULTS, -Q_QUERY_VERTICAL, Q_QUERY_HORIZONTAL, +Q_QUERY_VERTICAL, Q_QUERY_HORIZONTAL, Q_EXIT, Q_UNKNOWN, /* Unknown command. */ Q_COMMENT, /* Comments, ignored. */ -Q_COMMENT_WITH_COMMAND +Q_COMMENT_WITH_COMMAND, }; /* this should really be called command */ @@ -308,6 +308,7 @@ const char *command_names[]= "horizontal_results", "query_vertical", "query_horizontal", + "exit", 0 }; @@ -2592,12 +2593,11 @@ int main(int argc, char **argv) { int error = 0; struct st_query *q; - my_bool require_file=0, q_send_flag=0; + my_bool require_file=0, q_send_flag=0, abort_flag= 0; char save_file[FN_REFLEN]; MY_INIT(argv[0]); { DBUG_ENTER("main"); - DBUG_PROCESS(argv[0]); save_file[0]=0; TMPDIR[0]=0; @@ -2653,7 +2653,7 @@ int main(int argc, char **argv) init_var_hash(&cur_con->mysql); - while (!read_query(&q)) + while (!abort_flag && !read_query(&q)) { int current_line_inc = 1, processed = 0; if (q->type == Q_UNKNOWN || q->type == Q_COMMENT_WITH_COMMAND) @@ -2813,6 +2813,9 @@ int main(int argc, char **argv) case Q_EXEC: (void) do_exec(q); break; + case Q_EXIT: + abort_flag= 1; + break; default: processed = 0; break; } } diff --git a/include/my_time.h b/include/my_time.h index d4dbe459c3b..332b26a15eb 100644 --- a/include/my_time.h +++ b/include/my_time.h @@ -41,8 +41,13 @@ typedef long my_time_t; #define YY_PART_YEAR 70 /* Flags to str_to_datetime */ -#define TIME_FUZZY_DATE 1 -#define TIME_DATETIME_ONLY 2 +#define TIME_FUZZY_DATE 1 +#define TIME_DATETIME_ONLY 2 +/* Must be same as MODE_NO_ZERO_IN_DATE */ +#define TIME_NO_ZERO_IN_DATE (65536L*2*2*2*2*2*2*2) +/* Must be same as MODE_NO_ZERO_DATE */ +#define TIME_NO_ZERO_DATE (TIME_NO_ZERO_IN_DATE*2) +#define TIME_INVALID_DATES (TIME_NO_ZERO_DATE*2) enum enum_mysql_timestamp_type str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, @@ -52,6 +57,7 @@ bool str_to_time(const char *str,uint length, MYSQL_TIME *l_time, int *was_cut); long calc_daynr(uint year,uint month,uint day); +uint calc_days_in_year(uint year); void init_time(void); diff --git a/include/mysql_com.h b/include/mysql_com.h index 3cf1a011e3c..16a1581a6c9 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -79,6 +79,7 @@ enum enum_server_command #define AUTO_INCREMENT_FLAG 512 /* field is a autoincrement field */ #define TIMESTAMP_FLAG 1024 /* Field is a timestamp */ #define SET_FLAG 2048 /* field is a set */ +#define NO_DEFAULT_VALUE_FLAG 4096 /* Field doesn't have default value */ #define NUM_FLAG 32768 /* Field is num (for clients) */ #define PART_KEY_FLAG 16384 /* Intern; Part of some key */ #define GROUP_FLAG 32768 /* Intern: Group field */ diff --git a/include/mysqld_error.h b/include/mysqld_error.h index cf032380e2f..36dd4d00ef2 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -380,4 +380,7 @@ #define ER_TRG_ON_VIEW_OR_TEMP_TABLE 1361 #define ER_TRG_CANT_CHANGE_ROW 1362 #define ER_TRG_NO_SUCH_ROW_IN_TRG 1363 -#define ER_ERROR_MESSAGES 364 +#define ER_NO_DEFAULT_FOR_FIELD 1364 +#define ER_DIVISION_BY_ZERO 1365 +#define ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 1366 +#define ER_ERROR_MESSAGES 367 diff --git a/include/sql_state.h b/include/sql_state.h index 7bf05203463..0b31fc07933 100644 --- a/include/sql_state.h +++ b/include/sql_state.h @@ -92,7 +92,7 @@ ER_PASSWORD_ANONYMOUS_USER, "42000", "", ER_PASSWORD_NOT_ALLOWED, "42000", "", ER_PASSWORD_NO_MATCH, "42000", "", ER_WRONG_VALUE_COUNT_ON_ROW, "21S01", "", -ER_INVALID_USE_OF_NULL, "42000", "", +ER_INVALID_USE_OF_NULL, "22004", "", ER_REGEXP_ERROR, "42000", "", ER_MIX_OF_GROUP_FUNC_AND_FIELDS,"42000", "", ER_NONEXISTING_GRANT, "42000", "", @@ -157,11 +157,12 @@ ER_COLLATION_CHARSET_MISMATCH, "42000", "", ER_WARN_TOO_FEW_RECORDS, "01000", "", ER_WARN_TOO_MANY_RECORDS, "01000", "", ER_WARN_NULL_TO_NOTNULL, "01000", "", -ER_WARN_DATA_OUT_OF_RANGE, "01000", "", +ER_WARN_DATA_OUT_OF_RANGE, "22003", "", ER_WARN_DATA_TRUNCATED, "01000", "", ER_WRONG_NAME_FOR_INDEX, "42000", "", ER_WRONG_NAME_FOR_CATALOG, "42000", "", ER_UNKNOWN_STORAGE_ENGINE, "42000", "", +ER_TRUNCATED_WRONG_VALUE, "22007", "", /* 5.0 */ ER_SP_NO_RECURSIVE_CREATE, "2F003", "", ER_SP_ALREADY_EXISTS, "42000", "", @@ -200,3 +201,4 @@ ER_SP_NO_USE, "42000", "", ER_SP_VARCOND_AFTER_CURSHNDLR, "42000", "", ER_SP_CURSOR_AFTER_HANDLER, "42000", "", ER_SP_CASE_NOT_FOUND, "20000", "", +ER_DIVISION_BY_ZERO, "22012", "", diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index f5ec5f1f852..9dad18e03e7 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -163,7 +163,7 @@ last_insert_id() 255 insert into t1 set i = null; Warnings: -Warning 1264 Data truncated; out of range for column 'i' at row 1 +Warning 1264 Out of range value adjusted for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index 26fd688c312..fb344855e83 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -56,15 +56,25 @@ subdate("1997-12-31 23:59:59.000001", 10) select datediff("1997-12-31 23:59:59.000001","1997-12-30"); datediff("1997-12-31 23:59:59.000001","1997-12-30") 1 +select datediff("1997-11-30 23:59:59.000001","1997-12-31"); +datediff("1997-11-30 23:59:59.000001","1997-12-31") +-31 +SET @@SQL_MODE="ALLOW_INVALID_DATES"; select datediff("1997-11-31 23:59:59.000001","1997-12-31"); datediff("1997-11-31 23:59:59.000001","1997-12-31") -30 -select datediff("1997-11-31 23:59:59.000001",null); -datediff("1997-11-31 23:59:59.000001",null) +SET @@SQL_MODE=""; +select datediff("1997-11-31 23:59:59.000001","1997-12-31"); +datediff("1997-11-31 23:59:59.000001","1997-12-31") +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '1997-11-31 23:59:59.000001' +select datediff("1997-11-30 23:59:59.000001",null); +datediff("1997-11-30 23:59:59.000001",null) NULL -select weekofyear("1997-11-31 23:59:59.000001"); -weekofyear("1997-11-31 23:59:59.000001") -49 +select weekofyear("1997-11-30 23:59:59.000001"); +weekofyear("1997-11-30 23:59:59.000001") +48 select makedate(1997,1); makedate(1997,1) 1997-01-01 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 901fcfbe68b..400d79be835 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -658,16 +658,16 @@ drop table t1, t2; create table t1 (c1 INT, c2 INT UNSIGNED); insert into t1 values ('21474836461','21474836461'); Warnings: -Warning 1265 Data truncated for column 'c1' at row 1 -Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1264 Out of range value adjusted for column 'c1' at row 1 +Warning 1264 Out of range value adjusted for column 'c2' at row 1 insert into t1 values ('-21474836461','-21474836461'); Warnings: -Warning 1265 Data truncated for column 'c1' at row 1 -Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1264 Out of range value adjusted for column 'c1' at row 1 +Warning 1264 Out of range value adjusted for column 'c2' at row 1 show warnings; Level Code Message -Warning 1265 Data truncated for column 'c1' at row 1 -Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1264 Out of range value adjusted for column 'c1' at row 1 +Warning 1264 Out of range value adjusted for column 'c2' at row 1 select * from t1; c1 c2 2147483647 4294967295 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 64ace522fe6..678ad9c9fdc 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -591,7 +591,7 @@ select date_add(time,INTERVAL 1 SECOND) from t1; date_add(time,INTERVAL 1 SECOND) NULL Warnings: -Warning 1264 Data truncated; out of range for column 'time' at row 1 +Warning 1264 Out of range value adjusted for column 'time' at row 1 drop table t1; select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2, last_day('2003-03-32') as f3, last_day('2003-04-01') as f4, diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index a666e016348..474820488a8 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -159,18 +159,18 @@ insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@val Warnings: Warning 1265 Data truncated for column 'f_double' at row 1 Warning 1265 Data truncated for column 'f_float' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float' at row 1 Warning 1265 Data truncated for column 'f_double_7_2' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_7_2' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_7_2' at row 1 Warning 1265 Data truncated for column 'f_float_4_3' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_4_3' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_4_3' at row 1 Warning 1265 Data truncated for column 'f_double_u' at row 1 Warning 1265 Data truncated for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 4 original_value 1e+1111111111a @@ -187,19 +187,19 @@ insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@val Warnings: Warning 1265 Data truncated for column 'f_double' at row 1 Warning 1265 Data truncated for column 'f_float' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float' at row 1 Warning 1265 Data truncated for column 'f_double_7_2' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_7_2' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_7_2' at row 1 Warning 1265 Data truncated for column 'f_float_4_3' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_4_3' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_4_3' at row 1 Warning 1265 Data truncated for column 'f_double_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_u' at row 1 Warning 1265 Data truncated for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 5 original_value -1e+1111111111a @@ -214,12 +214,12 @@ f_float_3_1_u 0.0 set @value= 1e+1111111111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1264 Data truncated; out of range for column 'f_float' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_7_2' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_4_3' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_7_2' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_4_3' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 6 original_value 1.7976931348623e+308 @@ -234,13 +234,13 @@ f_float_3_1_u 99.9 set @value= -1e+1111111111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1264 Data truncated; out of range for column 'f_float' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_7_2' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_4_3' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_7_2' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_4_3' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 7 original_value -1.7976931348623e+308 @@ -255,12 +255,12 @@ f_float_3_1_u 0.0 set @value= 1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1264 Data truncated; out of range for column 'f_float' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_7_2' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_4_3' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_7_2' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_4_3' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 8 original_value 1e+111 @@ -275,13 +275,13 @@ f_float_3_1_u 99.9 set @value= -1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1264 Data truncated; out of range for column 'f_float' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_7_2' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_4_3' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_7_2' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_4_3' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 9 original_value -1e+111 @@ -309,10 +309,10 @@ f_float_3_1_u 1.0 set @value= -1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: -Warning 1264 Data truncated; out of range for column 'f_double_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_double_15_1_u' at row 1 -Warning 1264 Data truncated; out of range for column 'f_float_3_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_double_15_1_u' at row 1 +Warning 1264 Out of range value adjusted for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 11 original_value -1 diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index c0baabcc507..c17b96ee60c 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -43,9 +43,9 @@ drop table t1; create table t1 (a int, b char(10)); load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: -Warning 1265 Data truncated for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 Warning 1262 Row 3 was truncated; it contained more data than there were input columns -Warning 1265 Data truncated for column 'a' at row 5 +Warning 1264 Out of range value adjusted for column 'a' at row 5 Warning 1262 Row 5 was truncated; it contained more data than there were input columns select * from t1; a b @@ -57,7 +57,7 @@ a b truncate table t1; load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; Warnings: -Warning 1265 Data truncated for column 'a' at row 4 +Warning 1264 Out of range value adjusted for column 'a' at row 4 Warning 1261 Row 4 doesn't contain data for all columns select * from t1; a b diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 16b12a436d3..8c8557cc167 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2074,8 +2074,8 @@ INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12 SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; gvid the_success the_fail the_size the_time Warnings: -Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' -Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' +Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 +Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; gvid the_success the_fail the_size the_time DROP TABLE t1,t2; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index f84b224b8e0..4d67ed76188 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1229,7 +1229,7 @@ drop table if exists t3| create table t3 (s1 smallint)| insert into t3 values (123456789012)| Warnings: -Warning 1264 Data truncated; out of range for column 's1' at row 1 +Warning 1264 Out of range value adjusted for column 's1' at row 1 create procedure bug() begin declare exit handler for sqlwarning set @x = 1; diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result new file mode 100644 index 00000000000..9b1e15e3eaa --- /dev/null +++ b/mysql-test/r/strict.result @@ -0,0 +1,678 @@ +set @@sql_mode='ansi,traditional'; +select @@sql_mode; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (col1 date); +INSERT INTO t1 VALUES('2004-01-01'),('0000-10-31'),('2004-02-29'); +INSERT INTO t1 VALUES('2004-0-31'); +ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31'); +ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2 +INSERT INTO t1 VALUES('2004-10-0'); +ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-09-31'); +ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-32'); +ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2003-02-29'); +ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-13-15'); +ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1 +INSERT INTO t1 VALUES('0000-00-00'); +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +INSERT INTO t1 VALUES ('59'); +ERROR 22007: Incorrect date value: '59' for column 'col1' at row 1 +set @@sql_mode='STRICT_ALL_TABLES'; +INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; +INSERT INTO t1 VALUES('2004-0-31'); +ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05'); +ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2 +INSERT INTO t1 VALUES('0000-00-00'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; +INSERT INTO t1 VALUES('0000-00-00'); +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +INSERT INTO t1 VALUES ('2004-0-30'); +INSERT INTO t1 VALUES ('2004-2-30'); +ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1 +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT INTO t1 VALUES ('2004-2-30'); +set @@sql_mode='ansi,traditional'; +INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 2 +Warning 1265 Data truncated for column 'col1' at row 3 +select * from t1; +col1 +2004-01-01 +0000-10-31 +2004-02-29 +2004-01-02 +2004-01-03 +2004-00-31 +2004-01-04 +0000-00-00 +2004-00-30 +2004-02-30 +2004-02-29 +0000-00-00 +0000-00-00 +drop table t1; +set @@sql_mode="strict_trans_tables"; +CREATE TABLE t1 (col1 date) engine=myisam; +INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); +ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1 +INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 2 +INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT INTO t1 VALUES ('2003-02-29'); +ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1 +INSERT ignore INTO t1 VALUES('2003-02-30'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT ignore INTO t1 VALUES('2003-02-31'); +select * from t1; +col1 +2004-01-02 +0000-00-00 +2004-01-03 +0000-00-00 +2004-01-04 +0000-00-00 +2003-02-31 +drop table t1; +set @@sql_mode="strict_trans_tables"; +CREATE TABLE t1 (col1 date) engine=innodb; +INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); +ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1 +INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); +ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2 +INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT INTO t1 VALUES ('2003-02-29'); +ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1 +INSERT ignore INTO t1 VALUES('2003-02-30'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT ignore INTO t1 VALUES('2003-02-31'); +select * from t1; +col1 +0000-00-00 +2004-01-04 +0000-00-00 +2003-02-31 +drop table t1; +set @@sql_mode='ansi,traditional'; +CREATE TABLE t1 (col1 datetime); +INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('0000-10-31 15:30:00'),('2004-02-29 15:30:00'); +INSERT INTO t1 VALUES('2004-0-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-0 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-09-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-32 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-13-15 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('0000-00-00 15:30:00'); +ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES ('59'); +ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1 +select * from t1; +col1 +2004-10-31 15:30:00 +0000-10-31 15:30:00 +2004-02-29 15:30:00 +drop table t1; +CREATE TABLE t1 (col1 timestamp); +INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); +INSERT INTO t1 VALUES('0000-10-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-0-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-0 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-09-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-32 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-13-15 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-02-29 25:30:00'); +ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-02-29 15:65:00'); +ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-02-29 15:31:61'); +ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1 +INSERT INTO t1 VALUES('0000-00-00 15:30:00'); +ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 +INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT INTO t1 VALUES ('59'); +ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1 +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT INTO t1 VALUES('2004-0-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-0 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-10-32 15:30:00'); +ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 VALUES('2004-02-30 15:30:04'); +ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1 +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 +set @@sql_mode='ansi,traditional'; +SELECT * FROM t1; +col1 +2004-10-31 15:30:00 +2004-02-29 15:30:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +DROP TABLE t1; +CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); +INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0); +INSERT INTO t1 (col1) VALUES(-129); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(128); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(256); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES('-129'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES('128'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES('-1'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES('256'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES(128.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(256.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0; +ERROR 22003: Out of range value adjusted for column 'col2' at row 3 +UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0; +ERROR 22012: Division by 0 +SELECT MOD(col1,0) FROM t1; +ERROR 22012: Division by 0 +INSERT INTO t1 (col1) VALUES (''); +ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('a59b'); +ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('1a'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 values (1/0,1/0); +Warnings: +Error 1365 Division by 0 +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256'); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +col1 col2 +-128 0 +0 NULL +127 255 +-128 0 +0 NULL +127 255 +-128 0 +0 NULL +127 255 +2 NULL +NULL NULL +-128 0 +127 255 +-128 0 +127 255 +DROP TABLE t1; +CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED); +INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0); +INSERT INTO t1 (col1) VALUES(-32769); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(32768); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(65536); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES('-32769'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES('32768'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES('-1'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES('65536'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES(-32769.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(32768.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(65536.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; +ERROR 22003: Out of range value adjusted for column 'col2' at row 3 +UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0; +ERROR 22012: Division by 0 +SELECT MOD(col1,0) FROM t1; +ERROR 22012: Division by 0 +INSERT INTO t1 (col1) VALUES (''); +ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('a59b'); +ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('1a'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 values (1/0,1/0); +Warnings: +Error 1365 Division by 0 +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536'); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +col1 col2 +-32768 0 +0 NULL +32767 65535 +-32768 0 +32767 65535 +-32768 0 +32767 65535 +2 NULL +NULL NULL +-32768 0 +32767 65535 +-32768 0 +32767 65535 +-32768 0 +32767 65535 +DROP TABLE t1; +CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED); +INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0); +INSERT INTO t1 (col1) VALUES(-8388609); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(8388608); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(16777216); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES('-8388609'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES('8388608'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES('-1'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES('16777216'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES(-8388609.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(8388608.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(16777216.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; +ERROR 22003: Out of range value adjusted for column 'col2' at row 3 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +ERROR 22012: Division by 0 +SELECT MOD(col1,0) FROM t1; +ERROR 22012: Division by 0 +INSERT INTO t1 (col1) VALUES (''); +ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('a59b'); +ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('1a'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 values (1/0,1/0); +Warnings: +Error 1365 Division by 0 +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216'); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +col1 col2 +-8388608 0 +0 NULL +8388607 16777215 +-8388608 0 +8388607 16777215 +-8388608 0 +8388607 16777215 +2 NULL +NULL NULL +-8388608 0 +8388607 16777215 +-8388608 0 +8388607 16777215 +-8388608 0 +8388607 16777215 +DROP TABLE t1; +CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED); +INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0); +INSERT INTO t1 (col1) VALUES(-2147483649); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(2147643648); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(4294967296); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES('-2147483649'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES('2147643648'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES('-1'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES('4294967296'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES(-2147483649.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(2147643648.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(4294967296.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0; +ERROR 22003: Out of range value adjusted for column 'col2' at row 3 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +ERROR 22012: Division by 0 +SELECT MOD(col1,0) FROM t1; +ERROR 22012: Division by 0 +INSERT INTO t1 (col1) VALUES (''); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('a59b'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('1a'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 values (1/0,1/0); +Warnings: +Error 1365 Division by 0 +Error 1365 Division by 0 +INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296'); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +col1 col2 +-2147483648 0 +0 NULL +2147483647 4294967295 +-2147483648 0 +2147483647 4294967295 +-2147483648 0 +2147483647 4294967295 +2 NULL +NULL NULL +-2147483648 0 +2147483647 4294967295 +-2147483648 0 +2147483647 4294967295 +-2147483648 0 +2147483647 4294967295 +DROP TABLE t1; +CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED); +INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615); +INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); +INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); +INSERT INTO t1 (col1) VALUES(-9223372036854775809); +INSERT INTO t1 (col1) VALUES(9223372036854775808); +INSERT INTO t1 (col2) VALUES(-1); +INSERT INTO t1 (col2) VALUES(18446744073709551616); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES('-9223372036854775809'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES('9223372036854775808'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES('-1'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES('18446744073709551616'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES(-9223372036854775809.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(9223372036854775808.0); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(-1.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(18446744073709551616.0); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +ERROR 22012: Division by 0 +SELECT MOD(col1,0) FROM t1; +ERROR 22012: Division by 0 +INSERT INTO t1 (col1) VALUES (''); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('a59b'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('1a'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 values (1/0,1/0); +Warnings: +Error 1365 Division by 0 +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616); +Warnings: +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616'); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES(-9223372036854775809.0,-1.0),(9223372036854775808.0,18446744073709551616.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +Warning 1264 Out of range value adjusted for column 'col2' at row 2 +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +col1 col2 +-9223372036854775808 0 +0 NULL +9223372036854775807 18446744073709551615 +-9223372036854775808 0 +9223372036854775807 18446744073709551615 +-9223372036854773760 0 +-9223372036854775808 1844674407370953984 +-9223372036854775808 NULL +-9223372036854775808 NULL +NULL 18446744073709551615 +2 NULL +NULL NULL +-9223372036854775808 18446744073709551615 +-9223372036854775808 18446744073709551615 +-9223372036854775808 0 +9223372036854775807 18446744073709551615 +-9223372036854775808 0 +9223372036854775807 18446744073709551615 +DROP TABLE t1; +CREATE TABLE t1 (col1 NUMERIC(4,2)); +INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); +INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); +Warnings: +Note 1265 Data truncated for column 'col1' at row 2 +Note 1265 Data truncated for column 'col1' at row 4 +INSERT INTO t1 VALUES (101.55); +INSERT INTO t1 VALUES (101); +INSERT INTO t1 VALUES (-101.55); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 VALUES (1010.55); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 VALUES (1010); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 VALUES ('101.55'); +INSERT INTO t1 VALUES ('101'); +INSERT INTO t1 VALUES ('-101.55'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 VALUES ('-1010.55'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 VALUES ('-100E+1'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 VALUES ('-100E'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; +ERROR 22003: Out of range value adjusted for column 'col1' at row 6 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +ERROR 22012: Division by 0 +SELECT MOD(col1,0) FROM t1; +ERROR 22012: Division by 0 +INSERT INTO t1 (col1) VALUES (''); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('a59b'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('1a'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +INSERT IGNORE INTO t1 values (1/0); +Warnings: +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES(1000),(-1000); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +INSERT IGNORE INTO t1 VALUES('1000'),('-1000'); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +UPDATE IGNORE t1 SET col1=1/NULL where col1=0; +SELECT * FROM t1; +col1 +10.55 +10.56 +NULL +-10.55 +-10.56 +11.00 +10.00 +10.55 +10.55 +-10.55 +-10.55 +11.00 +10.00 +101.55 +101.00 +101.55 +101.00 +2.00 +NULL +999.99 +-99.99 +999.99 +-99.99 +999.99 +-99.99 +DROP TABLE t1; diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result index 02406b77a65..9e496f44772 100644 --- a/mysql-test/r/timezone2.result +++ b/mysql-test/r/timezone2.result @@ -108,9 +108,9 @@ insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'), ('1970-01-01 00:00:00'),('1970-01-01 00:00:01'), ('2037-12-31 23:59:59'),('2038-01-01 00:00:00'); Warnings: -Warning 1264 Data truncated; out of range for column 'ts' at row 2 -Warning 1264 Data truncated; out of range for column 'ts' at row 3 -Warning 1264 Data truncated; out of range for column 'ts' at row 6 +Warning 1264 Out of range value adjusted for column 'ts' at row 2 +Warning 1264 Out of range value adjusted for column 'ts' at row 3 +Warning 1264 Out of range value adjusted for column 'ts' at row 6 select * from t1; ts 0000-00-00 00:00:00 @@ -125,9 +125,9 @@ insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'), ('1970-01-01 01:00:00'),('1970-01-01 01:00:01'), ('2038-01-01 00:59:59'),('2038-01-01 01:00:00'); Warnings: -Warning 1264 Data truncated; out of range for column 'ts' at row 2 -Warning 1264 Data truncated; out of range for column 'ts' at row 3 -Warning 1264 Data truncated; out of range for column 'ts' at row 6 +Warning 1264 Out of range value adjusted for column 'ts' at row 2 +Warning 1264 Out of range value adjusted for column 'ts' at row 3 +Warning 1264 Out of range value adjusted for column 'ts' at row 6 select * from t1; ts 0000-00-00 00:00:00 @@ -142,9 +142,9 @@ insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'), ('1970-01-01 01:30:00'),('1970-01-01 01:30:01'), ('2038-01-01 01:29:59'),('2038-01-01 01:30:00'); Warnings: -Warning 1264 Data truncated; out of range for column 'ts' at row 2 -Warning 1264 Data truncated; out of range for column 'ts' at row 3 -Warning 1264 Data truncated; out of range for column 'ts' at row 6 +Warning 1264 Out of range value adjusted for column 'ts' at row 2 +Warning 1264 Out of range value adjusted for column 'ts' at row 3 +Warning 1264 Out of range value adjusted for column 'ts' at row 6 select * from t1; ts 0000-00-00 00:00:00 diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 524bc9c50d4..bbfd3da9d11 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -114,11 +114,11 @@ t delete from t1; insert into t1 values ("20030102030460"),("20030102036301"),("20030102240401"),("20030132030401"),("20031302030460"); Warnings: -Warning 1264 Data truncated; out of range for column 't' at row 1 -Warning 1264 Data truncated; out of range for column 't' at row 2 -Warning 1264 Data truncated; out of range for column 't' at row 3 -Warning 1264 Data truncated; out of range for column 't' at row 4 -Warning 1264 Data truncated; out of range for column 't' at row 5 +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 select * from t1; t 0000-00-00 00:00:00 @@ -129,8 +129,8 @@ t delete from t1; insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); Warnings: -Warning 1264 Data truncated; out of range for column 't' at row 1 -Warning 1264 Data truncated; out of range for column 't' at row 2 +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 select * from t1; t 0000-00-00 00:00:00 diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index a9dcabd121e..c9a272df1ba 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -158,17 +158,17 @@ insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 Warning 1265 Data truncated for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Warning 1265 Data truncated for column 'a' at row 3 +Note 1265 Data truncated for column 'a' at row 3 select * from t1; a 0.00 @@ -201,32 +201,32 @@ drop table t1; create table t1 (a decimal(10,2) unsigned); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 6 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 6 insert into t1 values ("-.1"),("+.1"),(".1"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 Warning 1265 Data truncated for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Warning 1265 Data truncated for column 'a' at row 3 +Note 1265 Data truncated for column 'a' at row 3 select * from t1; a 0.00 @@ -259,32 +259,32 @@ drop table t1; create table t1 (a decimal(10,2) zerofill); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 6 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 6 insert into t1 values ("-.1"),("+.1"),(".1"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 Warning 1265 Data truncated for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Warning 1265 Data truncated for column 'a' at row 3 +Note 1265 Data truncated for column 'a' at row 3 select * from t1; a 00000000.00 @@ -321,13 +321,13 @@ insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values (1e+100),(1e-100),(-1e+100); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 3 insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; a @@ -361,8 +361,8 @@ drop table t1; create table t1 (a decimal); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 7 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 7 select * from t1; a -9999999999 @@ -376,9 +376,9 @@ drop table t1; create table t1 (a decimal unsigned); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 7 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 7 select * from t1; a 0 @@ -392,9 +392,9 @@ drop table t1; create table t1 (a decimal zerofill); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 7 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 7 select * from t1; a 0000000000 @@ -408,9 +408,9 @@ drop table t1; create table t1 (a decimal unsigned zerofill); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); Warnings: -Warning 1264 Data truncated; out of range for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 7 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 7 select * from t1; a 0000000000 @@ -425,7 +425,7 @@ create table t1(a decimal(10,0)); insert into t1 values ("1e4294967295"); Warnings: Warning 1265 Data truncated for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 select * from t1; a 99999999999 @@ -433,7 +433,7 @@ delete from t1; insert into t1 values("1e4294967297"); Warnings: Warning 1265 Data truncated for column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 select * from t1; a 99999999999 diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 843bdc2bdc5..e1606aaf067 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -15,8 +15,8 @@ f1 float NULL YES NULL select,insert,update,references f2 double NULL YES NULL select,insert,update,references insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150); Warnings: -Warning 1264 Data truncated; out of range for column 'f1' at row 7 -Warning 1264 Data truncated; out of range for column 'f1' at row 8 +Warning 1264 Out of range value adjusted for column 'f1' at row 7 +Warning 1264 Out of range value adjusted for column 'f1' at row 8 insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150); select * from t1; f1 f2 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 5a65c90c5c7..f08a160be3b 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -89,33 +89,33 @@ insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,N insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3); insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1); Warnings: -Warning 1264 Data truncated; out of range for column 'utiny' at row 1 -Warning 1264 Data truncated; out of range for column 'ushort' at row 1 -Warning 1264 Data truncated; out of range for column 'umedium' at row 1 -Warning 1264 Data truncated; out of range for column 'ulong' at row 1 +Warning 1264 Out of range value adjusted for column 'utiny' at row 1 +Warning 1264 Out of range value adjusted for column 'ushort' at row 1 +Warning 1264 Out of range value adjusted for column 'umedium' at row 1 +Warning 1264 Out of range value adjusted for column 'ulong' at row 1 Warning 1265 Data truncated for column 'options' at row 1 Warning 1265 Data truncated for column 'flags' at row 1 insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree"); Warnings: Warning 1265 Data truncated for column 'string' at row 1 -Warning 1264 Data truncated; out of range for column 'tiny' at row 1 -Warning 1264 Data truncated; out of range for column 'short' at row 1 -Warning 1264 Data truncated; out of range for column 'medium' at row 1 -Warning 1264 Data truncated; out of range for column 'long_int' at row 1 -Warning 1264 Data truncated; out of range for column 'utiny' at row 1 -Warning 1264 Data truncated; out of range for column 'ushort' at row 1 -Warning 1264 Data truncated; out of range for column 'umedium' at row 1 -Warning 1264 Data truncated; out of range for column 'ulong' at row 1 +Warning 1264 Out of range value adjusted for column 'tiny' at row 1 +Warning 1264 Out of range value adjusted for column 'short' at row 1 +Warning 1264 Out of range value adjusted for column 'medium' at row 1 +Warning 1264 Out of range value adjusted for column 'long_int' at row 1 +Warning 1264 Out of range value adjusted for column 'utiny' at row 1 +Warning 1264 Out of range value adjusted for column 'ushort' at row 1 +Warning 1264 Out of range value adjusted for column 'umedium' at row 1 +Warning 1264 Out of range value adjusted for column 'ulong' at row 1 Warning 1265 Data truncated for column 'options' at row 1 insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0); Warnings: -Warning 1264 Data truncated; out of range for column 'tiny' at row 1 -Warning 1264 Data truncated; out of range for column 'short' at row 1 -Warning 1264 Data truncated; out of range for column 'medium' at row 1 -Warning 1264 Data truncated; out of range for column 'long_int' at row 1 -Warning 1264 Data truncated; out of range for column 'utiny' at row 1 -Warning 1264 Data truncated; out of range for column 'ushort' at row 1 -Warning 1264 Data truncated; out of range for column 'umedium' at row 1 +Warning 1264 Out of range value adjusted for column 'tiny' at row 1 +Warning 1264 Out of range value adjusted for column 'short' at row 1 +Warning 1264 Out of range value adjusted for column 'medium' at row 1 +Warning 1264 Out of range value adjusted for column 'long_int' at row 1 +Warning 1264 Out of range value adjusted for column 'utiny' at row 1 +Warning 1264 Out of range value adjusted for column 'ushort' at row 1 +Warning 1264 Out of range value adjusted for column 'umedium' at row 1 Warning 1265 Data truncated for column 'options' at row 1 insert into t1 (tiny) values (1); select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1; diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 025cf2a57f1..442435b0459 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -26,9 +26,9 @@ t insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a"); Warnings: Warning 1265 Data truncated for column 't' at row 1 -Warning 1264 Data truncated; out of range for column 't' at row 2 -Warning 1264 Data truncated; out of range for column 't' at row 3 -Warning 1264 Data truncated; out of range for column 't' at row 4 +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 1265 Data truncated for column 't' at row 6 select * from t1; t diff --git a/mysql-test/r/type_uint.result b/mysql-test/r/type_uint.result index d8edf9085b7..0474f3a24f4 100644 --- a/mysql-test/r/type_uint.result +++ b/mysql-test/r/type_uint.result @@ -4,10 +4,10 @@ create table t1 (this int unsigned); insert into t1 values (1); insert into t1 values (-1); Warnings: -Warning 1264 Data truncated; out of range for column 'this' at row 1 +Warning 1264 Out of range value adjusted for column 'this' at row 1 insert into t1 values ('5000000000'); Warnings: -Warning 1265 Data truncated for column 'this' at row 1 +Warning 1264 Out of range value adjusted for column 'this' at row 1 select * from t1; this 1 diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index d883feb7ce2..55931452c82 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -4,19 +4,19 @@ create table t1 (a int); insert into t1 values (1); insert into t1 values ("hej"); Warnings: -Warning 1265 Data truncated for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 insert into t1 values ("hej"),("då"); Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -Warning 1265 Data truncated for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 set SQL_WARNINGS=1; insert into t1 values ("hej"); Warnings: -Warning 1265 Data truncated for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 1 insert into t1 values ("hej"),("då"); Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -Warning 1265 Data truncated for column 'a' at row 2 +Warning 1264 Out of range value adjusted for column 'a' at row 1 +Warning 1264 Out of range value adjusted for column 'a' at row 2 drop table t1; set SQL_WARNINGS=0; drop temporary table if exists not_exists; @@ -49,7 +49,7 @@ Warning 1265 Data truncated for column 'c' at row 4 Warning 1261 Row 5 doesn't contain data for all columns Warning 1265 Data truncated for column 'b' at row 6 Warning 1262 Row 7 was truncated; it contained more data than there were input columns -Warning 1264 Data truncated; out of range for column 'a' at row 8 +Warning 1264 Out of range value adjusted for column 'a' at row 8 select @@warning_count; @@warning_count 7 @@ -58,10 +58,10 @@ create table t1(a tinyint NOT NULL, b tinyint unsigned, c char(5)); insert into t1 values(NULL,100,'mysql'),(10,-1,'mysql ab'),(500,256,'open source'),(20,NULL,'test'); Warnings: Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 1 -Warning 1264 Data truncated; out of range for column 'b' at row 2 +Warning 1264 Out of range value adjusted for column 'b' at row 2 Warning 1265 Data truncated for column 'c' at row 2 -Warning 1264 Data truncated; out of range for column 'a' at row 3 -Warning 1264 Data truncated; out of range for column 'b' at row 3 +Warning 1264 Out of range value adjusted for column 'a' at row 3 +Warning 1264 Out of range value adjusted for column 'b' at row 3 Warning 1265 Data truncated for column 'c' at row 3 alter table t1 modify c char(4); Warnings: diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test index 93e57daad82..ea845d836ec 100644 --- a/mysql-test/t/func_sapdb.test +++ b/mysql-test/t/func_sapdb.test @@ -30,10 +30,16 @@ select adddate("1997-12-31 23:59:59.000001", 10); select subdate("1997-12-31 23:59:59.000001", 10); select datediff("1997-12-31 23:59:59.000001","1997-12-30"); +select datediff("1997-11-30 23:59:59.000001","1997-12-31"); +SET @@SQL_MODE="ALLOW_INVALID_DATES"; select datediff("1997-11-31 23:59:59.000001","1997-12-31"); -select datediff("1997-11-31 23:59:59.000001",null); +SET @@SQL_MODE=""; -select weekofyear("1997-11-31 23:59:59.000001"); +-- This will give a warning +select datediff("1997-11-31 23:59:59.000001","1997-12-31"); +select datediff("1997-11-30 23:59:59.000001",null); + +select weekofyear("1997-11-30 23:59:59.000001"); select makedate(1997,1); select makedate(1997,0); diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test new file mode 100644 index 00000000000..c42fd767117 --- /dev/null +++ b/mysql-test/t/strict.test @@ -0,0 +1,476 @@ +# Testing of "strict" mode + +-- source include/have_innodb.inc + +set @@sql_mode='ansi,traditional'; +select @@sql_mode; + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# Test INSERT with DATE + +CREATE TABLE t1 (col1 date); +INSERT INTO t1 VALUES('2004-01-01'),('0000-10-31'),('2004-02-29'); +--error 1292 +INSERT INTO t1 VALUES('2004-0-31'); +--error 1292 +INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-0'); +--error 1292 +INSERT INTO t1 VALUES('2004-09-31'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-32'); +--error 1292 +INSERT INTO t1 VALUES('2003-02-29'); +--error 1292 +INSERT INTO t1 VALUES('2004-13-15'); +--error 1292 +INSERT INTO t1 VALUES('0000-00-00'); +# Standard says we should return SQLSTATE 22018 +--error 1292 +INSERT INTO t1 VALUES ('59'); + +# Test the different related modes +set @@sql_mode='STRICT_ALL_TABLES'; +INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; +--error 1292 +INSERT INTO t1 VALUES('2004-0-31'); +--error 1292 +INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05'); +INSERT INTO t1 VALUES('0000-00-00'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; +--error 1292 +INSERT INTO t1 VALUES('0000-00-00'); +INSERT INTO t1 VALUES ('2004-0-30'); +--error 1292 +INSERT INTO t1 VALUES ('2004-2-30'); +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT INTO t1 VALUES ('2004-2-30'); +set @@sql_mode='ansi,traditional'; +INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00'); + +select * from t1; +drop table t1; + +# Test difference in behaviour with InnoDB and MyISAM tables + +set @@sql_mode="strict_trans_tables"; +CREATE TABLE t1 (col1 date) engine=myisam; +--error 1292 +INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); +INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); +INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); +--error 1292 +INSERT INTO t1 VALUES ('2003-02-29'); +INSERT ignore INTO t1 VALUES('2003-02-30'); +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT ignore INTO t1 VALUES('2003-02-31'); +select * from t1; +drop table t1; + +set @@sql_mode="strict_trans_tables"; +CREATE TABLE t1 (col1 date) engine=innodb; +--error 1292 +INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1'); +--error 1292 +INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3'); +INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4'); +--error 1292 +INSERT INTO t1 VALUES ('2003-02-29'); +INSERT ignore INTO t1 VALUES('2003-02-30'); +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +INSERT ignore INTO t1 VALUES('2003-02-31'); +select * from t1; +drop table t1; +set @@sql_mode='ansi,traditional'; + +# Test INSERT with DATETIME + +CREATE TABLE t1 (col1 datetime); +INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('0000-10-31 15:30:00'),('2004-02-29 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-0-31 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-0 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-09-31 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-32 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-13-15 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('0000-00-00 15:30:00'); +# Standard says we should return SQLSTATE 22018 +--error 1292 +INSERT INTO t1 VALUES ('59'); +select * from t1; +drop table t1; + +# Test INSERT with TIMESTAMP + +CREATE TABLE t1 (col1 timestamp); +INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); +# Standard says we should return ok, but we can't as this is out of range +--error 1292 +INSERT INTO t1 VALUES('0000-10-31 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-0-31 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-0 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-09-31 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-32 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-13-15 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-02-29 25:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-02-29 15:65:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-02-29 15:31:61'); +--error 1292 +INSERT INTO t1 VALUES('0000-00-00 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00'); +# Standard says we should return SQLSTATE 22018 +--error 1292 +INSERT INTO t1 VALUES ('59'); + +set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'; +--error 1292 +INSERT INTO t1 VALUES('2004-0-31 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-0 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-10-32 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('2004-02-30 15:30:04'); +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; +--error 1292 +INSERT INTO t1 VALUES('0000-00-00 00:00:00'); +set @@sql_mode='ansi,traditional'; +SELECT * FROM t1; +DROP TABLE t1; + +# Test TINYINT + +CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); +INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0); +-- error 1264 +INSERT INTO t1 (col1) VALUES(-129); +-- error 1264 +INSERT INTO t1 (col1) VALUES(128); +-- error 1264 +INSERT INTO t1 (col2) VALUES(-1); +-- error 1264 +INSERT INTO t1 (col2) VALUES(256); +-- error 1264 +INSERT INTO t1 (col1) VALUES('-129'); +-- error 1264 +INSERT INTO t1 (col1) VALUES('128'); +-- error 1264 +INSERT INTO t1 (col2) VALUES('-1'); +-- error 1264 +INSERT INTO t1 (col2) VALUES('256'); +-- error 1264 +INSERT INTO t1 (col1) VALUES(128.0); +-- error 1264 +INSERT INTO t1 (col2) VALUES(-1.0); +-- error 1264 +INSERT INTO t1 (col2) VALUES(256.0); +--error 1264 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +--error 1264 +UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0; +--error 1365 +UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0; +--error 1365 +SELECT MOD(col1,0) FROM t1; +# Should return SQLSTATE 22018 invalid character value for cast +--error 1366 +INSERT INTO t1 (col1) VALUES (''); +--error 1366 +INSERT INTO t1 (col1) VALUES ('a59b'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('1a'); +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +INSERT IGNORE INTO t1 values (1/0,1/0); +INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256'); +INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0); +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; + +SELECT * FROM t1; +DROP TABLE t1; + +# Test SMALLINT + +CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED); +INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0); + +--error 1264 +INSERT INTO t1 (col1) VALUES(-32769); +--error 1264 +INSERT INTO t1 (col1) VALUES(32768); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1); +--error 1264 +INSERT INTO t1 (col2) VALUES(65536); +--error 1264 +INSERT INTO t1 (col1) VALUES('-32769'); +--error 1264 +INSERT INTO t1 (col1) VALUES('32768'); +--error 1264 +INSERT INTO t1 (col2) VALUES('-1'); +--error 1264 +INSERT INTO t1 (col2) VALUES('65536'); +--error 1264 +INSERT INTO t1 (col1) VALUES(-32769.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(32768.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(65536.0); +--error 1264 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +--error 1264 +UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; +--error 1365 +UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0; +--error 1365 +SELECT MOD(col1,0) FROM t1; +--error 1366 +INSERT INTO t1 (col1) VALUES (''); +--error 1366 +INSERT INTO t1 (col1) VALUES ('a59b'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('1a'); +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +INSERT IGNORE INTO t1 values (1/0,1/0); +INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536); +INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536'); +INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0); +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; + +SELECT * FROM t1; +DROP TABLE t1; + +# Test MEDIUMINT + +CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED); +INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(-8388609); +--error 1264 +INSERT INTO t1 (col1) VALUES(8388608); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1); +--error 1264 +INSERT INTO t1 (col2) VALUES(16777216); +--error 1264 +INSERT INTO t1 (col1) VALUES('-8388609'); +--error 1264 +INSERT INTO t1 (col1) VALUES('8388608'); +--error 1264 +INSERT INTO t1 (col2) VALUES('-1'); +--error 1264 +INSERT INTO t1 (col2) VALUES('16777216'); +--error 1264 +INSERT INTO t1 (col1) VALUES(-8388609.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(8388608.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(16777216.0); + +--error 1264 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +--error 1264 +UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0; +--error 1365 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +--error 1365 +SELECT MOD(col1,0) FROM t1; +--error 1366 +INSERT INTO t1 (col1) VALUES (''); +--error 1366 +INSERT INTO t1 (col1) VALUES ('a59b'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('1a'); +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +INSERT IGNORE INTO t1 values (1/0,1/0); +INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216); +INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216'); +INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0); +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; + +SELECT * FROM t1; +DROP TABLE t1; + +# Test INT + +CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED); +INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(-2147483649); +--error 1264 +INSERT INTO t1 (col1) VALUES(2147643648); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1); +--error 1264 +INSERT INTO t1 (col2) VALUES(4294967296); +--error 1264 +INSERT INTO t1 (col1) VALUES('-2147483649'); +--error 1264 +INSERT INTO t1 (col1) VALUES('2147643648'); +--error 1264 +INSERT INTO t1 (col2) VALUES('-1'); +--error 1264 +INSERT INTO t1 (col2) VALUES('4294967296'); +--error 1264 +INSERT INTO t1 (col1) VALUES(-2147483649.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(2147643648.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(4294967296.0); + +--error 1264 +UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0; +--error 1264 +UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0; +--error 1365 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +--error 1365 +SELECT MOD(col1,0) FROM t1; +--error 1264 +INSERT INTO t1 (col1) VALUES (''); +--error 1264 +INSERT INTO t1 (col1) VALUES ('a59b'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('1a'); +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +INSERT IGNORE INTO t1 values (1/0,1/0); +INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296); +INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296'); +INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0); +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +DROP TABLE t1; + +# Test BIGINT +# Note that this doesn't behave 100 % to standard as we rotate +# integers when it's too big/small (just like C) + +CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED); +INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615); +INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615'); +INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0); + +# The following should give an error, but doesn't until we fix the interface +# for Field_longlong::store() + +INSERT INTO t1 (col1) VALUES(-9223372036854775809); +INSERT INTO t1 (col1) VALUES(9223372036854775808); +INSERT INTO t1 (col2) VALUES(-1); + +--error 1264 +INSERT INTO t1 (col2) VALUES(18446744073709551616); +--error 1264 +INSERT INTO t1 (col1) VALUES('-9223372036854775809'); +--error 1264 +INSERT INTO t1 (col1) VALUES('9223372036854775808'); +--error 1264 +INSERT INTO t1 (col2) VALUES('-1'); +--error 1264 +INSERT INTO t1 (col2) VALUES('18446744073709551616'); +--error 1264 +INSERT INTO t1 (col1) VALUES(-9223372036854775809.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(9223372036854775808.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(-1.0); +--error 1264 +INSERT INTO t1 (col2) VALUES(18446744073709551616.0); + +# The following doesn't give an error as it's done in integer context +# UPDATE t1 SET col1=col1 - 5000 WHERE col1 < 0; +# UPDATE t1 SET col2 =col2 + 5000 WHERE col2 > 0; + +--error 1365 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +--error 1365 +SELECT MOD(col1,0) FROM t1; +--error 1264 +INSERT INTO t1 (col1) VALUES (''); +--error 1264 +INSERT INTO t1 (col1) VALUES ('a59b'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('1a'); +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +INSERT IGNORE INTO t1 values (1/0,1/0); +INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616); +INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616'); +INSERT IGNORE INTO t1 VALUES(-9223372036854775809.0,-1.0),(9223372036854775808.0,18446744073709551616.0); +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (col1 NUMERIC(4,2)); +INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01); +-- Note that the +/-10.5555 is inserted as +/-10.55, not +/-10.56 ! +INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01'); + +-- The 2 following inserts should generate a warning, but doesn't yet +-- because NUMERIC works like DECIMAL +INSERT INTO t1 VALUES (101.55); +INSERT INTO t1 VALUES (101); +--error 1264 +INSERT INTO t1 VALUES (-101.55); +--error 1264 +INSERT INTO t1 VALUES (1010.55); +--error 1264 +INSERT INTO t1 VALUES (1010); +-- The 2 following inserts should generate a warning, but doesn't yet +-- because NUMERIC works like DECIMAL +INSERT INTO t1 VALUES ('101.55'); +INSERT INTO t1 VALUES ('101'); +--error 1264 +INSERT INTO t1 VALUES ('-101.55'); +--error 1264 +INSERT INTO t1 VALUES ('-1010.55'); +--error 1264 +INSERT INTO t1 VALUES ('-100E+1'); +--error 1264 +INSERT INTO t1 VALUES ('-100E'); +--error 1264 +UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11; +--error 1365 +UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0; +--error 1365 +SELECT MOD(col1,0) FROM t1; +--error 1265 +INSERT INTO t1 (col1) VALUES (''); +--error 1265 +INSERT INTO t1 (col1) VALUES ('a59b'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('1a'); +INSERT IGNORE INTO t1 (col1) VALUES ('2a'); +INSERT IGNORE INTO t1 values (1/0); +INSERT IGNORE INTO t1 VALUES(1000),(-1000); +INSERT IGNORE INTO t1 VALUES('1000'),('-1000'); +INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0); +UPDATE IGNORE t1 SET col1=1/NULL where col1=0; +SELECT * FROM t1; +DROP TABLE t1; diff --git a/sql-common/my_time.c b/sql-common/my_time.c index 4b5daf53bea..703cfb089c0 100644 --- a/sql-common/my_time.c +++ b/sql-common/my_time.c @@ -47,6 +47,43 @@ uchar days_in_month[]= {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}; static long my_time_zone=0; +/* Calc days in one year. works with 0 <= year <= 99 */ + +uint calc_days_in_year(uint year) +{ + return ((year & 3) == 0 && (year%100 || (year%400 == 0 && year)) ? + 366 : 365); +} + +/* + check date. + + SYNOPOSIS + bool check_date() + time Date to check. + + NOTES + Here we assume that year and month is ok ! + If month is 0 we allow any date. (This only happens if we allow zero + date parts in str_to_datetime()) + + RETURN + 0 ok + 1 errro +*/ + +bool check_date(MYSQL_TIME *ltime) +{ + if (ltime->month && ltime->day > days_in_month[ltime->month-1]) + { + if (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 || + ltime->day != 29) + return 1; + } + return 0; +} + + /* Convert a timestamp string to a MYSQL_TIME value. @@ -58,8 +95,12 @@ static long my_time_zone=0; flags Bitmap of following items TIME_FUZZY_DATE Set if we should allow partial dates TIME_DATETIME_ONLY Set if we only allow full datetimes. - was_cut Set to 1 if value was cut during conversion or to 0 - otherwise. + TIME_NO_ZERO_IN_DATE Don't allow partial dates + TIME_NO_ZERO_DATE Don't allow 0000-00-00 date + 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 DESCRIPTION At least the following formats are recogniced (based on number of digits) @@ -127,6 +168,8 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, *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 */ @@ -343,10 +386,21 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, (l_time->month || l_time->day)) 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->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))) + (!(flags & TIME_FUZZY_DATE) && (l_time->month == 0 || + l_time->day == 0) && + not_zero_date)) { /* Only give warning for a zero date if there is some garbage after */ if (!not_zero_date) /* If zero date */ @@ -360,14 +414,19 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, } } } - if (not_zero_date) - *was_cut= 1; + *was_cut= test(not_zero_date); 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)) diff --git a/sql/field.cc b/sql/field.cc index 5356fbc773a..ada8381653b 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -83,32 +83,48 @@ void Field_num::prepend_zeros(String *value) RETURN 0 ok - 1 error + 1 error. A warning is pushed if field_name != 0 */ -bool test_if_int(const char *str, int length, const char *int_end, - CHARSET_INFO *cs) +bool Field::check_int(const char *str, int length, const char *int_end, + CHARSET_INFO *cs) { + const char *end; if (str == int_end) - return 0; // Empty string - const char *end=str+length; + { + char buff[128]; + String tmp(buff,(uint32) sizeof(buff), system_charset_info); + tmp.copy(str, length, system_charset_info); + push_warning_printf(table->in_use, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE_FOR_FIELD, + ER(ER_TRUNCATED_WRONG_VALUE_FOR_FIELD), + "integer", tmp.c_ptr(), field_name, + (ulong) table->in_use->row_count); + return 1; // Empty string + } + end= str+length; if ((str= int_end) == end) - return 1; // All digits was used + return 0; // ok; All digits was used /* Allow end .0000 */ if (*str == '.') { - for (str++ ; str != end && *str == '0'; str++) ; + for (str++ ; str != end && *str == '0'; str++) + ; } /* Allow end space */ - for (str++ ; str != end ; str++) + for ( ; str != end ; str++) { if (!my_isspace(cs,*str)) - return 0; + { + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + return 1; + } } - return 1; + return 0; } + #ifdef NOT_USED static bool test_if_real(const char *str,int length, CHARSET_INFO *cs) { @@ -846,7 +862,13 @@ int Field_decimal::store(const char *from, uint len, CHARSET_INFO *cs) if (tmp_char != '0') // Losing a non zero digit ? { if (!is_cuted_fields_incr) - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + { + /* + This is a note, not a warning, as we don't want to abort + when we cut decimals in strict mode + */ + set_warning(MYSQL_ERROR::WARN_LEVEL_NOTE, ER_WARN_DATA_TRUNCATED, 1); + } return 0; } continue; @@ -1073,11 +1095,8 @@ int Field_tiny::store(const char *from,uint len,CHARSET_INFO *cs) set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } - else if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + else if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) error= 1; - } } else { @@ -1093,11 +1112,8 @@ int Field_tiny::store(const char *from,uint len,CHARSET_INFO *cs) set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } - else if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + else if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) error= 1; - } } ptr[0]= (char) tmp; return error; @@ -1277,11 +1293,8 @@ int Field_short::store(const char *from,uint len,CHARSET_INFO *cs) set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } - else if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + else if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) error= 1; - } } else { @@ -1297,11 +1310,8 @@ int Field_short::store(const char *from,uint len,CHARSET_INFO *cs) set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } - else if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + else if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) error= 1; - } } #ifdef WORDS_BIGENDIAN if (table->db_low_byte_first) @@ -1552,11 +1562,8 @@ int Field_medium::store(const char *from,uint len,CHARSET_INFO *cs) set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } - else if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + else if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) error= 1; - } } else { @@ -1572,11 +1579,8 @@ int Field_medium::store(const char *from,uint len,CHARSET_INFO *cs) set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } - else if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + else if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) error= 1; - } } int3store(ptr,tmp); @@ -1752,71 +1756,72 @@ void Field_medium::sql_type(String &res) const int Field_long::store(const char *from,uint len,CHARSET_INFO *cs) { - long tmp; - int error= 0; + longlong tmp; + long store_tmp; + int error; + bool warning_given= 0; char *end; tmp= cs->cset->scan(cs, from, from+len, MY_SEQ_SPACES); len-= tmp; from+= tmp; - my_errno=0; - if (unsigned_flag) + end= (char*) from+len; + tmp= my_strtoll10(from, &end, &error); + + if (error != MY_ERRNO_EDOM) { - if (!len || *from == '-') + if (unsigned_flag) { - tmp=0; // Set negative to 0 - my_errno=ERANGE; - error= 1; + if (error < 0) + { + error= 1; + tmp= 0; + } + else if ((ulonglong) tmp > (ulonglong) UINT_MAX32) + { + tmp= UINT_MAX32; + error= 1; + } + else + error= 0; } else - tmp=(long) my_strntoul(cs,from,len,10,&end,&error); - } - else - tmp=my_strntol(cs,from,len,10,&end,&error); - if (error || - (from+len != end && table->in_use->count_cuted_fields && - !test_if_int(from,len,end,cs))) - error= 1; -#if SIZEOF_LONG > 4 - if (unsigned_flag) - { - if ((ulong) tmp > UINT_MAX32) - { - tmp= UINT_MAX32; - error= 1; - my_errno=ERANGE; - } - } - else - { - if (tmp > INT_MAX32) - { - tmp= INT_MAX32; - error= 1; - my_errno=ERANGE; - } - else if (tmp < INT_MIN32) { - tmp= INT_MIN32; - error= 1; - my_errno=ERANGE; + if (error < 0) + { + error= 0; + if (tmp < INT_MIN32) + { + tmp= INT_MIN32; + error= 1; + } + } + else if (tmp > INT_MAX32) + { + tmp= INT_MAX32; + error= 1; + } } } -#endif if (error) { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); error= 1; + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); } + else if (from+len != end && table->in_use->count_cuted_fields && + check_int(from,len,end,cs)) + error= 1; + + store_tmp= (long) tmp; #ifdef WORDS_BIGENDIAN if (table->db_low_byte_first) { - int4store(ptr,tmp); + int4store(ptr, store_tmp); } else #endif - longstore(ptr,tmp); + longstore(ptr, store_tmp); return error; } @@ -1831,13 +1836,11 @@ int Field_long::store(double nr) if (nr < 0) { res=0; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else if (nr > (double) (ulong) ~0L) { res=(int32) (uint32) ~0L; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else @@ -1848,18 +1851,19 @@ int Field_long::store(double nr) if (nr < (double) INT_MIN32) { res=(int32) INT_MIN32; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else if (nr > (double) INT_MAX32) { res=(int32) INT_MAX32; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else res=(int32) nr; } + if (error) + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); + #ifdef WORDS_BIGENDIAN if (table->db_low_byte_first) { @@ -1876,26 +1880,18 @@ int Field_long::store(longlong nr) { int error= 0; int32 res; - - /* - This assert has nothing to do with this method per se, it was put here - only because it is one of the best places for catching places there its - condition is broken. - */ - DBUG_ASSERT(table->in_use == current_thd); + DBUG_ASSERT(table->in_use == current_thd); // General safety if (unsigned_flag) { if (nr < 0) { res=0; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else if (nr >= (LL(1) << 32)) { res=(int32) (uint32) ~0L; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else @@ -1906,18 +1902,19 @@ int Field_long::store(longlong nr) if (nr < (longlong) INT_MIN32) { res=(int32) INT_MIN32; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else if (nr > (longlong) INT_MAX32) { res=(int32) INT_MAX32; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else res=(int32) nr; } + if (error) + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); + #ifdef WORDS_BIGENDIAN if (table->db_low_byte_first) { @@ -2052,17 +2049,16 @@ int Field_longlong::store(const char *from,uint len,CHARSET_INFO *cs) longlong tmp; int error= 0; char *end; + bool warning_given; tmp= cs->cset->scan(cs, from, from+len, MY_SEQ_SPACES); len-= (uint)tmp; from+= tmp; - my_errno=0; if (unsigned_flag) { if (!len || *from == '-') { tmp=0; // Set negative to 0 - my_errno= ERANGE; error= 1; } else @@ -2070,13 +2066,14 @@ int Field_longlong::store(const char *from,uint len,CHARSET_INFO *cs) } else tmp=my_strntoll(cs,from,len,10,&end,&error); - if (error || - (from+len != end && table->in_use->count_cuted_fields && - !test_if_int(from,len,end,cs))) + if (error) { - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } + else if (from+len != end && table->in_use->count_cuted_fields && + check_int(from,len,end,cs)) + error= 1; #ifdef WORDS_BIGENDIAN if (table->db_low_byte_first) { @@ -2093,19 +2090,18 @@ int Field_longlong::store(double nr) { int error= 0; longlong res; - nr=rint(nr); + + nr= rint(nr); if (unsigned_flag) { if (nr < 0) { res=0; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else if (nr >= (double) ~ (ulonglong) 0) { res= ~(longlong) 0; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); error= 1; } else @@ -2113,21 +2109,22 @@ int Field_longlong::store(double nr) } else { - if (nr <= (double) LONGLONG_MIN) + if (nr < (double) LONGLONG_MIN) { - res=(longlong) LONGLONG_MIN; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); + res= LONGLONG_MIN; error= 1; } - else if (nr >= (double) LONGLONG_MAX) + else if (nr > (double) LONGLONG_MAX) { - res=(longlong) LONGLONG_MAX; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); + res= LONGLONG_MAX; error= 1; } else res=(longlong) nr; } + if (error) + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); + #ifdef WORDS_BIGENDIAN if (table->db_low_byte_first) { @@ -2951,14 +2948,22 @@ int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs) bool in_dst_time_gap; THD *thd= table->in_use; - have_smth_to_conv= (str_to_datetime(from, len, &l_time, 0, &error) > + 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) > MYSQL_TIMESTAMP_ERROR); - if (error) + if (error || !have_smth_to_conv) + { + error= 1; set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, from, len, MYSQL_TIMESTAMP_DATETIME, 1); + } - if (have_smth_to_conv) + /* Only convert a correct date (not a zero date) */ + if (have_smth_to_conv && l_time.month) { if (!(tmp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap))) { @@ -2988,6 +2993,7 @@ int Field_timestamp::store(const char *from,uint len,CHARSET_INFO *cs) return error; } + int Field_timestamp::store(double nr) { int error= 0; @@ -3162,7 +3168,7 @@ bool Field_timestamp::get_date(TIME *ltime, uint fuzzydate) longget(temp,ptr); if (temp == 0L) { /* Zero time is "000000" */ - if (!fuzzydate) + if (fuzzydate & TIME_NO_ZERO_DATE) return 1; bzero((char*) ltime,sizeof(*ltime)); } @@ -3183,7 +3189,7 @@ bool Field_timestamp::get_time(TIME *ltime) bool Field_timestamp::send_binary(Protocol *protocol) { TIME tm; - Field_timestamp::get_date(&tm, TIME_FUZZY_DATE); + Field_timestamp::get_date(&tm, 0); return protocol->store(&tm); } @@ -3415,7 +3421,7 @@ String *Field_time::val_str(String *val_buffer, bool Field_time::get_date(TIME *ltime, uint fuzzydate) { long tmp; - if (!fuzzydate) + if (!(fuzzydate & TIME_FUZZY_DATE)) { push_warning_printf(table->in_use, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, @@ -3497,18 +3503,19 @@ void Field_time::sql_type(String &res) const int Field_year::store(const char *from, uint len,CHARSET_INFO *cs) { - int not_used; // We can ignore result from str2int char *end; - long nr= my_strntol(cs, from, len, 10, &end, ¬_used); + int error; + long nr= my_strntol(cs, from, len, 10, &end, &error); - if (nr < 0 || nr >= 100 && nr <= 1900 || nr > 2155) + if (nr < 0 || nr >= 100 && nr <= 1900 || nr > 2155 || error) { *ptr=0; set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1); return 1; } - if (table->in_use->count_cuted_fields && !test_if_int(from,len,end,cs)) - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); + if (table->in_use->count_cuted_fields && check_int(from,len,end,cs)) + error= 1; + if (nr != 0 || len != 4) { if (nr < YY_PART_YEAR) @@ -3517,7 +3524,7 @@ int Field_year::store(const char *from, uint len,CHARSET_INFO *cs) nr-= 1900; } *ptr= (char) (unsigned char) nr; - return 0; + return error; } @@ -3603,7 +3610,11 @@ int Field_date::store(const char *from, uint len,CHARSET_INFO *cs) uint32 tmp; int error; - if (str_to_datetime(from, len, &l_time, 1, &error) <= MYSQL_TIMESTAMP_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; error= 1; @@ -3794,7 +3805,12 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs) TIME l_time; long tmp; int error; - if (str_to_datetime(from, len, &l_time, 1, &error) <= MYSQL_TIMESTAMP_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=0L; error= 1; @@ -3934,7 +3950,8 @@ bool Field_newdate::get_date(TIME *ltime,uint fuzzydate) ltime->year= (tmp >> 9); ltime->time_type= MYSQL_TIMESTAMP_DATE; ltime->hour= ltime->minute= ltime->second= ltime->second_part= ltime->neg= 0; - return (!fuzzydate && (!ltime->month || !ltime->day)) ? 1 : 0; + return ((!(fuzzydate & TIME_FUZZY_DATE) && (!ltime->month || !ltime->day)) ? + 1 : 0); } bool Field_newdate::get_time(TIME *ltime) @@ -3976,7 +3993,12 @@ int Field_datetime::store(const char *from,uint len,CHARSET_INFO *cs) int error; ulonglong tmp= 0; - if (str_to_datetime(from, len, &time_tmp, 1, &error) > MYSQL_TIMESTAMP_ERROR) + if (str_to_datetime(from, len, &time_tmp, + (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= TIME_to_ulonglong_datetime(&time_tmp); if (error) @@ -4148,7 +4170,7 @@ bool Field_datetime::get_date(TIME *ltime, uint fuzzydate) ltime->day= (int) (part1%100); ltime->month= (int) (part1/100%100); ltime->year= (int) (part1/10000); - return (!fuzzydate && (!ltime->month || !ltime->day)) ? 1 : 0; + return (!(fuzzydate & TIME_FUZZY_DATE) && (!ltime->month || !ltime->day)) ? 1 : 0; } bool Field_datetime::get_time(TIME *ltime) @@ -6059,7 +6081,7 @@ Field::set_warning(const uint level, const uint code, int cuted_increment) Produce warning or note about datetime string data saved into field SYNOPSYS - set_warning() + set_datime_warning() level - level of message (Note/Warning/Error) code - error code of message to be produced str - string value which we tried to save @@ -6077,8 +6099,10 @@ Field::set_datetime_warning(const uint level, const uint code, const char *str, uint str_length, timestamp_type ts_type, int cuted_increment) { - if (set_warning(level, code, cuted_increment)) - make_truncated_value_warning(table->in_use, str, str_length, ts_type); + if (table->in_use->really_abort_on_warning() || + set_warning(level, code, cuted_increment)) + make_truncated_value_warning(table->in_use, str, str_length, ts_type, + field_name); } @@ -6103,12 +6127,13 @@ Field::set_datetime_warning(const uint level, const uint code, longlong nr, timestamp_type ts_type, int cuted_increment) { - if (set_warning(level, code, cuted_increment)) + if (table->in_use->really_abort_on_warning() || + set_warning(level, code, cuted_increment)) { char str_nr[22]; char *str_end= longlong10_to_str(nr, str_nr, -10); make_truncated_value_warning(table->in_use, str_nr, str_end - str_nr, - ts_type); + ts_type, field_name); } } @@ -6132,12 +6157,14 @@ void Field::set_datetime_warning(const uint level, const uint code, double nr, timestamp_type ts_type) { - if (set_warning(level, code, 1)) + if (table->in_use->really_abort_on_warning() || + set_warning(level, code, 1)) { /* DBL_DIG is enough to print '-[digits].E+###' */ char str_nr[DBL_DIG + 8]; uint str_len= my_sprintf(str_nr, (str_nr, "%g", nr)); - make_truncated_value_warning(table->in_use, str_nr, str_len, ts_type); + make_truncated_value_warning(table->in_use, str_nr, str_len, ts_type, + field_name); } } diff --git a/sql/field.h b/sql/field.h index e12dd60c13b..f953613deb7 100644 --- a/sql/field.h +++ b/sql/field.h @@ -274,6 +274,8 @@ public: virtual void set_charset(CHARSET_INFO *charset) { } bool set_warning(const unsigned int level, const unsigned int code, int cuted_increment); + bool check_int(const char *str, int length, const char *int_end, + CHARSET_INFO *cs); void set_datetime_warning(const uint level, const uint code, const char *str, uint str_len, timestamp_type ts_type, int cuted_increment); @@ -1252,8 +1254,6 @@ uint pack_length_to_packflag(uint type); uint32 calc_pack_length(enum_field_types type,uint32 length); int set_field_to_null(Field *field); int set_field_to_null_with_conversions(Field *field, bool no_conversions); -bool test_if_int(const char *str, int length, const char *int_end, - CHARSET_INFO *cs); /* The following are for the interface with the .frm file diff --git a/sql/item_func.cc b/sql/item_func.cc index 8300d139a71..4aec7fea2d4 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -432,6 +432,16 @@ void Item_func::fix_num_length_and_dec() max_length=float_length(decimals); } +void Item_func::signal_divide_by_null() +{ + THD *thd= current_thd; + if (thd->variables.sql_mode & MODE_ERROR_FOR_DIVISION_BY_ZERO) + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_ERROR, ER_DIVISION_BY_ZERO, + ER(ER_DIVISION_BY_ZERO)); + null_value= 1; +} + + Item *Item_func::get_tmp_table_item(THD *thd) { if (!with_sum_func && !const_item()) @@ -597,11 +607,17 @@ double Item_func_div::val() DBUG_ASSERT(fixed == 1); double value=args[0]->val(); double val2=args[1]->val(); - if ((null_value= val2 == 0.0 || args[0]->null_value || args[1]->null_value)) + if ((null_value= args[0]->null_value || args[1]->null_value)) return 0.0; + if (val2 == 0.0) + { + signal_divide_by_null(); + return 0.0; + } return value/val2; } + longlong Item_func_div::val_int() { DBUG_ASSERT(fixed == 1); @@ -609,13 +625,19 @@ longlong Item_func_div::val_int() { longlong value=args[0]->val_int(); longlong val2=args[1]->val_int(); - if ((null_value= val2 == 0 || args[0]->null_value || args[1]->null_value)) + if ((null_value= args[0]->null_value || args[1]->null_value)) + return 0; + if (val2 == 0) + { + signal_divide_by_null(); return 0; + } return value/val2; } return (longlong) Item_func_div::val(); } + void Item_func_div::fix_length_and_dec() { decimals=max(args[0]->decimals,args[1]->decimals)+2; @@ -633,8 +655,13 @@ longlong Item_func_int_div::val_int() DBUG_ASSERT(fixed == 1); longlong value=args[0]->val_int(); longlong val2=args[1]->val_int(); - if ((null_value= val2 == 0 || args[0]->null_value || args[1]->null_value)) + if (args[0]->null_value || args[1]->null_value) return 0; + if (val2 == 0) + { + signal_divide_by_null(); + return 0; + } return (unsigned_flag ? (ulonglong) value / (ulonglong) val2 : value / val2); @@ -654,8 +681,13 @@ double Item_func_mod::val() DBUG_ASSERT(fixed == 1); double value= floor(args[0]->val()+0.5); double val2=floor(args[1]->val()+0.5); - if ((null_value=val2 == 0.0 || args[0]->null_value || args[1]->null_value)) + if ((null_value= args[0]->null_value || args[1]->null_value)) return 0.0; /* purecov: inspected */ + if (val2 == 0.0) + { + signal_divide_by_null(); + return 0.0; + } return fmod(value,val2); } @@ -664,8 +696,13 @@ longlong Item_func_mod::val_int() DBUG_ASSERT(fixed == 1); longlong value= args[0]->val_int(); longlong val2= args[1]->val_int(); - if ((null_value=val2 == 0 || args[0]->null_value || args[1]->null_value)) + if ((null_value= args[0]->null_value || args[1]->null_value)) return 0; /* purecov: inspected */ + if (val2 == 0) + { + signal_divide_by_null(); + return 0; + } return value % val2; } diff --git a/sql/item_func.h b/sql/item_func.h index 48a33bad80d..f1a74bdf3ab 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -137,6 +137,7 @@ public: return (null_value=args[0]->get_time(ltime)); } bool is_null() { (void) val_int(); return null_value; } + void signal_divide_by_null(); friend class udf_handler; Field *tmp_table_field() { return result_field; } Field *tmp_table_field(TABLE *t_arg); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 3f8701a8d50..988529c845c 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -459,7 +459,7 @@ static bool extract_date_time(DATE_TIME_FORMAT *format, if (!my_isspace(&my_charset_latin1,*val)) { make_truncated_value_warning(current_thd, val_begin, length, - cached_timestamp_type); + cached_timestamp_type, NullS); break; } } while (++val != val_end); @@ -790,7 +790,7 @@ longlong Item_func_to_days::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - if (get_arg0_date(<ime,0)) + if (get_arg0_date(<ime, TIME_NO_ZERO_DATE)) return 0; return (longlong) calc_daynr(ltime.year,ltime.month,ltime.day); } @@ -799,7 +799,7 @@ longlong Item_func_dayofyear::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - if (get_arg0_date(<ime,0)) + if (get_arg0_date(<ime,TIME_NO_ZERO_DATE)) return 0; return (longlong) calc_daynr(ltime.year,ltime.month,ltime.day) - calc_daynr(ltime.year,1,1) + 1; @@ -809,7 +809,7 @@ longlong Item_func_dayofmonth::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - (void) get_arg0_date(<ime,1); + (void) get_arg0_date(<ime, TIME_FUZZY_DATE); return (longlong) ltime.day; } @@ -817,7 +817,7 @@ longlong Item_func_month::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - (void) get_arg0_date(<ime,1); + (void) get_arg0_date(<ime, TIME_FUZZY_DATE); return (longlong) ltime.month; } @@ -846,7 +846,7 @@ longlong Item_func_quarter::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - (void) get_arg0_date(<ime,1); + (void) get_arg0_date(<ime, TIME_FUZZY_DATE); return (longlong) ((ltime.month+2)/3); } @@ -918,7 +918,7 @@ longlong Item_func_week::val_int() DBUG_ASSERT(fixed == 1); uint year; TIME ltime; - if (get_arg0_date(<ime,0)) + if (get_arg0_date(<ime, TIME_NO_ZERO_DATE)) return 0; return (longlong) calc_week(<ime, week_mode((uint) args[1]->val_int()), @@ -931,7 +931,7 @@ longlong Item_func_yearweek::val_int() DBUG_ASSERT(fixed == 1); uint year,week; TIME ltime; - if (get_arg0_date(<ime,0)) + if (get_arg0_date(<ime, TIME_NO_ZERO_DATE)) return 0; week= calc_week(<ime, (week_mode((uint) args[1]->val_int()) | WEEK_YEAR), @@ -972,7 +972,7 @@ longlong Item_func_year::val_int() { DBUG_ASSERT(fixed == 1); TIME ltime; - (void) get_arg0_date(<ime,1); + (void) get_arg0_date(<ime, TIME_FUZZY_DATE); return (longlong) ltime.year; } @@ -1553,7 +1553,7 @@ String *Item_func_date_format::val_str(String *str) if (!is_time_format) { - if (get_arg0_date(&l_time,1)) + if (get_arg0_date(&l_time, TIME_FUZZY_DATE)) return 0; } else @@ -1715,7 +1715,7 @@ longlong Item_func_convert_tz::val_int() bool Item_func_convert_tz::get_date(TIME *ltime, - uint fuzzy_date __attribute__((unused))) + uint fuzzy_date __attribute__((unused))) { my_time_t my_time_tmp; bool not_used; @@ -1727,7 +1727,7 @@ bool Item_func_convert_tz::get_date(TIME *ltime, if (!args[2]->const_item()) to_tz= my_tz_find(args[2]->val_str(&str), tz_tables); - if (from_tz==0 || to_tz==0 || get_arg0_date(ltime, 0)) + if (from_tz==0 || to_tz==0 || get_arg0_date(ltime, TIME_NO_ZERO_DATE)) { null_value= 1; return 1; @@ -1791,7 +1791,7 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date) INTERVAL interval; ltime->neg= 0; - if (args[0]->get_date(ltime,0) || + if (args[0]->get_date(ltime, TIME_NO_ZERO_DATE) || get_interval_value(args[1],int_type,&value,&interval)) goto null_date; sign= (interval.neg ? -1 : 1); @@ -1900,7 +1900,7 @@ String *Item_date_add_interval::val_str(String *str) TIME ltime; enum date_time_format_types format; - if (Item_date_add_interval::get_date(<ime,0)) + if (Item_date_add_interval::get_date(<ime, TIME_NO_ZERO_DATE)) return 0; if (ltime.time_type == MYSQL_TIMESTAMP_DATE) @@ -1923,7 +1923,7 @@ longlong Item_date_add_interval::val_int() DBUG_ASSERT(fixed == 1); TIME ltime; longlong date; - if (Item_date_add_interval::get_date(<ime,0)) + if (Item_date_add_interval::get_date(<ime, TIME_NO_ZERO_DATE)) return (longlong) 0; date = (ltime.year*100L + ltime.month)*100L + ltime.day; return ltime.time_type == MYSQL_TIMESTAMP_DATE ? date : @@ -2000,7 +2000,7 @@ longlong Item_extract::val_int() long neg; if (date_value) { - if (get_arg0_date(<ime,1)) + if (get_arg0_date(<ime, TIME_FUZZY_DATE)) return 0; neg=1; } @@ -2172,7 +2172,7 @@ String *Item_datetime_typecast::val_str(String *str) { DBUG_ASSERT(fixed == 1); TIME ltime; - if (!get_arg0_date(<ime,1) && + if (!get_arg0_date(<ime, TIME_FUZZY_DATE) && !make_datetime(ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME, <ime, str)) return str; @@ -2207,7 +2207,7 @@ String *Item_time_typecast::val_str(String *str) bool Item_date_typecast::get_date(TIME *ltime, uint fuzzy_date) { - bool res= get_arg0_date(ltime,1); + bool res= get_arg0_date(ltime, TIME_FUZZY_DATE); ltime->time_type= MYSQL_TIMESTAMP_DATE; return res; } @@ -2218,7 +2218,7 @@ String *Item_date_typecast::val_str(String *str) DBUG_ASSERT(fixed == 1); TIME ltime; - if (!get_arg0_date(<ime,1) && !str->alloc(11)) + if (!get_arg0_date(<ime, TIME_FUZZY_DATE) && !str->alloc(11)) { make_date((DATE_TIME_FORMAT *) 0, <ime, str); return str; @@ -2312,7 +2312,7 @@ String *Item_func_add_time::val_str(String *str) l_time3.neg= 0; if (is_date) // TIMESTAMP function { - if (get_arg0_date(&l_time1,1) || + if (get_arg0_date(&l_time1, TIME_FUZZY_DATE) || args[1]->get_time(&l_time2) || l_time1.time_type == MYSQL_TIMESTAMP_TIME || l_time2.time_type != MYSQL_TIMESTAMP_TIME) @@ -2599,8 +2599,8 @@ longlong Item_func_timestamp_diff::val_int() int neg= 1; null_value= 0; - if (args[0]->get_date(<ime1, 0) || - args[1]->get_date(<ime2, 0)) + if (args[0]->get_date(<ime1, TIME_NO_ZERO_DATE) || + args[1]->get_date(<ime2, TIME_NO_ZERO_DATE)) goto null_date; if (calc_time_diff(<ime2,<ime1, 1, diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 6ba76cf52b6..99474467065 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -266,13 +266,20 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset; #define MODE_DB2 2048 #define MODE_MAXDB 4096 #define MODE_NO_KEY_OPTIONS 8192 -#define MODE_NO_TABLE_OPTIONS 16384 -#define MODE_NO_FIELD_OPTIONS 32768 -#define MODE_MYSQL323 65536 -#define MODE_MYSQL40 (MODE_MYSQL323*2) -#define MODE_ANSI (MODE_MYSQL40*2) -#define MODE_NO_AUTO_VALUE_ON_ZERO (MODE_ANSI*2) -#define MODE_NO_BACKSLASH_ESCAPES (MODE_NO_AUTO_VALUE_ON_ZERO*2) +#define MODE_NO_TABLE_OPTIONS 16384 +#define MODE_NO_FIELD_OPTIONS 32768 +#define MODE_MYSQL323 65536 +#define MODE_MYSQL40 (MODE_MYSQL323*2) +#define MODE_ANSI (MODE_MYSQL40*2) +#define MODE_NO_AUTO_VALUE_ON_ZERO (MODE_ANSI*2) +#define MODE_NO_BACKSLASH_ESCAPES (MODE_NO_AUTO_VALUE_ON_ZERO*2) +#define MODE_STRICT_TRANS_TABLES (MODE_NO_BACKSLASH_ESCAPES*2) +#define MODE_STRICT_ALL_TABLES (MODE_STRICT_TRANS_TABLES*2) +#define MODE_NO_ZERO_IN_DATE (MODE_STRICT_ALL_TABLES*2) +#define MODE_NO_ZERO_DATE (MODE_NO_ZERO_IN_DATE*2) +#define MODE_INVALID_DATES (MODE_NO_ZERO_DATE*2) +#define MODE_ERROR_FOR_DIVISION_BY_ZERO (MODE_INVALID_DATES*2) +#define MODE_TRADITIONAL (MODE_ERROR_FOR_DIVISION_BY_ZERO*2) #define RAID_BLOCK_SIZE 1024 @@ -619,6 +626,7 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, TABLE *table, int mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields, List<List_item> &values, List<Item> &update_fields, List<Item> &update_values, enum_duplicates flag); +int check_that_all_fields_are_given_values(THD *thd, TABLE *entry); int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds); int mysql_delete(THD *thd, TABLE_LIST *table, COND *conds, SQL_LIST *order, ha_rows rows, ulong options); @@ -831,7 +839,7 @@ bool eval_const_cond(COND *cond); int mysql_load(THD *thd,sql_exchange *ex, TABLE_LIST *table_list, List<Item> &fields, enum enum_duplicates handle_duplicates, bool local_file,thr_lock_type lock_type); -int write_record(TABLE *table,COPY_INFO *info); +int write_record(THD *thd, TABLE *table, COPY_INFO *info); /* sql_manager.cc */ /* bits set in manager_status */ @@ -1082,7 +1090,6 @@ void free_blobs(TABLE *table); int set_zone(int nr,int min_zone,int max_zone); ulong convert_period_to_month(ulong period); ulong convert_month_to_period(ulong month); -uint calc_days_in_year(uint year); void get_date_from_daynr(long daynr,uint *year, uint *month, uint *day); my_time_t TIME_to_timestamp(THD *thd, const TIME *t, bool *not_exist); @@ -1095,7 +1102,8 @@ void localtime_to_TIME(TIME *to, struct tm *from); void calc_time_from_sec(TIME *to, long seconds, long microseconds); void make_truncated_value_warning(THD *thd, const char *str_val, - uint str_length, timestamp_type time_type); + uint str_length, timestamp_type time_type, + const char *field_name); extern DATE_TIME_FORMAT *date_time_format_make(timestamp_type format_type, const char *format_str, uint format_length); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 21633858ad8..59abee55a3a 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -219,7 +219,10 @@ const char *sql_mode_names[] = "NO_DIR_IN_CREATE", "POSTGRESQL", "ORACLE", "MSSQL", "DB2", "MAXDB", "NO_KEY_OPTIONS", "NO_TABLE_OPTIONS", "NO_FIELD_OPTIONS", "MYSQL323", "MYSQL40", "ANSI", - "NO_AUTO_VALUE_ON_ZERO", "NO_BACKSLASH_ESCAPES", NullS + "NO_AUTO_VALUE_ON_ZERO", "NO_BACKSLASH_ESCAPES", "STRICT_TRANS_TABLES", "STRICT_ALL_TABLES", + "NO_ZERO_IN_DATE", "NO_ZERO_DATE", "ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO", + "TRADITIONAL", + NullS }; TYPELIB sql_mode_typelib= { array_elements(sql_mode_names)-1,"", sql_mode_names }; @@ -2090,7 +2093,8 @@ extern "C" int my_message_sql(uint error, const char *str, DBUG_PRINT("error", ("Message: '%s'", str)); if ((thd= current_thd)) { - if (thd->spcont && thd->spcont->find_handler(error)) + if (thd->spcont && + thd->spcont->find_handler(error, MYSQL_ERROR::WARN_LEVEL_ERROR)) { DBUG_RETURN(0); } @@ -6112,7 +6116,7 @@ get_one_option(int optid, const struct my_option *opt __attribute__((unused)), char *end; uint length= strlen(argument); long value= my_strntol(&my_charset_latin1, argument, length, 10, &end, &err); - if (test_if_int(argument,(uint) length, end, &my_charset_latin1)) + if (end == argument+length) berkeley_lock_scan_time= value; else { diff --git a/sql/opt_range.cc b/sql/opt_range.cc index a47b4bcfbca..ebb9c8098cf 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3363,6 +3363,7 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part, { uint maybe_null=(uint) field->real_maybe_null(), copies; uint field_length=field->pack_length()+maybe_null; + bool optimize_range; SEL_ARG *tree; char *str, *str2; DBUG_ENTER("get_mm_leaf"); @@ -3393,6 +3394,9 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part, ((Field_str*)field)->charset() != conf_func->compare_collation()) DBUG_RETURN(0); + optimize_range= field->optimize_range(param->real_keynr[key_part->key], + key_part->part); + if (type == Item_func::LIKE_FUNC) { bool like_error; @@ -3400,8 +3404,7 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part, String tmp(buff1,sizeof(buff1),value->collation.collation),*res; uint length,offset,min_length,max_length; - if (!field->optimize_range(param->real_keynr[key_part->key], - key_part->part)) + if (!optimize_range) DBUG_RETURN(0); // Can't optimize this if (!(res= value->val_str(&tmp))) DBUG_RETURN(&null_element); @@ -3466,8 +3469,7 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part, DBUG_RETURN(new SEL_ARG(field,min_str,max_str)); } - if (!field->optimize_range(param->real_keynr[key_part->key], - key_part->part) && + if (!optimize_range && type != Item_func::EQ_FUNC && type != Item_func::EQUAL_FUNC) DBUG_RETURN(0); // Can't optimize this diff --git a/sql/protocol.cc b/sql/protocol.cc index bceac780037..187989158df 100644 --- a/sql/protocol.cc +++ b/sql/protocol.cc @@ -65,7 +65,8 @@ void send_error(THD *thd, uint sql_errno, const char *err) err ? err : net->last_error[0] ? net->last_error : "NULL")); - if (thd->spcont && thd->spcont->find_handler(sql_errno)) + if (thd->spcont && thd->spcont->find_handler(sql_errno, + MYSQL_ERROR::WARN_LEVEL_ERROR)) { DBUG_VOID_RETURN; } @@ -152,7 +153,8 @@ void send_error(THD *thd, uint sql_errno, const char *err) void send_warning(THD *thd, uint sql_errno, const char *err) { DBUG_ENTER("send_warning"); - if (thd->spcont && thd->spcont->find_handler(sql_errno)) + if (thd->spcont && + thd->spcont->find_handler(sql_errno, MYSQL_ERROR::WARN_LEVEL_WARN)) { DBUG_VOID_RETURN; } @@ -186,7 +188,8 @@ net_printf(THD *thd, uint errcode, ...) DBUG_ENTER("net_printf"); DBUG_PRINT("enter",("message: %u",errcode)); - if (thd->spcont && thd->spcont->find_handler(errcode)) + if (thd->spcont && thd->spcont->find_handler(errcode, + MYSQL_ERROR::WARN_LEVEL_ERROR)) { DBUG_VOID_RETURN; } diff --git a/sql/set_var.cc b/sql/set_var.cc index 04cd4d13b26..6c83ba9470f 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -3101,6 +3101,13 @@ ulong fix_sql_mode(ulong sql_mode) sql_mode|= MODE_NO_FIELD_OPTIONS; if (sql_mode & MODE_MYSQL323) sql_mode|= MODE_NO_FIELD_OPTIONS; + if (sql_mode & MODE_TRADITIONAL) + { + sql_mode|= (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES | + MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | + MODE_ERROR_FOR_DIVISION_BY_ZERO); + sql_mode&= ~MODE_INVALID_DATES; + } return sql_mode; } diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt index c613a22088a..7b24036d385 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -292,7 +292,7 @@ character-set=latin2 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -392,3 +392,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt index 0f3a8f6ffdb..81a310a3f0a 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -283,7 +283,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -383,3 +383,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt index d0d86a07b7e..a67577b1c99 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -292,7 +292,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -392,3 +392,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt index 0b502244a64..e7f1d1b7f64 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -280,7 +280,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt index 7886c785c40..b6058bc56fc 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -285,7 +285,7 @@ character-set=latin7 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -385,3 +385,6 @@ character-set=latin7 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt index 9668cbf3c9f..345d20e2203 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -280,7 +280,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index c2c82443f91..ccc69c68683 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -393,3 +393,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt index 3aac21ec481..edd4e07c4d0 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -280,7 +280,7 @@ character-set=greek "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -380,3 +380,6 @@ character-set=greek "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt index 8f96a0bf183..27c51b3ce2b 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -285,7 +285,7 @@ character-set=latin2 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -385,3 +385,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt index 8d63656bac2..40e1271b187 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -280,7 +280,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt index 30eb8daffa4..67de9337ff5 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -284,7 +284,7 @@ character-set=ujis "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -384,3 +384,6 @@ character-set=ujis "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt index dac28683a78..e2d4e29ede7 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -280,7 +280,7 @@ character-set=euckr "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -380,3 +380,6 @@ character-set=euckr "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt index 58661f16bcf..a8bc6129db9 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -282,7 +282,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -382,3 +382,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt index ea204145d54..ddf6960c740 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -282,7 +282,7 @@ character-set=latin1 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -382,3 +382,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt index db0d4914c19..5a871fbf776 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -285,7 +285,7 @@ character-set=latin2 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -385,3 +385,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt index d3508f120db..c0861879702 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -382,3 +382,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt index 65ab66e1256..60c3cb4245d 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -285,7 +285,7 @@ character-set=latin2 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -385,3 +385,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt index 6501598383c..e62c5a84a44 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -285,7 +285,7 @@ character-set=koi8r "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -385,3 +385,6 @@ character-set=koi8r "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt index 6e7826e33d6..2c366890653 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -273,7 +273,7 @@ character-set=cp1250 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -373,3 +373,6 @@ character-set=cp1250 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt index 4c82ae5e3af..12eacb82dac 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -288,7 +288,7 @@ character-set=latin2 "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", "Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", -"Data truncated; out of range for column '%s' at row %ld", +"Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", "Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'", @@ -388,3 +388,6 @@ character-set=latin2 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt index c9a1d7229ea..94b180914fe 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -384,3 +384,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt index e80cb48d157..718db694494 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -380,3 +380,6 @@ character-set=latin1 "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt index 2a71aa088f4..7a4c37abd1d 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -386,3 +386,6 @@ character-set=koi8u "Trigger's '%-.64s' is view or temporary table" "Updating of %s row is not allowed in %strigger" "There is no %s row in %s trigger" +"Field '%-.64s' doesn't have a default value", +"Division by 0", +"Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc index 2f7bdbffa2b..169c9809383 100644 --- a/sql/sp_rcontext.cc +++ b/sql/sp_rcontext.cc @@ -57,7 +57,8 @@ sp_rcontext::set_item_eval(uint idx, Item *i, enum_field_types type) } int -sp_rcontext::find_handler(uint sql_errno) +sp_rcontext::find_handler(uint sql_errno, + MYSQL_ERROR::enum_warning_level level) { if (in_handler) return 0; // Already executing a handler @@ -82,7 +83,8 @@ sp_rcontext::find_handler(uint sql_errno) found= 1; break; case sp_cond_type_t::warning: - if (sqlstate[0] == '0' && sqlstate[1] == '1') + if (sqlstate[0] == '0' && sqlstate[1] == '1' || + level == MYSQL_ERROR::WARN_LEVEL_WARN) found= 1; break; case sp_cond_type_t::notfound: @@ -90,7 +92,8 @@ sp_rcontext::find_handler(uint sql_errno) found= 1; break; case sp_cond_type_t::exception: - if (sqlstate[0] != '0' || sqlstate[1] > '2') + if (sqlstate[0] != '0' || sqlstate[1] > '2' || + level == MYSQL_ERROR::WARN_LEVEL_ERROR) found= 1; break; } diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h index f26b6760310..fff10ff3d0a 100644 --- a/sql/sp_rcontext.h +++ b/sql/sp_rcontext.h @@ -123,7 +123,7 @@ class sp_rcontext : public Sql_alloc // Returns 1 if a handler was found, 0 otherwise. int - find_handler(uint sql_errno); + find_handler(uint sql_errno,MYSQL_ERROR::enum_warning_level level); // Returns handler type and sets *ip to location if one was found inline int diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 8587942e30e..ffbb2afd266 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1420,12 +1420,12 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db, READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD, thd->open_options, entry)) && (error != 5 || - fn_format(path, path, 0, reg_ext, MY_UNPACK_FILENAME), - open_new_frm(path, alias, db, name, - (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | - HA_GET_INDEX | HA_TRY_READ_ONLY), - READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD, - thd->open_options, entry, table_desc, mem_root))) + (fn_format(path, path, 0, reg_ext, MY_UNPACK_FILENAME), + open_new_frm(path, alias, db, name, + (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | + HA_GET_INDEX | HA_TRY_READ_ONLY), + READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD, + thd->open_options, entry, table_desc, mem_root)))) { if (!entry->crashed) diff --git a/sql/sql_class.cc b/sql/sql_class.cc index cc6b86f4bc2..392534492df 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -200,7 +200,7 @@ THD::THD() #endif net.last_error[0]=0; // If error on boot ull=0; - system_thread=cleanup_done=0; + system_thread= cleanup_done= abort_on_warning= 0; peer_port= 0; // For SHOW PROCESSLIST transaction.changed_tables = 0; #ifdef __WIN__ diff --git a/sql/sql_class.h b/sql/sql_class.h index d917eeef550..43d4b25e518 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -950,7 +950,7 @@ public: /* for user variables replication*/ DYNAMIC_ARRAY user_var_events; - enum killed_state { NOT_KILLED=0, KILL_CONNECTION=ER_SERVER_SHUTDOWN, KILL_QUERY=ER_QUERY_INTERRUPTED }; + enum killed_state { NOT_KILLED=0, KILL_BAD_DATA=1, KILL_CONNECTION=ER_SERVER_SHUTDOWN, KILL_QUERY=ER_QUERY_INTERRUPTED }; killed_state volatile killed; /* scramble - random string sent to client on handshake */ @@ -967,7 +967,7 @@ public: bool tmp_table_used; bool charset_is_system_charset, charset_is_collation_connection; bool slow_command; - + bool no_trans_update, abort_on_warning; longlong row_count_func; /* For the ROW_COUNT() function */ sp_rcontext *spcont; // SP runtime context sp_cache *sp_proc_cache; @@ -1125,12 +1125,19 @@ public: void update_charset(); inline int killed_errno() const { - return killed; + return killed != KILL_BAD_DATA ? killed : 0; } inline void send_kill_message() const { my_error(killed_errno(), MYF(0)); } + /* return TRUE if we will abort query if we make a warning now */ + inline bool really_abort_on_warning() + { + return (abort_on_warning && + (!no_trans_update || + (variables.sql_mode & MODE_STRICT_ALL_TABLES))); + } void set_status_var_init(); }; diff --git a/sql/sql_error.cc b/sql/sql_error.cc index d68d62a8820..c0f76ab0388 100644 --- a/sql/sql_error.cc +++ b/sql/sql_error.cc @@ -103,14 +103,30 @@ void mysql_reset_errors(THD *thd) MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code, const char *msg) { + MYSQL_ERROR *err= NULL; DBUG_ENTER("push_warning"); + if (thd->query_id != thd->warn_id) mysql_reset_errors(thd); - MYSQL_ERROR *err= NULL; + if (thd->spcont && + thd->spcont->find_handler(code, + ((int) level >= + (int) MYSQL_ERROR::WARN_LEVEL_WARN && + thd->really_abort_on_warning()) ? + MYSQL_ERROR::WARN_LEVEL_ERROR : level)) + { + DBUG_RETURN(NULL); + } - if (thd->spcont && thd->spcont->find_handler(code)) + /* Abort if we are using strict mode and we are not using IGNORE */ + if ((int) level >= (int) MYSQL_ERROR::WARN_LEVEL_WARN && + thd->really_abort_on_warning()) + { + thd->killed= THD::KILL_BAD_DATA; + my_message(code, msg, MYF(0)); DBUG_RETURN(NULL); + } if (thd->warn_list.elements < thd->variables.max_error_count) { @@ -120,8 +136,7 @@ MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, */ MEM_ROOT *old_root=my_pthread_getspecific_ptr(MEM_ROOT*,THR_MALLOC); my_pthread_setspecific_ptr(THR_MALLOC, &thd->warn_root); - err= new MYSQL_ERROR(thd, code, level, msg); - if (err) + if ((err= new MYSQL_ERROR(thd, code, level, msg))) thd->warn_list.push_back(err); my_pthread_setspecific_ptr(THR_MALLOC, old_root); } diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 5828f0be2f9..1fd126abe45 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -270,13 +270,24 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, if (lock_type != TL_WRITE_DELAYED) table->file->start_bulk_insert(values_list.elements); + thd->no_trans_update= 0; + thd->abort_on_warning= (duplic != DUP_IGNORE && + (thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES))); + + if (check_that_all_fields_are_given_values(thd, table)) + { + /* thd->net.report_error is now set, which will abort the next loop */ + error= 1; + } + while ((values= its++)) { if (fields.elements || !value_count) { restore_record(table,default_values); // Get empty record - if (fill_record(fields, *values, 0)|| thd->net.report_error || - check_null_fields(thd,table)) + if (fill_record(fields, *values, 0)|| thd->net.report_error) { if (values_list.elements != 1 && !thd->net.report_error) { @@ -305,10 +316,13 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, } } - // FIXME: Actually we should do this before check_null_fields. - // Or even go into write_record ? + /* + FIXME: Actually we should do this before + check_that_all_fields_are_given_values Or even go into write_record ? + */ if (table->triggers) - table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_BEFORE); + table->triggers->process_triggers(thd, TRG_EVENT_INSERT, + TRG_ACTION_BEFORE); #ifndef EMBEDDED_LIBRARY if (lock_type == TL_WRITE_DELAYED) @@ -318,7 +332,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, } else #endif - error=write_record(table,&info); + error=write_record(thd, table ,&info); /* If auto_increment values are used, save the first one for LAST_INSERT_ID() and for the update log. @@ -447,6 +461,7 @@ abort: #endif free_underlaid_joins(thd, &thd->lex->select_lex); table->insert_values=0; + thd->abort_on_warning= 0; DBUG_RETURN(-1); } @@ -633,10 +648,12 @@ static int last_uniq_key(TABLE *table,uint keynr) /* Write a record to table with optional deleting of conflicting records + + Sets thd->no_trans_update if table which is updated didn't have transactions */ -int write_record(TABLE *table,COPY_INFO *info) +int write_record(THD *thd, TABLE *table,COPY_INFO *info) { int error; char *key=0; @@ -735,6 +752,8 @@ int write_record(TABLE *table,COPY_INFO *info) else if ((error=table->file->delete_row(table->record[1]))) goto err; info->deleted++; + if (!table->file->has_transactions()) + thd->no_trans_update= 1; } } info->copied++; @@ -750,6 +769,8 @@ int write_record(TABLE *table,COPY_INFO *info) info->copied++; if (key) my_safe_afree(key,table->max_unique_length,MAX_KEY_LENGTH); + if (!table->file->has_transactions()) + thd->no_trans_update= 1; DBUG_RETURN(0); err: @@ -767,22 +788,22 @@ err: fields. ******************************************************************************/ -static int check_null_fields(THD *thd __attribute__((unused)), - TABLE *entry __attribute__((unused))) +int check_that_all_fields_are_given_values(THD *thd, TABLE *entry) { -#ifdef DONT_USE_DEFAULT_FIELDS + if (!thd->abort_on_warning) + return 0; + for (Field **field=entry->field ; *field ; field++) { - if ((*field)->query_id != thd->query_id && !(*field)->maybe_null() && - *field != entry->timestamp_field && - *field != entry->next_number_field) + if ((*field)->query_id != thd->query_id && + ((*field)->flags & NO_DEFAULT_VALUE_FLAG)) { - my_printf_error(ER_BAD_NULL_ERROR, ER(ER_BAD_NULL_ERROR),MYF(0), + my_printf_error(ER_NO_DEFAULT_FOR_FIELD, + ER(ER_NO_DEFAULT_FOR_FIELD),MYF(0), (*field)->field_name); return 1; } } -#endif return 0; } @@ -1503,7 +1524,7 @@ bool delayed_insert::handle_inserts(void) using_ignore=1; } thd.clear_error(); // reset error for binlog - if (write_record(table,&info)) + if (write_record(&thd, table, &info)) { info.error_count++; // Ignore errors thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status); @@ -1640,7 +1661,12 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) info.handle_duplicates == DUP_REPLACE) table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); table->file->start_bulk_insert((ha_rows) 0); - DBUG_RETURN(0); + thd->no_trans_update= 0; + thd->abort_on_warning= (info.handle_duplicates != DUP_IGNORE && + (thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES))); + DBUG_RETURN(check_that_all_fields_are_given_values(thd, table)); } @@ -1659,6 +1685,7 @@ select_insert::~select_insert() table->file->reset(); } thd->count_cuted_fields= CHECK_FIELD_IGNORE; + thd->abort_on_warning= 0; DBUG_VOID_RETURN; } @@ -1675,7 +1702,7 @@ bool select_insert::send_data(List<Item> &values) fill_record(*fields, values, 1); else fill_record(table->field, values, 1); - if (thd->net.report_error || write_record(table,&info)) + if (thd->net.report_error || write_record(thd, table, &info)) DBUG_RETURN(1); if (table->next_number_field) // Clear for next record { @@ -1824,7 +1851,12 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) info.handle_duplicates == DUP_REPLACE) table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); table->file->start_bulk_insert((ha_rows) 0); - DBUG_RETURN(0); + thd->no_trans_update= 0; + thd->abort_on_warning= (info.handle_duplicates != DUP_IGNORE && + (thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES))); + DBUG_RETURN(check_that_all_fields_are_given_values(thd, table)); } @@ -1836,7 +1868,7 @@ bool select_create::send_data(List<Item> &values) return 0; } fill_record(field, values, 1); - if (thd->net.report_error ||write_record(table,&info)) + if (thd->net.report_error || write_record(thd, table, &info)) return 1; if (table->next_number_field) // Clear for next record { diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 9b050c0863a..28de2b9d116 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -146,6 +146,8 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, my_error(ER_FIELD_SPECIFIED_TWICE, MYF(0), thd->dupp_field->field_name); DBUG_RETURN(-1); } + if (check_that_all_fields_are_given_values(thd, table)) + DBUG_RETURN(1); } uint tot_length=0; @@ -284,6 +286,13 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); table->file->start_bulk_insert((ha_rows) 0); table->copy_blobs=1; + + thd->no_trans_update= 0; + thd->abort_on_warning= (handle_duplicates != DUP_IGNORE && + (thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES))); + if (!field_term->length() && !enclosed->length()) error=read_fixed_length(thd,info,table,fields,read_info, skip_lines); @@ -376,12 +385,14 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, #endif /*!EMBEDDED_LIBRARY*/ if (transactional_table) error=ha_autocommit_or_rollback(thd,error); + err: if (thd->lock) { mysql_unlock_tables(thd, thd->lock); thd->lock=0; } + thd->abort_on_warning= 0; DBUG_RETURN(error); } @@ -396,6 +407,7 @@ read_fixed_length(THD *thd,COPY_INFO &info,TABLE *table,List<Item> &fields, List_iterator_fast<Item> it(fields); Item_field *sql_field; ulonglong id; + bool no_trans_update; DBUG_ENTER("read_fixed_length"); id= 0; @@ -427,6 +439,7 @@ read_fixed_length(THD *thd,COPY_INFO &info,TABLE *table,List<Item> &fields, #ifdef HAVE_purify read_info.row_end[0]=0; #endif + no_trans_update= !table->file->has_transactions(); while ((sql_field= (Item_field*) it++)) { Field *field= sql_field->field; @@ -446,7 +459,7 @@ read_fixed_length(THD *thd,COPY_INFO &info,TABLE *table,List<Item> &fields, field->field_length) length=field->field_length; save_chr=pos[length]; pos[length]='\0'; // Safeguard aganst malloc - field->store((char*) pos,length,read_info.read_charset); + field->store((char*) pos,length,read_info.read_charset); pos[length]=save_chr; if ((pos+=length) > read_info.row_end) pos= read_info.row_end; /* Fills rest with space */ @@ -459,8 +472,10 @@ read_fixed_length(THD *thd,COPY_INFO &info,TABLE *table,List<Item> &fields, ER_WARN_TOO_MANY_RECORDS, ER(ER_WARN_TOO_MANY_RECORDS), thd->row_count); } - if (write_record(table,&info)) + if (thd->killed || write_record(thd,table,&info)) DBUG_RETURN(1); + thd->no_trans_update= no_trans_update; + /* If auto_increment values are used, save the first one for LAST_INSERT_ID() and for the binary/update log. @@ -498,10 +513,12 @@ read_sep_field(THD *thd,COPY_INFO &info,TABLE *table, Item_field *sql_field; uint enclosed_length; ulonglong id; + bool no_trans_update; DBUG_ENTER("read_sep_field"); enclosed_length=enclosed.length(); id= 0; + no_trans_update= !table->file->has_transactions(); for (;;it.rewind()) { @@ -563,7 +580,7 @@ read_sep_field(THD *thd,COPY_INFO &info,TABLE *table, ER(ER_WARN_TOO_FEW_RECORDS), thd->row_count); } } - if (write_record(table,&info)) + if (thd->killed || write_record(thd, table, &info)) DBUG_RETURN(1); /* If auto_increment values are used, save the first one @@ -575,6 +592,7 @@ read_sep_field(THD *thd,COPY_INFO &info,TABLE *table, id= thd->last_insert_id; if (table->next_number_field) table->next_number_field->reset(); // Clear for next record + thd->no_trans_update= no_trans_update; if (read_info.next_line()) // Skip to next line break; if (read_info.line_cuted) @@ -583,6 +601,8 @@ read_sep_field(THD *thd,COPY_INFO &info,TABLE *table, push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_TOO_MANY_RECORDS, ER(ER_WARN_TOO_MANY_RECORDS), thd->row_count); + if (thd->killed) + DBUG_RETURN(1); } thd->row_count++; } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 8c63b6d1b9f..7be33c751e1 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1273,7 +1273,7 @@ bool do_command(THD *thd) } else { - if (thd->killed == THD::KILL_QUERY) + if (thd->killed == THD::KILL_QUERY || thd->killed == THD::KILL_BAD_DATA) thd->killed= THD::NOT_KILLED; packet=(char*) net->read_pos; @@ -3185,8 +3185,6 @@ purposes internal to the MySQL server", MYF(0)); thd->in_lock_tables=1; thd->options|= OPTION_TABLE_LOCK; - - if (!(res= open_and_lock_tables(thd, all_tables))) { #ifdef HAVE_QUERY_CACHE @@ -4620,6 +4618,7 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, register create_field *new_field; LEX *lex= thd->lex; uint allowed_type_modifier=0; + uint sign_len; char warn_buff[MYSQL_ERRMSG_SIZE]; DBUG_ENTER("add_field_to_list"); @@ -4711,9 +4710,14 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, new_field->comment.str= (char*) comment->str; new_field->comment.length=comment->length; } + /* Set flag if this field doesn't have a default value */ + if (!default_value && !(type_modifier & AUTO_INCREMENT_FLAG) && + (type_modifier & NOT_NULL_FLAG) && type != FIELD_TYPE_TIMESTAMP) + new_field->flags|= NO_DEFAULT_VALUE_FLAG; + if (length && !(new_field->length= (uint) atoi(length))) length=0; /* purecov: inspected */ - uint sign_len=type_modifier & UNSIGNED_FLAG ? 0 : 1; + sign_len=type_modifier & UNSIGNED_FLAG ? 0 : 1; if (new_field->length && new_field->decimals && new_field->length < new_field->decimals+1 && diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cd2eb7c1516..5d5cb0794f0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8050,6 +8050,19 @@ join_read_system(JOIN_TAB *tab) } +/* + Read a table when there is at most one matching row + + SYNOPSIS + join_read_const() + tab Table to read + + RETURN + 0 Row was found + -1 Row was not found + 1 Got an error (other than row not found) during read +*/ + static int join_read_const(JOIN_TAB *tab) { @@ -8057,6 +8070,7 @@ join_read_const(JOIN_TAB *tab) TABLE *table= tab->table; if (table->status & STATUS_GARBAGE) // If first read { + table->status= 0; if (cp_buffer_from_ref(&tab->ref)) error=HA_ERR_KEY_NOT_FOUND; else @@ -8067,6 +8081,7 @@ join_read_const(JOIN_TAB *tab) } if (error) { + table->status= STATUS_NOT_FOUND; table->null_row=1; empty_record(table); if (error != HA_ERR_KEY_NOT_FOUND) diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 1df419d04c3..cf4203bf5b8 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -77,7 +77,7 @@ bool select_union::send_data(List<Item> &values) return 0; } fill_record(table->field, values, 1); - if (thd->net.report_error || write_record(table,&info)) + if (thd->net.report_error || write_record(thd, table,&info)) { if (thd->net.last_errno == ER_RECORD_FILE_FULL) { diff --git a/sql/sql_update.cc b/sql/sql_update.cc index dc867968262..8f4cbbf264c 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -348,6 +348,12 @@ int mysql_update(THD *thd, thd->proc_info="Updating"; query_id=thd->query_id; + transactional_table= table->file->has_transactions(); + thd->no_trans_update= 0; + thd->abort_on_warning= test(thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES)); + while (!(error=info.read_record(&info)) && !thd->killed) { if (!(select && select->skip_record())) @@ -366,6 +372,7 @@ int mysql_update(THD *thd, (byte*) table->record[0]))) { updated++; + thd->no_trans_update= !transactional_table; } else if (handle_duplicates != DUP_IGNORE || error != HA_ERR_FOUND_DUPP_KEY) @@ -406,7 +413,6 @@ int mysql_update(THD *thd, query_cache_invalidate3(thd, table_list, 1); } - transactional_table= table->file->has_transactions(); log_delayed= (transactional_table || table->tmp_table); if ((updated || (error < 0)) && (error <= 0 || !transactional_table)) { @@ -460,6 +466,7 @@ err: table->key_read=0; table->file->extra(HA_EXTRA_NO_KEYREAD); } + thd->abort_on_warning= 0; DBUG_RETURN(-1); } @@ -657,6 +664,11 @@ int mysql_multi_update(THD *thd, handle_duplicates))) DBUG_RETURN(-1); + thd->no_trans_update= 0; + thd->abort_on_warning= test(thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES)); + List<Item> total_list; res= mysql_select(thd, &select_lex->ref_pointer_array, table_list, select_lex->with_wild, @@ -666,6 +678,7 @@ int mysql_multi_update(THD *thd, options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK, result, unit, select_lex); delete result; + thd->abort_on_warning= 0; DBUG_RETURN(res); } @@ -1013,6 +1026,8 @@ bool multi_update::send_data(List<Item> ¬_used_values) updated--; DBUG_RETURN(1); } + if (!table->file->has_transactions()) + thd->no_trans_update= 1; } } else diff --git a/sql/table.cc b/sql/table.cc index 946dbc0766a..88f0cefc09a 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -461,6 +461,7 @@ int openfrm(THD *thd, const char *name, const char *alias, uint db_stat, field_length= (uint) strpos[3]; recpos= uint2korr(strpos+4), pack_flag= uint2korr(strpos+6); + pack_flag&= ~NO_DEFAULT_VALUE_FLAG; // Safety for old files unireg_type= (uint) strpos[8]; interval_nr= (uint) strpos[10]; diff --git a/sql/time.cc b/sql/time.cc index 4421b6aa00f..f47d7652864 100644 --- a/sql/time.cc +++ b/sql/time.cc @@ -33,15 +33,6 @@ int calc_weekday(long daynr,bool sunday_first_day_of_week) DBUG_RETURN ((int) ((daynr + 5L + (sunday_first_day_of_week ? 1L : 0L)) % 7)); } - /* Calc days in one year. works with 0 <= year <= 99 */ - -uint calc_days_in_year(uint year) -{ - return (year & 3) == 0 && (year%100 || (year%400 == 0 && year)) ? - 366 : 365; -} - - /* The bits in week_format has the following meaning: WEEK_MONDAY_FIRST (0) If not set Sunday is first day of week @@ -200,9 +191,16 @@ str_to_datetime_with_warn(const char *str, uint length, TIME *l_time, uint flags) { int was_cut; - timestamp_type ts_type= str_to_datetime(str, length, l_time, flags, &was_cut); + THD *thd= current_thd; + timestamp_type ts_type; + + ts_type= str_to_datetime(str, length, l_time, + (flags | (thd->variables.sql_mode & + (MODE_INVALID_DATES | + MODE_NO_ZERO_DATE))), + &was_cut); if (was_cut) - make_truncated_value_warning(current_thd, str, length, ts_type); + make_truncated_value_warning(current_thd, str, length, ts_type, NullS); return ts_type; } @@ -258,7 +256,8 @@ str_to_time_with_warn(const char *str, uint length, TIME *l_time) int was_cut; bool ret_val= str_to_time(str, length, l_time, &was_cut); if (was_cut) - make_truncated_value_warning(current_thd, str, length, MYSQL_TIMESTAMP_TIME); + make_truncated_value_warning(current_thd, str, length, + MYSQL_TIMESTAMP_TIME, NullS); return ret_val; } @@ -791,16 +790,15 @@ void make_datetime(const DATE_TIME_FORMAT *format __attribute__((unused)), void make_truncated_value_warning(THD *thd, const char *str_val, - uint str_length, timestamp_type time_type) + uint str_length, timestamp_type time_type, + const char *field_name) { char warn_buff[MYSQL_ERRMSG_SIZE]; const char *type_str; - + CHARSET_INFO *cs= &my_charset_latin1; char buff[128]; String str(buff,(uint32) sizeof(buff), system_charset_info); - str.length(0); - str.append(str_val, str_length); - str.append('\0'); + str.copy(str_val, str_length, system_charset_info); switch (time_type) { case MYSQL_TIMESTAMP_DATE: @@ -814,8 +812,15 @@ void make_truncated_value_warning(THD *thd, const char *str_val, type_str= "datetime"; break; } - sprintf(warn_buff, ER(ER_TRUNCATED_WRONG_VALUE), - type_str, str.ptr()); + if (field_name) + cs->cset->snprintf(cs, warn_buff, sizeof(warn_buff), + ER(ER_TRUNCATED_WRONG_VALUE_FOR_FIELD), + type_str, str.c_ptr(), field_name, + (ulong) thd->row_count); + else + cs->cset->snprintf(cs, warn_buff, sizeof(warn_buff), + ER(ER_TRUNCATED_WRONG_VALUE), + type_str, str.ptr()); push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_TRUNCATED_WRONG_VALUE, warn_buff); } |