diff options
65 files changed, 661 insertions, 258 deletions
diff --git a/include/mysqld_error.h b/include/mysqld_error.h index 36dd4d00ef2..6f0ce13013a 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -383,4 +383,5 @@ #define ER_NO_DEFAULT_FOR_FIELD 1364 #define ER_DIVISION_BY_ZERO 1365 #define ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 1366 -#define ER_ERROR_MESSAGES 367 +#define ER_ILLEGAL_VALUE_FOR_TYPE 1367 +#define ER_ERROR_MESSAGES 368 diff --git a/include/sql_state.h b/include/sql_state.h index 0b31fc07933..bd3e8b9b3ef 100644 --- a/include/sql_state.h +++ b/include/sql_state.h @@ -156,7 +156,7 @@ ER_SPATIAL_CANT_HAVE_NULL, "42000", "", ER_COLLATION_CHARSET_MISMATCH, "42000", "", ER_WARN_TOO_FEW_RECORDS, "01000", "", ER_WARN_TOO_MANY_RECORDS, "01000", "", -ER_WARN_NULL_TO_NOTNULL, "01000", "", +ER_WARN_NULL_TO_NOTNULL, "22004", "", ER_WARN_DATA_OUT_OF_RANGE, "22003", "", ER_WARN_DATA_TRUNCATED, "01000", "", ER_WRONG_NAME_FOR_INDEX, "42000", "", @@ -202,3 +202,4 @@ ER_SP_VARCOND_AFTER_CURSHNDLR, "42000", "", ER_SP_CURSOR_AFTER_HANDLER, "42000", "", ER_SP_CASE_NOT_FOUND, "20000", "", ER_DIVISION_BY_ZERO, "22012", "", +ER_ILLEGAL_VALUE_FOR_TYPE, "22007", "", 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; diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql index 5b335862584..3c01b69f57e 100644 --- a/scripts/mysql_fix_privilege_tables.sql +++ b/scripts/mysql_fix_privilege_tables.sql @@ -15,7 +15,7 @@ ALTER TABLE host type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; ALTER TABLE func type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; ALTER TABLE columns_priv type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; ALTER TABLE tables_priv type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; -ALTER TABLE user change Password Password char(41) binary not null; +ALTER TABLE user change Password Password char(41) binary not null default ''; ALTER TABLE user add File_priv enum('N','Y') NOT NULL; CREATE TABLE IF NOT EXISTS func ( name char(64) binary DEFAULT '' NOT NULL, @@ -115,9 +115,9 @@ UPDATE user SET Show_db_priv= Select_priv, Super_priv=Process_priv, Execute_priv -- for some users. ALTER TABLE user -ADD max_questions int(11) NOT NULL AFTER x509_subject, -ADD max_updates int(11) unsigned NOT NULL AFTER max_questions, -ADD max_connections int(11) unsigned NOT NULL AFTER max_updates; +ADD max_questions int(11) NOT NULL DEFAULT 0 AFTER x509_subject, +ADD max_updates int(11) unsigned NOT NULL DEFAULT 0 AFTER max_questions, +ADD max_connections int(11) unsigned NOT NULL DEFAULT 0 AFTER max_updates; -- diff --git a/sql/field.cc b/sql/field.cc index ada8381653b..1cadf186a4c 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -2290,10 +2290,13 @@ int Field_float::store(const char *from,uint len,CHARSET_INFO *cs) int error; char *end; double nr= my_strntod(cs,(char*) from,len,&end,&error); - if (error || ((uint) (end-from) != len && table->in_use->count_cuted_fields)) + if (error || (!len || (uint) (end-from) != len && + table->in_use->count_cuted_fields)) { + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + (error ? ER_WARN_DATA_OUT_OF_RANGE : ER_WARN_DATA_TRUNCATED), + 1); error= 1; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); } Field_float::store(nr); return error; @@ -2592,10 +2595,13 @@ int Field_double::store(const char *from,uint len,CHARSET_INFO *cs) int error; char *end; double nr= my_strntod(cs,(char*) from, len, &end, &error); - if (error || ((uint) (end-from) != len && table->in_use->count_cuted_fields)) + if (error || (!len || (uint) (end-from) != len && + table->in_use->count_cuted_fields)) { + set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + (error ? ER_WARN_DATA_OUT_OF_RANGE : ER_WARN_DATA_TRUNCATED), + 1); error= 1; - set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_TRUNCATED, 1); } Field_double::store(nr); return error; diff --git a/sql/field.h b/sql/field.h index f953613deb7..725e962ca07 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1272,6 +1272,7 @@ int set_field_to_null_with_conversions(Field *field, bool no_conversions); #define FIELDFLAG_LEFT_FULLSCREEN 8192 #define FIELDFLAG_RIGHT_FULLSCREEN 16384 #define FIELDFLAG_FORMAT_NUMBER 16384 // predit: ###,,## in output +#define FIELDFLAG_NO_DEFAULT 16384 /* sql */ #define FIELDFLAG_SUM ((uint) 32768)// predit: +#fieldflag #define FIELDFLAG_MAYBE_NULL ((uint) 32768)// sql #define FIELDFLAG_PACK_SHIFT 3 @@ -1280,8 +1281,6 @@ int set_field_to_null_with_conversions(Field *field, bool no_conversions); #define FIELDFLAG_NUM_SCREEN_TYPE 0x7F01 #define FIELDFLAG_ALFA_SCREEN_TYPE 0x7800 -#define FIELD_SORT_REVERSE 16384 - #define MTYP_TYPENR(type) (type & 127) /* Remove bits from type */ #define f_is_dec(x) ((x) & FIELDFLAG_DECIMAL) @@ -1299,3 +1298,4 @@ int set_field_to_null_with_conversions(Field *field, bool no_conversions); #define f_is_equ(x) ((x) & (1+2+FIELDFLAG_PACK+31*256)) #define f_settype(x) (((int) x) << FIELDFLAG_PACK_SHIFT) #define f_maybe_null(x) (x & FIELDFLAG_MAYBE_NULL) +#define f_no_default(x) (x & FIELDFLAG_NO_DEFAULT) diff --git a/sql/item.cc b/sql/item.cc index 3953aecaa08..267560b0709 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1862,6 +1862,34 @@ Item_num *Item_uint::neg() return new Item_real(name, - ((double) value), 0, max_length); } + +/* + This function is only called during parsing. We will signal an error if + value is not a true double value (overflow) +*/ + +Item_real::Item_real(const char *str_arg, uint length) +{ + int error; + char *end; + value= my_strntod(&my_charset_bin, (char*) str_arg, length, &end, &error); + if (error) + { + /* + Note that we depend on that str_arg is null terminated, which is true + when we are in the parser + */ + DBUG_ASSERT(str_arg[length] == 0); + my_printf_error(ER_ILLEGAL_VALUE_FOR_TYPE, ER(ER_ILLEGAL_VALUE_FOR_TYPE), + MYF(0), "double", (char*) str_arg); + } + presentation= name=(char*) str_arg; + decimals=(uint8) nr_of_decimals(str_arg); + max_length=length; + fixed= 1; +} + + int Item_real::save_in_field(Field *field, bool no_conversions) { double nr=val(); @@ -2381,7 +2409,10 @@ bool Item_default_value::fix_fields(THD *thd, struct st_table_list *table_list, Item **items) { + Item_field *field_arg; + Field *def_field; DBUG_ASSERT(fixed == 0); + if (!arg) { fixed= 1; @@ -2399,9 +2430,14 @@ bool Item_default_value::fix_fields(THD *thd, } arg= ref->ref[0]; } - Item_field *field_arg= (Item_field *)arg; - Field *def_field= (Field*) sql_alloc(field_arg->field->size_of()); - if (!def_field) + field_arg= (Item_field *)arg; + if (field_arg->field->flags & NO_DEFAULT_VALUE_FLAG) + { + my_printf_error(ER_NO_DEFAULT_FOR_FIELD, ER(ER_NO_DEFAULT_FOR_FIELD), + MYF(0), field_arg->field->field_name); + return 1; + } + if (!(def_field= (Field*) sql_alloc(field_arg->field->size_of()))) return 1; memcpy(def_field, field_arg->field, field_arg->field->size_of()); def_field->move_field(def_field->table->default_values - diff --git a/sql/item.h b/sql/item.h index 8094a2a10d7..71db03ad687 100644 --- a/sql/item.h +++ b/sql/item.h @@ -707,13 +707,7 @@ class Item_real :public Item_num public: double value; // Item_real() :value(0) {} - Item_real(const char *str_arg, uint length) :value(my_atof(str_arg)) - { - presentation= name=(char*) str_arg; - decimals=(uint8) nr_of_decimals(str_arg); - max_length=length; - fixed= 1; - } + Item_real(const char *str_arg, uint length); Item_real(const char *str,double val_arg,uint decimal_par,uint length) :value(val_arg) { diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 59abee55a3a..8ab5daffc16 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -2090,7 +2090,7 @@ extern "C" int my_message_sql(uint error, const char *str, { THD *thd; DBUG_ENTER("my_message_sql"); - DBUG_PRINT("error", ("Message: '%s'", str)); + DBUG_PRINT("error", ("error: %u message: '%s'", error, str)); if ((thd= current_thd)) { if (thd->spcont && diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt index 7b24036d385..1bfc7a1af0a 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -291,7 +291,7 @@ character-set=latin2 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -395,3 +395,4 @@ character-set=latin2 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt index 81a310a3f0a..8dda6c5af37 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -282,7 +282,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -386,3 +386,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt index a67577b1c99..308e48adcd9 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -291,7 +291,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -395,3 +395,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt index e7f1d1b7f64..4ae4e1c4b6e 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -279,7 +279,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -383,3 +383,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt index b6058bc56fc..390771c2564 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -284,7 +284,7 @@ character-set=latin7 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -388,3 +388,4 @@ character-set=latin7 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt index 345d20e2203..22c52dade49 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -279,7 +279,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -383,3 +383,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index ccc69c68683..b673c105c92 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -396,3 +396,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt index edd4e07c4d0..cf2fe96a68b 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -279,7 +279,7 @@ character-set=greek "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -383,3 +383,4 @@ character-set=greek "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt index 27c51b3ce2b..9f311af93a7 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -284,7 +284,7 @@ character-set=latin2 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -388,3 +388,4 @@ character-set=latin2 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt index 40e1271b187..2a2fdde7892 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -279,7 +279,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -383,3 +383,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt index 67de9337ff5..e6f274d9da3 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -283,7 +283,7 @@ character-set=ujis "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -387,3 +387,4 @@ character-set=ujis "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt index e2d4e29ede7..b740bd339b8 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -279,7 +279,7 @@ character-set=euckr "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -383,3 +383,4 @@ character-set=euckr "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt index a8bc6129db9..76cb88b8717 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -281,7 +281,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -385,3 +385,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt index ddf6960c740..b74972e3510 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -281,7 +281,7 @@ character-set=latin1 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -385,3 +385,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt index 5a871fbf776..526a15bda61 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -284,7 +284,7 @@ character-set=latin2 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -388,3 +388,4 @@ character-set=latin2 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt index c0861879702..7b1d377b6b7 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -385,3 +385,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt index 60c3cb4245d..56431481eb0 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -284,7 +284,7 @@ character-set=latin2 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -388,3 +388,4 @@ character-set=latin2 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt index e62c5a84a44..c8780936c4f 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -284,7 +284,7 @@ character-set=koi8r "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -388,3 +388,4 @@ character-set=koi8r "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt index 2c366890653..a8119a21f21 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -272,7 +272,7 @@ character-set=cp1250 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -376,3 +376,4 @@ character-set=cp1250 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt index 12eacb82dac..9f8f28aaae5 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -287,7 +287,7 @@ character-set=latin2 "%d line(s) were cut by GROUP_CONCAT()", "Row %ld doesn't contain data for all columns", "Row %ld was truncated; it contained more data than there were input columns", -"Data truncated; NULL supplied to NOT NULL column '%s' at row %ld", +"Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld", "Out of range value adjusted for column '%s' at row %ld", "Data truncated for column '%s' at row %ld", "Using storage engine %s for table '%s'", @@ -391,3 +391,4 @@ character-set=latin2 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt index 94b180914fe..c94aa99ee90 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -387,3 +387,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt index 718db694494..3b75eaea5e6 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -383,3 +383,4 @@ character-set=latin1 "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt index 7a4c37abd1d..3ee6324794a 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -389,3 +389,4 @@ character-set=koi8u "Field '%-.64s' doesn't have a default value", "Division by 0", "Incorrect %-.32s value: '%-.128s' for column '%.64s' at row %ld", +"Illegal %s '%-.64s' value found during parsing", diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 8f082c45e75..314a8ca9e44 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -246,9 +246,10 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, /* Count warnings for all inserts. For single line insert, generate an error if try to set a NOT NULL field - to NULL + to NULL. */ - thd->count_cuted_fields= ((values_list.elements == 1) ? + thd->count_cuted_fields= ((values_list.elements == 1 && + duplic != DUP_IGNORE) ? CHECK_FIELD_ERROR_FOR_NULL : CHECK_FIELD_WARN); thd->cuted_fields = 0L; @@ -276,7 +277,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES))); - if (check_that_all_fields_are_given_values(thd, table)) + if (fields.elements && check_that_all_fields_are_given_values(thd, table)) { /* thd->net.report_error is now set, which will abort the next loop */ error= 1; @@ -784,13 +785,11 @@ err: /****************************************************************************** Check that all fields with arn't null_fields are used - If DONT_USE_DEFAULT_FIELDS isn't defined use default value for not set - fields. ******************************************************************************/ int check_that_all_fields_are_given_values(THD *thd, TABLE *entry) { - if (!thd->abort_on_warning) + if (!thd->abort_on_warning) // No check if not strict mode return 0; for (Field **field=entry->field ; *field ; field++) @@ -1666,7 +1665,8 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) (thd->variables.sql_mode & (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES))); - DBUG_RETURN(check_that_all_fields_are_given_values(thd, table)); + DBUG_RETURN(fields->elements && + check_that_all_fields_are_given_values(thd, table)); } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 7be33c751e1..32748ffef81 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4710,9 +4710,14 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, new_field->comment.str= (char*) comment->str; new_field->comment.length=comment->length; } - /* Set flag if this field doesn't have a default value */ + /* + Set flag if this field doesn't have a default value + Enum values has always the first value as a default (set in + make_empty_rec(). + */ if (!default_value && !(type_modifier & AUTO_INCREMENT_FLAG) && - (type_modifier & NOT_NULL_FLAG) && type != FIELD_TYPE_TIMESTAMP) + (type_modifier & NOT_NULL_FLAG) && type != FIELD_TYPE_TIMESTAMP && + type != FIELD_TYPE_ENUM) new_field->flags|= NO_DEFAULT_VALUE_FLAG; if (length && !(new_field->length= (uint) atoi(length))) diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 49e2c244d32..951fc9de6d4 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -786,7 +786,8 @@ mysqld_show_fields(THD *thd, TABLE_LIST *table_list,const char *wild, protocol->store("CURRENT_TIMESTAMP", system_charset_info); } else if (field->unireg_check != Field::NEXT_NUMBER && - !field->is_null()) + !field->is_null() && + !(field->flags & NO_DEFAULT_VALUE_FLAG)) { // Not null by default /* Note: we have to convert the default value into @@ -1396,6 +1397,7 @@ store_create_info(THD *thd, TABLE *table, String *packet) field->unireg_check != Field::TIMESTAMP_UN_FIELD; has_default= (field->type() != FIELD_TYPE_BLOB && + !(field->flags & NO_DEFAULT_VALUE_FLAG) && field->unireg_check != Field::NEXT_NUMBER && !((foreign_db_mode || limited_mysql_mode) && has_now_default)); diff --git a/sql/sql_table.cc b/sql/sql_table.cc index fb4ad28202a..399fed00040 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -609,7 +609,9 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, break; } if (!(sql_field->flags & NOT_NULL_FLAG)) - sql_field->pack_flag|=FIELDFLAG_MAYBE_NULL; + sql_field->pack_flag|= FIELDFLAG_MAYBE_NULL; + if (sql_field->flags & NO_DEFAULT_VALUE_FLAG) + sql_field->pack_flag|= FIELDFLAG_NO_DEFAULT; sql_field->offset= pos; if (MTYP_TYPENR(sql_field->unireg_check) == Field::NEXT_NUMBER) auto_increment++; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 024d220b08d..c3741c4c94c 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -351,9 +351,10 @@ int mysql_update(THD *thd, transactional_table= table->file->has_transactions(); thd->no_trans_update= 0; - thd->abort_on_warning= test(thd->variables.sql_mode & - (MODE_STRICT_TRANS_TABLES | - MODE_STRICT_ALL_TABLES)); + thd->abort_on_warning= test(handle_duplicates != DUP_IGNORE && + (thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES))); while (!(error=info.read_record(&info)) && !thd->killed) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 79c5c094c58..800b586072b 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6448,8 +6448,24 @@ NUM_literal: NUM { int error; $$ = new Item_int($1.str, (longlong) my_strtoll10($1.str, NULL, &error), $1.length); } | LONG_NUM { int error; $$ = new Item_int($1.str, (longlong) my_strtoll10($1.str, NULL, &error), $1.length); } | ULONGLONG_NUM { $$ = new Item_uint($1.str, $1.length); } - | REAL_NUM { $$ = new Item_real($1.str, $1.length); } - | FLOAT_NUM { $$ = new Item_float($1.str, $1.length); } + | REAL_NUM + { + $$= new Item_real($1.str, $1.length); + if (YYTHD->net.report_error) + { + send_error(YYTHD, 0, NullS); + YYABORT; + } + } + | FLOAT_NUM + { + $$ = new Item_float($1.str, $1.length); + if (YYTHD->net.report_error) + { + send_error(YYTHD, 0, NullS); + YYABORT; + } + } ; /********************************************************************** diff --git a/sql/table.cc b/sql/table.cc index 88f0cefc09a..b6e6ad69f04 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -461,7 +461,7 @@ int openfrm(THD *thd, const char *name, const char *alias, uint db_stat, field_length= (uint) strpos[3]; recpos= uint2korr(strpos+4), pack_flag= uint2korr(strpos+6); - pack_flag&= ~NO_DEFAULT_VALUE_FLAG; // Safety for old files + pack_flag&= ~FIELDFLAG_NO_DEFAULT; // Safety for old files unireg_type= (uint) strpos[8]; interval_nr= (uint) strpos[10]; @@ -498,6 +498,8 @@ int openfrm(THD *thd, const char *name, const char *alias, uint db_stat, null_bit=1; } } + if (f_no_default(pack_flag)) + reg_field->flags|= NO_DEFAULT_VALUE_FLAG; if (reg_field->unireg_check == Field::NEXT_NUMBER) outparam->found_next_number_field= reg_field; if (outparam->timestamp_field == reg_field) |