diff options
Diffstat (limited to 'mysql-test/t/strict.test')
| -rw-r--r-- | mysql-test/t/strict.test | 639 |
1 files changed, 639 insertions, 0 deletions
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test new file mode 100644 index 00000000000..d9f4f4f2d0c --- /dev/null +++ b/mysql-test/t/strict.test @@ -0,0 +1,639 @@ +# 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-30'); +--error 1292 +INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05'); +INSERT INTO t1 VALUES('0000-00-00'); +INSERT IGNORE INTO t1 VALUES('2004-0-29'); +set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE'; +--error 1292 +INSERT INTO t1 VALUES('0000-00-00'); +INSERT IGNORE 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 INSERT with 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); +# Test that we restored the mode checking properly after an ok query +SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; +-- 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); +SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1; +--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; +set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; +INSERT INTO t1 values (1/0,1/0); +set @@sql_mode='ansi,traditional'; +SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2; +# 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); +set @@sql_mode='ansi'; +INSERT INTO t1 values (1/0,1/0); +set @@sql_mode='ansi,traditional'; +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 INSERT with 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 +UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; +--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 INSERT with 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 +UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; +--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 INSERT with 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 +UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; +--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 INSERT with 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'); + +# Note that the following two double numbers are slighty bigger than max/min +# bigint becasue of rounding errors when converting it to bigint +--error 1264 +INSERT INTO t1 (col1) VALUES(-9223372036854785809.0); +--error 1264 +INSERT INTO t1 (col1) VALUES(9223372036854785808.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 +UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; +--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(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0); +UPDATE IGNORE t1 SET col2=1/NULL where col1=0; +SELECT * FROM t1; +DROP TABLE t1; + +# Test INSERT with NUMERIC + +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 +UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0; +--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; + +# Test INSERT with FLOAT + +CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED); +INSERT INTO t1 VALUES (-1.1E-38,0),(+3.4E+38,+3.4E+38); +INSERT INTO t1 VALUES ('-1.1E-38',0),('+3.4E+38','+3.4E+38'); +# We don't give warnings for underflow +INSERT INTO t1 (col1) VALUES (3E-46); +--error 1264 +INSERT INTO t1 (col1) VALUES (+3.4E+39); +--error 1264 +INSERT INTO t1 (col2) VALUES (-1.1E-3); +--error 1264 +INSERT INTO t1 (col1) VALUES ('+3.4E+39'); +--error 1264 +INSERT INTO t1 (col2) VALUES ('-1.1E-3'); +--error 1264 +UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; +--error 1365 +UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; +--error 1365 +UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; +--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 (col1) VALUES (1/0); +INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39); +INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39'); +SELECT * FROM t1; +DROP TABLE t1; + +# Test INSERT with DOUBLE + +CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED); +INSERT INTO t1 VALUES (-2.2E-308,0),(+1.7E+308,+1.7E+308); +INSERT INTO t1 VALUES ('-2.2E-308',0),('+1.7E+308','+1.7E+308'); +# We don't give warnings for underflow +INSERT INTO t1 (col1) VALUES (-2.2E-330); +--error 1367 +INSERT INTO t1 (col1) VALUES (+1.7E+309); +--error 1264 +INSERT INTO t1 (col2) VALUES (-1.1E-3); +--error 1264 +INSERT INTO t1 (col1) VALUES ('+1.8E+309'); +--error 1264 +INSERT INTO t1 (col2) VALUES ('-1.2E-3'); +--error 1264 +UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; +--error 1365 +UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; +--error 1365 +UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; +--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 (col1) values (1/0); +--error 1367 +INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309); +INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309'); +# stupid... +--replace_result -0 0 +SELECT * FROM t1; +DROP TABLE t1; + +# Testing INSERT with CHAR/VARCHAR + +CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(5)); +INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); +--error 1265 +INSERT INTO t1 (col1) VALUES ('hellobob'); +--error 1265 +INSERT INTO t1 (col2) VALUES ('hellobob'); +--error 1265 +UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; +--error 1265 +UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; +INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); +UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; +SELECT * FROM t1; +DROP TABLE t1; + +# Testing INSERT with ENUM + +CREATE TABLE t1 (col1 enum('red','blue','green')); +INSERT INTO t1 VALUES ('red'),('blue'),('green'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('yellow'); +--error 1265 +INSERT INTO t1 (col1) VALUES ('redd'); +--error 1265 +INSERT INTO t1 VALUES (''); +--error 1265 +UPDATE t1 SET col1 ='yellow' WHERE col1 ='green'; +INSERT IGNORE INTO t1 VALUES ('yellow'); +UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue'; +SELECT * FROM t1; +DROP TABLE t1; + +# Testing of insert of NULL in not NULL column + +CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL); +INSERT INTO t1 VALUES (100, 'hello', '2004-08-20'); +INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21'); +--error 1048 +INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01'); +--error 1048 +INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01'); +--error 1048 +INSERT INTO t1 VALUES (103,'',NULL); +--error 1263 +UPDATE t1 SET col1=NULL WHERE col1 =100; +--error 1263 +UPDATE t1 SET col2 =NULL WHERE col2 ='hello'; +--error 1263 +UPDATE t1 SET col2 =NULL where col3 IS NOT NULL; +INSERT IGNORE INTO t1 values (NULL,NULL,NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# Testing of default values + +CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1, 'hello'); +INSERT INTO t1 (col2) VALUES ('hello2'); +--error 1048 +INSERT INTO t1 (col2) VALUES (NULL); +--error 1364 +INSERT INTO t1 (col1) VALUES (2); +--error 1364 +INSERT INTO t1 VALUES(default(col1),default(col2)); +--error 1364 +INSERT INTO t1 (col1) SELECT 1; +--error 1263 +INSERT INTO t1 SELECT 1,NULL; +INSERT IGNORE INTO t1 values (NULL,NULL); +INSERT IGNORE INTO t1 (col1) values (3); +INSERT IGNORE INTO t1 () values (); +SELECT * FROM t1; +DROP TABLE t1; |