diff options
Diffstat (limited to 'mysql-test')
28 files changed, 157 insertions, 46 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index a323669ac97..cafe6f23ccf 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -267,11 +267,15 @@ drop table t1; create table t1 (a int not null, b int, primary key (a)); insert into t1 values (1,1); create table if not exists t1 select 2; +Warnings: +Warning 1364 Field 'a' doesn't have a default value select * from t1; a b 1 1 0 2 create table if not exists t1 select 3 as 'a',4 as 'b'; +Warnings: +Warning 1364 Field 'a' doesn't have a default value create table if not exists t1 select 3 as 'a',3 as 'b'; ERROR 23000: Duplicate entry '3' for key 1 select * from t1; diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 5e885d006cb..43947edbd48 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -220,12 +220,12 @@ select * from t1 where match a against ("te*" in boolean mode)+0; a test drop table t1; -create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `word` varchar(255) collate latin1_german2_ci NOT NULL, - `word2` varchar(255) collate latin1_german2_ci NOT NULL, + `word2` varchar(255) collate latin1_german2_ci NOT NULL default '', 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 5932bd75787..87b83acfe0f 100644 --- a/mysql-test/r/ctype_many.result +++ b/mysql-test/r/ctype_many.result @@ -2,27 +2,27 @@ DROP TABLE IF EXISTS t1; SET CHARACTER SET latin1; CREATE TABLE t1 ( comment CHAR(32) ASCII NOT NULL, -koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL +koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL default '' ) CHARSET=latin5; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `comment` char(32) character set latin1 NOT NULL, - `koi8_ru_f` char(32) character set koi8r NOT NULL + `koi8_ru_f` char(32) character set koi8r NOT NULL default '' ) 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, - `koi8_ru_f` char(32) character set koi8r NOT NULL + `koi8_ru_f` char(32) character set koi8r NOT NULL default '' ) 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, - `koi8_ru_f` char(32) character set koi8r NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL default '', `latin5_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin5 ALTER TABLE t1 DEFAULT CHARSET=latin2; @@ -31,7 +31,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `comment` char(32) NOT NULL, - `koi8_ru_f` char(32) character set koi8r NOT NULL, + `koi8_ru_f` char(32) character set koi8r NOT NULL default '', `latin5_f` char(32) character set latin5 NOT NULL, `latin2_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin2 @@ -40,7 +40,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `comment` char(32) NOT NULL, - `koi8_ru_f` char(32) character set koi8r NOT NULL + `koi8_ru_f` char(32) character set koi8r NOT NULL default '' ) 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'); @@ -219,7 +219,7 @@ z LAT CAPIT Z 2 э CYR CAPIT E 2 ю CYR CAPIT YU 2 я CYR CAPIT YA 2 -ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL; +ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL default ''; UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8); SET CHARACTER SET koi8r; SELECT * FROM t1; @@ -340,7 +340,7 @@ CYR CAPIT SOFT SIGN CYR CAPIT E CYR CAPIT YU CYR CAPIT YA -ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL; +ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default ''; UPDATE t1 SET bin_f=koi8_ru_f; SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1; COUNT(DISTINCT bin_f) COUNT(DISTINCT koi8_ru_f) COUNT(DISTINCT utf8_f) diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index efd3b0f8e15..0e558e47594 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -758,7 +758,6 @@ SPATIAL KEY(g) INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText('LineString(1 2, 2 4)')); drop table t1; CREATE TABLE t1 ( -geoobjid INT NOT NULL, line LINESTRING NOT NULL, kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po', name VARCHAR(32), diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index eed38b16143..c8ae8303d99 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -157,6 +157,10 @@ select 1; 1 1 insert into mysql.user (host, user) values ('localhost', 'test11'); +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value insert into mysql.db (host, db, user, select_priv) values ('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); alter table mysql.db order by db asc; diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index b7639d49c7d..dd35d1c3dac 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -261,6 +261,10 @@ GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO 'mysqltest_3'@'localhost' select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' insert into mysql.user set host='%', user='mysqltest_B'; +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value create user mysqltest_A@'%'; rename user mysqltest_B@'%' to mysqltest_C@'%'; drop user mysqltest_C@'%'; diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result index edf7d0e91cb..85ca832828d 100644 --- a/mysql-test/r/help.result +++ b/mysql-test/r/help.result @@ -1,32 +1,48 @@ insert into mysql.help_category(help_category_id,name)values(1,'impossible_category_1'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @category1_id:= 1; @category1_id:= 1 1 insert into mysql.help_category(help_category_id,name)values(2,'impossible_category_2'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @category2_id:= 2; @category2_id:= 2 2 insert into mysql.help_category(help_category_id,name,parent_category_id)values(3,'impossible_category_3',@category2_id); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @category3_id:= 3; @category3_id:= 3 3 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(1,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @topic1_id:= 1; @topic1_id:= 1 1 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(2,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @topic2_id:= 2; @topic2_id:= 2 2 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(3,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @topic3_id:= 3; @topic3_id:= 3 3 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(4,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @topic4_id:= 4; @topic4_id:= 4 4 insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(5,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); +Warnings: +Warning 1364 Field 'url' doesn't have a default value select @topic5_id:= 5; @topic5_id:= 5 5 diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result index 6a3d2bffab0..1e38f762dd1 100644 --- a/mysql-test/r/limit.result +++ b/mysql-test/r/limit.result @@ -1,5 +1,5 @@ drop table if exists t1; -create table t1 (a int primary key, b int not null); +create table t1 (a int not null default 0 primary key, b int not null default 0); insert into t1 () values (); insert into t1 values (1,1),(2,1),(3,1); update t1 set a=4 where b=1 limit 1; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 9f05703e69e..806e2b6f254 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -88,7 +88,7 @@ b ifnull(t2.b,"this is null") NULL this is null NULL this is null drop table t1; -CREATE TABLE t1 (a varchar(16) NOT NULL, b smallint(6) NOT NULL, c datetime NOT NULL, d smallint(6) NOT NULL); +CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default '', c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0); INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55"; Warnings: Warning 1265 Data truncated for column 'd' at row 1 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 3474da93d75..628ca5fd958 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -257,7 +257,7 @@ favo_tijdschrift varchar(50) NOT NULL default '', favo_tv varchar(50) NOT NULL default '', favo_eten varchar(50) NOT NULL default '', favo_muziek varchar(30) NOT NULL default '', -info text NOT NULL, +info text NOT NULL default '', ipnr varchar(30) NOT NULL default '', PRIMARY KEY (member_id) ) ENGINE=MyISAM PACK_KEYS=1; diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result index 74075b188ba..450e728090e 100644 --- a/mysql-test/r/rpl000001.result +++ b/mysql-test/r/rpl000001.result @@ -61,6 +61,10 @@ create table t1 (n int); insert into t1 values(3456); insert into mysql.user (Host, User, Password) VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value select select_priv,user from mysql.user where user = _binary'blafasel2'; select_priv user N blafasel2 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 93884bf4cf4..daad032b854 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1,7 +1,7 @@ use test; drop table if exists t1; create table t1 ( -id char(16) not null, +id char(16) not null default '', data int not null ); drop table if exists t2; @@ -2164,7 +2164,7 @@ 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, + `id` char(16) NOT NULL default '', `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Database Create Database @@ -2218,7 +2218,7 @@ 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, + `id` char(16) NOT NULL default '', `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Database Create Database diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 99ea0d577da..5e1768a207e 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1029,7 +1029,11 @@ 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); +Warnings: +Warning 1364 Field 'col2' doesn't have a default value INSERT IGNORE INTO t1 () values (); +Warnings: +Warning 1364 Field 'col2' doesn't have a default value SELECT * FROM t1; col1 col2 1 hello @@ -1038,6 +1042,47 @@ col1 col2 3 99 DROP TABLE t1; +SET @@sql_mode = 'traditional'; +CREATE TABLE t1 (i int not null); +INSERT INTO t1 VALUES (); +ERROR HY000: Field 'i' doesn't have a default value +INSERT INTO t1 VALUES (DEFAULT); +ERROR HY000: Field 'i' doesn't have a default value +INSERT INTO t1 VALUES (DEFAULT(i)); +ERROR HY000: Field 'i' doesn't have a default value +ALTER TABLE t1 ADD j int; +INSERT INTO t1 SET j = 1; +ERROR HY000: Field 'i' doesn't have a default value +INSERT INTO t1 SET j = 1, i = DEFAULT; +ERROR HY000: Field 'i' doesn't have a default value +INSERT INTO t1 SET j = 1, i = DEFAULT(i); +ERROR HY000: Field 'i' doesn't have a default value +INSERT INTO t1 VALUES (DEFAULT,1); +ERROR HY000: Field 'i' doesn't have a default value +DROP TABLE t1; +SET @@sql_mode = ''; +CREATE TABLE t1 (i int not null); +INSERT INTO t1 VALUES (); +Warnings: +Warning 1364 Field 'i' doesn't have a default value +INSERT INTO t1 VALUES (DEFAULT); +Warnings: +Warning 1364 Field 'i' doesn't have a default value +INSERT INTO t1 VALUES (DEFAULT(i)); +ERROR HY000: Field 'i' doesn't have a default value +ALTER TABLE t1 ADD j int; +INSERT INTO t1 SET j = 1; +Warnings: +Warning 1364 Field 'i' doesn't have a default value +INSERT INTO t1 SET j = 1, i = DEFAULT; +Warnings: +Warning 1364 Field 'i' doesn't have a default value +INSERT INTO t1 SET j = 1, i = DEFAULT(i); +ERROR HY000: Field 'i' doesn't have a default value +INSERT INTO t1 VALUES (DEFAULT,1); +Warnings: +Warning 1364 Field 'i' doesn't have a default value +DROP TABLE t1; set sql_mode='traditional'; create table t1 (charcol char(255), varcharcol varchar(255), binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext, diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 743819472a5..6f437ddbe0d 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -502,7 +502,7 @@ select foobar, boggle from t1 where foobar = 'fish' and boggle = 10; foobar boggle fish 10 drop table t1; -create table t1 (id integer auto_increment unique,imagem LONGBLOB not null); +create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default ''); insert into t1 (id) values (1); select charset(load_file('../../std_data/words.dat')), diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 0e8c721adf3..3b07464c8f4 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -21,10 +21,10 @@ time_field time, date_time datetime, blob_col blob, tinyblob_col tinyblob, -mediumblob_col mediumblob not null, -longblob_col longblob not null, -options enum('one','two','tree') not null, -flags set('one','two','tree') not null, +mediumblob_col mediumblob not null default '', +longblob_col longblob not null default '', +options enum('one','two','tree') not null , +flags set('one','two','tree') not null default '', PRIMARY KEY (auto), KEY (utiny), KEY (tiny), diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index d143b66ae94..442d0784953 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -117,10 +117,12 @@ Warning 1263 Column set to default value; NULL supplied to NOT NULL column 'a' a Warning 1265 Data truncated for column 'b' at row 4 insert into t2(b) values('mysqlab'); Warnings: +Warning 1364 Field 'a' doesn't have a default value Warning 1265 Data truncated for column 'b' at row 1 set sql_warnings=1; insert into t2(b) values('mysqlab'); Warnings: +Warning 1364 Field 'a' doesn't have a default value Warning 1265 Data truncated for column 'b' at row 1 set sql_warnings=0; drop table t1, t2; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 512ae88a445..e17806a54aa 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -66,7 +66,7 @@ drop table t1; # # The below checks both binary and character comparisons. # -create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word)); +create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word)); show create table t1; insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae'); update t1 set word2=word; diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test index 26057e7c997..454e2c63cbb 100644 --- a/mysql-test/t/ctype_many.test +++ b/mysql-test/t/ctype_many.test @@ -8,7 +8,7 @@ SET CHARACTER SET latin1; CREATE TABLE t1 ( comment CHAR(32) ASCII NOT NULL, - koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL + koi8_ru_f CHAR(32) CHARACTER SET koi8r NOT NULL default '' ) CHARSET=latin5; SHOW CREATE TABLE t1; @@ -142,13 +142,13 @@ INSERT INTO t1 (koi8_ru_f,comment) VALUES (_koi8r'','CYR CAPIT YA'); SET CHARACTER SET utf8; SELECT koi8_ru_f,MIN(comment),COUNT(*) FROM t1 GROUP BY 1; -ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL; +ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL default ''; UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8); SET CHARACTER SET koi8r; SELECT * FROM t1; -ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL; +ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default ''; UPDATE t1 SET bin_f=koi8_ru_f; SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 716dd38a119..da59c6ae5e4 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -120,7 +120,6 @@ INSERT INTO t1 (g) VALUES (GeomFromText('LineString(1 2, 2 3)')),(GeomFromText(' drop table t1; CREATE TABLE t1 ( - geoobjid INT NOT NULL, line LINESTRING NOT NULL, kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po', name VARCHAR(32), diff --git a/mysql-test/t/index_merge_ror.test b/mysql-test/t/index_merge_ror.test index 223bd241d96..5375f9d1a31 100644 --- a/mysql-test/t/index_merge_ror.test +++ b/mysql-test/t/index_merge_ror.test @@ -8,13 +8,13 @@ drop table if exists t0,t1,t2; create table t1 ( /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */ - st_a int not null, - swt1a int not null, - swt2a int not null, + st_a int not null default 0, + swt1a int not null default 0, + swt2a int not null default 0, - st_b int not null, - swt1b int not null, - swt2b int not null, + st_b int not null default 0, + swt1b int not null default 0, + swt2b int not null default 0, /* fields/keys for row retrieval tests */ key1 int, diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 28b287a5d4a..3dc56295375 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1 (a int primary key, b int not null); +create table t1 (a int not null default 0 primary key, b int not null default 0); insert into t1 () values (); -- Testing default values insert into t1 values (1,1),(2,1),(3,1); update t1 set a=4 where b=1 limit 1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 4bc6a4f051a..934b165f604 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -59,7 +59,7 @@ drop table t1; # # Test inserting and updating with NULL # -CREATE TABLE t1 (a varchar(16) NOT NULL, b smallint(6) NOT NULL, c datetime NOT NULL, d smallint(6) NOT NULL); +CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default '', c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0); INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55"; UPDATE t1 SET d=1/NULL; UPDATE t1 SET d=NULL; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 9608b7900ee..36eee6e43b7 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -206,7 +206,7 @@ CREATE TABLE t1 ( favo_tv varchar(50) NOT NULL default '', favo_eten varchar(50) NOT NULL default '', favo_muziek varchar(30) NOT NULL default '', - info text NOT NULL, + info text NOT NULL default '', ipnr varchar(30) NOT NULL default '', PRIMARY KEY (member_id) ) ENGINE=MyISAM PACK_KEYS=1; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 23aec58e419..0004591288f 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -106,7 +106,7 @@ drop table t1; # Do a create table that tries to cover all types and options # create table t1 ( -type_bool bool not null, +type_bool bool not null default false, type_tiny tinyint not null auto_increment primary key, type_short smallint(3), type_mediumint mediumint, @@ -117,9 +117,9 @@ empty_char char(0), type_char char(2), type_varchar varchar(10), type_timestamp timestamp not null, -type_date date not null, -type_time time not null, -type_datetime datetime not null, +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_year year, type_enum enum ('red', 'green', 'blue'), type_set enum ('red', 'green', 'blue'), diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index b8eb371931f..a741a4af7d8 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -24,7 +24,7 @@ use test; drop table if exists t1; --enable_warnings create table t1 ( - id char(16) not null, + id char(16) not null default '', data int not null ); --disable_warnings diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index 6b7ce1a8f9e..333ec40b1b9 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -1049,3 +1049,37 @@ drop table t1; # Restore mode # set sql_mode=@org_mode; + +# Test fields with no default value that are NOT NULL (Bug #5986) +SET @@sql_mode = 'traditional'; +CREATE TABLE t1 (i int not null); +--error 1364 +INSERT INTO t1 VALUES (); +--error 1364 +INSERT INTO t1 VALUES (DEFAULT); +--error 1364 +INSERT INTO t1 VALUES (DEFAULT(i)); +ALTER TABLE t1 ADD j int; +--error 1364 +INSERT INTO t1 SET j = 1; +--error 1364 +INSERT INTO t1 SET j = 1, i = DEFAULT; +--error 1364 +INSERT INTO t1 SET j = 1, i = DEFAULT(i); +--error 1364 +INSERT INTO t1 VALUES (DEFAULT,1); +DROP TABLE t1; +SET @@sql_mode = ''; +CREATE TABLE t1 (i int not null); +INSERT INTO t1 VALUES (); +INSERT INTO t1 VALUES (DEFAULT); +# DEFAULT(i) is an error even with the default sql_mode +--error 1364 +INSERT INTO t1 VALUES (DEFAULT(i)); +ALTER TABLE t1 ADD j int; +INSERT INTO t1 SET j = 1; +INSERT INTO t1 SET j = 1, i = DEFAULT; +--error 1364 +INSERT INTO t1 SET j = 1, i = DEFAULT(i); +INSERT INTO t1 VALUES (DEFAULT,1); +DROP TABLE t1; diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index 456c65e57d9..c33ea3f435d 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -304,7 +304,7 @@ drop table t1; # Bug when blob is updated # -create table t1 (id integer auto_increment unique,imagem LONGBLOB not null); +create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default ''); insert into t1 (id) values (1); # We have to clean up the path in the results for safe comparison --replace_result $MYSQL_TEST_DIR ../.. diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test index cada0c371f0..a2deb367e14 100644 --- a/mysql-test/t/type_ranges.test +++ b/mysql-test/t/type_ranges.test @@ -28,10 +28,10 @@ CREATE TABLE t1 ( date_time datetime, blob_col blob, tinyblob_col tinyblob, - mediumblob_col mediumblob not null, - longblob_col longblob not null, - options enum('one','two','tree') not null, - flags set('one','two','tree') not null, + mediumblob_col mediumblob not null default '', + longblob_col longblob not null default '', + options enum('one','two','tree') not null , + flags set('one','two','tree') not null default '', PRIMARY KEY (auto), KEY (utiny), KEY (tiny), |