1
by brian
clean slate |
1 |
#
|
2 |
# Test timestamp |
|
3 |
#
|
|
4 |
||
5 |
--disable_warnings
|
|
6 |
drop table if exists t1,t2; |
|
7 |
--enable_warnings
|
|
8 |
||
9 |
# Set timezone to GMT-3, to make it possible to use "interval 3 hour" |
|
506
by Brian Aker
Added back more tests. |
10 |
#set time_zone="+03:00"; |
1
by brian
clean slate |
11 |
|
12 |
CREATE TABLE t1 (a int, t timestamp); |
|
13 |
CREATE TABLE t2 (a int, t datetime); |
|
14 |
SET TIMESTAMP=1234; |
|
15 |
insert into t1 values(1,NULL); |
|
16 |
insert into t1 values(2,"2002-03-03"); |
|
17 |
SET TIMESTAMP=1235; |
|
18 |
insert into t1 values(3,NULL); |
|
19 |
SET TIMESTAMP=1236; |
|
20 |
insert into t1 (a) values(4); |
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
21 |
insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,NULL); |
1
by brian
clean slate |
22 |
SET TIMESTAMP=1237; |
23 |
insert into t1 select * from t2; |
|
24 |
SET TIMESTAMP=1238; |
|
25 |
insert into t1 (a) select a+1 from t2 where a=8; |
|
26 |
select * from t1; |
|
27 |
drop table t1,t2; |
|
28 |
||
29 |
SET TIMESTAMP=1234; |
|
30 |
CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id)); |
|
31 |
INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00"); |
|
32 |
SELECT stamp FROM t1 WHERE id="myKey"; |
|
33 |
UPDATE t1 SET value="my value" WHERE id="myKey"; |
|
34 |
SELECT stamp FROM t1 WHERE id="myKey"; |
|
35 |
UPDATE t1 SET id="myKey" WHERE value="my value"; |
|
36 |
SELECT stamp FROM t1 WHERE id="myKey"; |
|
37 |
drop table t1; |
|
38 |
||
39 |
create table t1 (a timestamp); |
|
40 |
insert into t1 values (now()); |
|
41 |
select date_format(a,"%Y %y"),year(a),year(now()) from t1; |
|
42 |
drop table t1; |
|
43 |
||
44 |
create table t1 (ix timestamp); |
|
45 |
insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); |
|
46 |
select ix+0 from t1; |
|
47 |
truncate table t1; |
|
48 |
insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"); |
|
49 |
select ix+0 from t1; |
|
50 |
drop table t1; |
|
51 |
||
52 |
CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp); |
|
53 |
INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959); |
|
54 |
INSERT INTO t1 VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000); |
|
55 |
INSERT INTO t1 VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959); |
|
56 |
INSERT INTO t1 VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000); |
|
57 |
INSERT INTO t1 VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000); |
|
58 |
INSERT INTO t1 VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000); |
|
59 |
INSERT INTO t1 VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000); |
|
60 |
INSERT INTO t1 VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959); |
|
61 |
INSERT INTO t1 VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000); |
|
62 |
INSERT INTO t1 VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959); |
|
63 |
INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000); |
|
64 |
INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000); |
|
65 |
# The following will get you an different answer on 64 bit machines |
|
66 |
#INSERT INTO t1 VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000); |
|
67 |
SELECT * FROM t1; |
|
68 |
drop table t1; |
|
69 |
||
70 |
create table t1 (t2 timestamp, t4 timestamp, t6 timestamp, |
|
71 |
t8 timestamp, t10 timestamp, t12 timestamp, |
|
72 |
t14 timestamp); |
|
907.1.7
by Jay Pipes
Merged in remove-timezone work |
73 |
insert t1 values |
1
by brian
clean slate |
74 |
("1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", |
75 |
"1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", |
|
76 |
"1997-12-31 23:47:59"); |
|
77 |
select * from t1; |
|
78 |
select * from t1; |
|
79 |
drop table t1; |
|
80 |
||
81 |
#
|
|
82 |
# Let us check if we properly treat wrong datetimes and produce proper warnings |
|
83 |
# (for both strings and numbers) |
|
84 |
#
|
|
85 |
create table t1 (ix timestamp); |
|
2069.2.4
by Brian Aker
Fix issue with return value from unix_timestamp(). Also clean up error |
86 |
--error ER_INVALID_TIMESTAMP_VALUE # Bad unix timestamp |
1
by brian
clean slate |
87 |
insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000); |
88 |
select ix+0 from t1; |
|
89 |
truncate table t1; |
|
2069.2.4
by Brian Aker
Fix issue with return value from unix_timestamp(). Also clean up error |
90 |
--error ER_INVALID_TIMESTAMP_VALUE # Bad unix timestamp |
1
by brian
clean slate |
91 |
insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000"); |
92 |
select ix+0 from t1; |
|
93 |
truncate table t1; |
|
2069.2.4
by Brian Aker
Fix issue with return value from unix_timestamp(). Also clean up error |
94 |
--error ER_INVALID_TIMESTAMP_VALUE # Bad unix timestamp |
1
by brian
clean slate |
95 |
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); |
96 |
select ix+0 from t1; |
|
97 |
drop table t1; |
|
98 |
||
99 |
#
|
|
100 |
# Test for TIMESTAMP column with default now() and on update now() clauses |
|
101 |
#
|
|
102 |
||
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
103 |
--error ER_TOO_MUCH_AUTO_TIMESTAMP_COLS |
1
by brian
clean slate |
104 |
create table t1 (t1 timestamp default now(), t2 timestamp on update now()); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
105 |
--error ER_TOO_MUCH_AUTO_TIMESTAMP_COLS |
1
by brian
clean slate |
106 |
create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now()); |
107 |
||
108 |
# Let us test TIMESTAMP auto-update behaviour |
|
109 |
# Also we will test behaviour of TIMESTAMP field in SHOW CREATE TABLE and |
|
110 |
# behaviour of DEFAULT literal for such fields |
|
111 |
create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp); |
|
112 |
SET TIMESTAMP=1000000000; |
|
113 |
insert into t1 values (); |
|
114 |
SET TIMESTAMP=1000000001; |
|
115 |
update t1 set t2=now(); |
|
116 |
SET TIMESTAMP=1000000002; |
|
117 |
insert into t1 (t1,t3) values (default, default); |
|
118 |
select * from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
119 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
120 |
show create table t1; |
121 |
show columns from t1; |
|
122 |
drop table t1; |
|
123 |
||
124 |
create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp); |
|
125 |
SET TIMESTAMP=1000000002; |
|
126 |
insert into t1 values (); |
|
127 |
SET TIMESTAMP=1000000003; |
|
128 |
update t1 set t2=now(); |
|
129 |
SET TIMESTAMP=1000000003; |
|
130 |
insert into t1 (t1,t3) values (default, default); |
|
131 |
select * from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
132 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
133 |
show create table t1; |
134 |
show columns from t1; |
|
135 |
drop table t1; |
|
136 |
||
137 |
create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime); |
|
138 |
SET TIMESTAMP=1000000004; |
|
139 |
insert into t1 values (); |
|
140 |
select * from t1; |
|
141 |
SET TIMESTAMP=1000000005; |
|
142 |
update t1 set t2=now(); |
|
143 |
SET TIMESTAMP=1000000005; |
|
144 |
insert into t1 (t1) values (default); |
|
145 |
select * from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
146 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
147 |
show create table t1; |
148 |
show columns from t1; |
|
149 |
drop table t1; |
|
150 |
||
151 |
create table t1 (t1 timestamp default now() on update now(), t2 datetime); |
|
152 |
SET TIMESTAMP=1000000006; |
|
153 |
insert into t1 values (); |
|
154 |
select * from t1; |
|
155 |
SET TIMESTAMP=1000000007; |
|
156 |
update t1 set t2=now(); |
|
157 |
SET TIMESTAMP=1000000007; |
|
158 |
insert into t1 (t1) values (default); |
|
159 |
select * from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
160 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
161 |
show create table t1; |
162 |
show columns from t1; |
|
163 |
drop table t1; |
|
164 |
||
165 |
create table t1 (t1 timestamp, t2 datetime, t3 timestamp); |
|
166 |
SET TIMESTAMP=1000000007; |
|
167 |
insert into t1 values (); |
|
168 |
select * from t1; |
|
169 |
SET TIMESTAMP=1000000008; |
|
170 |
update t1 set t2=now(); |
|
171 |
SET TIMESTAMP=1000000008; |
|
172 |
insert into t1 (t1,t3) values (default, default); |
|
173 |
select * from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
174 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
175 |
show create table t1; |
176 |
show columns from t1; |
|
177 |
drop table t1; |
|
178 |
||
179 |
# Let us test if CURRENT_TIMESTAMP also works well as default value |
|
180 |
# (Of course NOW and CURRENT_TIMESTAMP are same for parser but still just |
|
181 |
# for demonstartion.) |
|
182 |
create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime); |
|
183 |
SET TIMESTAMP=1000000009; |
|
184 |
insert into t1 values (); |
|
185 |
select * from t1; |
|
186 |
SET TIMESTAMP=1000000010; |
|
187 |
update t1 set t2=now(); |
|
188 |
SET TIMESTAMP=1000000011; |
|
189 |
insert into t1 (t1) values (default); |
|
190 |
select * from t1; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
191 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
192 |
show create table t1; |
193 |
show columns from t1; |
|
194 |
truncate table t1; |
|
195 |
||
196 |
#
|
|
197 |
# Let us test some cases when auto-set should be disabled or influence |
|
198 |
# on server behavior in some other way. |
|
199 |
#
|
|
200 |
||
201 |
# Update statement that explicitly sets field should not auto-set it. |
|
202 |
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); |
|
203 |
SET TIMESTAMP=1000000012; |
|
204 |
update t1 set t1= '2004-04-02 00:00:00'; |
|
205 |
select * from t1; |
|
206 |
# The same for multi updates |
|
1108.1.1
by Brian Aker
Remove multi-update from parser/tests. |
207 |
update t1 set t1.t1= '2004-04-03 00:00:00'; |
1
by brian
clean slate |
208 |
select * from t1; |
209 |
drop table t1; |
|
210 |
||
211 |
# Now let us test replace it should behave exactly like delete+insert |
|
212 |
# Case where optimization is possible DEFAULT = ON UPDATE |
|
213 |
create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int); |
|
214 |
insert into t1 values (1, '2004-04-01 00:00:00', 10); |
|
215 |
SET TIMESTAMP=1000000013; |
|
216 |
replace into t1 set pk = 1, bulk= 20; |
|
217 |
select * from t1; |
|
218 |
drop table t1; |
|
219 |
# Case in which there should not be optimisation |
|
220 |
create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int); |
|
221 |
insert into t1 values (1, '2004-04-01 00:00:00', 10); |
|
222 |
SET TIMESTAMP=1000000014; |
|
223 |
replace into t1 set pk = 1, bulk= 20; |
|
224 |
select * from t1; |
|
225 |
drop table t1; |
|
226 |
# Other similar case |
|
227 |
create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int); |
|
228 |
insert into t1 values (1, '2004-04-01 00:00:00', 10); |
|
229 |
SET TIMESTAMP=1000000015; |
|
230 |
replace into t1 set pk = 1, bulk= 20; |
|
231 |
select * from t1; |
|
232 |
drop table t1; |
|
233 |
||
234 |
# Let us test alter now |
|
235 |
create table t1 (t1 timestamp default current_timestamp on update current_timestamp); |
|
236 |
insert into t1 values ('2004-04-01 00:00:00'); |
|
237 |
SET TIMESTAMP=1000000016; |
|
238 |
alter table t1 add i int default 10; |
|
239 |
select * from t1; |
|
240 |
drop table t1; |
|
241 |
||
242 |
#
|
|
243 |
# Test for TIMESTAMP columns which are able to store NULLs |
|
244 |
#
|
|
245 |
||
246 |
# Unlike for default TIMESTAMP fields we don't interpret first field |
|
247 |
# in this table as TIMESTAMP with DEFAULT NOW() ON UPDATE NOW() properties.
|
|
248 |
create table t1 (a timestamp null, b timestamp null);
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
249 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
250 |
show create table t1;
|
251 |
insert into t1 values (NULL, NULL);
|
|
252 |
SET TIMESTAMP=1000000017;
|
|
253 |
insert into t1 values ();
|
|
254 |
select * from t1;
|
|
255 |
drop table t1;
|
|
256 |
||
257 |
# But explicit auto-set properties still should be OK.
|
|
258 |
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
259 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
260 |
show create table t1;
|
261 |
insert into t1 values (NULL, NULL);
|
|
262 |
SET TIMESTAMP=1000000018;
|
|
263 |
insert into t1 values ();
|
|
264 |
select * from t1;
|
|
265 |
drop table t1;
|
|
266 |
||
267 |
# It is also OK to specify NULL as default explicitly for such fields.
|
|
268 |
# This is also a test for bug #2464, DEFAULT keyword in INSERT statement
|
|
269 |
# should return default value for column.
|
|
270 |
||
271 |
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00'); |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
272 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/ |
1
by brian
clean slate |
273 |
show create table t1;
|
274 |
insert into t1 values (NULL, NULL);
|
|
275 |
insert into t1 values (DEFAULT, DEFAULT);
|
|
276 |
select * from t1;
|
|
277 |
drop table t1;
|
|
278 |
||
279 |
#
|
|
280 |
# Let us test behavior of ALTER TABLE when it converts columns
|
|
281 |
# containing NULL to TIMESTAMP columns.
|
|
282 |
#
|
|
283 |
create table t1 (a bigint, b bigint);
|
|
284 |
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
|
|
285 |
set timestamp=1000000019;
|
|
286 |
alter table t1 modify a timestamp, modify b timestamp;
|
|
287 |
select * from t1;
|
|
288 |
drop table t1;
|
|
289 |
||
290 |
#
|
|
291 |
# Test for bug #4131, TIMESTAMP columns missing minutes and seconds when
|
|
292 |
# using GROUP BY in @@new=1 mode.
|
|
293 |
#
|
|
294 |
create table t1 (a char(2), t timestamp);
|
|
295 |
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'), |
|
296 |
('b', '2004-02-01 00:00:00'); |
|
297 |
select max(t) from t1 group by a;
|
|
298 |
drop table t1;
|
|
299 |
||
300 |
#
|
|
301 |
# Bug#7806 - insert on duplicate key and auto-update of timestamp
|
|
302 |
#
|
|
303 |
create table t1 (a int auto_increment primary key, b int, c timestamp);
|
|
304 |
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'), |
|
305 |
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03'); |
|
306 |
select * from t1;
|
|
307 |
update t1 set b = 2, c = c where a = 2;
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
308 |
--sorted_result
|
1
by brian
clean slate |
309 |
select * from t1;
|
310 |
insert into t1 (a) values (4);
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
311 |
--sorted_result
|
1
by brian
clean slate |
312 |
select * from t1;
|
313 |
update t1 set c = '2004-04-04 04:04:04' where a = 4; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
314 |
--sorted_result
|
1
by brian
clean slate |
315 |
select * from t1;
|
316 |
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
317 |
--sorted_result
|
1
by brian
clean slate |
318 |
select * from t1;
|
319 |
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'), |
|
320 |
(6, '2006-06-06 06:06:06') on duplicate key update b = 4; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
321 |
--sorted_result
|
1
by brian
clean slate |
322 |
select * from t1;
|
323 |
drop table t1;
|
|
324 |
||
325 |
# End of 4.1 tests
|
|
326 |
||
327 |
CREATE TABLE t1 (
|
|
506
by Brian Aker
Added back more tests. |
328 |
`id` int NOT NULL auto_increment,
|
1
by brian
clean slate |
329 |
`username` varchar(80) NOT NULL default '',
|
506
by Brian Aker
Added back more tests. |
330 |
`posted_on` timestamp NOT NULL default now(),
|
1
by brian
clean slate |
331 |
PRIMARY KEY (`id`)
|
1063.9.43
by Stewart Smith
type_timestamp.test for MyISAM as temp only. Must use non-temporary table as we're testing I_S and I_S doesn't do temp tables. |
332 |
) AUTO_INCREMENT=1;
|
1
by brian
clean slate |
333 |
|
334 |
show fields from t1;
|
|
1273.19.5
by Brian Aker
Remove the old columns I_S table. |
335 |
select is_nullable from data_dictionary.columns where TABLE_NAME='t1' and COLUMN_NAME='posted_on'; |
1
by brian
clean slate |
336 |
drop table t1; |