|
1
by brian
clean slate |
1 |
drop table if exists t1,t2,t3,t4,t5,t6; |
2 |
drop database if exists mysqltest; |
|
3 |
CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', |
|
4 |
b varchar(1) binary NOT NULL DEFAULT ' ', |
|
5 |
c varchar(4) binary NOT NULL DEFAULT '0000', |
|
6 |
d tinyblob NULL, |
|
7 |
e tinyblob NULL, |
|
8 |
f tinyblob NULL, |
|
9 |
g tinyblob NULL, |
|
10 |
h tinyblob NULL, |
|
11 |
i tinyblob NULL, |
|
12 |
j tinyblob NULL, |
|
13 |
k tinyblob NULL, |
|
14 |
l tinyblob NULL, |
|
15 |
m tinyblob NULL, |
|
16 |
n tinyblob NULL, |
|
17 |
o tinyblob NULL, |
|
18 |
p tinyblob NULL, |
|
19 |
q varchar(30) binary NOT NULL DEFAULT ' ', |
|
20 |
r varchar(30) binary NOT NULL DEFAULT ' ', |
|
21 |
s tinyblob NULL, |
|
22 |
t varchar(4) binary NOT NULL DEFAULT ' ', |
|
23 |
u varchar(1) binary NOT NULL DEFAULT ' ', |
|
24 |
v varchar(30) binary NOT NULL DEFAULT ' ', |
|
25 |
w varchar(30) binary NOT NULL DEFAULT ' ', |
|
26 |
x tinyblob NULL, |
|
27 |
y varchar(5) binary NOT NULL DEFAULT ' ', |
|
28 |
z varchar(20) binary NOT NULL DEFAULT ' ', |
|
29 |
a1 varchar(30) binary NOT NULL DEFAULT ' ', |
|
30 |
b1 tinyblob NULL) |
|
31 |
ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; |
|
32 |
INSERT into t1 (b) values ('1'); |
|
33 |
SHOW WARNINGS; |
|
34 |
Level Code Message |
|
35 |
SELECT * from t1; |
|
36 |
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 |
|
37 |
1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL |
|
38 |
CREATE TABLE t2 (a varchar(30) binary NOT NULL DEFAULT ' ', |
|
39 |
b varchar(1) binary NOT NULL DEFAULT ' ', |
|
40 |
c varchar(4) binary NOT NULL DEFAULT '0000', |
|
41 |
d tinyblob NULL, |
|
42 |
e tinyblob NULL, |
|
43 |
f tinyblob NULL, |
|
44 |
g tinyblob NULL, |
|
45 |
h tinyblob NULL, |
|
46 |
i tinyblob NULL, |
|
47 |
j tinyblob NULL, |
|
48 |
k tinyblob NULL, |
|
49 |
l tinyblob NULL, |
|
50 |
m tinyblob NULL, |
|
51 |
n tinyblob NULL, |
|
52 |
o tinyblob NULL, |
|
53 |
p tinyblob NULL, |
|
54 |
q varchar(30) binary NOT NULL DEFAULT ' ', |
|
55 |
r varchar(30) binary NOT NULL DEFAULT ' ', |
|
56 |
s tinyblob NULL, |
|
57 |
t varchar(4) binary NOT NULL DEFAULT ' ', |
|
58 |
u varchar(1) binary NOT NULL DEFAULT ' ', |
|
59 |
v varchar(30) binary NOT NULL DEFAULT ' ', |
|
60 |
w varchar(30) binary NOT NULL DEFAULT ' ', |
|
61 |
x tinyblob NULL, |
|
62 |
y varchar(5) binary NOT NULL DEFAULT ' ', |
|
63 |
z varchar(20) binary NOT NULL DEFAULT ' ', |
|
64 |
a1 varchar(30) binary NOT NULL DEFAULT ' ', |
|
65 |
b1 tinyblob NULL) |
|
66 |
ENGINE=MyISAM DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; |
|
67 |
SHOW CREATE TABLE t2; |
|
68 |
Table Create Table |
|
69 |
t2 CREATE TABLE `t2` ( |
|
70 |
`a` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
71 |
`b` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
72 |
`c` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '0000', |
|
73 |
`d` tinyblob, |
|
74 |
`e` tinyblob, |
|
75 |
`f` tinyblob, |
|
76 |
`g` tinyblob, |
|
77 |
`h` tinyblob, |
|
78 |
`i` tinyblob, |
|
79 |
`j` tinyblob, |
|
80 |
`k` tinyblob, |
|
81 |
`l` tinyblob, |
|
82 |
`m` tinyblob, |
|
83 |
`n` tinyblob, |
|
84 |
`o` tinyblob, |
|
85 |
`p` tinyblob, |
|
86 |
`q` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
87 |
`r` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
88 |
`s` tinyblob, |
|
89 |
`t` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
90 |
`u` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
91 |
`v` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
92 |
`w` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
93 |
`x` tinyblob, |
|
94 |
`y` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
95 |
`z` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
96 |
`a1` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', |
|
97 |
`b1` tinyblob |
|
98 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
|
99 |
INSERT into t2 (b) values ('1'); |
|
100 |
SHOW WARNINGS; |
|
101 |
Level Code Message |
|
102 |
SELECT * from t2; |
|
103 |
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 |
|
104 |
1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL |
|
105 |
drop table t1; |
|
106 |
drop table t2; |
|
107 |
create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00'); |
|
108 |
insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT); |
|
109 |
Warnings: |
|
110 |
Warning 1364 Field 'd' doesn't have a default value |
|
111 |
Warning 1364 Field 'd' doesn't have a default value |
|
112 |
insert into bug20691 (i) values (2); |
|
113 |
Warnings: |
|
114 |
Warning 1364 Field 'd' doesn't have a default value |
|
115 |
desc bug20691; |
|
116 |
Field Type Null Key Default Extra |
|
117 |
i int(11) YES NULL |
|
118 |
d datetime NO NULL |
|
119 |
dn datetime NO 0000-00-00 00:00:00 |
|
120 |
insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); |
|
121 |
Warnings: |
|
122 |
Warning 1364 Field 'd' doesn't have a default value |
|
123 |
Warning 1364 Field 'd' doesn't have a default value |
|
124 |
insert into bug20691 (i) values (4); |
|
125 |
Warnings: |
|
126 |
Warning 1364 Field 'd' doesn't have a default value |
|
127 |
insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); |
|
128 |
Warnings: |
|
129 |
Warning 1364 Field 'd' doesn't have a default value |
|
130 |
Warning 1364 Field 'd' doesn't have a default value |
|
131 |
SET sql_mode = 'ALLOW_INVALID_DATES'; |
|
132 |
insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT); |
|
133 |
Warnings: |
|
134 |
Warning 1364 Field 'd' doesn't have a default value |
|
135 |
Warning 1364 Field 'd' doesn't have a default value |
|
136 |
SET sql_mode = 'STRICT_ALL_TABLES'; |
|
137 |
insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); |
|
138 |
ERROR HY000: Field 'd' doesn't have a default value |
|
139 |
select * from bug20691 order by i asc; |
|
140 |
i d dn |
|
141 |
1 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
142 |
1 1975-07-10 07:10:03 1978-01-13 14:08:51 |
|
143 |
1 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
144 |
2 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
145 |
3 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
146 |
3 1975-07-10 07:10:03 1978-01-13 14:08:51 |
|
147 |
3 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
148 |
4 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
149 |
5 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
150 |
5 1975-07-10 07:10:03 1978-01-13 14:08:51 |
|
151 |
5 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
152 |
6 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
153 |
6 1975-07-10 07:10:03 1978-01-13 14:08:51 |
|
154 |
6 0000-00-00 00:00:00 0000-00-00 00:00:00 |
|
155 |
drop table bug20691; |
|
156 |
SET sql_mode = ''; |
|
157 |
create table bug20691 ( |
|
158 |
a set('one', 'two', 'three') not null, |
|
159 |
b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null, |
|
160 |
c time not null, |
|
161 |
d date not null, |
|
162 |
e int not null, |
|
163 |
f long not null, |
|
164 |
g blob not null, |
|
165 |
h datetime not null, |
|
166 |
i decimal not null, |
|
167 |
x int); |
|
168 |
insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1); |
|
169 |
insert into bug20691 (x) values (2); |
|
170 |
Warnings: |
|
171 |
Warning 1364 Field 'a' doesn't have a default value |
|
172 |
Warning 1364 Field 'c' doesn't have a default value |
|
173 |
Warning 1364 Field 'd' doesn't have a default value |
|
174 |
Warning 1364 Field 'e' doesn't have a default value |
|
175 |
Warning 1364 Field 'f' doesn't have a default value |
|
176 |
Warning 1364 Field 'g' doesn't have a default value |
|
177 |
Warning 1364 Field 'h' doesn't have a default value |
|
178 |
Warning 1364 Field 'i' doesn't have a default value |
|
179 |
insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3); |
|
180 |
insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); |
|
181 |
Warnings: |
|
182 |
Warning 1364 Field 'a' doesn't have a default value |
|
183 |
Warning 1364 Field 'b' doesn't have a default value |
|
184 |
Warning 1364 Field 'c' doesn't have a default value |
|
185 |
Warning 1364 Field 'd' doesn't have a default value |
|
186 |
Warning 1364 Field 'e' doesn't have a default value |
|
187 |
Warning 1364 Field 'f' doesn't have a default value |
|
188 |
Warning 1364 Field 'g' doesn't have a default value |
|
189 |
Warning 1364 Field 'h' doesn't have a default value |
|
190 |
Warning 1364 Field 'i' doesn't have a default value |
|
191 |
select * from bug20691 order by x asc; |
|
192 |
a b c d e f g h i x |
|
193 |
two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 1 |
|
194 |
small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 2 |
|
195 |
two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 3 |
|
196 |
00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 |
|
197 |
drop table bug20691; |
|
198 |
create table t1 (id int not null); |
|
199 |
insert into t1 values(default); |
|
200 |
Warnings: |
|
201 |
Warning 1364 Field 'id' doesn't have a default value |
|
202 |
create view v1 (c) as select id from t1; |
|
203 |
insert into t1 values(default); |
|
204 |
Warnings: |
|
205 |
Warning 1364 Field 'id' doesn't have a default value |
|
206 |
drop view v1; |
|
207 |
drop table t1; |
|
208 |
End of 5.0 tests. |