Edit File: default.result
drop table if exists t1,t2,t3,t4,t5,t6; drop database if exists mysqltest; CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', b varchar(1) binary NOT NULL DEFAULT ' ', c varchar(4) binary NOT NULL DEFAULT '0000', d tinyblob NULL, e tinyblob NULL, f tinyblob NULL, g tinyblob NULL, h tinyblob NULL, i tinyblob NULL, j tinyblob NULL, k tinyblob NULL, l tinyblob NULL, m tinyblob NULL, n tinyblob NULL, o tinyblob NULL, p tinyblob NULL, q varchar(30) binary NOT NULL DEFAULT ' ', r varchar(30) binary NOT NULL DEFAULT ' ', s tinyblob NULL, t varchar(4) binary NOT NULL DEFAULT ' ', u varchar(1) binary NOT NULL DEFAULT ' ', v varchar(30) binary NOT NULL DEFAULT ' ', w varchar(30) binary NOT NULL DEFAULT ' ', x tinyblob NULL, y varchar(5) binary NOT NULL DEFAULT ' ', z varchar(20) binary NOT NULL DEFAULT ' ', a1 varchar(30) binary NOT NULL DEFAULT ' ', b1 tinyblob NULL) ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; INSERT into t1 (b) values ('1'); SHOW WARNINGS; Level Code Message SELECT * from t1; a b c d e f g h i j k l m n o p q r s t u v w x y z a1 b1 1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL CREATE TABLE t2 (a varchar(30) binary NOT NULL DEFAULT ' ', b varchar(1) binary NOT NULL DEFAULT ' ', c varchar(4) binary NOT NULL DEFAULT '0000', d tinyblob NULL, e tinyblob NULL, f tinyblob NULL, g tinyblob NULL, h tinyblob NULL, i tinyblob NULL, j tinyblob NULL, k tinyblob NULL, l tinyblob NULL, m tinyblob NULL, n tinyblob NULL, o tinyblob NULL, p tinyblob NULL, q varchar(30) binary NOT NULL DEFAULT ' ', r varchar(30) binary NOT NULL DEFAULT ' ', s tinyblob NULL, t varchar(4) binary NOT NULL DEFAULT ' ', u varchar(1) binary NOT NULL DEFAULT ' ', v varchar(30) binary NOT NULL DEFAULT ' ', w varchar(30) binary NOT NULL DEFAULT ' ', x tinyblob NULL, y varchar(5) binary NOT NULL DEFAULT ' ', z varchar(20) binary NOT NULL DEFAULT ' ', a1 varchar(30) binary NOT NULL DEFAULT ' ', b1 tinyblob NULL) ENGINE=MyISAM DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', `b` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT ' ', `c` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '0000', `d` tinyblob, `e` tinyblob, `f` tinyblob, `g` tinyblob, `h` tinyblob, `i` tinyblob, `j` tinyblob, `k` tinyblob, `l` tinyblob, `m` tinyblob, `n` tinyblob, `o` tinyblob, `p` tinyblob, `q` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', `r` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', `s` tinyblob, `t` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT ' ', `u` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT ' ', `v` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', `w` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', `x` tinyblob, `y` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT ' ', `z` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT ' ', `a1` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', `b1` tinyblob ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT into t2 (b) values ('1'); SHOW WARNINGS; Level Code Message SELECT * from t2; a b c d e f g h i j k l m n o p q r s t u v w x y z a1 b1 1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL drop table t1; drop table t2; create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00'); insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value insert into bug20691 (i) values (2); Warnings: Warning 1364 Field 'd' doesn't have a default value desc bug20691; Field Type Null Key Default Extra i int(11) YES NULL d datetime NO NULL dn datetime NO 0000-00-00 00:00:00 insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value insert into bug20691 (i) values (4); Warnings: Warning 1364 Field 'd' doesn't have a default value insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value SET sql_mode = 'ALLOW_INVALID_DATES'; insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value SET sql_mode = 'STRICT_ALL_TABLES'; insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); ERROR HY000: Field 'd' doesn't have a default value select * from bug20691 order by i asc; i d dn 1 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1975-07-10 07:10:03 1978-01-13 14:08:51 1 0000-00-00 00:00:00 0000-00-00 00:00:00 2 0000-00-00 00:00:00 0000-00-00 00:00:00 3 0000-00-00 00:00:00 0000-00-00 00:00:00 3 1975-07-10 07:10:03 1978-01-13 14:08:51 3 0000-00-00 00:00:00 0000-00-00 00:00:00 4 0000-00-00 00:00:00 0000-00-00 00:00:00 5 0000-00-00 00:00:00 0000-00-00 00:00:00 5 1975-07-10 07:10:03 1978-01-13 14:08:51 5 0000-00-00 00:00:00 0000-00-00 00:00:00 6 0000-00-00 00:00:00 0000-00-00 00:00:00 6 1975-07-10 07:10:03 1978-01-13 14:08:51 6 0000-00-00 00:00:00 0000-00-00 00:00:00 drop table bug20691; SET sql_mode = ''; create table bug20691 ( a set('one', 'two', 'three') not null, b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null, c time not null, d date not null, e int not null, f long not null, g blob not null, h datetime not null, i decimal not null, x int); insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1); insert into bug20691 (x) values (2); Warnings: Warning 1364 Field 'a' doesn't have a default value Warning 1364 Field 'c' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'e' doesn't have a default value Warning 1364 Field 'f' doesn't have a default value Warning 1364 Field 'g' doesn't have a default value Warning 1364 Field 'h' doesn't have a default value Warning 1364 Field 'i' doesn't have a default value insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3); insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); Warnings: Warning 1364 Field 'a' doesn't have a default value Warning 1364 Field 'c' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'e' doesn't have a default value Warning 1364 Field 'f' doesn't have a default value Warning 1364 Field 'g' doesn't have a default value Warning 1364 Field 'h' doesn't have a default value Warning 1364 Field 'i' doesn't have a default value select * from bug20691 order by x asc; a b c d e f g h i x two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 1 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 2 two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 3 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 drop table bug20691; create table t1 (id int not null); insert into t1 values(default); Warnings: Warning 1364 Field 'id' doesn't have a default value create view v1 (c) as select id from t1; insert into t1 values(default); Warnings: Warning 1364 Field 'id' doesn't have a default value drop view v1; drop table t1; create table t1 (a int unique); create table t2 (b int default 10); insert into t1 (a) values (1); insert into t2 (b) values (1); insert into t1 (a) select b from t2 on duplicate key update a=default; select * from t1; a NULL insert into t1 (a) values (1); insert into t1 (a) select b from t2 on duplicate key update a=default(b); select * from t1; a NULL 10 drop table t1, t2; End of 5.0 tests.