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

17 files changed, 852 insertions, 164 deletions

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: