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

1 files changed, 476 insertions, 0 deletions

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;