diff options
Diffstat (limited to 'mysql-test')
27 files changed, 514 insertions, 200 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index e85ad303564..1c729cd20b5 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -463,7 +463,7 @@ ALTER TABLE t1 DROP PRIMARY KEY; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` int(11) default NULL, UNIQUE KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 9dad18e03e7..233cd255211 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -232,7 +232,7 @@ a b delete from t1 where a=0; update t1 set a=NULL where b=6; Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 4 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 4 update t1 set a=300 where b=7; SET SQL_MODE=''; insert into t1(a,b)values(NULL,8); @@ -274,7 +274,7 @@ a b delete from t1 where a=0; update t1 set a=NULL where b=13; Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 9 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 9 update t1 set a=500 where b=14; select * from t1 order by b; a b diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 8131399872a..596e8a4c4da 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -11,7 +11,7 @@ create table t1 (b char(0) not null); create table if not exists t1 (b char(0) not null); insert into t1 values (""),(null); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'b' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'b' at row 2 select * from t1; b @@ -152,7 +152,7 @@ create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`), @@ -203,7 +203,7 @@ CREATE TABLE t1 (a int not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0' + `a` int(11) NOT NULL ) ENGINE=HEAP DEFAULT CHARSET=latin1 drop table t1; SET SESSION storage_engine="gemini"; @@ -215,7 +215,7 @@ CREATE TABLE t1 (a int not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0' + `a` int(11) NOT NULL ) ENGINE=HEAP DEFAULT CHARSET=latin1 SET SESSION storage_engine=default; drop table t1; @@ -291,7 +291,7 @@ create table t3 like t1; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, `name` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t3; @@ -306,7 +306,7 @@ create temporary table t3 like t2; show create table t3; Table Create Table t3 CREATE TEMPORARY TABLE `t3` ( - `id` int(11) NOT NULL default '0' + `id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t3; id @@ -314,7 +314,7 @@ drop table t3; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, `name` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t3; @@ -326,14 +326,14 @@ create temporary table t3 like mysqltest.t3; show create table t3; Table Create Table t3 CREATE TEMPORARY TABLE `t3` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, `name` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 create table t2 like t3; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, `name` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t2; @@ -360,7 +360,7 @@ CREATE TABLE t1 (a int not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0' + `a` int(11) NOT NULL ) ENGINE=HEAP DEFAULT CHARSET=latin1 drop table t1; SET SESSION storage_engine="gemini"; @@ -372,7 +372,7 @@ CREATE TABLE t1 (a int not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0' + `a` int(11) NOT NULL ) ENGINE=HEAP DEFAULT CHARSET=latin1 SET SESSION storage_engine=default; drop table t1; diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index d4a8beda185..aebf8b4637b 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -484,7 +484,7 @@ ERROR HY000: Unknown collation: 'koi8r' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `latin1_f` char(32) NOT NULL default '' + `latin1_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SHOW FIELDS FROM t1; Field Type Null Key Default Extra diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index c500019042f..e80ba5f30bf 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -224,8 +224,8 @@ create table t1 (word varchar(255) not null, word2 varchar(255) not null, index( show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `word` varchar(255) collate latin1_german2_ci NOT NULL default '', - `word2` varchar(255) collate latin1_german2_ci NOT NULL default '', + `word` varchar(255) collate latin1_german2_ci NOT NULL, + `word2` varchar(255) collate latin1_german2_ci NOT NULL, KEY `word` (`word`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result index 8bfc6e98226..5932bd75787 100644 --- a/mysql-test/r/ctype_many.result +++ b/mysql-test/r/ctype_many.result @@ -7,40 +7,40 @@ koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `comment` char(32) character set latin1 NOT NULL default '', - `koi8_ru_f` char(32) character set koi8r NOT NULL default '' + `comment` char(32) character set latin1 NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin5 ALTER TABLE t1 CHANGE comment comment CHAR(32) CHARACTER SET latin2 NOT NULL; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `comment` char(32) character set latin2 NOT NULL default '', - `koi8_ru_f` char(32) character set koi8r NOT NULL default '' + `comment` char(32) character set latin2 NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin5 ALTER TABLE t1 ADD latin5_f CHAR(32) NOT NULL; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `comment` char(32) character set latin2 NOT NULL default '', - `koi8_ru_f` char(32) character set koi8r NOT NULL default '', - `latin5_f` char(32) NOT NULL default '' + `comment` char(32) character set latin2 NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL, + `latin5_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin5 ALTER TABLE t1 DEFAULT CHARSET=latin2; ALTER TABLE t1 ADD latin2_f CHAR(32) NOT NULL; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `comment` char(32) NOT NULL default '', - `koi8_ru_f` char(32) character set koi8r NOT NULL default '', - `latin5_f` char(32) character set latin5 NOT NULL default '', - `latin2_f` char(32) NOT NULL default '' + `comment` char(32) NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL, + `latin5_f` char(32) character set latin5 NOT NULL, + `latin2_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin2 ALTER TABLE t1 DROP latin2_f, DROP latin5_f; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `comment` char(32) NOT NULL default '', - `koi8_ru_f` char(32) character set koi8r NOT NULL default '' + `comment` char(32) NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin2 INSERT INTO t1 (koi8_ru_f,comment) VALUES ('a','LAT SMALL A'); INSERT INTO t1 (koi8_ru_f,comment) VALUES ('b','LAT SMALL B'); diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result index 1ef185388a8..4605a7ab257 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -50,7 +50,7 @@ Tables_in_test table_type SHOW CREATE TABLE ÔÁÂÌÉÃÁ; Table Create Table ÔÁÂÌÉÃÁ CREATE TABLE `ÔÁÂÌÉÃÁ` ( - `ÐÏÌÅ` char(32) character set koi8r NOT NULL default '' COMMENT 'ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ' + `ÐÏÌÅ` char(32) character set koi8r NOT NULL COMMENT 'ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ' SHOW FIELDS FROM ÔÁÂÌÉÃÁ; Field Type Null Key Default Extra @@ -62,7 +62,7 @@ Tables_in_test table_type SHOW CREATE TABLE òàáëèöà; Table Create Table òàáëèöà CREATE TABLE `òàáëèöà` ( - `ïîëå` char(32) character set koi8r NOT NULL default '' COMMENT 'êîììåíòàðèé ïîëÿ' + `ïîëå` char(32) character set koi8r NOT NULL COMMENT 'êîììåíòàðèé ïîëÿ' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='êîììåíòàðèé òàáëèöû' SHOW FIELDS FROM òàáëèöà; Field Type Null Key Default Extra @@ -74,7 +74,7 @@ Tables_in_test table_type SHOW CREATE TABLE таблица; Table Create Table таблица CREATE TABLE `таблица` ( - `поле` char(32) character set koi8r NOT NULL default '' COMMENT 'комментарий полÑ' + `поле` char(32) character set koi8r NOT NULL COMMENT 'комментарий полÑ' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='комментарий таблицы' SHOW FIELDS FROM таблица; Field Type Null Key Default Extra diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 3fb1a5dd31c..175fb50786e 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -8,7 +8,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `fid` int(11) NOT NULL auto_increment, - `g` geometry NOT NULL default '', + `g` geometry NOT NULL, PRIMARY KEY (`fid`), SPATIAL KEY `g` (`g`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -291,7 +291,7 @@ SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `fid` int(11) NOT NULL auto_increment, - `g` geometry NOT NULL default '', + `g` geometry NOT NULL, PRIMARY KEY (`fid`), SPATIAL KEY `g` (`g`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 864d9d04ff5..b09b232b167 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -9,35 +9,35 @@ CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMET CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY); SHOW FIELDS FROM gis_point; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g point YES NULL SHOW FIELDS FROM gis_line; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g linestring YES NULL SHOW FIELDS FROM gis_polygon; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g polygon YES NULL SHOW FIELDS FROM gis_multi_point; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g multipoint YES NULL SHOW FIELDS FROM gis_multi_line; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g multilinestring YES NULL SHOW FIELDS FROM gis_multi_polygon; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g multipolygon YES NULL SHOW FIELDS FROM gis_geometrycollection; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g geometrycollection YES NULL SHOW FIELDS FROM gis_geometry; Field Type Null Key Default Extra -fid int(11) PRI 0 +fid int(11) PRI g geometry YES NULL INSERT INTO gis_point VALUES (101, PointFromText('POINT(10 10)')), @@ -430,7 +430,7 @@ mln multilinestring YES NULL mpg multipolygon YES NULL gc geometrycollection YES NULL gm geometry YES NULL -fid int(11) 0 +fid int(11) DROP TABLE t1; SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))) diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index b46626d0322..bb18fe9eef0 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1460,14 +1460,14 @@ create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) ref show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL default '0', - `id2` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, + `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, KEY `t1_id_fk` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1475,7 +1475,7 @@ create index id on t2 (id); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, KEY `id` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1483,7 +1483,7 @@ create index id2 on t2 (id); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, KEY `id` (`id`), KEY `id2` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) @@ -1494,7 +1494,7 @@ Got one of the listed errors show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, KEY `id` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1503,8 +1503,8 @@ create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', - `id2` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, + `id2` int(11) NOT NULL, KEY `t1_id_fk` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1512,8 +1512,8 @@ create unique index id on t2 (id,id2); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', - `id2` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, + `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1522,8 +1522,8 @@ create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),cons show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', - `id2` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, + `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), KEY `t1_id_fk` (`id2`,`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`) @@ -1533,8 +1533,8 @@ create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), con show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', - `id2` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, + `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1543,8 +1543,8 @@ create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),cons show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL default '0', - `id2` int(11) NOT NULL default '0', + `id` int(11) NOT NULL, + `id2` int(11) NOT NULL, UNIQUE KEY `id` (`id`,`id2`), KEY `t1_id_fk` (`id2`,`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`) @@ -1555,7 +1555,7 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL auto_increment, - `id2` int(11) NOT NULL default '0', + `id2` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) @@ -1566,7 +1566,7 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL auto_increment, - `id2` int(11) NOT NULL default '0', + `id2` int(11) NOT NULL, KEY `t1_id_fk` (`id`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -1575,7 +1575,7 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL auto_increment, - `id2` int(11) NOT NULL default '0', + `id2` int(11) NOT NULL, KEY `id_test` (`id`), KEY `id_test2` (`id`,`id2`), CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 474820488a8..47a9819f440 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -63,7 +63,7 @@ insert into t1 values(NULL); ERROR 23000: Column 'id' cannot be null insert into t1 values (1), (NULL), (2); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'id' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'id' at row 2 select * from t1; id 1 @@ -157,19 +157,19 @@ f_float_3_1_u 0.0 set @value= "1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); 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 Out of range value adjusted for column 'f_double' 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_float' at row 1 -Warning 1265 Data truncated 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 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 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 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_float_u' at row 1 -Warning 1265 Data truncated 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 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 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 @@ -185,20 +185,20 @@ f_float_3_1_u 99.9 set @value= "-1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); 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 Out of range value adjusted for column 'f_double' 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_float' at row 1 -Warning 1265 Data truncated 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 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_4_3' at row 1 -Warning 1265 Data truncated 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 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_float_u' at row 1 -Warning 1265 Data truncated 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 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 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 @@ -212,46 +212,9 @@ f_float_u 0 f_double_15_1_u 0.0 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 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 -f_double 1.79769313486232e+308 -f_float 3.40282e+38 -f_double_7_2 99999.99 -f_float_4_3 9.999 -f_double_u 1.79769313486232e+308 -f_float_u 3.40282e+38 -f_double_15_1_u 99999999999999.9 -f_float_3_1_u 99.9 +ERROR 22007: Illegal double '1e+1111111111' value found during parsing set @value= -1e+1111111111; -insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); -Warnings: -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 -f_double -1.79769313486232e+308 -f_float -3.40282e+38 -f_double_7_2 -99999.99 -f_float_4_3 -9.999 -f_double_u 0 -f_float_u 0 -f_double_15_1_u 0.0 -f_float_3_1_u 0.0 +ERROR 22007: Illegal double '1e+1111111111' value found during parsing set @value= 1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: @@ -262,7 +225,7 @@ 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 +number 6 original_value 1e+111 f_double 1e+111 f_float 3.40282e+38 @@ -283,7 +246,7 @@ 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 +number 7 original_value -1e+111 f_double -1e+111 f_float -3.40282e+38 @@ -296,7 +259,7 @@ f_float_3_1_u 0.0 set @value= 1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); select * from t1 where number =last_insert_id(); -number 10 +number 8 original_value 1 f_double 1 f_float 1 @@ -314,7 +277,7 @@ 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 +number 9 original_value -1 f_double -1 f_float -1 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 83e47639fe3..323302128da 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -622,8 +622,8 @@ NULL 2 100 create table t2(No int not null, Field int not null, Count int not null); insert into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2; Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'No' at row 1 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'No' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'No' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'No' at row 2 select * from t2; No Field Count 0 1 100 diff --git a/mysql-test/r/isam.result b/mysql-test/r/isam.result index 52eb2d73ed5..cb308a1c71d 100644 --- a/mysql-test/r/isam.result +++ b/mysql-test/r/isam.result @@ -65,14 +65,14 @@ test.t2 check error Table 't2' was not locked with LOCK TABLES test.t1 check status OK show columns from t1; Field Type Null Key Default Extra -a int(11) PRI 0 -b int(11) MUL 0 -c int(11) 0 +a int(11) PRI +b int(11) MUL +c int(11) show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment -a int(11) NULL PRI 0 select,insert,update,references -b int(11) NULL MUL 0 select,insert,update,references -c int(11) NULL 0 select,insert,update,references +a int(11) NULL PRI select,insert,update,references +b int(11) NULL MUL select,insert,update,references +c int(11) NULL select,insert,update,references show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 4 NULL NULL BTREE diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 98ff5fad7b2..cc3213bea19 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -159,8 +159,8 @@ CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, UNIQUE (c,i)); INSERT INTO t1 (c) VALUES (NULL),(NULL); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'c' at row 1 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'c' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'c' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'c' at row 2 SELECT * FROM t1; c i 1 diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 7334d5acf4d..b115b926f88 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -172,7 +172,7 @@ a b show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` char(20) default NULL, KEY `a` (`a`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) @@ -187,7 +187,7 @@ create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1 show create table t5; Table Create Table t5 CREATE TABLE `t5` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` char(20) default NULL, KEY `a` (`a`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`) @@ -260,16 +260,16 @@ ENGINE=MERGE UNION=(t1,t2); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `incr` int(11) NOT NULL default '0', - `othr` int(11) NOT NULL default '0', + `incr` int(11) NOT NULL, + `othr` int(11) NOT NULL, PRIMARY KEY (`incr`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) alter table t3 drop primary key; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `incr` int(11) NOT NULL default '0', - `othr` int(11) NOT NULL default '0' + `incr` int(11) NOT NULL, + `othr` int(11) NOT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) drop table t3,t2,t1; create table t1 (a int not null, key(a)) engine=merge; @@ -296,28 +296,28 @@ create table t6 (a int not null, b int not null auto_increment, primary key(a,b) show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t4; Table Create Table t4 CREATE TABLE `t4` ( - `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) @@ -382,8 +382,8 @@ alter table t4 UNION=(t1,t2,t3); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( - `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`,`t3`) select * from t4 order by a,b; @@ -408,8 +408,8 @@ alter table t4 INSERT_METHOD=FIRST; show create table t4; Table Create Table t4 CREATE TABLE `t4` ( - `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`) insert into t4 values (4,1),(4,2); diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index ced80a033e7..f39bd0c6f6c 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -97,39 +97,39 @@ Warnings: Warning 1265 Data truncated for column 'd' at row 1 UPDATE t1 SET d=NULL; Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'd' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'd' at row 1 INSERT INTO t1 (a) values (null); ERROR 23000: Column 'a' cannot be null INSERT INTO t1 (a) values (1/null); ERROR 23000: Column 'a' cannot be null INSERT INTO t1 (a) values (null),(null); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 1 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 2 INSERT INTO t1 (b) values (null); ERROR 23000: Column 'b' cannot be null INSERT INTO t1 (b) values (1/null); ERROR 23000: Column 'b' cannot be null INSERT INTO t1 (b) values (null),(null); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'b' at row 1 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'b' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'b' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'b' at row 2 INSERT INTO t1 (c) values (null); ERROR 23000: Column 'c' cannot be null INSERT INTO t1 (c) values (1/null); ERROR 23000: Column 'c' cannot be null INSERT INTO t1 (c) values (null),(null); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'c' at row 1 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'c' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'c' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'c' at row 2 INSERT INTO t1 (d) values (null); ERROR 23000: Column 'd' cannot be null INSERT INTO t1 (d) values (1/null); ERROR 23000: Column 'd' cannot be null INSERT INTO t1 (d) values (null),(null); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'd' at row 1 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'd' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'd' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'd' at row 2 select * from t1; a b c d 0 0000-00-00 00:00:00 0 diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 7980c3f7f91..b9824cc4b81 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -342,7 +342,7 @@ index (id2) ); insert into t1 values(null,null),(1,1); Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'id2' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'id2' at row 1 select * from t1; id id2 NULL 0 diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index dc67fda7efa..f4939592363 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -257,7 +257,7 @@ t2 BASE TABLE prepare stmt4 from ' show columns from t2 from test like ''a%'' '; execute stmt4; Field Type Null Key Default Extra -a int(11) PRI 0 +a int(11) PRI create index t2_idx on t2(b); prepare stmt4 from ' show index from t2 from test '; execute stmt4; @@ -355,7 +355,7 @@ drop user drop_user@localhost; prepare stmt3 from ' describe t2 '; execute stmt3; Field Type Null Key Default Extra -a int(11) PRI 0 +a int(11) PRI b char(10) YES MUL NULL drop table t2 ; execute stmt3; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 8c8557cc167..41eae1700b1 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3,t4; +drop view if exists v1; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index ecf91fe6f68..b2da4e6a481 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -77,13 +77,13 @@ create temporary table t1 (a int not null); show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( - `a` int(11) NOT NULL default '0' + `a` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 rename t2; show create table t2; Table Create Table t2 CREATE TEMPORARY TABLE `t2` ( - `a` int(11) NOT NULL default '0' + `a` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t2; create table t1 ( @@ -98,7 +98,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default', - `c` int(11) NOT NULL default '0' COMMENT 'int column', + `c` int(11) NOT NULL COMMENT 'int column', `c-b` int(11) default NULL COMMENT 'name with a minus', `space 2` int(11) default NULL COMMENT 'name with a space' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' @@ -108,7 +108,7 @@ Table Create Table t1 CREATE TABLE t1 ( test_set set('val1','val2','val3') NOT NULL default '', `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default', - c int(11) NOT NULL default '0' COMMENT 'int column', + c int(11) NOT NULL COMMENT 'int column', `c-b` int(11) default NULL COMMENT 'name with a minus', `space 2` int(11) default NULL COMMENT 'name with a space' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' @@ -117,7 +117,7 @@ show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment test_set set('val1','val2','val3') latin1_swedish_ci select,insert,update,references name char(20) latin1_swedish_ci YES O'Brien select,insert,update,references O'Brien as default -c int(11) NULL 0 select,insert,update,references int column +c int(11) NULL select,insert,update,references int column c-b int(11) NULL YES NULL select,insert,update,references name with a minus space 2 int(11) NULL YES NULL select,insert,update,references name with a space drop table t1; @@ -125,7 +125,7 @@ create table t1 (a int not null, unique aa (a)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, UNIQUE KEY `aa` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; @@ -133,7 +133,7 @@ create table t1 (a int not null, primary key (a)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; @@ -150,7 +150,7 @@ create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CH show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` char(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' @@ -158,7 +158,7 @@ alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` varchar(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' @@ -166,7 +166,7 @@ ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL default '0', + `a` int(11) NOT NULL, `b` varchar(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -216,7 +216,7 @@ index(type_short) show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `type_bool` tinyint(1) NOT NULL default '0', + `type_bool` tinyint(1) NOT NULL, `type_tiny` tinyint(4) NOT NULL auto_increment, `type_short` smallint(3) default NULL, `type_mediumint` mediumint(9) default NULL, @@ -227,9 +227,9 @@ t1 CREATE TABLE `t1` ( `type_char` char(2) default NULL, `type_varchar` varchar(10) default NULL, `type_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `type_date` date NOT NULL default '0000-00-00', - `type_time` time NOT NULL default '00:00:00', - `type_datetime` datetime NOT NULL default '0000-00-00 00:00:00', + `type_date` date NOT NULL, + `type_time` time NOT NULL, + `type_datetime` datetime NOT NULL, `type_year` year(4) default NULL, `type_enum` enum('red','green','blue') default NULL, `type_set` enum('red','green','blue') default NULL, diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index eedc692bb85..dee2ed24474 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1620,8 +1620,8 @@ tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer Table Create Table t1 CREATE TABLE `t1` ( - `id` char(16) NOT NULL default '', - `data` int(11) NOT NULL default '0' + `id` char(16) NOT NULL, + `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Database Create Database test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ @@ -1629,7 +1629,7 @@ Database (foo) Level Code Message Field Type Null Key Default Extra id char(16) -data int(11) 0 +data int(11) Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment @@ -1670,8 +1670,8 @@ tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer Table Create Table t1 CREATE TABLE `t1` ( - `id` char(16) NOT NULL default '', - `data` int(11) NOT NULL default '0' + `id` char(16) NOT NULL, + `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Database Create Database test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ @@ -1679,7 +1679,7 @@ Database (foo) Level Code Message Field Type Null Key Default Extra id char(16) -data int(11) 0 +data int(11) Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 9b1e15e3eaa..3b0c1d4a4ef 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -60,7 +60,7 @@ col1 0000-00-00 0000-00-00 drop table t1; -set @@sql_mode="strict_trans_tables"; +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 @@ -87,7 +87,7 @@ col1 0000-00-00 2003-02-31 drop table t1; -set @@sql_mode="strict_trans_tables"; +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 @@ -676,3 +676,207 @@ NULL 999.99 -99.99 DROP TABLE t1; +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'); +INSERT INTO t1 (col1) VALUES (3E-46); +INSERT INTO t1 (col1) VALUES (+3.4E+39); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES (-1.1E-3); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES ('+3.4E+39'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES ('-1.1E-3'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; +ERROR 22003: Out of range value adjusted for column 'col1' at row 2 +UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 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 (col1) VALUES (1/0); +Warnings: +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39); +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 +INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39'); +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 +SELECT * FROM t1; +col1 col2 +-1.1e-38 0 +3.4e+38 3.4e+38 +-1.1e-38 0 +3.4e+38 3.4e+38 +0 NULL +2 NULL +NULL NULL +3.40282e+38 0 +3.40282e+38 0 +DROP TABLE t1; +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'); +INSERT INTO t1 (col1) VALUES (-2.2E-330); +INSERT INTO t1 (col1) VALUES (+1.7E+309); +ERROR 22007: Illegal double '1.7E+309' value found during parsing +INSERT INTO t1 (col2) VALUES (-1.1E-3); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +INSERT INTO t1 (col1) VALUES ('+1.8E+309'); +ERROR 22003: Out of range value adjusted for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES ('-1.2E-3'); +ERROR 22003: Out of range value adjusted for column 'col2' at row 1 +UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; +ERROR 22003: Out of range value adjusted for column 'col1' at row 2 +UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 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 (col1) values (1/0); +Warnings: +Error 1365 Division by 0 +INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309); +ERROR 22007: Illegal double '1.9E+309' value found during parsing +INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309'); +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 'col2' at row 1 +SELECT * FROM t1; +col1 col2 +-2.2e-308 0 +1.7e+308 1.7e+308 +-2.2e-308 0 +1.7e+308 1.7e+308 +-0 NULL +2 NULL +NULL NULL +1.79769313486232e+308 0 +DROP TABLE t1; +CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(5)); +INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); +INSERT INTO t1 (col1) VALUES ('hellobob'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES ('hellobob'); +ERROR 01000: Data truncated for column 'col2' at row 1 +UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he'; +ERROR 01000: Data truncated for column 'col1' at row 2 +UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he'; +ERROR 01000: Data truncated for column 'col2' at row 2 +INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +Warning 1265 Data truncated for column 'col2' at row 1 +UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he'; +Warnings: +Warning 1265 Data truncated for column 'col2' at row 2 +SELECT * FROM t1; +col1 col2 +hello hello +he hello +hello hello +hello hello +DROP TABLE t1; +CREATE TABLE t1 (col1 enum('red','blue','green')); +INSERT INTO t1 VALUES ('red'),('blue'),('green'); +INSERT INTO t1 (col1) VALUES ('yellow'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES ('redd'); +ERROR 01000: Data truncated for column 'col1' at row 1 +INSERT INTO t1 VALUES (''); +ERROR 01000: Data truncated for column 'col1' at row 1 +UPDATE t1 SET col1 ='yellow' WHERE col1 ='green'; +ERROR 01000: Data truncated for column 'col1' at row 3 +INSERT IGNORE INTO t1 VALUES ('yellow'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue'; +Warnings: +Warning 1265 Data truncated for column 'col1' at row 2 +SELECT * FROM t1; +col1 +red + +green + +DROP TABLE t1; +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'); +INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01'); +ERROR 23000: Column 'col1' cannot be null +INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01'); +ERROR 23000: Column 'col2' cannot be null +INSERT INTO t1 VALUES (103,'',NULL); +ERROR 23000: Column 'col3' cannot be null +UPDATE t1 SET col1=NULL WHERE col1 =100; +ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'col1' at row 1 +UPDATE t1 SET col2 =NULL WHERE col2 ='hello'; +ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'col2' at row 1 +UPDATE t1 SET col2 =NULL where col3 IS NOT NULL; +ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'col2' at row 1 +INSERT IGNORE INTO t1 values (NULL,NULL,NULL); +Warnings: +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'col1' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'col2' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'col3' at row 1 +SELECT * FROM t1; +col1 col2 col3 +100 hello 2004-08-20 +101 hell2 2004-08-21 +0 0000-00-00 +DROP TABLE t1; +CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "col1" int(11) NOT NULL default '99', + "col2" char(6) NOT NULL +) +INSERT INTO t1 VALUES (1, 'hello'); +INSERT INTO t1 (col2) VALUES ('hello2'); +INSERT INTO t1 (col2) VALUES (NULL); +ERROR 23000: Column 'col2' cannot be null +INSERT INTO t1 (col1) VALUES (2); +ERROR HY000: Field 'col2' doesn't have a default value +INSERT INTO t1 VALUES(default(col1),default(col2)); +ERROR HY000: Field 'col2' doesn't have a default value +INSERT INTO t1 (col1) SELECT 1; +ERROR HY000: Field 'col2' doesn't have a default value +INSERT INTO t1 SELECT 1,NULL; +ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'col2' at row 1 +INSERT IGNORE INTO t1 values (NULL,NULL); +Warnings: +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'col1' at row 1 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'col2' at row 1 +INSERT IGNORE INTO t1 (col1) values (3); +INSERT IGNORE INTO t1 () values (); +SELECT * FROM t1; +col1 col2 +1 hello +99 hello2 +0 +3 +99 +DROP TABLE t1; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 084e004cd47..de79de1ffe9 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -3,7 +3,7 @@ create table t1 (a set (' ','a','b') not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` set('','a','b') NOT NULL default '' + `a` set('','a','b') NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a set (' ','a','b ') not null default 'b '); diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 55931452c82..a2e99be62ad 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -43,7 +43,7 @@ drop table t1; create table t1(a tinyint, b int not null, c date, d char(5)); load data infile '../../std_data/warnings_loaddata.dat' into table t1 fields terminated by ','; Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'b' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 3 Warning 1265 Data truncated for column 'c' at row 4 Warning 1261 Row 5 doesn't contain data for all columns @@ -57,7 +57,7 @@ drop table t1; 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 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 1 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 Out of range value adjusted for column 'a' at row 3 @@ -70,7 +70,7 @@ Warning 1265 Data truncated for column 'c' at row 2 alter table t1 add d char(2); update t1 set a=NULL where a=10; Warnings: -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 2 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 2 update t1 set c='mysql ab' where c='test'; Warnings: Warning 1265 Data truncated for column 'c' at row 4 @@ -86,7 +86,7 @@ Warnings: Warning 1265 Data truncated for column 'b' at row 1 Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'b' at row 3 -Warning 1263 Data truncated; NULL supplied to NOT NULL column 'a' at row 4 +Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' at row 4 Warning 1265 Data truncated for column 'b' at row 4 insert into t2(b) values('mysqlab'); Warnings: diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index dc47b3ba990..c01be1b5992 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -115,13 +115,10 @@ set @value= "-1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() +--error 1367 set @value= 1e+1111111111; -insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); ---query_vertical select * from t1 where number =last_insert_id() - +--error 1367 set @value= -1e+1111111111; -insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); ---query_vertical select * from t1 where number =last_insert_id() set @value= 1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 57827f3cc7f..a1a97a17fd5 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -8,6 +8,7 @@ --disable_warnings drop table if exists t1,t2,t3,t4; +drop view if exists v1; --enable_warnings CREATE TABLE t1 ( diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index c42fd767117..e187ee32403 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -58,7 +58,7 @@ drop table t1; # Test difference in behaviour with InnoDB and MyISAM tables -set @@sql_mode="strict_trans_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'); @@ -72,7 +72,7 @@ INSERT ignore INTO t1 VALUES('2003-02-31'); select * from t1; drop table t1; -set @@sql_mode="strict_trans_tables"; +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'); @@ -161,7 +161,7 @@ set @@sql_mode='ansi,traditional'; SELECT * FROM t1; DROP TABLE t1; -# Test TINYINT +# 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); @@ -211,7 +211,7 @@ UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; DROP TABLE t1; -# Test SMALLINT +# 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); @@ -264,7 +264,7 @@ UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; DROP TABLE t1; -# Test MEDIUMINT +# 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); @@ -317,7 +317,7 @@ UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; DROP TABLE t1; -# Test INT +# 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); @@ -369,7 +369,7 @@ UPDATE IGNORE t1 SET col2=1/NULL where col1=0; SELECT * FROM t1; DROP TABLE t1; -# Test BIGINT +# 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) @@ -427,6 +427,8 @@ 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 ! @@ -474,3 +476,149 @@ 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 +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 (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 +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 (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'); +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; |