1 2--source include/have_partition.inc 3 4let type=datetime; 5--source include/type_hrtime.inc 6 7# 8# partitioning 9# 10eval CREATE TABLE t1 ( 11 taken $type(5) NOT NULL DEFAULT '0000-00-00 00:00:00', 12 id int(11) NOT NULL DEFAULT '0', 13 PRIMARY KEY (id,taken), 14 KEY taken (taken) 15) 16PARTITION BY RANGE (to_days(taken)) 17( 18PARTITION p01 VALUES LESS THAN (732920), 19PARTITION p02 VALUES LESS THAN (732950), 20PARTITION p03 VALUES LESS THAN MAXVALUE); 21 22INSERT INTO t1 VALUES 23('2006-09-27 21:50:01.123456',0), 24('2006-09-27 21:50:01.123456',1), 25('2006-09-27 21:50:01.123456',2), 26('2006-09-28 21:50:01.123456',3), 27('2006-09-29 21:50:01.123456',4), 28('2006-09-29 21:50:01.123456',5), 29('2006-09-30 21:50:01.123456',6), 30('2006-10-01 21:50:01.123456',7), 31('2006-10-02 21:50:01.123456',8), 32('2006-10-02 21:50:01.123456',9); 33 34SELECT id,to_days(taken) FROM t1 order by 2; 35 36eval CREATE TABLE t2 ( 37 taken $type(5) NOT NULL DEFAULT '0000-00-00 00:00:00', 38 id int(11) NOT NULL DEFAULT '0', 39 PRIMARY KEY (id,taken), 40 KEY taken (taken) 41) 42PARTITION BY RANGE (extract(microsecond from taken)) 43( 44PARTITION p01 VALUES LESS THAN (123000), 45PARTITION p02 VALUES LESS THAN (500000), 46PARTITION p03 VALUES LESS THAN MAXVALUE); 47 48INSERT INTO t2 VALUES 49('2006-09-27 21:50:01',0), 50('2006-09-27 21:50:01.1',1), 51('2006-09-27 21:50:01.12',2), 52('2006-09-28 21:50:01.123',3), 53('2006-09-29 21:50:01.1234',4), 54('2006-09-29 21:50:01.12345',5), 55('2006-09-30 21:50:01.123456',6), 56('2006-10-01 21:50:01.56',7), 57('2006-10-02 21:50:01.567',8), 58('2006-10-02 21:50:01.5678',9); 59 60--sorted_result 61select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2'); 62 63drop table t1, t2; 64 65create table t1 (a datetime, b datetime(6)); 66insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912'); 67update t1 set b=a; 68select * from t1; 69alter table t1 modify b datetime, modify a datetime(6); 70select * from t1; 71drop table t1; 72 73--echo # 74--echo # MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query 75--echo # 76SET @@time_zone='+00:00'; 77CREATE TABLE t1 (a DATETIME(4) NOT NULL); 78INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00'); 79SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1; 80DROP TABLE t1; 81SET @@time_zone=DEFAULT; 82 83 84--echo # 85--echo # Start of 10.4 tests 86--echo # 87 88--echo # 89--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() 90--echo # 91 92CREATE TABLE t1 (a1 DATETIME(6), a2 DATETIME(6) NOT NULL); 93CREATE TABLE t2 AS SELECT 94 ROUND(a1) AS r1, 95 ROUND(a2) AS r2, 96 TRUNCATE(a1,0) AS t1, 97 TRUNCATE(a2,0) AS t2 98FROM t1; 99SHOW CREATE TABLE t2; 100DROP TABLE t2; 101DROP TABLE t1; 102 103 104CREATE TABLE t1 (a DATETIME(6)); 105INSERT INTO t1 VALUES 106('0000-00-00 00:00:00.999999'), 107('0000-00-00 23:59:59.999999'), 108('0000-00-01 00:00:00.999999'), 109('0000-00-01 23:59:59.999999'), 110('0000-00-31 23:59:59.999999'), 111('0000-01-01 00:00:00.999999'), 112('0000-01-01 23:59:59.999999'), 113('0000-01-31 23:59:59.999999'), 114('0000-02-28 23:59:59.999999'), 115('0000-12-31 23:59:59.999999'), 116('0001-01-01 00:00:00.999999'), 117('0001-02-28 23:59:59.999999'), 118('0001-12-31 23:59:59.999999'), 119('0004-02-28 23:59:59.999999'), 120('0004-02-29 23:59:59.999999'), 121('2000-02-29 23:59:59.999999'), 122('2000-12-31 23:59:59.999999'), 123('9999-12-31 23:59:59.999999'); 124SELECT a, TRUNCATE(a,0) FROM t1; 125SELECT a, TRUNCATE(a,1) FROM t1; 126SELECT a, TRUNCATE(a,2) FROM t1; 127SELECT a, TRUNCATE(a,3) FROM t1; 128SELECT a, TRUNCATE(a,4) FROM t1; 129SELECT a, TRUNCATE(a,5) FROM t1; 130SELECT a, TRUNCATE(a,6) FROM t1; 131SELECT a, TRUNCATE(a,7) FROM t1; 132SELECT a, TRUNCATE(a,-1) FROM t1; 133SELECT a, TRUNCATE(a,-6) FROM t1; 134 135SELECT a, ROUND(a) FROM t1; 136SELECT a, ROUND(a,0) FROM t1; 137SELECT a, ROUND(a,1) FROM t1; 138SELECT a, ROUND(a,2) FROM t1; 139SELECT a, ROUND(a,3) FROM t1; 140SELECT a, ROUND(a,4) FROM t1; 141SELECT a, ROUND(a,5) FROM t1; 142SELECT a, ROUND(a,6) FROM t1; 143SELECT a, ROUND(a,7) FROM t1; 144SELECT a, ROUND(a,-1) FROM t1; 145SELECT a, ROUND(a,-6) FROM t1; 146 147DROP TABLE t1; 148 149--echo # 150--echo # MDEV-20984 Possibly wrong result or Assertion `args[0]->type_handler()->mysql_timestamp_type() == MYSQL_TIMESTAMP_DATETIME' failed in Item_func_round::date_op 151--echo # 152 153CREATE TABLE t1 (a DATETIME); 154INSERT INTO t1 VALUES ('1979-01-03 10:33:32'),('2012-12-12 12:12:12'); 155SELECT ROUND(a) AS f FROM t1 GROUP BY a WITH ROLLUP; 156DROP TABLE t1; 157 158 159--echo # 160--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() 161--echo # 162 163CREATE TABLE t1 AS SELECT 164 FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999'), 165 CEILING(TIMESTAMP'2001-01-01 00:00:00.999999'); 166SHOW CREATE TABLE t1; 167DROP TABLE t1; 168 169CREATE TABLE t1 (a DATETIME(6)); 170 171INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999'); 172INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99999'); 173INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999'); 174INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999'); 175INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99'); 176INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9'); 177INSERT INTO t1 VALUES ('9999-12-31 23:59:59.1'); 178INSERT INTO t1 VALUES ('9999-12-31 23:59:59.0'); 179 180INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999999'); 181INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99999'); 182INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9999'); 183INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999'); 184INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99'); 185INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9'); 186INSERT INTO t1 VALUES ('9999-12-30 23:59:59.1'); 187INSERT INTO t1 VALUES ('9999-12-30 23:59:59.0'); 188 189INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999999'); 190INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99999'); 191INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9999'); 192INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999'); 193INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99'); 194INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9'); 195INSERT INTO t1 VALUES ('0999-12-31 23:59:59.1'); 196INSERT INTO t1 VALUES ('0999-12-31 23:59:59.0'); 197 198INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999999'); 199INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99999'); 200INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9999'); 201INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999'); 202INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99'); 203INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9'); 204INSERT INTO t1 VALUES ('0001-12-31 23:59:59.1'); 205INSERT INTO t1 VALUES ('0001-12-31 23:59:59.0'); 206 207 208DELIMITER $$; 209CREATE FUNCTION FLOOR_SP(a DATETIME(6)) RETURNS DATETIME 210BEGIN 211 RETURN 212 CASE 213 WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a 214 ELSE TRUNCATE(a,0) 215 END; 216END; 217$$ 218DELIMITER ;$$ 219 220DELIMITER $$; 221CREATE FUNCTION CEILING_SP(a DATETIME(6)) RETURNS DATETIME 222BEGIN 223 RETURN 224 CASE 225 WHEN TRUNCATE(a,0)=TIMESTAMP'9999-12-31 23:59:59' THEN a 226 WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a 227 ELSE TRUNCATE(a,0)+INTERVAL 1 SECOND 228 END; 229END; 230$$ 231DELIMITER ;$$ 232 233SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a; 234SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a; 235 236DROP FUNCTION FLOOR_SP; 237DROP FUNCTION CEILING_SP; 238 239DROP TABLE t1; 240 241CREATE TABLE t1 (a DATETIME(6)); 242INSERT INTO t1 VALUES ('0000-00-00 23:59:59.999999'); 243INSERT INTO t1 VALUES ('0000-00-01 23:59:59.999999'); 244INSERT INTO t1 VALUES ('0000-01-01 23:59:59.999999'); 245INSERT INTO t1 VALUES ('0001-00-00 23:59:59.999999'); 246SELECT a, FLOOR(a), CEILING(a) FROM t1; 247DROP TABLE t1; 248 249SET sql_mode=ALLOW_INVALID_DATES; 250CREATE TABLE t1 (a DATETIME(6)); 251INSERT INTO t1 VALUES ('2001-02-28 23:59:59.999999'); 252INSERT INTO t1 VALUES ('2001-02-29 23:59:59.999999'); 253SELECT a, FLOOR(a), CEILING(a) FROM t1; 254DROP TABLE t1; 255SET sql_mode=DEFAULT; 256 257--echo # 258--echo # End of 10.4 tests 259--echo # 260