~drizzle-trunk/drizzle/development : contents of mysql-test/suite/parts/r/partition_datetime

~drizzle-trunk/drizzle/development : (revision 1)

1 by brian clean slate	1	create table t1 (a timestamp not null, primary key(a)) engine='MyISAM'
	2	partition by key (a) (
	3	partition pa1 max_rows=20 min_rows=2,
	4	partition pa2 max_rows=30 min_rows=3,
	5	partition pa3 max_rows=30 min_rows=4,
	6	partition pa4 max_rows=40 min_rows=2);
	7	show create table t1;
	8	Table Create Table
	9	t1 CREATE TABLE `t1` (
	10	`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	11	PRIMARY KEY (`a`)
	12	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) /
	13	insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
	14	select * from t1;
	15	a
	16	1975-01-01 21:21:21
	17	1980-10-14 03:03:00
	18	2000-06-15 23:59:00
	19	2020-12-31 12:10:30
	20	select * from t1 where a=19801014030300;
	21	a
	22	1980-10-14 03:03:00
	23	delete from t1 where a=19801014030300;
	24	select * from t1;
	25	a
	26	1975-01-01 21:21:21
	27	2000-06-15 23:59:00
	28	2020-12-31 12:10:30
	29	drop table t1;
	30	create table t2 (a timestamp not null, primary key(a)) engine='MyISAM'
	31	partition by key (a) partitions 12;
	32	show create table t2;
	33	Table Create Table
	34	t2 CREATE TABLE `t2` (
	35	`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	36	PRIMARY KEY (`a`)
	37	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) PARTITIONS 12 /
	38	insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
	39	select * from t2;
	40	a
	41	1975-01-01 00:01:01
	42	1980-10-14 13:14:15
	43	2000-06-15 14:15:16
	44	2020-12-31 10:11:12
	45	select * from t2 where a='1980-10-14 13:14:15';
	46	a
	47	1980-10-14 13:14:15
	48	delete from t2 where a='1980-10-14 13:14:15';
	49	select * from t2;
	50	a
	51	1975-01-01 00:01:01
	52	2000-06-15 14:15:16
	53	2020-12-31 10:11:12
	54	delete from t2;
	55	59 inserts;
	56	insert into t2 values (19710101000000+59);
	57	insert into t2 values (19710101000000+58);
	58	insert into t2 values (19710101000000+57);
	59	insert into t2 values (19710101000000+56);
	60	insert into t2 values (19710101000000+55);
	61	insert into t2 values (19710101000000+54);
	62	insert into t2 values (19710101000000+53);
	63	insert into t2 values (19710101000000+52);
	64	insert into t2 values (19710101000000+51);
