1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
|
SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a datetime(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6.
create table t1 (a datetime(3), key(a));
insert t1 values ('2010-12-11 00:20:03.1234');
insert t1 values ('2010-12-11 15:47:11.1234');
insert t1 values (20101211010203.45678);
insert t1 values (20101211030405.789e0);
insert t1 values (99991231235959e1);
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select truncate(a, 6) from t1;
truncate(a, 6)
0.000000
20101211002003.120000
20101211010203.457031
20101211030405.790000
20101211154711.120000
select a DIV 1 from t1;
a DIV 1
0
20101211002003
20101211010203
20101211030405
20101211154711
select group_concat(distinct a) from t1;
group_concat(distinct a)
0000-00-00 00:00:00.000,2010-12-11 00:20:03.123,2010-12-11 01:02:03.456,2010-12-11 03:04:05.789,2010-12-11 15:47:11.123
alter table t1 engine=innodb;
select * from t1 order by a;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select * from t1 order by a+0;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
drop table t1;
create table t1 (a datetime(4)) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
select * from t1;
a
2010-12-11 01:02:03.4567
select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456';
extract(microsecond from a + interval 100 microsecond)
456800
select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
a
2010-12-11 01:02:03.4567
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` datetime(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
show columns from t1;
Field Type Null Key Default Extra
a datetime(4) YES NULL
select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1';
table_name t1
column_name a
column_default NULL
is_nullable YES
data_type datetime
character_maximum_length NULL
character_octet_length NULL
numeric_precision NULL
numeric_scale NULL
datetime_precision 4
character_set_name NULL
collation_name NULL
column_type datetime(4)
column_key
extra
select a, a+interval 9876543 microsecond from t1;
a a+interval 9876543 microsecond
2010-12-11 01:02:03.4567 2010-12-11 01:02:13.333243
update t1 set a=a+interval 9876543 microsecond;
select * from t1;
a
2010-12-11 01:02:13.3332
select a, a + interval 2 year from t1;
a a + interval 2 year
2010-12-11 01:02:13.3332 2012-12-11 01:02:13.3332
insert t1 select a + interval 2 year from t1;
select * from t1;
a
2010-12-11 01:02:13.3332
2012-12-11 01:02:13.3332
delete from t1 where a < 20110101;
select * from t1;
a
2012-12-11 01:02:13.3332
create table t2 select * from t1;
create table t3 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` datetime(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` datetime(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t2, t3;
insert t1 values ('2010-12-13 14:15:16.222222');
select a, a+0, a-1, a*1, a/2 from t1;
a a+0 a-1 a*1 a/2
2012-12-11 01:02:13.3332 20121211010213.3332 20121211010212.3332 20121211010213.3332 10060605505106.66660000
2010-12-13 14:15:16.2222 20101213141516.2222 20101213141515.2222 20101213141516.2222 10050606570758.11110000
select max(a), min(a), sum(a), avg(a) from t1;
max(a) min(a) sum(a) avg(a)
2012-12-11 01:02:13.3332 2010-12-13 14:15:16.2222 40222424151729.5554 20111212075864.77770000
create table t2 select a, a+0, a-1, a*1, a/2 from t1;
create table t3 select max(a), min(a), sum(a), avg(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` datetime(4) DEFAULT NULL,
`a+0` decimal(25,4) DEFAULT NULL,
`a-1` decimal(25,4) DEFAULT NULL,
`a*1` decimal(25,4) DEFAULT NULL,
`a/2` decimal(28,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`max(a)` datetime(4) DEFAULT NULL,
`min(a)` datetime(4) DEFAULT NULL,
`sum(a)` decimal(46,4) DEFAULT NULL,
`avg(a)` decimal(28,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;
create table t1 (f0_datetime datetime(0), f1_datetime datetime(1), f2_datetime datetime(2), f3_datetime datetime(3), f4_datetime datetime(4), f5_datetime datetime(5), f6_datetime datetime(6));
insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432');
select * from t1;
f0_datetime 2010-11-12 11:14:17
f1_datetime 2010-11-12 11:14:17.7
f2_datetime 2010-11-12 11:14:17.76
f3_datetime 2010-11-12 11:14:17.765
f4_datetime 2010-11-12 11:14:17.7654
f5_datetime 2010-11-12 11:14:17.76543
f6_datetime 2010-11-12 11:14:17.765432
select cast(f0_datetime as time(4)) time4_f0_datetime, cast(f1_datetime as datetime(3)) datetime3_f1_datetime, cast(f2_datetime as date) date_f2_datetime, cast(f4_datetime as double) double_f3_datetime, cast(f4_datetime as decimal(40,5)) decimal5_f4_datetime, cast(f5_datetime as signed) bigint_f5_datetime, cast(f6_datetime as char(255)) varchar_f6_datetime from t1;
time4_f0_datetime 11:14:17.0000
datetime3_f1_datetime 2010-11-12 11:14:17.700
date_f2_datetime 2010-11-12
double_f3_datetime 20101112111417.766
decimal5_f4_datetime 20101112111417.76540
bigint_f5_datetime 20101112111417
varchar_f6_datetime 2010-11-12 11:14:17.765432
create table t2 (time4_f0_datetime time(4), datetime3_f1_datetime datetime(3), date_f2_datetime date, double_f3_datetime double, decimal5_f4_datetime decimal(40,5), bigint_f5_datetime bigint, varchar_f6_datetime varchar(255));
insert t2 select * from t1;
Warnings:
Level Note
Code 1265
Message Data truncated for column 'time4_f0_datetime' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_datetime' at row 1
select * from t2;
time4_f0_datetime 11:14:17.0000
datetime3_f1_datetime 2010-11-12 11:14:17.700
date_f2_datetime 2010-11-12
double_f3_datetime 20101112111417.766
decimal5_f4_datetime 20101112111417.76540
bigint_f5_datetime 20101112111417
varchar_f6_datetime 2010-11-12 11:14:17.765432
alter table t1 change f0_datetime time4_f0_datetime time(4), change f1_datetime datetime3_f1_datetime datetime(3), change f2_datetime date_f2_datetime date, change f3_datetime double_f3_datetime double, change f4_datetime decimal5_f4_datetime decimal(40,5), change f5_datetime bigint_f5_datetime bigint, change f6_datetime varchar_f6_datetime varchar(255);
Warnings:
Level Note
Code 1265
Message Data truncated for column 'time4_f0_datetime' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_datetime' at row 1
select * from t1;
time4_f0_datetime 11:14:17.0000
datetime3_f1_datetime 2010-11-12 11:14:17.700
date_f2_datetime 2010-11-12
double_f3_datetime 20101112111417.766
decimal5_f4_datetime 20101112111417.76540
bigint_f5_datetime 20101112111417
varchar_f6_datetime 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_datetime datetime(0), modify datetime3_f1_datetime datetime(1), modify date_f2_datetime datetime(2), modify double_f3_datetime datetime(3), modify decimal5_f4_datetime datetime(4), modify bigint_f5_datetime datetime(5), modify varchar_f6_datetime datetime(6);
select * from t1;
time4_f0_datetime 2001-02-03 11:14:17
datetime3_f1_datetime 2010-11-12 11:14:17.7
date_f2_datetime 2010-11-12 00:00:00.00
double_f3_datetime 2010-11-12 11:14:17.766
decimal5_f4_datetime 2010-11-12 11:14:17.7654
bigint_f5_datetime 2010-11-12 11:14:17.00000
varchar_f6_datetime 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
select * from t1;
time4_f0_datetime 2001-02-03 11:14:17
datetime3_f1_datetime 2010-11-12 11:14:17.7
date_f2_datetime 2010-11-12 00:00:00.00
double_f3_datetime 2010-11-12 11:14:17.765
decimal5_f4_datetime 2010-11-12 11:14:17.7654
bigint_f5_datetime 2010-11-12 11:14:17.00000
varchar_f6_datetime 2010-11-12 11:14:17.765432
drop table t1, t2;
create table t1 (a datetime(6), b datetime(6));
create procedure foo(x datetime, y datetime(4)) insert into t1 values (x, y);
call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
select * from t1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
create procedure bar(a int, c datetime(5))
begin
declare b datetime(4);
set b = c + interval a microsecond;
insert t1 values (b, c + interval a microsecond);
end|
call bar(1111111, '2011-01-02 3:4:5.123456');
select * from t1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop procedure foo;
drop procedure bar;
create function xyz(s char(20)) returns datetime(4)
return addtime('2010-10-10 10:10:10.101010', s);
select xyz('1:1:1.010101');
xyz('1:1:1.010101')
2010-10-10 11:11:11.1111
drop function xyz;
create view v1 as select * from t1 group by a,b;
select * from v1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
show columns from v1;
Field Type Null Key Default Extra
a datetime(6) YES NULL
b datetime(6) YES NULL
create table t2 select * from v1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` datetime(6) DEFAULT NULL,
`b` datetime(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
SET timestamp=DEFAULT;
CREATE TABLE t1 (
taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00',
id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id,taken),
KEY taken (taken)
)
PARTITION BY RANGE (to_days(taken))
(
PARTITION p01 VALUES LESS THAN (732920),
PARTITION p02 VALUES LESS THAN (732950),
PARTITION p03 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES
('2006-09-27 21:50:01.123456',0),
('2006-09-27 21:50:01.123456',1),
('2006-09-27 21:50:01.123456',2),
('2006-09-28 21:50:01.123456',3),
('2006-09-29 21:50:01.123456',4),
('2006-09-29 21:50:01.123456',5),
('2006-09-30 21:50:01.123456',6),
('2006-10-01 21:50:01.123456',7),
('2006-10-02 21:50:01.123456',8),
('2006-10-02 21:50:01.123456',9);
SELECT id,to_days(taken) FROM t1 order by 2;
id to_days(taken)
0 732946
1 732946
2 732946
3 732947
5 732948
4 732948
6 732949
7 732950
8 732951
9 732951
CREATE TABLE t2 (
taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00',
id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id,taken),
KEY taken (taken)
)
PARTITION BY RANGE (extract(microsecond from taken))
(
PARTITION p01 VALUES LESS THAN (123000),
PARTITION p02 VALUES LESS THAN (500000),
PARTITION p03 VALUES LESS THAN MAXVALUE);
INSERT INTO t2 VALUES
('2006-09-27 21:50:01',0),
('2006-09-27 21:50:01.1',1),
('2006-09-27 21:50:01.12',2),
('2006-09-28 21:50:01.123',3),
('2006-09-29 21:50:01.1234',4),
('2006-09-29 21:50:01.12345',5),
('2006-09-30 21:50:01.123456',6),
('2006-10-01 21:50:01.56',7),
('2006-10-02 21:50:01.567',8),
('2006-10-02 21:50:01.5678',9);
select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2');
table_name partition_name partition_method partition_expression partition_description table_rows
t1 p01 RANGE to_days(taken) 732920 0
t1 p02 RANGE to_days(taken) 732950 7
t1 p03 RANGE to_days(taken) MAXVALUE 3
t2 p01 RANGE extract(microsecond from taken) 123000 3
t2 p02 RANGE extract(microsecond from taken) 500000 4
t2 p03 RANGE extract(microsecond from taken) MAXVALUE 3
drop table t1, t2;
create table t1 (a datetime, b datetime(6));
insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912');
update t1 set b=a;
select * from t1;
a b
2010-01-02 03:04:05 2010-01-02 03:04:05.000000
alter table t1 modify b datetime, modify a datetime(6);
select * from t1;
a b
2010-01-02 03:04:05.000000 2010-01-02 03:04:05
drop table t1;
#
# MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query
#
SET @@time_zone='+00:00';
CREATE TABLE t1 (a DATETIME(4) NOT NULL);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00');
SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1;
UNIX_TIMESTAMP(a)
NULL
978307200.0000
DROP TABLE t1;
SET @@time_zone=DEFAULT;
|
