release/src/router/mysql/mysql-test/t/type_timestamp.test

   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"
  10 set time_zone="+03:00";
  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);
  21 insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
  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(2), t4 timestamp(4), t6 timestamp(6),
  71                  t8 timestamp(8), t10 timestamp(10), t12 timestamp(12),
  72                  t14 timestamp(14));
  73 insert t1 values (0,0,0,0,0,0,0),
  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);
  86 insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000);
  87 select ix+0 from t1;
  88 truncate table t1;
  89 insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000");
  90 select ix+0 from t1;
  91 truncate table t1;
  92 insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
  93 select ix+0 from t1;
  94 drop table t1;
  95
  96 #
  97 # Test for TIMESTAMP column with default now() and on update now() clauses
  98 #
  99
 100 # These statements should fail.
 101 --error 1293
 102 create table t1 (t1 timestamp, t2 timestamp default now());
 103 --error 1293
 104 create table t1 (t1 timestamp, t2 timestamp on update now());
 105 --error 1293
 106 create table t1 (t1 timestamp, t2 timestamp default now() on update now());
 107 --error 1293
 108 create table t1 (t1 timestamp default now(), t2 timestamp on update now());
 109 --error 1293
 110 create table t1 (t1 timestamp on update now(), t2 timestamp default now() on update now());
 111
 112 # Let us test TIMESTAMP auto-update behaviour
 113 # Also we will test behaviour of TIMESTAMP field in SHOW CREATE TABLE and
 114 # behaviour of DEFAULT literal for such fields
 115 create table t1 (t1 timestamp default '2003-01-01 00:00:00', t2 datetime, t3 timestamp);
 116 SET TIMESTAMP=1000000000;
 117 insert into t1 values ();
 118 SET TIMESTAMP=1000000001;
 119 update t1 set t2=now();
 120 SET TIMESTAMP=1000000002;
 121 insert into t1 (t1,t3) values (default, default);
 122 select * from t1;
 123 show create table t1;
 124 show columns from t1;
 125 drop table t1;
 126
 127 create table t1 (t1 timestamp default now(), t2 datetime, t3 timestamp);
 128 SET TIMESTAMP=1000000002;
 129 insert into t1 values ();
 130 SET TIMESTAMP=1000000003;
 131 update t1 set t2=now();
 132 SET TIMESTAMP=1000000003;
 133 insert into t1 (t1,t3) values (default, default);
 134 select * from t1;
 135 show create table t1;
 136 show columns from t1;
 137 drop table t1;
 138
 139 create table t1 (t1 timestamp default '2003-01-01 00:00:00' on update now(), t2 datetime);
 140 SET TIMESTAMP=1000000004;
 141 insert into t1 values ();
 142 select * from t1;
 143 SET TIMESTAMP=1000000005;
 144 update t1 set t2=now();
 145 SET TIMESTAMP=1000000005;
 146 insert into t1 (t1) values (default);
 147 select * from t1;
 148 show create table t1;
 149 show columns from t1;
 150 drop table t1;
 151
 152 create table t1 (t1 timestamp default now() on update now(), t2 datetime);
 153 SET TIMESTAMP=1000000006;
 154 insert into t1 values ();
 155 select * from t1;
 156 SET TIMESTAMP=1000000007;
 157 update t1 set t2=now();
 158 SET TIMESTAMP=1000000007;
 159 insert into t1 (t1) values (default);
 160 select * from t1;
 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;
 174 show create table t1;
 175 show columns from t1;
 176 drop table t1;
 177
 178 # Let us test if CURRENT_TIMESTAMP also works well as default value
 179 # (Of course NOW and CURRENT_TIMESTAMP are same for parser but still just
 180 # for demonstartion.)
 181 create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime);
 182 SET TIMESTAMP=1000000009;
 183 insert into t1 values ();
 184 select * from t1;
 185 SET TIMESTAMP=1000000010;
 186 update t1 set t2=now();
 187 SET TIMESTAMP=1000000011;
 188 insert into t1 (t1) values (default);
 189 select * from t1;
 190 show create table t1;
 191 show columns from t1;
 192 truncate table t1;
 193
 194 #
 195 # Let us test some cases when auto-set should be disabled or influence
 196 # on server behavior in some other way.
 197 #
 198
 199 # Update statement that explicitly sets field should not auto-set it.
 200 insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
 201 SET TIMESTAMP=1000000012;
 202 update t1 set t1= '2004-04-02 00:00:00';
 203 select * from t1;
 204 # The same for multi updates
 205 update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
 206 select * from t1;
 207 drop table t1;
 208
 209 # Now let us test replace it should behave exactly like delete+insert
 210 # Case where optimization is possible DEFAULT = ON UPDATE
 211 create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int);
 212 insert into t1 values (1, '2004-04-01 00:00:00', 10);
 213 SET TIMESTAMP=1000000013;
 214 replace into t1 set pk = 1, bulk= 20;
 215 select * from t1;
 216 drop table t1;
 217 # Case in which there should not be optimisation
 218 create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
 219 insert into t1 values (1, '2004-04-01 00:00:00', 10);
 220 SET TIMESTAMP=1000000014;
 221 replace into t1 set pk = 1, bulk= 20;
 222 select * from t1;
 223 drop table t1;
 224 # Other similar case
 225 create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int);
 226 insert into t1 values (1, '2004-04-01 00:00:00', 10);
 227 SET TIMESTAMP=1000000015;
 228 replace into t1 set pk = 1, bulk= 20;
 229 select * from t1;
 230 drop table t1;
 231
 232 # Let us test alter now
 233 create table t1 (t1 timestamp default current_timestamp on update current_timestamp);
 234 insert into t1 values ('2004-04-01 00:00:00');
 235 SET TIMESTAMP=1000000016;
 236 alter table t1 add i int default 10;
 237 select * from t1;
 238 drop table t1;
 239
 240 #
 241 # Test for TIMESTAMP columns which are able to store NULLs
 242 #
 243
 244 # Unlike for default TIMESTAMP fields we don't interpret first field
 245 # in this table as TIMESTAMP with DEFAULT NOW() ON UPDATE NOW() properties.
 246 create table t1 (a timestamp null, b timestamp null);
 247 show create table t1;
 248 insert into t1 values (NULL, NULL);
 249 SET TIMESTAMP=1000000017;
 250 insert into t1 values ();
 251 select * from t1;
 252 drop table t1;
 253
 254 # But explicit auto-set properties still should be OK.
 255 create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
 256 show create table t1;
 257 insert into t1 values (NULL, NULL);
 258 SET TIMESTAMP=1000000018;
 259 insert into t1 values ();
 260 select * from t1;
 261 drop table t1;
 262
 263 # It is also OK to specify NULL as default explicitly for such fields.
 264 # This is also a test for bug #2464, DEFAULT keyword in INSERT statement
 265 # should return default value for column.
 266
 267 create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
 268 show create table t1;
 269 insert into t1 values (NULL, NULL);
 270 insert into t1 values (DEFAULT, DEFAULT);
 271 select * from t1;
 272 drop table t1;
 273
 274 #
 275 # Let us test behavior of ALTER TABLE when it converts columns
 276 # containing NULL to TIMESTAMP columns.
 277 #
 278 create table t1 (a bigint, b bigint);
 279 insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
 280 set timestamp=1000000019;
 281 alter table t1 modify a timestamp, modify b timestamp;
 282 select * from t1;
 283 drop table t1;
 284
 285 #
 286 # Test for bug #4131, TIMESTAMP columns missing minutes and seconds when
 287 # using GROUP BY in @@new=1 mode.
 288 #
 289 create table t1 (a char(2), t timestamp);
 290 insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
 291                       ('b', '2004-02-01 00:00:00');
 292 select max(t) from t1 group by a;
 293 drop table t1;
 294
 295 #
 296 # Test for bug #7418 "TIMESTAMP not always converted to DATETIME in MAXDB
 297 # mode". TIMESTAMP columns should be converted DATETIME columns in MAXDB
 298 # mode regardless of whether a display width is given.
 299 #
 300 set sql_mode='maxdb';
 301 create table t1 (a timestamp, b timestamp(19));
 302 show create table t1;
 303 # restore default mode
 304 set sql_mode='';
 305 drop table t1;
 306
 307 #
 308 # Bug#7806 - insert on duplicate key and auto-update of timestamp
 309 #
 310 create table t1 (a int auto_increment primary key, b int, c timestamp);
 311 insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
 312   (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
 313 select * from t1;
 314 update t1 set b = 2, c = c where a = 2;
 315 select * from t1;
 316 insert into t1 (a) values (4);
 317 select * from t1;
 318 update t1 set c = '2004-04-04 04:04:04' where a = 4;
 319 select * from t1;
 320 insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
 321 select * from t1;
 322 insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
 323   (6, '2006-06-06 06:06:06') on duplicate key update b = 4;
 324 select * from t1;
 325 drop table t1;
 326
 327 --echo End of 4.1 tests
 328
 329 # Restore timezone to default
 330 set time_zone= @@global.time_zone;
 331
 332 CREATE TABLE t1 (
 333 `id` int(11) NOT NULL auto_increment,
 334 `username` varchar(80) NOT NULL default '',
 335 `posted_on` timestamp NOT NULL default '0000-00-00 00:00:00',
 336 PRIMARY KEY (`id`)
 337 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
 338
 339 show fields from t1;
 340 select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on';
 341 drop table t1;
 342
 343 #
 344 # Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME()
 345 #
 346
 347 CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 348                   f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 349                   f3 TIMESTAMP);
 350 INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00");
 351 INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL);
 352 INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL));
 353 INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999'));
 354 INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL));
 355 UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1;
 356 SELECT f1,f2-f3 FROM t1;
 357 DROP TABLE t1;
 358
 359 --echo End of 5.0 tests
 360
 361 --echo #
 362 --echo # Bug #55779: select does not work properly in mysql server
 363 --echo #             Version "5.1.42 SUSE MySQL RPM"
 364 --echo #
 365
 366 CREATE TABLE t1 (a TIMESTAMP, KEY (a));
 367
 368 INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'),
 369                       ('2000-01-01 00:00:01'), ('2000-01-01 00:00:01');
 370
 371 SELECT a FROM t1 WHERE a >=  20000101000000;
 372 SELECT a FROM t1 WHERE a >= '20000101000000';
 373
 374 DROP TABLE t1;
 375
 376 --echo #
 377 --echo # Bug#50774: failed to get the correct resultset when timestamp values
 378 --echo # are appended with .0
 379 --echo #
 380 CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) );
 381
 382 INSERT INTO t1 VALUES( '2010-02-01 09:31:01' );
 383 INSERT INTO t1 VALUES( '2010-02-01 09:31:02' );
 384 INSERT INTO t1 VALUES( '2010-02-01 09:31:03' );
 385 INSERT INTO t1 VALUES( '2010-02-01 09:31:04' );
 386
 387 SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
 388 SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' <= a;
 389 SELECT * FROM t1 WHERE a <= '2010-02-01 09:31:02.0';
 390 SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' >= a;
 391
 392 --replace_column 1 x 2 x 3 x 5 x 6 x 7 x 8 x 9 x 10 x
 393 EXPLAIN
 394 SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
 395 SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
 396
 397 CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) );
 398
 399 INSERT INTO t2 VALUES( '2010-02-01 09:31:01' );
 400 INSERT INTO t2 VALUES( '2010-02-01 09:31:02' );
 401 INSERT INTO t2 VALUES( '2010-02-01 09:31:03' );
 402 INSERT INTO t2 VALUES( '2010-02-01 09:31:04' );
 403 INSERT INTO t2 VALUES( '2010-02-01 09:31:05' );
 404 INSERT INTO t2 VALUES( '2010-02-01 09:31:06' );
 405 INSERT INTO t2 VALUES( '2010-02-01 09:31:07' );
 406 INSERT INTO t2 VALUES( '2010-02-01 09:31:08' );
 407 INSERT INTO t2 VALUES( '2010-02-01 09:31:09' );
 408 INSERT INTO t2 VALUES( '2010-02-01 09:31:10' );
 409 INSERT INTO t2 VALUES( '2010-02-01 09:31:11' );
 410
 411 --echo # The bug would cause the range optimizer's comparison to use an open
 412 --echo # interval here. This reveals itself only in the number of reads
 413 --echo # performed.
 414 FLUSH STATUS;
 415 --replace_column 1 x 2 x 3 x 5 x 6 x 7 x 8 x 9 x 10 x
 416 EXPLAIN
 417 SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
 418 SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
 419 SHOW STATUS LIKE 'Handler_read_next';
 420
 421 DROP TABLE t1, t2;
 422
 423 --echo End of 5.1 tests