65	insert into t2 values (19710101000000+50);
66	insert into t2 values (19710101000000+49);
67	insert into t2 values (19710101000000+48);
68	insert into t2 values (19710101000000+47);
69	insert into t2 values (19710101000000+46);
70	insert into t2 values (19710101000000+45);
71	insert into t2 values (19710101000000+44);
72	insert into t2 values (19710101000000+43);
73	insert into t2 values (19710101000000+42);
74	insert into t2 values (19710101000000+41);
75	insert into t2 values (19710101000000+40);
76	insert into t2 values (19710101000000+39);
77	insert into t2 values (19710101000000+38);
78	insert into t2 values (19710101000000+37);
79	insert into t2 values (19710101000000+36);
80	insert into t2 values (19710101000000+35);
81	insert into t2 values (19710101000000+34);
82	insert into t2 values (19710101000000+33);
83	insert into t2 values (19710101000000+32);
84	insert into t2 values (19710101000000+31);
85	insert into t2 values (19710101000000+30);
86	insert into t2 values (19710101000000+29);
87	insert into t2 values (19710101000000+28);
88	insert into t2 values (19710101000000+27);
89	insert into t2 values (19710101000000+26);
90	insert into t2 values (19710101000000+25);
91	insert into t2 values (19710101000000+24);
92	insert into t2 values (19710101000000+23);
93	insert into t2 values (19710101000000+22);
94	insert into t2 values (19710101000000+21);
95	insert into t2 values (19710101000000+20);
96	insert into t2 values (19710101000000+19);
97	insert into t2 values (19710101000000+18);
98	insert into t2 values (19710101000000+17);
99	insert into t2 values (19710101000000+16);
100	insert into t2 values (19710101000000+15);
101	insert into t2 values (19710101000000+14);
102	insert into t2 values (19710101000000+13);
103	insert into t2 values (19710101000000+12);
104	insert into t2 values (19710101000000+11);
105	insert into t2 values (19710101000000+10);
106	insert into t2 values (19710101000000+9);
107	insert into t2 values (19710101000000+8);
108	insert into t2 values (19710101000000+7);
109	insert into t2 values (19710101000000+6);
110	insert into t2 values (19710101000000+5);
111	insert into t2 values (19710101000000+4);
112	insert into t2 values (19710101000000+3);
113	insert into t2 values (19710101000000+2);
114	insert into t2 values (19710101000000+1);
115	select count(*) from t2;
116	count(*)
117	59
118	select * from t2;
119	a
120	1971-01-01 00:00:01
121	1971-01-01 00:00:02
122	1971-01-01 00:00:03
123	1971-01-01 00:00:04
124	1971-01-01 00:00:05
125	1971-01-01 00:00:06
126	1971-01-01 00:00:07
127	1971-01-01 00:00:08
128	1971-01-01 00:00:09
129	1971-01-01 00:00:10
130	1971-01-01 00:00:11
131	1971-01-01 00:00:12
132	1971-01-01 00:00:13
133	1971-01-01 00:00:14
134	1971-01-01 00:00:15
135	1971-01-01 00:00:16
136	1971-01-01 00:00:17
137	1971-01-01 00:00:18
138	1971-01-01 00:00:19
139	1971-01-01 00:00:20
140	1971-01-01 00:00:21
141	1971-01-01 00:00:22
142	1971-01-01 00:00:23
143	1971-01-01 00:00:24
144	1971-01-01 00:00:25
145	1971-01-01 00:00:26
146	1971-01-01 00:00:27
147	1971-01-01 00:00:28
148	1971-01-01 00:00:29
149	1971-01-01 00:00:30
150	1971-01-01 00:00:31
151	1971-01-01 00:00:32
152	1971-01-01 00:00:33
153	1971-01-01 00:00:34
154	1971-01-01 00:00:35
155	1971-01-01 00:00:36
156	1971-01-01 00:00:37
157	1971-01-01 00:00:38
158	1971-01-01 00:00:39
159	1971-01-01 00:00:40
160	1971-01-01 00:00:41
161	1971-01-01 00:00:42
162	1971-01-01 00:00:43
163	1971-01-01 00:00:44
164	1971-01-01 00:00:45
165	1971-01-01 00:00:46
166	1971-01-01 00:00:47
167	1971-01-01 00:00:48
168	1971-01-01 00:00:49
169	1971-01-01 00:00:50
170	1971-01-01 00:00:51
171	1971-01-01 00:00:52
172	1971-01-01 00:00:53
173	1971-01-01 00:00:54
174	1971-01-01 00:00:55
175	1971-01-01 00:00:56
176	1971-01-01 00:00:57
177	1971-01-01 00:00:58
178	1971-01-01 00:00:59
179	drop table t2;
180	create table t3 (a timestamp not null, primary key(a)) engine='MyISAM'
181	partition by range (month(a)) subpartition by key (a)
182	subpartitions 3 (
183	partition quarter1 values less than (4),
184	partition quarter2 values less than (7),
185	partition quarter3 values less than (10),
186	partition quarter4 values less than (13)
187	);
188	show create table t3;
189	Table Create Table
190	t3 CREATE TABLE `t3` (
191	`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
192	PRIMARY KEY (`a`)
193	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY RANGE (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = MyISAM, PARTITION quarter2 VALUES LESS THAN (7) ENGINE = MyISAM, PARTITION quarter3 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION quarter4 VALUES LESS THAN (13) ENGINE = MyISAM) /
194	12 inserts;
195	insert into t3 values (date_add('1970-01-01 00:00:00',interval 12-1 month));
196	insert into t3 values (date_add('1970-01-01 00:00:00',interval 11-1 month));
197	insert into t3 values (date_add('1970-01-01 00:00:00',interval 10-1 month));
198	insert into t3 values (date_add('1970-01-01 00:00:00',interval 9-1 month));
199	insert into t3 values (date_add('1970-01-01 00:00:00',interval 8-1 month));
200	insert into t3 values (date_add('1970-01-01 00:00:00',interval 7-1 month));
201	insert into t3 values (date_add('1970-01-01 00:00:00',interval 6-1 month));
202	insert into t3 values (date_add('1970-01-01 00:00:00',interval 5-1 month));
203	insert into t3 values (date_add('1970-01-01 00:00:00',interval 4-1 month));
204	insert into t3 values (date_add('1970-01-01 00:00:00',interval 3-1 month));
205	insert into t3 values (date_add('1970-01-01 00:00:00',interval 2-1 month));
206	insert into t3 values (date_add('1970-01-01 00:00:00',interval 1-1 month));
207	Warnings:
208	Warning 1264 Out of range value for column 'a' at row 1
209	select count(*) from t3;
210	count(*)
211	12
212	select * from t3;
213	a
214	0000-00-00 00:00:00
215	1970-02-01 00:00:00
216	1970-03-01 00:00:00
217	1970-04-01 00:00:00
218	1970-05-01 00:00:00
219	1970-06-01 00:00:00
220	1970-07-01 00:00:00
221	1970-08-01 00:00:00
222	1970-09-01 00:00:00
223	1970-10-01 00:00:00
224	1970-11-01 00:00:00
225	1970-12-01 00:00:00
226	drop table t3;
227	create table t4 (a timestamp not null, primary key(a)) engine='MyISAM'
228	partition by list (month(a)) subpartition by key (a)
229	subpartitions 3 (
230	partition quarter1 values in (0,1,2,3),
231	partition quarter2 values in (4,5,6),
232	partition quarter3 values in (7,8,9),
233	partition quarter4 values in (10,11,12)
234	);
235	show create table t4;
236	Table Create Table
237	t4 CREATE TABLE `t4` (
238	`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
239	PRIMARY KEY (`a`)
240	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY LIST (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (0,1,2,3) ENGINE = MyISAM, PARTITION quarter2 VALUES IN (4,5,6) ENGINE = MyISAM, PARTITION quarter3 VALUES IN (7,8,9) ENGINE = MyISAM, PARTITION quarter4 VALUES IN (10,11,12) ENGINE = MyISAM) /
241	12 inserts;
242	insert into t4 values (date_add('1970-01-01 00:00:00',interval 12-1 month));
243	insert into t4 values (date_add('1970-01-01 00:00:00',interval 11-1 month));
244	insert into t4 values (date_add('1970-01-01 00:00:00',interval 10-1 month));
245	insert into t4 values (date_add('1970-01-01 00:00:00',interval 9-1 month));
246	insert into t4 values (date_add('1970-01-01 00:00:00',interval 8-1 month));
247	insert into t4 values (date_add('1970-01-01 00:00:00',interval 7-1 month));
248	insert into t4 values (date_add('1970-01-01 00:00:00',interval 6-1 month));
249	insert into t4 values (date_add('1970-01-01 00:00:00',interval 5-1 month));
250	insert into t4 values (date_add('1970-01-01 00:00:00',interval 4-1 month));
251	insert into t4 values (date_add('1970-01-01 00:00:00',interval 3-1 month));
252	insert into t4 values (date_add('1970-01-01 00:00:00',interval 2-1 month));
253	insert into t4 values (date_add('1970-01-01 00:00:00',interval 1-1 month));
254	Warnings:
255	Warning 1264 Out of range value for column 'a' at row 1
256	select count(*) from t4;
257	count(*)
258	12
259	select * from t4;
260	a
261	0000-00-00 00:00:00
262	1970-02-01 00:00:00
263	1970-03-01 00:00:00
264	1970-04-01 00:00:00
265	1970-05-01 00:00:00
266	1970-06-01 00:00:00
267	1970-07-01 00:00:00
268	1970-08-01 00:00:00
269	1970-09-01 00:00:00
270	1970-10-01 00:00:00
271	1970-11-01 00:00:00
272	1970-12-01 00:00:00
273	drop table t4;
274	create table t1 (a date not null, primary key(a)) engine='MyISAM'
275	partition by key (a) (
276	partition pa1 max_rows=20 min_rows=2,
277	partition pa2 max_rows=30 min_rows=3,
278	partition pa3 max_rows=30 min_rows=4,
279	partition pa4 max_rows=40 min_rows=2);
280	show create table t1;
281	Table Create Table
282	t1 CREATE TABLE `t1` (
283	`a` date NOT NULL,
284	PRIMARY KEY (`a`)
285	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) /
286	insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
287	select * from t1;
288	a
289	1975-01-01
290	1980-10-14
291	2000-06-15
292	2020-12-31
293	select * from t1 where a=19801014;
294	a
295	1980-10-14
296	delete from t1 where a=19801014;
297	select * from t1;
298	a
299	1975-01-01
300	2000-06-15
301	2020-12-31
302	drop table t1;
303	create table t2 (a date not null, primary key(a)) engine='MyISAM'
304	partition by key (a) partitions 12;
305	show create table t2;
306	Table Create Table
307	t2 CREATE TABLE `t2` (
308	`a` date NOT NULL,
309	PRIMARY KEY (`a`)
310	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) PARTITIONS 12 /
311	insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
312	select * from t2;
313	a
314	1975-01-01
315	1980-10-14
316	2000-06-15
317	2020-12-31
318	select * from t2 where a='1980-10-14';
319	a
320	1980-10-14
321	delete from t2 where a='1980-10-14';
322	select * from t2;
323	a
324	1975-01-01
325	2000-06-15
326	2020-12-31
327	delete from t2;
328	28 inserts;
329	insert into t2 values (19700101+28-1);
330	insert into t2 values (19700201+28-1);
331	insert into t2 values (19700301+28-1);
332	insert into t2 values (19700101+27-1);
333	insert into t2 values (19700201+27-1);
334	insert into t2 values (19700301+27-1);
335	insert into t2 values (19700101+26-1);
336	insert into t2 values (19700201+26-1);
337	insert into t2 values (19700301+26-1);
338	insert into t2 values (19700101+25-1);
339	insert into t2 values (19700201+25-1);
340	insert into t2 values (19700301+25-1);
341	insert into t2 values (19700101+24-1);
342	insert into t2 values (19700201+24-1);
343	insert into t2 values (19700301+24-1);
344	insert into t2 values (19700101+23-1);
345	insert into t2 values (19700201+23-1);
346	insert into t2 values (19700301+23-1);
347	insert into t2 values (19700101+22-1);
348	insert into t2 values (19700201+22-1);
349	insert into t2 values (19700301+22-1);
350	insert into t2 values (19700101+21-1);
351	insert into t2 values (19700201+21-1);
352	insert into t2 values (19700301+21-1);
353	insert into t2 values (19700101+20-1);
354	insert into t2 values (19700201+20-1);
355	insert into t2 values (19700301+20-1);
356	insert into t2 values (19700101+19-1);
357	insert into t2 values (19700201+19-1);
358	insert into t2 values (19700301+19-1);
359	insert into t2 values (19700101+18-1);
360	insert into t2 values (19700201+18-1);
361	insert into t2 values (19700301+18-1);
362	insert into t2 values (19700101+17-1);
363	insert into t2 values (19700201+17-1);
364	insert into t2 values (19700301+17-1);
365	insert into t2 values (19700101+16-1);
366	insert into t2 values (19700201+16-1);
367	insert into t2 values (19700301+16-1);
368	insert into t2 values (19700101+15-1);
369	insert into t2 values (19700201+15-1);
370	insert into t2 values (19700301+15-1);
371	insert into t2 values (19700101+14-1);
372	insert into t2 values (19700201+14-1);
373	insert into t2 values (19700301+14-1);
374	insert into t2 values (19700101+13-1);
375	insert into t2 values (19700201+13-1);
376	insert into t2 values (19700301+13-1);
377	insert into t2 values (19700101+12-1);
378	insert into t2 values (19700201+12-1);
379	insert into t2 values (19700301+12-1);
380	insert into t2 values (19700101+11-1);
381	insert into t2 values (19700201+11-1);
382	insert into t2 values (19700301+11-1);
383	insert into t2 values (19700101+10-1);
384	insert into t2 values (19700201+10-1);
385	insert into t2 values (19700301+10-1);
386	insert into t2 values (19700101+9-1);
387	insert into t2 values (19700201+9-1);
388	insert into t2 values (19700301+9-1);
389	insert into t2 values (19700101+8-1);
390	insert into t2 values (19700201+8-1);
391	insert into t2 values (19700301+8-1);
392	insert into t2 values (19700101+7-1);
393	insert into t2 values (19700201+7-1);
394	insert into t2 values (19700301+7-1);
395	insert into t2 values (19700101+6-1);
396	insert into t2 values (19700201+6-1);
397	insert into t2 values (19700301+6-1);
398	insert into t2 values (19700101+5-1);
399	insert into t2 values (19700201+5-1);
400	insert into t2 values (19700301+5-1);
401	insert into t2 values (19700101+4-1);
402	insert into t2 values (19700201+4-1);
403	insert into t2 values (19700301+4-1);
404	insert into t2 values (19700101+3-1);
405	insert into t2 values (19700201+3-1);
406	insert into t2 values (19700301+3-1);
407	insert into t2 values (19700101+2-1);
408	insert into t2 values (19700201+2-1);
409	insert into t2 values (19700301+2-1);
410	insert into t2 values (19700101+1-1);
411	insert into t2 values (19700201+1-1);
412	insert into t2 values (19700301+1-1);
413	select count(*) from t2;
414	count(*)
415	84
416	select * from t2;
417	a
418	1970-01-01
419	1970-01-02
420	1970-01-03
421	1970-01-04
422	1970-01-05
423	1970-01-06
424	1970-01-07
425	1970-01-08
426	1970-01-09
427	1970-01-10
428	1970-01-11
429	1970-01-12
430	1970-01-13
431	1970-01-14
432	1970-01-15
433	1970-01-16
434	1970-01-17
435	1970-01-18
436	1970-01-19
437	1970-01-20
438	1970-01-21
439	1970-01-22
440	1970-01-23
441	1970-01-24
442	1970-01-25
443	1970-01-26
444	1970-01-27
445	1970-01-28
446	1970-02-01
447	1970-02-02
448	1970-02-03
449	1970-02-04
450	1970-02-05
451	1970-02-06
452	1970-02-07
453	1970-02-08
454	1970-02-09
455	1970-02-10
456	1970-02-11
457	1970-02-12
458	1970-02-13
459	1970-02-14
460	1970-02-15
461	1970-02-16
462	1970-02-17
463	1970-02-18
464	1970-02-19
465	1970-02-20
466	1970-02-21
467	1970-02-22
468	1970-02-23
469	1970-02-24
470	1970-02-25
471	1970-02-26
472	1970-02-27
473	1970-02-28
474	1970-03-01
475	1970-03-02
476	1970-03-03
477	1970-03-04
478	1970-03-05
479	1970-03-06
480	1970-03-07
481	1970-03-08
482	1970-03-09
483	1970-03-10
484	1970-03-11
485	1970-03-12
486	1970-03-13
487	1970-03-14
488	1970-03-15
489	1970-03-16
490	1970-03-17
491	1970-03-18
492	1970-03-19
493	1970-03-20
494	1970-03-21
495	1970-03-22
496	1970-03-23
497	1970-03-24
498	1970-03-25
499	1970-03-26
500	1970-03-27
501	1970-03-28
502	drop table t2;
503	create table t3 (a date not null, primary key(a)) engine='MyISAM'
504	partition by range (month(a)) subpartition by key (a)
505	subpartitions 3 (
506	partition quarter1 values less than (4),
507	partition quarter2 values less than (7),
508	partition quarter3 values less than (10),
509	partition quarter4 values less than (13)
510	);
511	show create table t3;
512	Table Create Table
513	t3 CREATE TABLE `t3` (
514	`a` date NOT NULL,
515	PRIMARY KEY (`a`)
516	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY RANGE (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = MyISAM, PARTITION quarter2 VALUES LESS THAN (7) ENGINE = MyISAM, PARTITION quarter3 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION quarter4 VALUES LESS THAN (13) ENGINE = MyISAM) /
517	12 inserts;
518	insert into t3 values (adddate(19700101,interval 12-1 month));
519	insert into t3 values (adddate(19700101,interval 11-1 month));
520	insert into t3 values (adddate(19700101,interval 10-1 month));
521	insert into t3 values (adddate(19700101,interval 9-1 month));
522	insert into t3 values (adddate(19700101,interval 8-1 month));
523	insert into t3 values (adddate(19700101,interval 7-1 month));
524	insert into t3 values (adddate(19700101,interval 6-1 month));
525	insert into t3 values (adddate(19700101,interval 5-1 month));
526	insert into t3 values (adddate(19700101,interval 4-1 month));
527	insert into t3 values (adddate(19700101,interval 3-1 month));
528	insert into t3 values (adddate(19700101,interval 2-1 month));
529	insert into t3 values (adddate(19700101,interval 1-1 month));
530	select count(*) from t3;
531	count(*)
532	12
533	select * from t3;
534	a
535	1970-01-01
536	1970-02-01
537	1970-03-01
538	1970-04-01
539	1970-05-01
540	1970-06-01
541	1970-07-01
542	1970-08-01
543	1970-09-01
544	1970-10-01
545	1970-11-01
546	1970-12-01
547	drop table t3;
548	create table t4 (a date not null, primary key(a)) engine='MyISAM'
549	partition by list (month(a)) subpartition by key (a)
550	subpartitions 3 (
551	partition quarter1 values in (1,2,3),
552	partition quarter2 values in (4,5,6),
553	partition quarter3 values in (7,8,9),
554	partition quarter4 values in (10,11,12)
555	);
556	show create table t4;
557	Table Create Table
558	t4 CREATE TABLE `t4` (
559	`a` date NOT NULL,
560	PRIMARY KEY (`a`)
561	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY LIST (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = MyISAM, PARTITION quarter2 VALUES IN (4,5,6) ENGINE = MyISAM, PARTITION quarter3 VALUES IN (7,8,9) ENGINE = MyISAM, PARTITION quarter4 VALUES IN (10,11,12) ENGINE = MyISAM) /
562	12 inserts;
563	insert into t4 values (adddate(19700101,interval 12-1 month));
564	insert into t4 values (adddate(19700101,interval 11-1 month));
565	insert into t4 values (adddate(19700101,interval 10-1 month));
566	insert into t4 values (adddate(19700101,interval 9-1 month));
567	insert into t4 values (adddate(19700101,interval 8-1 month));
568	insert into t4 values (adddate(19700101,interval 7-1 month));
569	insert into t4 values (adddate(19700101,interval 6-1 month));
570	insert into t4 values (adddate(19700101,interval 5-1 month));
571	insert into t4 values (adddate(19700101,interval 4-1 month));
572	insert into t4 values (adddate(19700101,interval 3-1 month));
573	insert into t4 values (adddate(19700101,interval 2-1 month));
574	insert into t4 values (adddate(19700101,interval 1-1 month));
575	select count(*) from t4;
576	count(*)
577	12
578	select * from t4;
579	a
580	1970-01-01
581	1970-02-01
582	1970-03-01
583	1970-04-01
584	1970-05-01
585	1970-06-01
586	1970-07-01
587	1970-08-01
588	1970-09-01
589	1970-10-01
590	1970-11-01
591	1970-12-01
592	drop table t4;
593	create table t1 (a time not null, primary key(a)) engine='MyISAM'
594	partition by key (a) (
595	partition pa1 max_rows=20 min_rows=2,
596	partition pa2 max_rows=30 min_rows=3,
597	partition pa3 max_rows=30 min_rows=4,
598	partition pa4 max_rows=40 min_rows=2);
599	show create table t1;
600	Table Create Table
601	t1 CREATE TABLE `t1` (
602	`a` time NOT NULL,
603	PRIMARY KEY (`a`)
604	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) /
605	insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59');
606	select * from t1;
607	a
608	03:03:03
609	12:10:30
610	21:21:21
611	23:59:00
612	select * from t1 where a=030303;
613	a
614	03:03:03
615	delete from t1 where a=030303;
616	select * from t1;
617	a
618	12:10:30
619	21:21:21
620	23:59:00
621	drop table t1;
622	create table t2 (a time not null, primary key(a)) engine='MyISAM'
623	partition by key (a) partitions 12;
624	show create table t2;
625	Table Create Table
626	t2 CREATE TABLE `t2` (
627	`a` time NOT NULL,
628	PRIMARY KEY (`a`)
629	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) PARTITIONS 12 /
630	insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16');
631	select * from t2;
632	a
633	00:01:01
634	10:11:12
635	13:14:15
636	14:15:16
637	select * from t2 where a='13:14:15';
638	a
639	13:14:15
640	delete from t2 where a='13:14:15';
641	select * from t2;
642	a
643	00:01:01
644	10:11:12
645	14:15:16
646	delete from t2;
647	59 inserts;
648	insert into t2 values (000100+59);
649	insert into t2 values (000100+58);
650	insert into t2 values (000100+57);
651	insert into t2 values (000100+56);
652	insert into t2 values (000100+55);
653	insert into t2 values (000100+54);
654	insert into t2 values (000100+53);
655	insert into t2 values (000100+52);
656	insert into t2 values (000100+51);
657	insert into t2 values (000100+50);
658	insert into t2 values (000100+49);
659	insert into t2 values (000100+48);
660	insert into t2 values (000100+47);
661	insert into t2 values (000100+46);
662	insert into t2 values (000100+45);
663	insert into t2 values (000100+44);
664	insert into t2 values (000100+43);
665	insert into t2 values (000100+42);
666	insert into t2 values (000100+41);
667	insert into t2 values (000100+40);
668	insert into t2 values (000100+39);
669	insert into t2 values (000100+38);
670	insert into t2 values (000100+37);
671	insert into t2 values (000100+36);
672	insert into t2 values (000100+35);
673	insert into t2 values (000100+34);
674	insert into t2 values (000100+33);
675	insert into t2 values (000100+32);
676	insert into t2 values (000100+31);
677	insert into t2 values (000100+30);
678	insert into t2 values (000100+29);
679	insert into t2 values (000100+28);
680	insert into t2 values (000100+27);
681	insert into t2 values (000100+26);
682	insert into t2 values (000100+25);
683	insert into t2 values (000100+24);
684	insert into t2 values (000100+23);
685	insert into t2 values (000100+22);
686	insert into t2 values (000100+21);
687	insert into t2 values (000100+20);
688	insert into t2 values (000100+19);
689	insert into t2 values (000100+18);
690	insert into t2 values (000100+17);
691	insert into t2 values (000100+16);
692	insert into t2 values (000100+15);
693	insert into t2 values (000100+14);
694	insert into t2 values (000100+13);
695	insert into t2 values (000100+12);
696	insert into t2 values (000100+11);
697	insert into t2 values (000100+10);
698	insert into t2 values (000100+9);
699	insert into t2 values (000100+8);
700	insert into t2 values (000100+7);
701	insert into t2 values (000100+6);
702	insert into t2 values (000100+5);
703	insert into t2 values (000100+4);
704	insert into t2 values (000100+3);
705	insert into t2 values (000100+2);
706	insert into t2 values (000100+1);
707	select count(*) from t2;
708	count(*)
709	59
710	select * from t2;
711	a
712	00:01:01
713	00:01:02
714	00:01:03
715	00:01:04
716	00:01:05
717	00:01:06
718	00:01:07
719	00:01:08
720	00:01:09
721	00:01:10
722	00:01:11
723	00:01:12
724	00:01:13
725	00:01:14
726	00:01:15
727	00:01:16
728	00:01:17
729	00:01:18
730	00:01:19
731	00:01:20
732	00:01:21
733	00:01:22
734	00:01:23
735	00:01:24
736	00:01:25
737	00:01:26
738	00:01:27
739	00:01:28
740	00:01:29
741	00:01:30
742	00:01:31
743	00:01:32
744	00:01:33
745	00:01:34
746	00:01:35
747	00:01:36
748	00:01:37
749	00:01:38
750	00:01:39
751	00:01:40
752	00:01:41
753	00:01:42
754	00:01:43
755	00:01:44
756	00:01:45
757	00:01:46
758	00:01:47
759	00:01:48
760	00:01:49
761	00:01:50
762	00:01:51
763	00:01:52
764	00:01:53
765	00:01:54
766	00:01:55
767	00:01:56
768	00:01:57
769	00:01:58
770	00:01:59
771	drop table t2;
772	create table t3 (a time not null, primary key(a)) engine='MyISAM'
773	partition by range (second(a)) subpartition by key (a)
774	subpartitions 3 (
775	partition quarter1 values less than (16),
776	partition quarter2 values less than (31),
777	partition quarter3 values less than (46),
778	partition quarter4 values less than (61)
779	);
780	show create table t3;
781	Table Create Table
782	t3 CREATE TABLE `t3` (
783	`a` time NOT NULL,
784	PRIMARY KEY (`a`)
785	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY RANGE (second(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION quarter2 VALUES LESS THAN (31) ENGINE = MyISAM, PARTITION quarter3 VALUES LESS THAN (46) ENGINE = MyISAM, PARTITION quarter4 VALUES LESS THAN (61) ENGINE = MyISAM) /
786	59 inserts;
787	insert into t3 values (100000+59);
788	insert into t3 values (100000+58);
789	insert into t3 values (100000+57);
790	insert into t3 values (100000+56);
791	insert into t3 values (100000+55);
792	insert into t3 values (100000+54);
793	insert into t3 values (100000+53);
794	insert into t3 values (100000+52);
795	insert into t3 values (100000+51);
796	insert into t3 values (100000+50);
797	insert into t3 values (100000+49);
798	insert into t3 values (100000+48);
799	insert into t3 values (100000+47);
800	insert into t3 values (100000+46);
801	insert into t3 values (100000+45);
802	insert into t3 values (100000+44);
803	insert into t3 values (100000+43);
804	insert into t3 values (100000+42);
805	insert into t3 values (100000+41);
806	insert into t3 values (100000+40);
807	insert into t3 values (100000+39);
808	insert into t3 values (100000+38);
809	insert into t3 values (100000+37);
810	insert into t3 values (100000+36);
811	insert into t3 values (100000+35);
812	insert into t3 values (100000+34);
813	insert into t3 values (100000+33);
814	insert into t3 values (100000+32);
815	insert into t3 values (100000+31);
816	insert into t3 values (100000+30);
817	insert into t3 values (100000+29);
818	insert into t3 values (100000+28);
819	insert into t3 values (100000+27);
820	insert into t3 values (100000+26);
821	insert into t3 values (100000+25);
822	insert into t3 values (100000+24);
823	insert into t3 values (100000+23);
824	insert into t3 values (100000+22);
825	insert into t3 values (100000+21);
826	insert into t3 values (100000+20);
827	insert into t3 values (100000+19);
828	insert into t3 values (100000+18);
829	insert into t3 values (100000+17);
830	insert into t3 values (100000+16);
831	insert into t3 values (100000+15);
832	insert into t3 values (100000+14);
833	insert into t3 values (100000+13);
834	insert into t3 values (100000+12);
835	insert into t3 values (100000+11);
836	insert into t3 values (100000+10);
837	insert into t3 values (100000+9);
838	insert into t3 values (100000+8);
839	insert into t3 values (100000+7);
840	insert into t3 values (100000+6);
841	insert into t3 values (100000+5);
842	insert into t3 values (100000+4);
843	insert into t3 values (100000+3);
844	insert into t3 values (100000+2);
845	insert into t3 values (100000+1);
846	select count(*) from t3;
847	count(*)
848	59
849	select * from t3;
850	a
851	10:00:01
852	10:00:02
853	10:00:03
854	10:00:04
855	10:00:05
856	10:00:06
857	10:00:07
858	10:00:08
859	10:00:09
860	10:00:10
861	10:00:11
862	10:00:12
863	10:00:13
864	10:00:14
865	10:00:15
866	10:00:16
867	10:00:17
868	10:00:18
869	10:00:19
870	10:00:20
871	10:00:21
872	10:00:22
873	10:00:23
874	10:00:24
875	10:00:25
876	10:00:26
877	10:00:27
878	10:00:28
879	10:00:29
880	10:00:30
881	10:00:31
882	10:00:32
883	10:00:33
884	10:00:34
885	10:00:35
886	10:00:36
887	10:00:37
888	10:00:38
889	10:00:39
890	10:00:40
891	10:00:41
892	10:00:42
893	10:00:43
894	10:00:44
895	10:00:45
896	10:00:46
897	10:00:47
898	10:00:48
899	10:00:49
900	10:00:50
901	10:00:51
902	10:00:52
903	10:00:53
904	10:00:54
905	10:00:55
906	10:00:56
907	10:00:57
908	10:00:58
909	10:00:59
910	drop table t3;
911	create table t4 (a time not null, primary key(a)) engine='MyISAM'
912	partition by list (second(a)) subpartition by key (a)
913	subpartitions 3 (
914	partition quarter1 values in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
915	partition quarter2 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
916	partition quarter3 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
917	partition quarter4 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
918	);
919	show create table t4;
920	Table Create Table
921	t4 CREATE TABLE `t4` (
922	`a` time NOT NULL,
923	PRIMARY KEY (`a`)
924	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY LIST (second(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = MyISAM, PARTITION quarter2 VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION quarter3 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = MyISAM, PARTITION quarter4 VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) /
925	59 inserts;
926	insert into t4 values (100000+59);
927	insert into t4 values (100000+58);
928	insert into t4 values (100000+57);
929	insert into t4 values (100000+56);
930	insert into t4 values (100000+55);
931	insert into t4 values (100000+54);
932	insert into t4 values (100000+53);
933	insert into t4 values (100000+52);
934	insert into t4 values (100000+51);
935	insert into t4 values (100000+50);
936	insert into t4 values (100000+49);
937	insert into t4 values (100000+48);
938	insert into t4 values (100000+47);
939	insert into t4 values (100000+46);
940	insert into t4 values (100000+45);
941	insert into t4 values (100000+44);
942	insert into t4 values (100000+43);
943	insert into t4 values (100000+42);
944	insert into t4 values (100000+41);
945	insert into t4 values (100000+40);
946	insert into t4 values (100000+39);
947	insert into t4 values (100000+38);
948	insert into t4 values (100000+37);
949	insert into t4 values (100000+36);
950	insert into t4 values (100000+35);
951	insert into t4 values (100000+34);
952	insert into t4 values (100000+33);
953	insert into t4 values (100000+32);
954	insert into t4 values (100000+31);
955	insert into t4 values (100000+30);
956	insert into t4 values (100000+29);
957	insert into t4 values (100000+28);
958	insert into t4 values (100000+27);
959	insert into t4 values (100000+26);
960	insert into t4 values (100000+25);
961	insert into t4 values (100000+24);
962	insert into t4 values (100000+23);
963	insert into t4 values (100000+22);
964	insert into t4 values (100000+21);
965	insert into t4 values (100000+20);
966	insert into t4 values (100000+19);
967	insert into t4 values (100000+18);
968	insert into t4 values (100000+17);
969	insert into t4 values (100000+16);
970	insert into t4 values (100000+15);
971	insert into t4 values (100000+14);
972	insert into t4 values (100000+13);
973	insert into t4 values (100000+12);
974	insert into t4 values (100000+11);
975	insert into t4 values (100000+10);
976	insert into t4 values (100000+9);
977	insert into t4 values (100000+8);
978	insert into t4 values (100000+7);
979	insert into t4 values (100000+6);
980	insert into t4 values (100000+5);
981	insert into t4 values (100000+4);
982	insert into t4 values (100000+3);
983	insert into t4 values (100000+2);
984	insert into t4 values (100000+1);
985	select count(*) from t4;
986	count(*)
987	59
988	select * from t4;
989	a
990	10:00:01
991	10:00:02
992	10:00:03
993	10:00:04
994	10:00:05
995	10:00:06
996	10:00:07
997	10:00:08
998	10:00:09
999	10:00:10
1000	10:00:11
1001	10:00:12
1002	10:00:13
1003	10:00:14
1004	10:00:15
1005	10:00:16
1006	10:00:17
1007	10:00:18
1008	10:00:19
1009	10:00:20
1010	10:00:21
1011	10:00:22
1012	10:00:23
1013	10:00:24
1014	10:00:25
1015	10:00:26
1016	10:00:27
1017	10:00:28
1018	10:00:29
1019	10:00:30
1020	10:00:31
1021	10:00:32
1022	10:00:33
1023	10:00:34
1024	10:00:35
1025	10:00:36
1026	10:00:37
1027	10:00:38
1028	10:00:39
1029	10:00:40
1030	10:00:41
1031	10:00:42
1032	10:00:43
1033	10:00:44
1034	10:00:45
1035	10:00:46
1036	10:00:47
1037	10:00:48
1038	10:00:49
1039	10:00:50
1040	10:00:51
1041	10:00:52
1042	10:00:53
1043	10:00:54
1044	10:00:55
1045	10:00:56
1046	10:00:57
1047	10:00:58
1048	10:00:59
1049	drop table t4;
1050	create table t1 (a datetime not null, primary key(a)) engine='MyISAM'
1051	partition by key (a) (
1052	partition pa1 max_rows=20 min_rows=2,
1053	partition pa2 max_rows=30 min_rows=3,
1054	partition pa3 max_rows=30 min_rows=4,
1055	partition pa4 max_rows=40 min_rows=2);
1056	show create table t1;
1057	Table Create Table
1058	t1 CREATE TABLE `t1` (
1059	`a` datetime NOT NULL,
1060	PRIMARY KEY (`a`)
1061	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) /
1062	insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
1063	select * from t1;
1064	a
1065	1975-01-01 21:21:21
1066	1980-10-14 03:03:00
1067	2000-06-15 23:59:00
1068	2020-12-31 12:10:30
1069	select * from t1 where a=19801014030300;
1070	a
1071	1980-10-14 03:03:00
1072	delete from t1 where a=19801014030300;
1073	select * from t1;
1074	a
1075	1975-01-01 21:21:21
1076	2000-06-15 23:59:00
1077	2020-12-31 12:10:30
1078	drop table t1;
1079	create table t2 (a datetime not null, primary key(a)) engine='MyISAM'
1080	partition by key (a) partitions 12;
1081	show create table t2;
1082	Table Create Table
1083	t2 CREATE TABLE `t2` (
1084	`a` datetime NOT NULL,
1085	PRIMARY KEY (`a`)
1086	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) PARTITIONS 12 /
1087	insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
1088	select * from t2;
1089	a
1090	1975-01-01 00:01:01
1091	1980-10-14 13:14:15
1092	2000-06-15 14:15:16
1093	2020-12-31 10:11:12
1094	select * from t2 where a='1980-10-14 13:14:15';
1095	a
1096	1980-10-14 13:14:15
1097	delete from t2 where a='1980-10-14 13:14:15';
1098	select * from t2;
1099	a
1100	1975-01-01 00:01:01
1101	2000-06-15 14:15:16
1102	2020-12-31 10:11:12
1103	delete from t2;
1104	59 inserts;
1105	insert into t2 values (19700101000000+59);
1106	insert into t2 values (19700101000000+58);
1107	insert into t2 values (19700101000000+57);
1108	insert into t2 values (19700101000000+56);
1109	insert into t2 values (19700101000000+55);
1110	insert into t2 values (19700101000000+54);
1111	insert into t2 values (19700101000000+53);
1112	insert into t2 values (19700101000000+52);
1113	insert into t2 values (19700101000000+51);
1114	insert into t2 values (19700101000000+50);
1115	insert into t2 values (19700101000000+49);
1116	insert into t2 values (19700101000000+48);
1117	insert into t2 values (19700101000000+47);
1118	insert into t2 values (19700101000000+46);
1119	insert into t2 values (19700101000000+45);
1120	insert into t2 values (19700101000000+44);
1121	insert into t2 values (19700101000000+43);
1122	insert into t2 values (19700101000000+42);
1123	insert into t2 values (19700101000000+41);
1124	insert into t2 values (19700101000000+40);
1125	insert into t2 values (19700101000000+39);
1126	insert into t2 values (19700101000000+38);
1127	insert into t2 values (19700101000000+37);
1128	insert into t2 values (19700101000000+36);
1129	insert into t2 values (19700101000000+35);
1130	insert into t2 values (19700101000000+34);
1131	insert into t2 values (19700101000000+33);
1132	insert into t2 values (19700101000000+32);
1133	insert into t2 values (19700101000000+31);
1134	insert into t2 values (19700101000000+30);
1135	insert into t2 values (19700101000000+29);
1136	insert into t2 values (19700101000000+28);
1137	insert into t2 values (19700101000000+27);
1138	insert into t2 values (19700101000000+26);
1139	insert into t2 values (19700101000000+25);
1140	insert into t2 values (19700101000000+24);
1141	insert into t2 values (19700101000000+23);
1142	insert into t2 values (19700101000000+22);
1143	insert into t2 values (19700101000000+21);
1144	insert into t2 values (19700101000000+20);
1145	insert into t2 values (19700101000000+19);
1146	insert into t2 values (19700101000000+18);
1147	insert into t2 values (19700101000000+17);
1148	insert into t2 values (19700101000000+16);
1149	insert into t2 values (19700101000000+15);
1150	insert into t2 values (19700101000000+14);
1151	insert into t2 values (19700101000000+13);
1152	insert into t2 values (19700101000000+12);
1153	insert into t2 values (19700101000000+11);
1154	insert into t2 values (19700101000000+10);
1155	insert into t2 values (19700101000000+9);
1156	insert into t2 values (19700101000000+8);
1157	insert into t2 values (19700101000000+7);
1158	insert into t2 values (19700101000000+6);
1159	insert into t2 values (19700101000000+5);
1160	insert into t2 values (19700101000000+4);
1161	insert into t2 values (19700101000000+3);
1162	insert into t2 values (19700101000000+2);
1163	insert into t2 values (19700101000000+1);
1164	select count(*) from t2;
1165	count(*)
1166	59
1167	select * from t2;
1168	a
1169	1970-01-01 00:00:01
1170	1970-01-01 00:00:02
1171	1970-01-01 00:00:03
1172	1970-01-01 00:00:04
1173	1970-01-01 00:00:05
1174	1970-01-01 00:00:06
1175	1970-01-01 00:00:07
1176	1970-01-01 00:00:08
1177	1970-01-01 00:00:09
1178	1970-01-01 00:00:10
1179	1970-01-01 00:00:11
1180	1970-01-01 00:00:12
1181	1970-01-01 00:00:13
1182	1970-01-01 00:00:14
1183	1970-01-01 00:00:15
1184	1970-01-01 00:00:16
1185	1970-01-01 00:00:17
1186	1970-01-01 00:00:18
1187	1970-01-01 00:00:19
1188	1970-01-01 00:00:20
1189	1970-01-01 00:00:21
1190	1970-01-01 00:00:22
1191	1970-01-01 00:00:23
1192	1970-01-01 00:00:24
1193	1970-01-01 00:00:25
1194	1970-01-01 00:00:26
1195	1970-01-01 00:00:27
1196	1970-01-01 00:00:28
1197	1970-01-01 00:00:29
1198	1970-01-01 00:00:30
1199	1970-01-01 00:00:31
1200	1970-01-01 00:00:32
1201	1970-01-01 00:00:33
1202	1970-01-01 00:00:34
1203	1970-01-01 00:00:35
1204	1970-01-01 00:00:36
1205	1970-01-01 00:00:37
1206	1970-01-01 00:00:38
1207	1970-01-01 00:00:39
1208	1970-01-01 00:00:40
1209	1970-01-01 00:00:41
1210	1970-01-01 00:00:42
1211	1970-01-01 00:00:43
1212	1970-01-01 00:00:44
1213	1970-01-01 00:00:45
1214	1970-01-01 00:00:46
1215	1970-01-01 00:00:47
1216	1970-01-01 00:00:48
1217	1970-01-01 00:00:49
1218	1970-01-01 00:00:50
1219	1970-01-01 00:00:51
1220	1970-01-01 00:00:52
1221	1970-01-01 00:00:53
1222	1970-01-01 00:00:54
1223	1970-01-01 00:00:55
1224	1970-01-01 00:00:56
1225	1970-01-01 00:00:57
1226	1970-01-01 00:00:58
1227	1970-01-01 00:00:59
1228	drop table t2;
1229	create table t3 (a datetime not null, primary key(a)) engine='MyISAM'
1230	partition by range (month(a)) subpartition by key (a)
1231	subpartitions 3 (
1232	partition quarter1 values less than (4),
1233	partition quarter2 values less than (7),
1234	partition quarter3 values less than (10),
1235	partition quarter4 values less than (13)
1236	);
1237	show create table t3;
1238	Table Create Table
1239	t3 CREATE TABLE `t3` (
1240	`a` datetime NOT NULL,
1241	PRIMARY KEY (`a`)
1242	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY RANGE (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES LESS THAN (4) ENGINE = MyISAM, PARTITION quarter2 VALUES LESS THAN (7) ENGINE = MyISAM, PARTITION quarter3 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION quarter4 VALUES LESS THAN (13) ENGINE = MyISAM) /
1243	12 inserts;
1244	insert into t3 values (adddate(19700101000000,interval 12-1 month));
1245	insert into t3 values (adddate(19700101000000,interval 11-1 month));
1246	insert into t3 values (adddate(19700101000000,interval 10-1 month));
1247	insert into t3 values (adddate(19700101000000,interval 9-1 month));
1248	insert into t3 values (adddate(19700101000000,interval 8-1 month));
1249	insert into t3 values (adddate(19700101000000,interval 7-1 month));
1250	insert into t3 values (adddate(19700101000000,interval 6-1 month));
1251	insert into t3 values (adddate(19700101000000,interval 5-1 month));
1252	insert into t3 values (adddate(19700101000000,interval 4-1 month));
1253	insert into t3 values (adddate(19700101000000,interval 3-1 month));
1254	insert into t3 values (adddate(19700101000000,interval 2-1 month));
1255	insert into t3 values (adddate(19700101000000,interval 1-1 month));
1256	select count(*) from t3;
1257	count(*)
1258	12
1259	select * from t3;
1260	a
1261	1970-01-01 00:00:00
1262	1970-02-01 00:00:00
1263	1970-03-01 00:00:00
1264	1970-04-01 00:00:00
1265	1970-05-01 00:00:00
1266	1970-06-01 00:00:00
1267	1970-07-01 00:00:00
1268	1970-08-01 00:00:00
1269	1970-09-01 00:00:00
1270	1970-10-01 00:00:00
1271	1970-11-01 00:00:00
1272	1970-12-01 00:00:00
1273	drop table t3;
1274	create table t4 (a datetime not null, primary key(a)) engine='MyISAM'
1275	partition by list (month(a)) subpartition by key (a)
1276	subpartitions 3 (
1277	partition quarter1 values in (1,2,3),
1278	partition quarter2 values in (4,5,6),
1279	partition quarter3 values in (7,8,9),
1280	partition quarter4 values in (10,11,12)
1281	);
1282	show create table t4;
1283	Table Create Table
1284	t4 CREATE TABLE `t4` (
1285	`a` datetime NOT NULL,
1286	PRIMARY KEY (`a`)
1287	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY LIST (month(a)) SUBPARTITION BY KEY (a) SUBPARTITIONS 3 (PARTITION quarter1 VALUES IN (1,2,3) ENGINE = MyISAM, PARTITION quarter2 VALUES IN (4,5,6) ENGINE = MyISAM, PARTITION quarter3 VALUES IN (7,8,9) ENGINE = MyISAM, PARTITION quarter4 VALUES IN (10,11,12) ENGINE = MyISAM) /
1288	12 inserts;
1289	insert into t4 values (adddate(19700101000000,interval 12-1 month));
1290	insert into t4 values (adddate(19700101000000,interval 11-1 month));
1291	insert into t4 values (adddate(19700101000000,interval 10-1 month));
1292	insert into t4 values (adddate(19700101000000,interval 9-1 month));
1293	insert into t4 values (adddate(19700101000000,interval 8-1 month));
1294	insert into t4 values (adddate(19700101000000,interval 7-1 month));
1295	insert into t4 values (adddate(19700101000000,interval 6-1 month));
1296	insert into t4 values (adddate(19700101000000,interval 5-1 month));
1297	insert into t4 values (adddate(19700101000000,interval 4-1 month));
1298	insert into t4 values (adddate(19700101000000,interval 3-1 month));
1299	insert into t4 values (adddate(19700101000000,interval 2-1 month));
1300	insert into t4 values (adddate(19700101000000,interval 1-1 month));
1301	select count(*) from t4;
1302	count(*)
1303	12
1304	select * from t4;
1305	a
1306	1970-01-01 00:00:00
1307	1970-02-01 00:00:00
1308	1970-03-01 00:00:00
1309	1970-04-01 00:00:00
1310	1970-05-01 00:00:00
1311	1970-06-01 00:00:00
1312	1970-07-01 00:00:00
1313	1970-08-01 00:00:00
1314	1970-09-01 00:00:00
1315	1970-10-01 00:00:00
1316	1970-11-01 00:00:00
1317	1970-12-01 00:00:00
1318	drop table t4;
1319	create table t1 (a year not null, primary key(a)) engine='MyISAM'
1320	partition by key (a) (
1321	partition pa1 max_rows=20 min_rows=2,
1322	partition pa2 max_rows=30 min_rows=3,
1323	partition pa3 max_rows=30 min_rows=4,
1324	partition pa4 max_rows=40 min_rows=2);
1325	show create table t1;
1326	Table Create Table
1327	t1 CREATE TABLE `t1` (
1328	`a` year(4) NOT NULL,
1329	PRIMARY KEY (`a`)
1330	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = MyISAM, PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = MyISAM, PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = MyISAM, PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = MyISAM) /
1331	insert into t1 values ('1975'), (2020), ('1980'), ('2000');
1332	select * from t1;
1333	a
1334	1975
1335	1980
1336	2000
1337	2020
1338	select * from t1 where a=1980;
1339	a
1340	1980
1341	delete from t1 where a=1980;
1342	select * from t1;
1343	a
1344	1975
1345	2000
1346	2020
1347	drop table t1;
1348	create table t2 (a year not null, primary key(a)) engine='MyISAM'
1349	partition by key (a) partitions 12;
1350	show create table t2;
1351	Table Create Table
1352	t2 CREATE TABLE `t2` (
1353	`a` year(4) NOT NULL,
1354	PRIMARY KEY (`a`)
1355	) ENGINE=MyISAM DEFAULT CHARSET=latin1 /!50100 PARTITION BY KEY (a) PARTITIONS 12 /
1356	insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
1357	select * from t2;
1358	a
1359	1975
1360	1980
1361	2000
1362	2020
1363	select * from t2 where a='1980';
1364	a
1365	1980
1366	delete from t2 where a='1980';
1367	select * from t2;
1368	a
1369	1975
1370	2000
1371	2020
1372	delete from t2;
1373	255 inserts;
1374	Warnings:
1375	Warning 1264 Out of range value for column 'a' at row 1
1376	select count(*) from t2;
1377	count(*)
1378	255
1379	select * from t2;
1380	a
1381	0000
1382	1902
1383	1903
1384	1904
1385	1905
1386	1906
1387	1907
1388	1908
1389	1909
1390	1910
1391	1911
1392	1912
1393	1913
1394	1914
1395	1915
1396	1916
1397	1917
1398	1918
1399	1919
1400	1920
1401	1921
1402	1922
1403	1923
1404	1924
1405	1925
1406	1926
1407	1927
1408	1928
1409	1929
1410	1930
1411	1931
1412	1932
1413	1933
1414	1934
1415	1935
1416	1936
1417	1937
1418	1938
1419	1939
1420	1940
1421	1941
1422	1942
1423	1943
1424	1944
1425	1945
1426	1946
1427	1947
1428	1948
1429	1949
1430	1950
1431	1951
1432	1952
1433	1953
1434	1954
1435	1955
1436	1956
1437	1957
1438	1958
1439	1959
1440	1960
1441	1961
1442	1962
1443	1963
1444	1964
1445	1965
1446	1966
1447	1967
1448	1968
1449	1969
1450	1970
1451	1971
1452	1972
1453	1973
1454	1974
1455	1975
1456	1976
1457	1977
1458	1978
1459	1979
1460	1980
1461	1981
1462	1982
1463	1983
1464	1984
1465	1985
1466	1986
1467	1987
1468	1988
1469	1989
1470	1990
1471	1991
1472	1992
1473	1993
1474	1994
1475	1995
1476	1996
1477	1997
1478	1998
1479	1999
1480	2000
1481	2001
1482	2002
1483	2003
1484	2004
1485	2005
1486	2006
1487	2007
1488	2008
1489	2009
1490	2010
1491	2011
1492	2012
1493	2013
1494	2014
1495	2015
1496	2016
1497	2017
1498	2018
1499	2019
1500	2020
1501	2021
1502	2022
1503	2023
1504	2024
1505	2025
1506	2026
1507	2027
1508	2028
1509	2029
1510	2030
1511	2031
1512	2032
1513	2033
1514	2034
1515	2035
1516	2036
1517	2037
1518	2038
1519	2039
1520	2040
1521	2041
1522	2042
1523	2043
1524	2044
1525	2045
1526	2046
1527	2047
1528	2048
1529	2049
1530	2050
1531	2051
1532	2052
1533	2053
1534	2054
1535	2055
1536	2056
1537	2057
1538	2058
1539	2059
1540	2060
1541	2061
1542	2062
1543	2063
1544	2064
1545	2065
1546	2066
1547	2067
1548	2068
1549	2069
1550	2070
1551	2071
1552	2072
1553	2073
1554	2074
1555	2075
1556	2076
1557	2077
1558	2078
1559	2079
1560	2080
1561	2081
1562	2082
1563	2083
1564	2084
1565	2085
1566	2086
1567	2087
1568	2088
1569	2089
1570	2090
1571	2091
1572	2092
1573	2093
1574	2094
1575	2095
1576	2096
1577	2097
1578	2098
1579	2099
1580	2100
1581	2101
1582	2102
1583	2103
1584	2104
1585	2105
1586	2106
1587	2107
1588	2108
1589	2109
1590	2110
1591	2111
1592	2112
1593	2113
1594	2114
1595	2115
1596	2116
1597	2117
1598	2118
1599	2119
1600	2120
1601	2121
1602	2122
1603	2123
1604	2124
1605	2125
1606	2126
1607	2127
1608	2128
1609	2129
1610	2130
1611	2131
1612	2132
1613	2133
1614	2134
1615	2135
1616	2136
1617	2137
1618	2138
1619	2139
1620	2140
1621	2141
1622	2142
1623	2143
1624	2144
1625	2145
1626	2146
1627	2147
1628	2148
1629	2149
1630	2150
1631	2151
1632	2152
1633	2153
1634	2154
1635	2155
1636	drop table t2;