1
-------------------------------------------------------------------------
2
--- abs(col1) in partition with coltype int
3
-------------------------------------------------------------------------
4
drop table if exists t1 ;
5
drop table if exists t2 ;
6
drop table if exists t3 ;
7
drop table if exists t4 ;
8
drop table if exists t5 ;
9
drop table if exists t6 ;
10
-------------------------------------------------------------------------
11
--- Create tables with abs(col1)
12
-------------------------------------------------------------------------
13
create table t1 (col1 int) engine='MYISAM'
14
partition by range(abs(col1))
15
(partition p0 values less than (15),
16
partition p1 values less than maxvalue);
17
create table t2 (col1 int) engine='MYISAM'
18
partition by list(abs(col1))
19
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
20
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
21
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
22
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
23
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
24
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
26
create table t3 (col1 int) engine='MYISAM'
27
partition by hash(abs(col1));
28
create table t4 (colint int, col1 int) engine='MYISAM'
29
partition by range(colint)
30
subpartition by hash(abs(col1)) subpartitions 2
31
(partition p0 values less than (15),
32
partition p1 values less than maxvalue);
33
create table t5 (colint int, col1 int) engine='MYISAM'
34
partition by list(colint)
35
subpartition by hash(abs(col1)) subpartitions 2
36
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
37
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
38
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
39
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
40
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
41
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
43
create table t6 (colint int, col1 int) engine='MYISAM'
44
partition by range(colint)
45
(partition p0 values less than (abs(15)),
46
partition p1 values less than maxvalue);
47
-------------------------------------------------------------------------
48
--- Access tables with abs(col1)
49
-------------------------------------------------------------------------
50
insert into t1 values (5 );
51
insert into t1 values (13 );
52
insert into t2 values (5 );
53
insert into t2 values (13 );
54
insert into t2 values (17 );
55
insert into t3 values (5 );
56
insert into t3 values (13 );
57
insert into t3 values (17 );
58
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t4;
59
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t5;
60
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t6;
61
select abs(col1) from t1 order by col1;
65
select * from t1 order by col1;
69
select * from t2 order by col1;
74
select * from t3 order by col1;
79
select * from t4 order by colint;
126
select * from t5 order by colint;
173
select * from t6 order by colint;
220
update t1 set col1=15 where col1=5 ;
221
update t2 set col1=15 where col1=5 ;
222
update t3 set col1=15 where col1=5 ;
223
update t4 set col1=15 where col1=5 ;
224
update t5 set col1=15 where col1=5 ;
225
update t6 set col1=15 where col1=5 ;
226
select * from t1 order by col1;
230
select * from t2 order by col1;
235
select * from t3 order by col1;
240
select * from t4 order by colint;
287
select * from t5 order by colint;
334
select * from t6 order by colint;
381
-------------------------------------------------------------------------
382
--- Alter tables with abs(col1)
383
-------------------------------------------------------------------------
384
drop table if exists t11 ;
385
drop table if exists t22 ;
386
drop table if exists t33 ;
387
drop table if exists t44 ;
388
drop table if exists t55 ;
389
drop table if exists t66 ;
390
create table t11 engine='MYISAM' as select * from t1;
391
create table t22 engine='MYISAM' as select * from t2;
392
create table t33 engine='MYISAM' as select * from t3;
393
create table t44 engine='MYISAM' as select * from t4;
394
create table t55 engine='MYISAM' as select * from t5;
395
create table t66 engine='MYISAM' as select * from t6;
397
partition by range(abs(col1))
398
(partition p0 values less than (15),
399
partition p1 values less than maxvalue);
401
partition by list(abs(col1))
402
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
403
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
404
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
405
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
406
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
407
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
410
partition by hash(abs(col1));
412
partition by range(colint)
413
subpartition by hash(abs(col1)) subpartitions 2
414
(partition p0 values less than (15),
415
partition p1 values less than maxvalue);
417
partition by list(colint)
418
subpartition by hash(abs(col1)) subpartitions 2
419
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
420
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
421
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
422
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
423
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
424
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
427
partition by range(colint)
428
(partition p0 values less than (abs(15)),
429
partition p1 values less than maxvalue);
430
select * from t11 order by col1;
434
select * from t22 order by col1;
439
select * from t33 order by col1;
444
select * from t44 order by colint;
491
select * from t55 order by colint;
538
select * from t66 order by colint;
585
---------------------------
586
---- some alter table begin
587
---------------------------
589
reorganize partition p0,p1 into
590
(partition s1 values less than maxvalue);
591
select * from t11 order by col1;
596
reorganize partition s1 into
597
(partition p0 values less than (15),
598
partition p1 values less than maxvalue);
599
select * from t11 order by col1;
604
partition by list(colint)
605
subpartition by hash(abs(col1)) subpartitions 5
606
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
607
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
608
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
609
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
610
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
611
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
613
show create table t55;
615
t55 CREATE TABLE `t55` (
616
`colint` int(11) DEFAULT NULL,
617
`col1` int(11) DEFAULT NULL
618
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (abs(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
619
select * from t55 order by colint;
667
reorganize partition p0,p1 into
668
(partition s1 values less than maxvalue);
669
select * from t66 order by colint;
717
reorganize partition s1 into
718
(partition p0 values less than (abs(15)),
719
partition p1 values less than maxvalue);
720
select * from t66 order by colint;
768
reorganize partition p0,p1 into
769
(partition s1 values less than maxvalue);
770
select * from t66 order by colint;
818
reorganize partition s1 into
819
(partition p0 values less than (abs(15)),
820
partition p1 values less than maxvalue);
821
select * from t66 order by colint;
868
-------------------------------------------------------------------------
869
--- Delete rows and partitions of tables with abs(col1)
870
-------------------------------------------------------------------------
871
delete from t1 where col1=13 ;
872
delete from t2 where col1=13 ;
873
delete from t3 where col1=13 ;
874
delete from t4 where col1=13 ;
875
delete from t5 where col1=13 ;
876
delete from t6 where col1=13 ;
877
select * from t1 order by col1;
880
select * from t2 order by col1;
884
select * from t3 order by col1;
888
select * from t4 order by colint;
934
select * from t5 order by colint;
980
insert into t1 values (13 );
981
insert into t2 values (13 );
982
insert into t3 values (13 );
983
insert into t4 values (60,13 );
984
insert into t5 values (60,13 );
985
insert into t6 values (60,13 );
986
select * from t1 order by col1;
990
select * from t2 order by col1;
995
select * from t3 order by col1;
1000
select * from t4 order by colint;
1047
select * from t5 order by colint;
1094
select * from t6 order by colint;
1141
alter table t1 drop partition p0;
1142
alter table t2 drop partition p0;
1143
alter table t4 drop partition p0;
1144
alter table t5 drop partition p0;
1145
alter table t6 drop partition p0;
1146
select * from t1 order by col1;
1149
select * from t2 order by col1;
1154
select * from t3 order by col1;
1159
select * from t4 order by colint;
1193
select * from t5 order by colint;
1231
select * from t6 order by colint;
1265
-------------------------------------------------------------------------
1266
--- Delete rows and partitions of tables with abs(col1)
1267
-------------------------------------------------------------------------
1268
delete from t11 where col1=13 ;
1269
delete from t22 where col1=13 ;
1270
delete from t33 where col1=13 ;
1271
delete from t44 where col1=13 ;
1272
delete from t55 where col1=13 ;
1273
delete from t66 where col1=13 ;
1274
select * from t11 order by col1;
1277
select * from t22 order by col1;
1281
select * from t33 order by col1;
1285
select * from t44 order by colint;
1331
select * from t55 order by colint;
1377
insert into t11 values (13 );
1378
insert into t22 values (13 );
1379
insert into t33 values (13 );
1380
insert into t44 values (60,13 );
1381
insert into t55 values (60,13 );
1382
insert into t66 values (60,13 );
1383
select * from t11 order by col1;
1387
select * from t22 order by col1;
1392
select * from t33 order by col1;
1397
select * from t44 order by colint;
1444
select * from t55 order by colint;
1491
select * from t66 order by colint;
1538
alter table t11 drop partition p0;
1539
alter table t22 drop partition p0;
1540
alter table t44 drop partition p0;
1541
alter table t55 drop partition p0;
1542
alter table t66 drop partition p0;
1543
select * from t11 order by col1;
1546
select * from t22 order by col1;
1551
select * from t33 order by col1;
1556
select * from t44 order by colint;
1590
select * from t55 order by colint;
1628
select * from t66 order by colint;
1662
-------------------------
1663
---- some alter table end
1664
-------------------------
1665
drop table if exists t1 ;
1666
drop table if exists t2 ;
1667
drop table if exists t3 ;
1668
drop table if exists t4 ;
1669
drop table if exists t5 ;
1670
drop table if exists t6 ;
1671
drop table if exists t11 ;
1672
drop table if exists t22 ;
1673
drop table if exists t33 ;
1674
drop table if exists t44 ;
1675
drop table if exists t55 ;
1676
drop table if exists t66 ;
1677
-------------------------------------------------------------------------
1678
--- mod(col1,10) in partition with coltype int
1679
-------------------------------------------------------------------------
1680
drop table if exists t1 ;
1681
drop table if exists t2 ;
1682
drop table if exists t3 ;
1683
drop table if exists t4 ;
1684
drop table if exists t5 ;
1685
drop table if exists t6 ;
1686
-------------------------------------------------------------------------
1687
--- Create tables with mod(col1,10)
1688
-------------------------------------------------------------------------
1689
create table t1 (col1 int) engine='MYISAM'
1690
partition by range(mod(col1,10))
1691
(partition p0 values less than (15),
1692
partition p1 values less than maxvalue);
1693
create table t2 (col1 int) engine='MYISAM'
1694
partition by list(mod(col1,10))
1695
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1696
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1697
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1698
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1699
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1700
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1702
create table t3 (col1 int) engine='MYISAM'
1703
partition by hash(mod(col1,10));
1704
create table t4 (colint int, col1 int) engine='MYISAM'
1705
partition by range(colint)
1706
subpartition by hash(mod(col1,10)) subpartitions 2
1707
(partition p0 values less than (15),
1708
partition p1 values less than maxvalue);
1709
create table t5 (colint int, col1 int) engine='MYISAM'
1710
partition by list(colint)
1711
subpartition by hash(mod(col1,10)) subpartitions 2
1712
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
1713
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1714
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1715
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1716
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1717
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1719
create table t6 (colint int, col1 int) engine='MYISAM'
1720
partition by range(colint)
1721
(partition p0 values less than (mod(15,10)),
1722
partition p1 values less than maxvalue);
1723
-------------------------------------------------------------------------
1724
--- Access tables with mod(col1,10)
1725
-------------------------------------------------------------------------
1726
insert into t1 values (5);
1727
insert into t1 values (19);
1728
insert into t2 values (5);
1729
insert into t2 values (19);
1730
insert into t2 values (17);
1731
insert into t3 values (5);
1732
insert into t3 values (19);
1733
insert into t3 values (17);
1734
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t4;
1735
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t5;
1736
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t6;
1737
select mod(col1,10) from t1 order by col1;
1741
select * from t1 order by col1;
1745
select * from t2 order by col1;
1750
select * from t3 order by col1;
1755
select * from t4 order by colint;
1802
select * from t5 order by colint;
1849
select * from t6 order by colint;
1896
update t1 set col1=15 where col1=5;
1897
update t2 set col1=15 where col1=5;
1898
update t3 set col1=15 where col1=5;
1899
update t4 set col1=15 where col1=5;
1900
update t5 set col1=15 where col1=5;
1901
update t6 set col1=15 where col1=5;
1902
select * from t1 order by col1;
1906
select * from t2 order by col1;
1911
select * from t3 order by col1;
1916
select * from t4 order by colint;
1963
select * from t5 order by colint;
2010
select * from t6 order by colint;
2057
-------------------------------------------------------------------------
2058
--- Alter tables with mod(col1,10)
2059
-------------------------------------------------------------------------
2060
drop table if exists t11 ;
2061
drop table if exists t22 ;
2062
drop table if exists t33 ;
2063
drop table if exists t44 ;
2064
drop table if exists t55 ;
2065
drop table if exists t66 ;
2066
create table t11 engine='MYISAM' as select * from t1;
2067
create table t22 engine='MYISAM' as select * from t2;
2068
create table t33 engine='MYISAM' as select * from t3;
2069
create table t44 engine='MYISAM' as select * from t4;
2070
create table t55 engine='MYISAM' as select * from t5;
2071
create table t66 engine='MYISAM' as select * from t6;
2073
partition by range(mod(col1,10))
2074
(partition p0 values less than (15),
2075
partition p1 values less than maxvalue);
2077
partition by list(mod(col1,10))
2078
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2079
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2080
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2081
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2082
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2083
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2086
partition by hash(mod(col1,10));
2088
partition by range(colint)
2089
subpartition by hash(mod(col1,10)) subpartitions 2
2090
(partition p0 values less than (15),
2091
partition p1 values less than maxvalue);
2093
partition by list(colint)
2094
subpartition by hash(mod(col1,10)) subpartitions 2
2095
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2096
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2097
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2098
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2099
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2100
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2103
partition by range(colint)
2104
(partition p0 values less than (mod(15,10)),
2105
partition p1 values less than maxvalue);
2106
select * from t11 order by col1;
2110
select * from t22 order by col1;
2115
select * from t33 order by col1;
2120
select * from t44 order by colint;
2167
select * from t55 order by colint;
2214
select * from t66 order by colint;
2261
---------------------------
2262
---- some alter table begin
2263
---------------------------
2265
reorganize partition p0,p1 into
2266
(partition s1 values less than maxvalue);
2267
select * from t11 order by col1;
2272
reorganize partition s1 into
2273
(partition p0 values less than (15),
2274
partition p1 values less than maxvalue);
2275
select * from t11 order by col1;
2280
partition by list(colint)
2281
subpartition by hash(mod(col1,10)) subpartitions 5
2282
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2283
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2284
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2285
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2286
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2287
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2289
show create table t55;
2291
t55 CREATE TABLE `t55` (
2292
`colint` int(11) DEFAULT NULL,
2293
`col1` int(11) DEFAULT NULL
2294
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (mod(col1,10)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
2295
select * from t55 order by colint;
2343
reorganize partition p0,p1 into
2344
(partition s1 values less than maxvalue);
2345
select * from t66 order by colint;
2393
reorganize partition s1 into
2394
(partition p0 values less than (mod(15,10)),
2395
partition p1 values less than maxvalue);
2396
select * from t66 order by colint;
2444
reorganize partition p0,p1 into
2445
(partition s1 values less than maxvalue);
2446
select * from t66 order by colint;
2494
reorganize partition s1 into
2495
(partition p0 values less than (mod(15,10)),
2496
partition p1 values less than maxvalue);
2497
select * from t66 order by colint;
2544
-------------------------------------------------------------------------
2545
--- Delete rows and partitions of tables with mod(col1,10)
2546
-------------------------------------------------------------------------
2547
delete from t1 where col1=19;
2548
delete from t2 where col1=19;
2549
delete from t3 where col1=19;
2550
delete from t4 where col1=19;
2551
delete from t5 where col1=19;
2552
delete from t6 where col1=19;
2553
select * from t1 order by col1;
2556
select * from t2 order by col1;
2560
select * from t3 order by col1;
2564
select * from t4 order by colint;
2611
select * from t5 order by colint;
2658
insert into t1 values (19);
2659
insert into t2 values (19);
2660
insert into t3 values (19);
2661
insert into t4 values (60,19);
2662
insert into t5 values (60,19);
2663
insert into t6 values (60,19);
2664
select * from t1 order by col1;
2668
select * from t2 order by col1;
2673
select * from t3 order by col1;
2678
select * from t4 order by colint;
2726
select * from t5 order by colint;
2774
select * from t6 order by colint;
2822
alter table t1 drop partition p0;
2823
alter table t2 drop partition p0;
2824
alter table t4 drop partition p0;
2825
alter table t5 drop partition p0;
2826
alter table t6 drop partition p0;
2827
select * from t1 order by col1;
2829
select * from t2 order by col1;
2831
select * from t3 order by col1;
2836
select * from t4 order by colint;
2870
select * from t5 order by colint;
2908
select * from t6 order by colint;
2952
-------------------------------------------------------------------------
2953
--- Delete rows and partitions of tables with mod(col1,10)
2954
-------------------------------------------------------------------------
2955
delete from t11 where col1=19;
2956
delete from t22 where col1=19;
2957
delete from t33 where col1=19;
2958
delete from t44 where col1=19;
2959
delete from t55 where col1=19;
2960
delete from t66 where col1=19;
2961
select * from t11 order by col1;
2964
select * from t22 order by col1;
2968
select * from t33 order by col1;
2972
select * from t44 order by colint;
3019
select * from t55 order by colint;
3066
insert into t11 values (19);
3067
insert into t22 values (19);
3068
insert into t33 values (19);
3069
insert into t44 values (60,19);
3070
insert into t55 values (60,19);
3071
insert into t66 values (60,19);
3072
select * from t11 order by col1;
3076
select * from t22 order by col1;
3081
select * from t33 order by col1;
3086
select * from t44 order by colint;
3134
select * from t55 order by colint;
3182
select * from t66 order by colint;
3230
alter table t11 drop partition p0;
3231
alter table t22 drop partition p0;
3232
alter table t44 drop partition p0;
3233
alter table t55 drop partition p0;
3234
alter table t66 drop partition p0;
3235
select * from t11 order by col1;
3237
select * from t22 order by col1;
3239
select * from t33 order by col1;
3244
select * from t44 order by colint;
3278
select * from t55 order by colint;
3316
select * from t66 order by colint;
3360
-------------------------
3361
---- some alter table end
3362
-------------------------
3363
drop table if exists t1 ;
3364
drop table if exists t2 ;
3365
drop table if exists t3 ;
3366
drop table if exists t4 ;
3367
drop table if exists t5 ;
3368
drop table if exists t6 ;
3369
drop table if exists t11 ;
3370
drop table if exists t22 ;
3371
drop table if exists t33 ;
3372
drop table if exists t44 ;
3373
drop table if exists t55 ;
3374
drop table if exists t66 ;
3375
-------------------------------------------------------------------------
3376
--- day(col1) in partition with coltype date
3377
-------------------------------------------------------------------------
3378
drop table if exists t1 ;
3379
drop table if exists t2 ;
3380
drop table if exists t3 ;
3381
drop table if exists t4 ;
3382
drop table if exists t5 ;
3383
drop table if exists t6 ;
3384
-------------------------------------------------------------------------
3385
--- Create tables with day(col1)
3386
-------------------------------------------------------------------------
3387
create table t1 (col1 date) engine='MYISAM'
3388
partition by range(day(col1))
3389
(partition p0 values less than (15),
3390
partition p1 values less than maxvalue);
3391
create table t2 (col1 date) engine='MYISAM'
3392
partition by list(day(col1))
3393
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3394
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3395
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3396
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3397
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3398
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3400
create table t3 (col1 date) engine='MYISAM'
3401
partition by hash(day(col1));
3402
create table t4 (colint int, col1 date) engine='MYISAM'
3403
partition by range(colint)
3404
subpartition by hash(day(col1)) subpartitions 2
3405
(partition p0 values less than (15),
3406
partition p1 values less than maxvalue);
3407
create table t5 (colint int, col1 date) engine='MYISAM'
3408
partition by list(colint)
3409
subpartition by hash(day(col1)) subpartitions 2
3410
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3411
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3412
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3413
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3414
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3415
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3417
create table t6 (colint int, col1 date) engine='MYISAM'
3418
partition by range(colint)
3419
(partition p0 values less than (day('2006-12-21')),
3420
partition p1 values less than maxvalue);
3421
-------------------------------------------------------------------------
3422
--- Access tables with day(col1)
3423
-------------------------------------------------------------------------
3424
insert into t1 values ('2006-02-03');
3425
insert into t1 values ('2006-01-17');
3426
insert into t2 values ('2006-02-03');
3427
insert into t2 values ('2006-01-17');
3428
insert into t2 values ('2006-01-25');
3429
insert into t3 values ('2006-02-03');
3430
insert into t3 values ('2006-01-17');
3431
insert into t3 values ('2006-01-25');
3432
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
3433
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
3434
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
3435
select day(col1) from t1 order by col1;
3439
select * from t1 order by col1;
3443
select * from t2 order by col1;
3448
select * from t3 order by col1;
3453
select * from t4 order by colint;
3459
select * from t5 order by colint;
3465
select * from t6 order by colint;
3471
update t1 set col1='2006-02-05' where col1='2006-02-03';
3472
update t2 set col1='2006-02-05' where col1='2006-02-03';
3473
update t3 set col1='2006-02-05' where col1='2006-02-03';
3474
update t4 set col1='2006-02-05' where col1='2006-02-03';
3475
update t5 set col1='2006-02-05' where col1='2006-02-03';
3476
update t6 set col1='2006-02-05' where col1='2006-02-03';
3477
select * from t1 order by col1;
3481
select * from t2 order by col1;
3486
select * from t3 order by col1;
3491
select * from t4 order by colint;
3497
select * from t5 order by colint;
3503
select * from t6 order by colint;
3509
-------------------------------------------------------------------------
3510
--- Alter tables with day(col1)
3511
-------------------------------------------------------------------------
3512
drop table if exists t11 ;
3513
drop table if exists t22 ;
3514
drop table if exists t33 ;
3515
drop table if exists t44 ;
3516
drop table if exists t55 ;
3517
drop table if exists t66 ;
3518
create table t11 engine='MYISAM' as select * from t1;
3519
create table t22 engine='MYISAM' as select * from t2;
3520
create table t33 engine='MYISAM' as select * from t3;
3521
create table t44 engine='MYISAM' as select * from t4;
3522
create table t55 engine='MYISAM' as select * from t5;
3523
create table t66 engine='MYISAM' as select * from t6;
3525
partition by range(day(col1))
3526
(partition p0 values less than (15),
3527
partition p1 values less than maxvalue);
3529
partition by list(day(col1))
3530
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3531
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3532
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3533
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3534
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3535
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3538
partition by hash(day(col1));
3540
partition by range(colint)
3541
subpartition by hash(day(col1)) subpartitions 2
3542
(partition p0 values less than (15),
3543
partition p1 values less than maxvalue);
3545
partition by list(colint)
3546
subpartition by hash(day(col1)) subpartitions 2
3547
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3548
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3549
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3550
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3551
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3552
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3555
partition by range(colint)
3556
(partition p0 values less than (day('2006-12-21')),
3557
partition p1 values less than maxvalue);
3558
select * from t11 order by col1;
3562
select * from t22 order by col1;
3567
select * from t33 order by col1;
3572
select * from t44 order by colint;
3578
select * from t55 order by colint;
3584
select * from t66 order by colint;
3590
---------------------------
3591
---- some alter table begin
3592
---------------------------
3594
reorganize partition p0,p1 into
3595
(partition s1 values less than maxvalue);
3596
select * from t11 order by col1;
3601
reorganize partition s1 into
3602
(partition p0 values less than (15),
3603
partition p1 values less than maxvalue);
3604
select * from t11 order by col1;
3609
partition by list(colint)
3610
subpartition by hash(day(col1)) subpartitions 5
3611
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3612
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3613
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3614
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3615
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3616
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3618
show create table t55;
3620
t55 CREATE TABLE `t55` (
3621
`colint` int(11) DEFAULT NULL,
3622
`col1` date DEFAULT NULL
3623
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (day(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
3624
select * from t55 order by colint;
3631
reorganize partition p0,p1 into
3632
(partition s1 values less than maxvalue);
3633
select * from t66 order by colint;
3640
reorganize partition s1 into
3641
(partition p0 values less than (day('2006-12-21')),
3642
partition p1 values less than maxvalue);
3643
select * from t66 order by colint;
3650
reorganize partition p0,p1 into
3651
(partition s1 values less than maxvalue);
3652
select * from t66 order by colint;
3659
reorganize partition s1 into
3660
(partition p0 values less than (day('2006-12-21')),
3661
partition p1 values less than maxvalue);
3662
select * from t66 order by colint;
3668
-------------------------------------------------------------------------
3669
--- Delete rows and partitions of tables with day(col1)
3670
-------------------------------------------------------------------------
3671
delete from t1 where col1='2006-01-17';
3672
delete from t2 where col1='2006-01-17';
3673
delete from t3 where col1='2006-01-17';
3674
delete from t4 where col1='2006-01-17';
3675
delete from t5 where col1='2006-01-17';
3676
delete from t6 where col1='2006-01-17';
3677
select * from t1 order by col1;
3680
select * from t2 order by col1;
3684
select * from t3 order by col1;
3688
select * from t4 order by colint;
3693
select * from t5 order by colint;
3698
insert into t1 values ('2006-01-17');
3699
insert into t2 values ('2006-01-17');
3700
insert into t3 values ('2006-01-17');
3701
insert into t4 values (60,'2006-01-17');
3702
insert into t5 values (60,'2006-01-17');
3703
insert into t6 values (60,'2006-01-17');
3704
select * from t1 order by col1;
3708
select * from t2 order by col1;
3713
select * from t3 order by col1;
3718
select * from t4 order by colint;
3724
select * from t5 order by colint;
3730
select * from t6 order by colint;
3736
alter table t1 drop partition p0;
3737
alter table t2 drop partition p0;
3738
alter table t4 drop partition p0;
3739
alter table t5 drop partition p0;
3740
alter table t6 drop partition p0;
3741
select * from t1 order by col1;
3744
select * from t2 order by col1;
3748
select * from t3 order by col1;
3753
select * from t4 order by colint;
3756
select * from t5 order by colint;
3759
select * from t6 order by colint;
3762
-------------------------------------------------------------------------
3763
--- Delete rows and partitions of tables with day(col1)
3764
-------------------------------------------------------------------------
3765
delete from t11 where col1='2006-01-17';
3766
delete from t22 where col1='2006-01-17';
3767
delete from t33 where col1='2006-01-17';
3768
delete from t44 where col1='2006-01-17';
3769
delete from t55 where col1='2006-01-17';
3770
delete from t66 where col1='2006-01-17';
3771
select * from t11 order by col1;
3774
select * from t22 order by col1;
3778
select * from t33 order by col1;
3782
select * from t44 order by colint;
3787
select * from t55 order by colint;
3792
insert into t11 values ('2006-01-17');
3793
insert into t22 values ('2006-01-17');
3794
insert into t33 values ('2006-01-17');
3795
insert into t44 values (60,'2006-01-17');
3796
insert into t55 values (60,'2006-01-17');
3797
insert into t66 values (60,'2006-01-17');
3798
select * from t11 order by col1;
3802
select * from t22 order by col1;
3807
select * from t33 order by col1;
3812
select * from t44 order by colint;
3818
select * from t55 order by colint;
3824
select * from t66 order by colint;
3830
alter table t11 drop partition p0;
3831
alter table t22 drop partition p0;
3832
alter table t44 drop partition p0;
3833
alter table t55 drop partition p0;
3834
alter table t66 drop partition p0;
3835
select * from t11 order by col1;
3838
select * from t22 order by col1;
3842
select * from t33 order by col1;
3847
select * from t44 order by colint;
3850
select * from t55 order by colint;
3853
select * from t66 order by colint;
3856
-------------------------
3857
---- some alter table end
3858
-------------------------
3859
drop table if exists t1 ;
3860
drop table if exists t2 ;
3861
drop table if exists t3 ;
3862
drop table if exists t4 ;
3863
drop table if exists t5 ;
3864
drop table if exists t6 ;
3865
drop table if exists t11 ;
3866
drop table if exists t22 ;
3867
drop table if exists t33 ;
3868
drop table if exists t44 ;
3869
drop table if exists t55 ;
3870
drop table if exists t66 ;
3871
-------------------------------------------------------------------------
3872
--- dayofmonth(col1) in partition with coltype date
3873
-------------------------------------------------------------------------
3874
drop table if exists t1 ;
3875
drop table if exists t2 ;
3876
drop table if exists t3 ;
3877
drop table if exists t4 ;
3878
drop table if exists t5 ;
3879
drop table if exists t6 ;
3880
-------------------------------------------------------------------------
3881
--- Create tables with dayofmonth(col1)
3882
-------------------------------------------------------------------------
3883
create table t1 (col1 date) engine='MYISAM'
3884
partition by range(dayofmonth(col1))
3885
(partition p0 values less than (15),
3886
partition p1 values less than maxvalue);
3887
create table t2 (col1 date) engine='MYISAM'
3888
partition by list(dayofmonth(col1))
3889
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3890
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3891
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3892
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3893
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3894
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3896
create table t3 (col1 date) engine='MYISAM'
3897
partition by hash(dayofmonth(col1));
3898
create table t4 (colint int, col1 date) engine='MYISAM'
3899
partition by range(colint)
3900
subpartition by hash(dayofmonth(col1)) subpartitions 2
3901
(partition p0 values less than (15),
3902
partition p1 values less than maxvalue);
3903
create table t5 (colint int, col1 date) engine='MYISAM'
3904
partition by list(colint)
3905
subpartition by hash(dayofmonth(col1)) subpartitions 2
3906
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3907
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3908
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3909
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3910
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3911
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3913
create table t6 (colint int, col1 date) engine='MYISAM'
3914
partition by range(colint)
3915
(partition p0 values less than (dayofmonth('2006-12-24')),
3916
partition p1 values less than maxvalue);
3917
-------------------------------------------------------------------------
3918
--- Access tables with dayofmonth(col1)
3919
-------------------------------------------------------------------------
3920
insert into t1 values ('2006-02-03');
3921
insert into t1 values ('2006-01-17');
3922
insert into t2 values ('2006-02-03');
3923
insert into t2 values ('2006-01-17');
3924
insert into t2 values ('2006-01-25');
3925
insert into t3 values ('2006-02-03');
3926
insert into t3 values ('2006-01-17');
3927
insert into t3 values ('2006-01-25');
3928
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
3929
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
3930
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
3931
select dayofmonth(col1) from t1 order by col1;
3935
select * from t1 order by col1;
3939
select * from t2 order by col1;
3944
select * from t3 order by col1;
3949
select * from t4 order by colint;
3955
select * from t5 order by colint;
3961
select * from t6 order by colint;
3967
update t1 set col1='2006-02-05' where col1='2006-02-03';
3968
update t2 set col1='2006-02-05' where col1='2006-02-03';
3969
update t3 set col1='2006-02-05' where col1='2006-02-03';
3970
update t4 set col1='2006-02-05' where col1='2006-02-03';
3971
update t5 set col1='2006-02-05' where col1='2006-02-03';
3972
update t6 set col1='2006-02-05' where col1='2006-02-03';
3973
select * from t1 order by col1;
3977
select * from t2 order by col1;
3982
select * from t3 order by col1;
3987
select * from t4 order by colint;
3993
select * from t5 order by colint;
3999
select * from t6 order by colint;
4005
-------------------------------------------------------------------------
4006
--- Alter tables with dayofmonth(col1)
4007
-------------------------------------------------------------------------
4008
drop table if exists t11 ;
4009
drop table if exists t22 ;
4010
drop table if exists t33 ;
4011
drop table if exists t44 ;
4012
drop table if exists t55 ;
4013
drop table if exists t66 ;
4014
create table t11 engine='MYISAM' as select * from t1;
4015
create table t22 engine='MYISAM' as select * from t2;
4016
create table t33 engine='MYISAM' as select * from t3;
4017
create table t44 engine='MYISAM' as select * from t4;
4018
create table t55 engine='MYISAM' as select * from t5;
4019
create table t66 engine='MYISAM' as select * from t6;
4021
partition by range(dayofmonth(col1))
4022
(partition p0 values less than (15),
4023
partition p1 values less than maxvalue);
4025
partition by list(dayofmonth(col1))
4026
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4027
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4028
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4029
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4030
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4031
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4034
partition by hash(dayofmonth(col1));
4036
partition by range(colint)
4037
subpartition by hash(dayofmonth(col1)) subpartitions 2
4038
(partition p0 values less than (15),
4039
partition p1 values less than maxvalue);
4041
partition by list(colint)
4042
subpartition by hash(dayofmonth(col1)) subpartitions 2
4043
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4044
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4045
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4046
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4047
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4048
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4051
partition by range(colint)
4052
(partition p0 values less than (dayofmonth('2006-12-24')),
4053
partition p1 values less than maxvalue);
4054
select * from t11 order by col1;
4058
select * from t22 order by col1;
4063
select * from t33 order by col1;
4068
select * from t44 order by colint;
4074
select * from t55 order by colint;
4080
select * from t66 order by colint;
4086
---------------------------
4087
---- some alter table begin
4088
---------------------------
4090
reorganize partition p0,p1 into
4091
(partition s1 values less than maxvalue);
4092
select * from t11 order by col1;
4097
reorganize partition s1 into
4098
(partition p0 values less than (15),
4099
partition p1 values less than maxvalue);
4100
select * from t11 order by col1;
4105
partition by list(colint)
4106
subpartition by hash(dayofmonth(col1)) subpartitions 5
4107
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4108
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4109
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4110
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4111
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4112
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4114
show create table t55;
4116
t55 CREATE TABLE `t55` (
4117
`colint` int(11) DEFAULT NULL,
4118
`col1` date DEFAULT NULL
4119
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofmonth(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
4120
select * from t55 order by colint;
4127
reorganize partition p0,p1 into
4128
(partition s1 values less than maxvalue);
4129
select * from t66 order by colint;
4136
reorganize partition s1 into
4137
(partition p0 values less than (dayofmonth('2006-12-24')),
4138
partition p1 values less than maxvalue);
4139
select * from t66 order by colint;
4146
reorganize partition p0,p1 into
4147
(partition s1 values less than maxvalue);
4148
select * from t66 order by colint;
4155
reorganize partition s1 into
4156
(partition p0 values less than (dayofmonth('2006-12-24')),
4157
partition p1 values less than maxvalue);
4158
select * from t66 order by colint;
4164
-------------------------------------------------------------------------
4165
--- Delete rows and partitions of tables with dayofmonth(col1)
4166
-------------------------------------------------------------------------
4167
delete from t1 where col1='2006-01-17';
4168
delete from t2 where col1='2006-01-17';
4169
delete from t3 where col1='2006-01-17';
4170
delete from t4 where col1='2006-01-17';
4171
delete from t5 where col1='2006-01-17';
4172
delete from t6 where col1='2006-01-17';
4173
select * from t1 order by col1;
4176
select * from t2 order by col1;
4180
select * from t3 order by col1;
4184
select * from t4 order by colint;
4189
select * from t5 order by colint;
4194
insert into t1 values ('2006-01-17');
4195
insert into t2 values ('2006-01-17');
4196
insert into t3 values ('2006-01-17');
4197
insert into t4 values (60,'2006-01-17');
4198
insert into t5 values (60,'2006-01-17');
4199
insert into t6 values (60,'2006-01-17');
4200
select * from t1 order by col1;
4204
select * from t2 order by col1;
4209
select * from t3 order by col1;
4214
select * from t4 order by colint;
4220
select * from t5 order by colint;
4226
select * from t6 order by colint;
4232
alter table t1 drop partition p0;
4233
alter table t2 drop partition p0;
4234
alter table t4 drop partition p0;
4235
alter table t5 drop partition p0;
4236
alter table t6 drop partition p0;
4237
select * from t1 order by col1;
4240
select * from t2 order by col1;
4244
select * from t3 order by col1;
4249
select * from t4 order by colint;
4252
select * from t5 order by colint;
4255
select * from t6 order by colint;
4258
-------------------------------------------------------------------------
4259
--- Delete rows and partitions of tables with dayofmonth(col1)
4260
-------------------------------------------------------------------------
4261
delete from t11 where col1='2006-01-17';
4262
delete from t22 where col1='2006-01-17';
4263
delete from t33 where col1='2006-01-17';
4264
delete from t44 where col1='2006-01-17';
4265
delete from t55 where col1='2006-01-17';
4266
delete from t66 where col1='2006-01-17';
4267
select * from t11 order by col1;
4270
select * from t22 order by col1;
4274
select * from t33 order by col1;
4278
select * from t44 order by colint;
4283
select * from t55 order by colint;
4288
insert into t11 values ('2006-01-17');
4289
insert into t22 values ('2006-01-17');
4290
insert into t33 values ('2006-01-17');
4291
insert into t44 values (60,'2006-01-17');
4292
insert into t55 values (60,'2006-01-17');
4293
insert into t66 values (60,'2006-01-17');
4294
select * from t11 order by col1;
4298
select * from t22 order by col1;
4303
select * from t33 order by col1;
4308
select * from t44 order by colint;
4314
select * from t55 order by colint;
4320
select * from t66 order by colint;
4326
alter table t11 drop partition p0;
4327
alter table t22 drop partition p0;
4328
alter table t44 drop partition p0;
4329
alter table t55 drop partition p0;
4330
alter table t66 drop partition p0;
4331
select * from t11 order by col1;
4334
select * from t22 order by col1;
4338
select * from t33 order by col1;
4343
select * from t44 order by colint;
4346
select * from t55 order by colint;
4349
select * from t66 order by colint;
4352
-------------------------
4353
---- some alter table end
4354
-------------------------
4355
drop table if exists t1 ;
4356
drop table if exists t2 ;
4357
drop table if exists t3 ;
4358
drop table if exists t4 ;
4359
drop table if exists t5 ;
4360
drop table if exists t6 ;
4361
drop table if exists t11 ;
4362
drop table if exists t22 ;
4363
drop table if exists t33 ;
4364
drop table if exists t44 ;
4365
drop table if exists t55 ;
4366
drop table if exists t66 ;
4367
-------------------------------------------------------------------------
4368
--- dayofweek(col1) in partition with coltype date
4369
-------------------------------------------------------------------------
4370
drop table if exists t1 ;
4371
drop table if exists t2 ;
4372
drop table if exists t3 ;
4373
drop table if exists t4 ;
4374
drop table if exists t5 ;
4375
drop table if exists t6 ;
4376
-------------------------------------------------------------------------
4377
--- Create tables with dayofweek(col1)
4378
-------------------------------------------------------------------------
4379
create table t1 (col1 date) engine='MYISAM'
4380
partition by range(dayofweek(col1))
4381
(partition p0 values less than (15),
4382
partition p1 values less than maxvalue);
4383
create table t2 (col1 date) engine='MYISAM'
4384
partition by list(dayofweek(col1))
4385
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4386
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4387
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4388
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4389
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4390
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4392
create table t3 (col1 date) engine='MYISAM'
4393
partition by hash(dayofweek(col1));
4394
create table t4 (colint int, col1 date) engine='MYISAM'
4395
partition by range(colint)
4396
subpartition by hash(dayofweek(col1)) subpartitions 2
4397
(partition p0 values less than (15),
4398
partition p1 values less than maxvalue);
4399
create table t5 (colint int, col1 date) engine='MYISAM'
4400
partition by list(colint)
4401
subpartition by hash(dayofweek(col1)) subpartitions 2
4402
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4403
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4404
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4405
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4406
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4407
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4409
create table t6 (colint int, col1 date) engine='MYISAM'
4410
partition by range(colint)
4411
(partition p0 values less than (dayofweek('2006-12-24')),
4412
partition p1 values less than maxvalue);
4413
-------------------------------------------------------------------------
4414
--- Access tables with dayofweek(col1)
4415
-------------------------------------------------------------------------
4416
insert into t1 values ('2006-01-03');
4417
insert into t1 values ('2006-02-17');
4418
insert into t2 values ('2006-01-03');
4419
insert into t2 values ('2006-02-17');
4420
insert into t2 values ('2006-01-25');
4421
insert into t3 values ('2006-01-03');
4422
insert into t3 values ('2006-02-17');
4423
insert into t3 values ('2006-01-25');
4424
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
4425
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
4426
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
4427
select dayofweek(col1) from t1 order by col1;
4431
select * from t1 order by col1;
4435
select * from t2 order by col1;
4440
select * from t3 order by col1;
4445
select * from t4 order by colint;
4451
select * from t5 order by colint;
4457
select * from t6 order by colint;
4463
update t1 set col1='2006-02-05' where col1='2006-01-03';
4464
update t2 set col1='2006-02-05' where col1='2006-01-03';
4465
update t3 set col1='2006-02-05' where col1='2006-01-03';
4466
update t4 set col1='2006-02-05' where col1='2006-01-03';
4467
update t5 set col1='2006-02-05' where col1='2006-01-03';
4468
update t6 set col1='2006-02-05' where col1='2006-01-03';
4469
select * from t1 order by col1;
4473
select * from t2 order by col1;
4478
select * from t3 order by col1;
4483
select * from t4 order by colint;
4489
select * from t5 order by colint;
4495
select * from t6 order by colint;
4501
-------------------------------------------------------------------------
4502
--- Alter tables with dayofweek(col1)
4503
-------------------------------------------------------------------------
4504
drop table if exists t11 ;
4505
drop table if exists t22 ;
4506
drop table if exists t33 ;
4507
drop table if exists t44 ;
4508
drop table if exists t55 ;
4509
drop table if exists t66 ;
4510
create table t11 engine='MYISAM' as select * from t1;
4511
create table t22 engine='MYISAM' as select * from t2;
4512
create table t33 engine='MYISAM' as select * from t3;
4513
create table t44 engine='MYISAM' as select * from t4;
4514
create table t55 engine='MYISAM' as select * from t5;
4515
create table t66 engine='MYISAM' as select * from t6;
4517
partition by range(dayofweek(col1))
4518
(partition p0 values less than (15),
4519
partition p1 values less than maxvalue);
4521
partition by list(dayofweek(col1))
4522
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4523
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4524
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4525
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4526
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4527
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4530
partition by hash(dayofweek(col1));
4532
partition by range(colint)
4533
subpartition by hash(dayofweek(col1)) subpartitions 2
4534
(partition p0 values less than (15),
4535
partition p1 values less than maxvalue);
4537
partition by list(colint)
4538
subpartition by hash(dayofweek(col1)) subpartitions 2
4539
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4540
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4541
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4542
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4543
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4544
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4547
partition by range(colint)
4548
(partition p0 values less than (dayofweek('2006-12-24')),
4549
partition p1 values less than maxvalue);
4550
select * from t11 order by col1;
4554
select * from t22 order by col1;
4559
select * from t33 order by col1;
4564
select * from t44 order by colint;
4570
select * from t55 order by colint;
4576
select * from t66 order by colint;
4582
---------------------------
4583
---- some alter table begin
4584
---------------------------
4586
reorganize partition p0,p1 into
4587
(partition s1 values less than maxvalue);
4588
select * from t11 order by col1;
4593
reorganize partition s1 into
4594
(partition p0 values less than (15),
4595
partition p1 values less than maxvalue);
4596
select * from t11 order by col1;
4601
partition by list(colint)
4602
subpartition by hash(dayofweek(col1)) subpartitions 5
4603
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4604
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4605
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4606
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4607
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4608
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4610
show create table t55;
4612
t55 CREATE TABLE `t55` (
4613
`colint` int(11) DEFAULT NULL,
4614
`col1` date DEFAULT NULL
4615
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofweek(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
4616
select * from t55 order by colint;
4623
reorganize partition p0,p1 into
4624
(partition s1 values less than maxvalue);
4625
select * from t66 order by colint;
4632
reorganize partition s1 into
4633
(partition p0 values less than (dayofweek('2006-12-24')),
4634
partition p1 values less than maxvalue);
4635
select * from t66 order by colint;
4642
reorganize partition p0,p1 into
4643
(partition s1 values less than maxvalue);
4644
select * from t66 order by colint;
4651
reorganize partition s1 into
4652
(partition p0 values less than (dayofweek('2006-12-24')),
4653
partition p1 values less than maxvalue);
4654
select * from t66 order by colint;
4660
-------------------------------------------------------------------------
4661
--- Delete rows and partitions of tables with dayofweek(col1)
4662
-------------------------------------------------------------------------
4663
delete from t1 where col1='2006-02-17';
4664
delete from t2 where col1='2006-02-17';
4665
delete from t3 where col1='2006-02-17';
4666
delete from t4 where col1='2006-02-17';
4667
delete from t5 where col1='2006-02-17';
4668
delete from t6 where col1='2006-02-17';
4669
select * from t1 order by col1;
4672
select * from t2 order by col1;
4676
select * from t3 order by col1;
4680
select * from t4 order by colint;
4686
select * from t5 order by colint;
4692
insert into t1 values ('2006-02-17');
4693
insert into t2 values ('2006-02-17');
4694
insert into t3 values ('2006-02-17');
4695
insert into t4 values (60,'2006-02-17');
4696
insert into t5 values (60,'2006-02-17');
4697
insert into t6 values (60,'2006-02-17');
4698
select * from t1 order by col1;
4702
select * from t2 order by col1;
4707
select * from t3 order by col1;
4712
select * from t4 order by colint;
4719
select * from t5 order by colint;
4726
select * from t6 order by colint;
4733
alter table t1 drop partition p0;
4734
alter table t2 drop partition p0;
4735
alter table t4 drop partition p0;
4736
alter table t5 drop partition p0;
4737
alter table t6 drop partition p0;
4738
select * from t1 order by col1;
4740
select * from t2 order by col1;
4742
select * from t3 order by col1;
4747
select * from t4 order by colint;
4750
select * from t5 order by colint;
4753
select * from t6 order by colint;
4760
-------------------------------------------------------------------------
4761
--- Delete rows and partitions of tables with dayofweek(col1)
4762
-------------------------------------------------------------------------
4763
delete from t11 where col1='2006-02-17';
4764
delete from t22 where col1='2006-02-17';
4765
delete from t33 where col1='2006-02-17';
4766
delete from t44 where col1='2006-02-17';
4767
delete from t55 where col1='2006-02-17';
4768
delete from t66 where col1='2006-02-17';
4769
select * from t11 order by col1;
4772
select * from t22 order by col1;
4776
select * from t33 order by col1;
4780
select * from t44 order by colint;
4786
select * from t55 order by colint;
4792
insert into t11 values ('2006-02-17');
4793
insert into t22 values ('2006-02-17');
4794
insert into t33 values ('2006-02-17');
4795
insert into t44 values (60,'2006-02-17');
4796
insert into t55 values (60,'2006-02-17');
4797
insert into t66 values (60,'2006-02-17');
4798
select * from t11 order by col1;
4802
select * from t22 order by col1;
4807
select * from t33 order by col1;
4812
select * from t44 order by colint;
4819
select * from t55 order by colint;
4826
select * from t66 order by colint;
4833
alter table t11 drop partition p0;
4834
alter table t22 drop partition p0;
4835
alter table t44 drop partition p0;
4836
alter table t55 drop partition p0;
4837
alter table t66 drop partition p0;
4838
select * from t11 order by col1;
4840
select * from t22 order by col1;
4842
select * from t33 order by col1;
4847
select * from t44 order by colint;
4850
select * from t55 order by colint;
4853
select * from t66 order by colint;
4860
-------------------------
4861
---- some alter table end
4862
-------------------------
4863
drop table if exists t1 ;
4864
drop table if exists t2 ;
4865
drop table if exists t3 ;
4866
drop table if exists t4 ;
4867
drop table if exists t5 ;
4868
drop table if exists t6 ;
4869
drop table if exists t11 ;
4870
drop table if exists t22 ;
4871
drop table if exists t33 ;
4872
drop table if exists t44 ;
4873
drop table if exists t55 ;
4874
drop table if exists t66 ;
4875
-------------------------------------------------------------------------
4876
--- dayofyear(col1) in partition with coltype date
4877
-------------------------------------------------------------------------
4878
drop table if exists t1 ;
4879
drop table if exists t2 ;
4880
drop table if exists t3 ;
4881
drop table if exists t4 ;
4882
drop table if exists t5 ;
4883
drop table if exists t6 ;
4884
-------------------------------------------------------------------------
4885
--- Create tables with dayofyear(col1)
4886
-------------------------------------------------------------------------
4887
create table t1 (col1 date) engine='MYISAM'
4888
partition by range(dayofyear(col1))
4889
(partition p0 values less than (15),
4890
partition p1 values less than maxvalue);
4891
create table t2 (col1 date) engine='MYISAM'
4892
partition by list(dayofyear(col1))
4893
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4894
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4895
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4896
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4897
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4898
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4900
create table t3 (col1 date) engine='MYISAM'
4901
partition by hash(dayofyear(col1));
4902
create table t4 (colint int, col1 date) engine='MYISAM'
4903
partition by range(colint)
4904
subpartition by hash(dayofyear(col1)) subpartitions 2
4905
(partition p0 values less than (15),
4906
partition p1 values less than maxvalue);
4907
create table t5 (colint int, col1 date) engine='MYISAM'
4908
partition by list(colint)
4909
subpartition by hash(dayofyear(col1)) subpartitions 2
4910
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4911
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4912
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4913
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4914
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4915
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4917
create table t6 (colint int, col1 date) engine='MYISAM'
4918
partition by range(colint)
4919
(partition p0 values less than (dayofyear('2006-12-25')),
4920
partition p1 values less than maxvalue);
4921
-------------------------------------------------------------------------
4922
--- Access tables with dayofyear(col1)
4923
-------------------------------------------------------------------------
4924
insert into t1 values ('2006-01-03');
4925
insert into t1 values ('2006-01-17');
4926
insert into t2 values ('2006-01-03');
4927
insert into t2 values ('2006-01-17');
4928
insert into t2 values ('2006-02-25');
4929
insert into t3 values ('2006-01-03');
4930
insert into t3 values ('2006-01-17');
4931
insert into t3 values ('2006-02-25');
4932
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
4933
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
4934
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
4935
select dayofyear(col1) from t1 order by col1;
4939
select * from t1 order by col1;
4943
select * from t2 order by col1;
4948
select * from t3 order by col1;
4953
select * from t4 order by colint;
4959
select * from t5 order by colint;
4965
select * from t6 order by colint;
4971
update t1 set col1='2006-02-05' where col1='2006-01-03';
4972
update t2 set col1='2006-02-05' where col1='2006-01-03';
4973
update t3 set col1='2006-02-05' where col1='2006-01-03';
4974
update t4 set col1='2006-02-05' where col1='2006-01-03';
4975
update t5 set col1='2006-02-05' where col1='2006-01-03';
4976
update t6 set col1='2006-02-05' where col1='2006-01-03';
4977
select * from t1 order by col1;
4981
select * from t2 order by col1;
4986
select * from t3 order by col1;
4991
select * from t4 order by colint;
4997
select * from t5 order by colint;
5003
select * from t6 order by colint;
5009
-------------------------------------------------------------------------
5010
--- Alter tables with dayofyear(col1)
5011
-------------------------------------------------------------------------
5012
drop table if exists t11 ;
5013
drop table if exists t22 ;
5014
drop table if exists t33 ;
5015
drop table if exists t44 ;
5016
drop table if exists t55 ;
5017
drop table if exists t66 ;
5018
create table t11 engine='MYISAM' as select * from t1;
5019
create table t22 engine='MYISAM' as select * from t2;
5020
create table t33 engine='MYISAM' as select * from t3;
5021
create table t44 engine='MYISAM' as select * from t4;
5022
create table t55 engine='MYISAM' as select * from t5;
5023
create table t66 engine='MYISAM' as select * from t6;
5025
partition by range(dayofyear(col1))
5026
(partition p0 values less than (15),
5027
partition p1 values less than maxvalue);
5029
partition by list(dayofyear(col1))
5030
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5031
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5032
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5033
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5034
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5035
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5038
partition by hash(dayofyear(col1));
5040
partition by range(colint)
5041
subpartition by hash(dayofyear(col1)) subpartitions 2
5042
(partition p0 values less than (15),
5043
partition p1 values less than maxvalue);
5045
partition by list(colint)
5046
subpartition by hash(dayofyear(col1)) subpartitions 2
5047
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5048
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5049
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5050
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5051
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5052
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5055
partition by range(colint)
5056
(partition p0 values less than (dayofyear('2006-12-25')),
5057
partition p1 values less than maxvalue);
5058
select * from t11 order by col1;
5062
select * from t22 order by col1;
5067
select * from t33 order by col1;
5072
select * from t44 order by colint;
5078
select * from t55 order by colint;
5084
select * from t66 order by colint;
5090
---------------------------
5091
---- some alter table begin
5092
---------------------------
5094
reorganize partition p0,p1 into
5095
(partition s1 values less than maxvalue);
5096
select * from t11 order by col1;
5101
reorganize partition s1 into
5102
(partition p0 values less than (15),
5103
partition p1 values less than maxvalue);
5104
select * from t11 order by col1;
5109
partition by list(colint)
5110
subpartition by hash(dayofyear(col1)) subpartitions 5
5111
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5112
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5113
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5114
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5115
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5116
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5118
show create table t55;
5120
t55 CREATE TABLE `t55` (
5121
`colint` int(11) DEFAULT NULL,
5122
`col1` date DEFAULT NULL
5123
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofyear(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
5124
select * from t55 order by colint;
5131
reorganize partition p0,p1 into
5132
(partition s1 values less than maxvalue);
5133
select * from t66 order by colint;
5140
reorganize partition s1 into
5141
(partition p0 values less than (dayofyear('2006-12-25')),
5142
partition p1 values less than maxvalue);
5143
select * from t66 order by colint;
5150
reorganize partition p0,p1 into
5151
(partition s1 values less than maxvalue);
5152
select * from t66 order by colint;
5159
reorganize partition s1 into
5160
(partition p0 values less than (dayofyear('2006-12-25')),
5161
partition p1 values less than maxvalue);
5162
select * from t66 order by colint;
5168
-------------------------------------------------------------------------
5169
--- Delete rows and partitions of tables with dayofyear(col1)
5170
-------------------------------------------------------------------------
5171
delete from t1 where col1='2006-01-17';
5172
delete from t2 where col1='2006-01-17';
5173
delete from t3 where col1='2006-01-17';
5174
delete from t4 where col1='2006-01-17';
5175
delete from t5 where col1='2006-01-17';
5176
delete from t6 where col1='2006-01-17';
5177
select * from t1 order by col1;
5180
select * from t2 order by col1;
5184
select * from t3 order by col1;
5188
select * from t4 order by colint;
5193
select * from t5 order by colint;
5198
insert into t1 values ('2006-01-17');
5199
insert into t2 values ('2006-01-17');
5200
insert into t3 values ('2006-01-17');
5201
insert into t4 values (60,'2006-01-17');
5202
insert into t5 values (60,'2006-01-17');
5203
insert into t6 values (60,'2006-01-17');
5204
select * from t1 order by col1;
5208
select * from t2 order by col1;
5213
select * from t3 order by col1;
5218
select * from t4 order by colint;
5224
select * from t5 order by colint;
5230
select * from t6 order by colint;
5236
alter table t1 drop partition p0;
5237
alter table t2 drop partition p0;
5238
alter table t4 drop partition p0;
5239
alter table t5 drop partition p0;
5240
alter table t6 drop partition p0;
5241
select * from t1 order by col1;
5245
select * from t2 order by col1;
5250
select * from t3 order by col1;
5255
select * from t4 order by colint;
5258
select * from t5 order by colint;
5261
select * from t6 order by colint;
5263
-------------------------------------------------------------------------
5264
--- Delete rows and partitions of tables with dayofyear(col1)
5265
-------------------------------------------------------------------------
5266
delete from t11 where col1='2006-01-17';
5267
delete from t22 where col1='2006-01-17';
5268
delete from t33 where col1='2006-01-17';
5269
delete from t44 where col1='2006-01-17';
5270
delete from t55 where col1='2006-01-17';
5271
delete from t66 where col1='2006-01-17';
5272
select * from t11 order by col1;
5275
select * from t22 order by col1;
5279
select * from t33 order by col1;
5283
select * from t44 order by colint;
5288
select * from t55 order by colint;
5293
insert into t11 values ('2006-01-17');
5294
insert into t22 values ('2006-01-17');
5295
insert into t33 values ('2006-01-17');
5296
insert into t44 values (60,'2006-01-17');
5297
insert into t55 values (60,'2006-01-17');
5298
insert into t66 values (60,'2006-01-17');
5299
select * from t11 order by col1;
5303
select * from t22 order by col1;
5308
select * from t33 order by col1;
5313
select * from t44 order by colint;
5319
select * from t55 order by colint;
5325
select * from t66 order by colint;
5331
alter table t11 drop partition p0;
5332
alter table t22 drop partition p0;
5333
alter table t44 drop partition p0;
5334
alter table t55 drop partition p0;
5335
alter table t66 drop partition p0;
5336
select * from t11 order by col1;
5340
select * from t22 order by col1;
5345
select * from t33 order by col1;
5350
select * from t44 order by colint;
5353
select * from t55 order by colint;
5356
select * from t66 order by colint;
5358
-------------------------
5359
---- some alter table end
5360
-------------------------
5361
drop table if exists t1 ;
5362
drop table if exists t2 ;
5363
drop table if exists t3 ;
5364
drop table if exists t4 ;
5365
drop table if exists t5 ;
5366
drop table if exists t6 ;
5367
drop table if exists t11 ;
5368
drop table if exists t22 ;
5369
drop table if exists t33 ;
5370
drop table if exists t44 ;
5371
drop table if exists t55 ;
5372
drop table if exists t66 ;
5373
-------------------------------------------------------------------------
5374
--- dayofyear(col1) in partition with coltype char(30)
5375
-------------------------------------------------------------------------
5376
drop table if exists t1 ;
5377
drop table if exists t2 ;
5378
drop table if exists t3 ;
5379
drop table if exists t4 ;
5380
drop table if exists t5 ;
5381
drop table if exists t6 ;
5382
-------------------------------------------------------------------------
5383
--- Create tables with dayofyear(col1)
5384
-------------------------------------------------------------------------
5385
create table t1 (col1 char(30)) engine='MYISAM'
5386
partition by range(dayofyear(col1))
5387
(partition p0 values less than (15),
5388
partition p1 values less than maxvalue);
5389
create table t2 (col1 char(30)) engine='MYISAM'
5390
partition by list(dayofyear(col1))
5391
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5392
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5393
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5394
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5395
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5396
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5398
create table t3 (col1 char(30)) engine='MYISAM'
5399
partition by hash(dayofyear(col1));
5400
create table t4 (colint int, col1 char(30)) engine='MYISAM'
5401
partition by range(colint)
5402
subpartition by hash(dayofyear(col1)) subpartitions 2
5403
(partition p0 values less than (15),
5404
partition p1 values less than maxvalue);
5405
create table t5 (colint int, col1 char(30)) engine='MYISAM'
5406
partition by list(colint)
5407
subpartition by hash(dayofyear(col1)) subpartitions 2
5408
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5409
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5410
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5411
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5412
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5413
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5415
create table t6 (colint int, col1 char(30)) engine='MYISAM'
5416
partition by range(colint)
5417
(partition p0 values less than (dayofyear('2006-12-25')),
5418
partition p1 values less than maxvalue);
5419
-------------------------------------------------------------------------
5420
--- Access tables with dayofyear(col1)
5421
-------------------------------------------------------------------------
5422
insert into t1 values ('2006-01-03');
5423
insert into t1 values ('2006-01-17');
5424
insert into t2 values ('2006-01-03');
5425
insert into t2 values ('2006-01-17');
5426
insert into t2 values ('2006-02-25');
5427
insert into t3 values ('2006-01-03');
5428
insert into t3 values ('2006-01-17');
5429
insert into t3 values ('2006-02-25');
5430
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
5431
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
5432
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
5433
select dayofyear(col1) from t1 order by col1;
5437
select * from t1 order by col1;
5441
select * from t2 order by col1;
5446
select * from t3 order by col1;
5451
select * from t4 order by colint;
5457
select * from t5 order by colint;
5463
select * from t6 order by colint;
5469
update t1 set col1='2006-02-05' where col1='2006-01-03';
5470
update t2 set col1='2006-02-05' where col1='2006-01-03';
5471
update t3 set col1='2006-02-05' where col1='2006-01-03';
5472
update t4 set col1='2006-02-05' where col1='2006-01-03';
5473
update t5 set col1='2006-02-05' where col1='2006-01-03';
5474
update t6 set col1='2006-02-05' where col1='2006-01-03';
5475
select * from t1 order by col1;
5479
select * from t2 order by col1;
5484
select * from t3 order by col1;
5489
select * from t4 order by colint;
5495
select * from t5 order by colint;
5501
select * from t6 order by colint;
5507
-------------------------------------------------------------------------
5508
--- Alter tables with dayofyear(col1)
5509
-------------------------------------------------------------------------
5510
drop table if exists t11 ;
5511
drop table if exists t22 ;
5512
drop table if exists t33 ;
5513
drop table if exists t44 ;
5514
drop table if exists t55 ;
5515
drop table if exists t66 ;
5516
create table t11 engine='MYISAM' as select * from t1;
5517
create table t22 engine='MYISAM' as select * from t2;
5518
create table t33 engine='MYISAM' as select * from t3;
5519
create table t44 engine='MYISAM' as select * from t4;
5520
create table t55 engine='MYISAM' as select * from t5;
5521
create table t66 engine='MYISAM' as select * from t6;
5523
partition by range(dayofyear(col1))
5524
(partition p0 values less than (15),
5525
partition p1 values less than maxvalue);
5527
partition by list(dayofyear(col1))
5528
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5529
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5530
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5531
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5532
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5533
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5536
partition by hash(dayofyear(col1));
5538
partition by range(colint)
5539
subpartition by hash(dayofyear(col1)) subpartitions 2
5540
(partition p0 values less than (15),
5541
partition p1 values less than maxvalue);
5543
partition by list(colint)
5544
subpartition by hash(dayofyear(col1)) subpartitions 2
5545
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5546
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5547
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5548
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5549
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5550
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5553
partition by range(colint)
5554
(partition p0 values less than (dayofyear('2006-12-25')),
5555
partition p1 values less than maxvalue);
5556
select * from t11 order by col1;
5560
select * from t22 order by col1;
5565
select * from t33 order by col1;
5570
select * from t44 order by colint;
5576
select * from t55 order by colint;
5582
select * from t66 order by colint;
5588
---------------------------
5589
---- some alter table begin
5590
---------------------------
5592
reorganize partition p0,p1 into
5593
(partition s1 values less than maxvalue);
5594
select * from t11 order by col1;
5599
reorganize partition s1 into
5600
(partition p0 values less than (15),
5601
partition p1 values less than maxvalue);
5602
select * from t11 order by col1;
5607
partition by list(colint)
5608
subpartition by hash(dayofyear(col1)) subpartitions 5
5609
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5610
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5611
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5612
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5613
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5614
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5616
show create table t55;
5618
t55 CREATE TABLE `t55` (
5619
`colint` int(11) DEFAULT NULL,
5620
`col1` char(30) DEFAULT NULL
5621
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofyear(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
5622
select * from t55 order by colint;
5629
reorganize partition p0,p1 into
5630
(partition s1 values less than maxvalue);
5631
select * from t66 order by colint;
5638
reorganize partition s1 into
5639
(partition p0 values less than (dayofyear('2006-12-25')),
5640
partition p1 values less than maxvalue);
5641
select * from t66 order by colint;
5648
reorganize partition p0,p1 into
5649
(partition s1 values less than maxvalue);
5650
select * from t66 order by colint;
5657
reorganize partition s1 into
5658
(partition p0 values less than (dayofyear('2006-12-25')),
5659
partition p1 values less than maxvalue);
5660
select * from t66 order by colint;
5666
-------------------------------------------------------------------------
5667
--- Delete rows and partitions of tables with dayofyear(col1)
5668
-------------------------------------------------------------------------
5669
delete from t1 where col1='2006-01-17';
5670
delete from t2 where col1='2006-01-17';
5671
delete from t3 where col1='2006-01-17';
5672
delete from t4 where col1='2006-01-17';
5673
delete from t5 where col1='2006-01-17';
5674
delete from t6 where col1='2006-01-17';
5675
select * from t1 order by col1;
5678
select * from t2 order by col1;
5682
select * from t3 order by col1;
5686
select * from t4 order by colint;
5691
select * from t5 order by colint;
5696
insert into t1 values ('2006-01-17');
5697
insert into t2 values ('2006-01-17');
5698
insert into t3 values ('2006-01-17');
5699
insert into t4 values (60,'2006-01-17');
5700
insert into t5 values (60,'2006-01-17');
5701
insert into t6 values (60,'2006-01-17');
5702
select * from t1 order by col1;
5706
select * from t2 order by col1;
5711
select * from t3 order by col1;
5716
select * from t4 order by colint;
5722
select * from t5 order by colint;
5728
select * from t6 order by colint;
5734
alter table t1 drop partition p0;
5735
alter table t2 drop partition p0;
5736
alter table t4 drop partition p0;
5737
alter table t5 drop partition p0;
5738
alter table t6 drop partition p0;
5739
select * from t1 order by col1;
5743
select * from t2 order by col1;
5748
select * from t3 order by col1;
5753
select * from t4 order by colint;
5756
select * from t5 order by colint;
5759
select * from t6 order by colint;
5761
-------------------------------------------------------------------------
5762
--- Delete rows and partitions of tables with dayofyear(col1)
5763
-------------------------------------------------------------------------
5764
delete from t11 where col1='2006-01-17';
5765
delete from t22 where col1='2006-01-17';
5766
delete from t33 where col1='2006-01-17';
5767
delete from t44 where col1='2006-01-17';
5768
delete from t55 where col1='2006-01-17';
5769
delete from t66 where col1='2006-01-17';
5770
select * from t11 order by col1;
5773
select * from t22 order by col1;
5777
select * from t33 order by col1;
5781
select * from t44 order by colint;
5786
select * from t55 order by colint;
5791
insert into t11 values ('2006-01-17');
5792
insert into t22 values ('2006-01-17');
5793
insert into t33 values ('2006-01-17');
5794
insert into t44 values (60,'2006-01-17');
5795
insert into t55 values (60,'2006-01-17');
5796
insert into t66 values (60,'2006-01-17');
5797
select * from t11 order by col1;
5801
select * from t22 order by col1;
5806
select * from t33 order by col1;
5811
select * from t44 order by colint;
5817
select * from t55 order by colint;
5823
select * from t66 order by colint;
5829
alter table t11 drop partition p0;
5830
alter table t22 drop partition p0;
5831
alter table t44 drop partition p0;
5832
alter table t55 drop partition p0;
5833
alter table t66 drop partition p0;
5834
select * from t11 order by col1;
5838
select * from t22 order by col1;
5843
select * from t33 order by col1;
5848
select * from t44 order by colint;
5851
select * from t55 order by colint;
5854
select * from t66 order by colint;
5856
-------------------------
5857
---- some alter table end
5858
-------------------------
5859
drop table if exists t1 ;
5860
drop table if exists t2 ;
5861
drop table if exists t3 ;
5862
drop table if exists t4 ;
5863
drop table if exists t5 ;
5864
drop table if exists t6 ;
5865
drop table if exists t11 ;
5866
drop table if exists t22 ;
5867
drop table if exists t33 ;
5868
drop table if exists t44 ;
5869
drop table if exists t55 ;
5870
drop table if exists t66 ;
5871
-------------------------------------------------------------------------
5872
--- extract(month from col1) in partition with coltype date
5873
-------------------------------------------------------------------------
5874
drop table if exists t1 ;
5875
drop table if exists t2 ;
5876
drop table if exists t3 ;
5877
drop table if exists t4 ;
5878
drop table if exists t5 ;
5879
drop table if exists t6 ;
5880
-------------------------------------------------------------------------
5881
--- Create tables with extract(month from col1)
5882
-------------------------------------------------------------------------
5883
create table t1 (col1 date) engine='MYISAM'
5884
partition by range(extract(month from col1))
5885
(partition p0 values less than (15),
5886
partition p1 values less than maxvalue);
5887
create table t2 (col1 date) engine='MYISAM'
5888
partition by list(extract(month from col1))
5889
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5890
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5891
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5892
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5893
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5894
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5896
create table t3 (col1 date) engine='MYISAM'
5897
partition by hash(extract(month from col1));
5898
create table t4 (colint int, col1 date) engine='MYISAM'
5899
partition by range(colint)
5900
subpartition by hash(extract(month from col1)) subpartitions 2
5901
(partition p0 values less than (15),
5902
partition p1 values less than maxvalue);
5903
create table t5 (colint int, col1 date) engine='MYISAM'
5904
partition by list(colint)
5905
subpartition by hash(extract(month from col1)) subpartitions 2
5906
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5907
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5908
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5909
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5910
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5911
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5913
create table t6 (colint int, col1 date) engine='MYISAM'
5914
partition by range(colint)
5915
(partition p0 values less than (extract(year from '1998-11-23')),
5916
partition p1 values less than maxvalue);
5917
-------------------------------------------------------------------------
5918
--- Access tables with extract(month from col1)
5919
-------------------------------------------------------------------------
5920
insert into t1 values ('2006-01-03');
5921
insert into t1 values ('2006-02-17');
5922
insert into t2 values ('2006-01-03');
5923
insert into t2 values ('2006-02-17');
5924
insert into t2 values ('2006-01-25');
5925
insert into t3 values ('2006-01-03');
5926
insert into t3 values ('2006-02-17');
5927
insert into t3 values ('2006-01-25');
5928
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
5929
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
5930
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
5931
select extract(month from col1) from t1 order by col1;
5932
extract(month from col1)
5935
select * from t1 order by col1;
5939
select * from t2 order by col1;
5944
select * from t3 order by col1;
5949
select * from t4 order by colint;
5955
select * from t5 order by colint;
5961
select * from t6 order by colint;
5967
update t1 set col1='2006-02-05' where col1='2006-01-03';
5968
update t2 set col1='2006-02-05' where col1='2006-01-03';
5969
update t3 set col1='2006-02-05' where col1='2006-01-03';
5970
update t4 set col1='2006-02-05' where col1='2006-01-03';
5971
update t5 set col1='2006-02-05' where col1='2006-01-03';
5972
update t6 set col1='2006-02-05' where col1='2006-01-03';
5973
select * from t1 order by col1;
5977
select * from t2 order by col1;
5982
select * from t3 order by col1;
5987
select * from t4 order by colint;
5993
select * from t5 order by colint;
5999
select * from t6 order by colint;
6005
-------------------------------------------------------------------------
6006
--- Alter tables with extract(month from col1)
6007
-------------------------------------------------------------------------
6008
drop table if exists t11 ;
6009
drop table if exists t22 ;
6010
drop table if exists t33 ;
6011
drop table if exists t44 ;
6012
drop table if exists t55 ;
6013
drop table if exists t66 ;
6014
create table t11 engine='MYISAM' as select * from t1;
6015
create table t22 engine='MYISAM' as select * from t2;
6016
create table t33 engine='MYISAM' as select * from t3;
6017
create table t44 engine='MYISAM' as select * from t4;
6018
create table t55 engine='MYISAM' as select * from t5;
6019
create table t66 engine='MYISAM' as select * from t6;
6021
partition by range(extract(month from col1))
6022
(partition p0 values less than (15),
6023
partition p1 values less than maxvalue);
6025
partition by list(extract(month from col1))
6026
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6027
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6028
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6029
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6030
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6031
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6034
partition by hash(extract(month from col1));
6036
partition by range(colint)
6037
subpartition by hash(extract(month from col1)) subpartitions 2
6038
(partition p0 values less than (15),
6039
partition p1 values less than maxvalue);
6041
partition by list(colint)
6042
subpartition by hash(extract(month from col1)) subpartitions 2
6043
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6044
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6045
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6046
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6047
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6048
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6051
partition by range(colint)
6052
(partition p0 values less than (extract(year from '1998-11-23')),
6053
partition p1 values less than maxvalue);
6054
select * from t11 order by col1;
6058
select * from t22 order by col1;
6063
select * from t33 order by col1;
6068
select * from t44 order by colint;
6074
select * from t55 order by colint;
6080
select * from t66 order by colint;
6086
---------------------------
6087
---- some alter table begin
6088
---------------------------
6090
reorganize partition p0,p1 into
6091
(partition s1 values less than maxvalue);
6092
select * from t11 order by col1;
6097
reorganize partition s1 into
6098
(partition p0 values less than (15),
6099
partition p1 values less than maxvalue);
6100
select * from t11 order by col1;
6105
partition by list(colint)
6106
subpartition by hash(extract(month from col1)) subpartitions 5
6107
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6108
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6109
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6110
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6111
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6112
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6114
show create table t55;
6116
t55 CREATE TABLE `t55` (
6117
`colint` int(11) DEFAULT NULL,
6118
`col1` date DEFAULT NULL
6119
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (extract(month from col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
6120
select * from t55 order by colint;
6127
reorganize partition p0,p1 into
6128
(partition s1 values less than maxvalue);
6129
select * from t66 order by colint;
6136
reorganize partition s1 into
6137
(partition p0 values less than (extract(year from '1998-11-23')),
6138
partition p1 values less than maxvalue);
6139
select * from t66 order by colint;
6146
reorganize partition p0,p1 into
6147
(partition s1 values less than maxvalue);
6148
select * from t66 order by colint;
6155
reorganize partition s1 into
6156
(partition p0 values less than (extract(year from '1998-11-23')),
6157
partition p1 values less than maxvalue);
6158
select * from t66 order by colint;
6164
-------------------------------------------------------------------------
6165
--- Delete rows and partitions of tables with extract(month from col1)
6166
-------------------------------------------------------------------------
6167
delete from t1 where col1='2006-02-17';
6168
delete from t2 where col1='2006-02-17';
6169
delete from t3 where col1='2006-02-17';
6170
delete from t4 where col1='2006-02-17';
6171
delete from t5 where col1='2006-02-17';
6172
delete from t6 where col1='2006-02-17';
6173
select * from t1 order by col1;
6176
select * from t2 order by col1;
6180
select * from t3 order by col1;
6184
select * from t4 order by colint;
6190
select * from t5 order by colint;
6196
insert into t1 values ('2006-02-17');
6197
insert into t2 values ('2006-02-17');
6198
insert into t3 values ('2006-02-17');
6199
insert into t4 values (60,'2006-02-17');
6200
insert into t5 values (60,'2006-02-17');
6201
insert into t6 values (60,'2006-02-17');
6202
select * from t1 order by col1;
6206
select * from t2 order by col1;
6211
select * from t3 order by col1;
6216
select * from t4 order by colint;
6223
select * from t5 order by colint;
6230
select * from t6 order by colint;
6237
alter table t1 drop partition p0;
6238
alter table t2 drop partition p0;
6239
alter table t4 drop partition p0;
6240
alter table t5 drop partition p0;
6241
alter table t6 drop partition p0;
6242
select * from t1 order by col1;
6244
select * from t2 order by col1;
6246
select * from t3 order by col1;
6251
select * from t4 order by colint;
6254
select * from t5 order by colint;
6257
select * from t6 order by colint;
6259
-------------------------------------------------------------------------
6260
--- Delete rows and partitions of tables with extract(month from col1)
6261
-------------------------------------------------------------------------
6262
delete from t11 where col1='2006-02-17';
6263
delete from t22 where col1='2006-02-17';
6264
delete from t33 where col1='2006-02-17';
6265
delete from t44 where col1='2006-02-17';
6266
delete from t55 where col1='2006-02-17';
6267
delete from t66 where col1='2006-02-17';
6268
select * from t11 order by col1;
6271
select * from t22 order by col1;
6275
select * from t33 order by col1;
6279
select * from t44 order by colint;
6285
select * from t55 order by colint;
6291
insert into t11 values ('2006-02-17');
6292
insert into t22 values ('2006-02-17');
6293
insert into t33 values ('2006-02-17');
6294
insert into t44 values (60,'2006-02-17');
6295
insert into t55 values (60,'2006-02-17');
6296
insert into t66 values (60,'2006-02-17');
6297
select * from t11 order by col1;
6301
select * from t22 order by col1;
6306
select * from t33 order by col1;
6311
select * from t44 order by colint;
6318
select * from t55 order by colint;
6325
select * from t66 order by colint;
6332
alter table t11 drop partition p0;
6333
alter table t22 drop partition p0;
6334
alter table t44 drop partition p0;
6335
alter table t55 drop partition p0;
6336
alter table t66 drop partition p0;
6337
select * from t11 order by col1;
6339
select * from t22 order by col1;
6341
select * from t33 order by col1;
6346
select * from t44 order by colint;
6349
select * from t55 order by colint;
6352
select * from t66 order by colint;
6354
-------------------------
6355
---- some alter table end
6356
-------------------------
6357
drop table if exists t1 ;
6358
drop table if exists t2 ;
6359
drop table if exists t3 ;
6360
drop table if exists t4 ;
6361
drop table if exists t5 ;
6362
drop table if exists t6 ;
6363
drop table if exists t11 ;
6364
drop table if exists t22 ;
6365
drop table if exists t33 ;
6366
drop table if exists t44 ;
6367
drop table if exists t55 ;
6368
drop table if exists t66 ;
6369
-------------------------------------------------------------------------
6370
--- hour(col1) in partition with coltype time
6371
-------------------------------------------------------------------------
6372
drop table if exists t1 ;
6373
drop table if exists t2 ;
6374
drop table if exists t3 ;
6375
drop table if exists t4 ;
6376
drop table if exists t5 ;
6377
drop table if exists t6 ;
6378
-------------------------------------------------------------------------
6379
--- Create tables with hour(col1)
6380
-------------------------------------------------------------------------
6381
create table t1 (col1 time) engine='MYISAM'
6382
partition by range(hour(col1))
6383
(partition p0 values less than (15),
6384
partition p1 values less than maxvalue);
6385
create table t2 (col1 time) engine='MYISAM'
6386
partition by list(hour(col1))
6387
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6388
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6389
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6390
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6391
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6392
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6394
create table t3 (col1 time) engine='MYISAM'
6395
partition by hash(hour(col1));
6396
create table t4 (colint int, col1 time) engine='MYISAM'
6397
partition by range(colint)
6398
subpartition by hash(hour(col1)) subpartitions 2
6399
(partition p0 values less than (15),
6400
partition p1 values less than maxvalue);
6401
create table t5 (colint int, col1 time) engine='MYISAM'
6402
partition by list(colint)
6403
subpartition by hash(hour(col1)) subpartitions 2
6404
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6405
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6406
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6407
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6408
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6409
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6411
create table t6 (colint int, col1 time) engine='MYISAM'
6412
partition by range(colint)
6413
(partition p0 values less than (hour('18:30')),
6414
partition p1 values less than maxvalue);
6415
-------------------------------------------------------------------------
6416
--- Access tables with hour(col1)
6417
-------------------------------------------------------------------------
6418
insert into t1 values ('09:09');
6419
insert into t1 values ('14:30');
6420
insert into t2 values ('09:09');
6421
insert into t2 values ('14:30');
6422
insert into t2 values ('21:59');
6423
insert into t3 values ('09:09');
6424
insert into t3 values ('14:30');
6425
insert into t3 values ('21:59');
6426
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t4;
6427
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t5;
6428
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t6;
6429
select hour(col1) from t1 order by col1;
6433
select * from t1 order by col1;
6437
select * from t2 order by col1;
6442
select * from t3 order by col1;
6447
select * from t4 order by colint;
6453
select * from t5 order by colint;
6459
select * from t6 order by colint;
6465
update t1 set col1='10:30' where col1='09:09';
6466
update t2 set col1='10:30' where col1='09:09';
6467
update t3 set col1='10:30' where col1='09:09';
6468
update t4 set col1='10:30' where col1='09:09';
6469
update t5 set col1='10:30' where col1='09:09';
6470
update t6 set col1='10:30' where col1='09:09';
6471
select * from t1 order by col1;
6475
select * from t2 order by col1;
6480
select * from t3 order by col1;
6485
select * from t4 order by colint;
6491
select * from t5 order by colint;
6497
select * from t6 order by colint;
6503
-------------------------------------------------------------------------
6504
--- Alter tables with hour(col1)
6505
-------------------------------------------------------------------------
6506
drop table if exists t11 ;
6507
drop table if exists t22 ;
6508
drop table if exists t33 ;
6509
drop table if exists t44 ;
6510
drop table if exists t55 ;
6511
drop table if exists t66 ;
6512
create table t11 engine='MYISAM' as select * from t1;
6513
create table t22 engine='MYISAM' as select * from t2;
6514
create table t33 engine='MYISAM' as select * from t3;
6515
create table t44 engine='MYISAM' as select * from t4;
6516
create table t55 engine='MYISAM' as select * from t5;
6517
create table t66 engine='MYISAM' as select * from t6;
6519
partition by range(hour(col1))
6520
(partition p0 values less than (15),
6521
partition p1 values less than maxvalue);
6523
partition by list(hour(col1))
6524
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6525
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6526
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6527
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6528
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6529
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6532
partition by hash(hour(col1));
6534
partition by range(colint)
6535
subpartition by hash(hour(col1)) subpartitions 2
6536
(partition p0 values less than (15),
6537
partition p1 values less than maxvalue);
6539
partition by list(colint)
6540
subpartition by hash(hour(col1)) subpartitions 2
6541
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6542
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6543
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6544
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6545
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6546
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6549
partition by range(colint)
6550
(partition p0 values less than (hour('18:30')),
6551
partition p1 values less than maxvalue);
6552
select * from t11 order by col1;
6556
select * from t22 order by col1;
6561
select * from t33 order by col1;
6566
select * from t44 order by colint;
6572
select * from t55 order by colint;
6578
select * from t66 order by colint;
6584
---------------------------
6585
---- some alter table begin
6586
---------------------------
6588
reorganize partition p0,p1 into
6589
(partition s1 values less than maxvalue);
6590
select * from t11 order by col1;
6595
reorganize partition s1 into
6596
(partition p0 values less than (15),
6597
partition p1 values less than maxvalue);
6598
select * from t11 order by col1;
6603
partition by list(colint)
6604
subpartition by hash(hour(col1)) subpartitions 5
6605
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6606
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6607
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6608
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6609
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6610
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6612
show create table t55;
6614
t55 CREATE TABLE `t55` (
6615
`colint` int(11) DEFAULT NULL,
6616
`col1` time DEFAULT NULL
6617
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (hour(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
6618
select * from t55 order by colint;
6625
reorganize partition p0,p1 into
6626
(partition s1 values less than maxvalue);
6627
select * from t66 order by colint;
6634
reorganize partition s1 into
6635
(partition p0 values less than (hour('18:30')),
6636
partition p1 values less than maxvalue);
6637
select * from t66 order by colint;
6644
reorganize partition p0,p1 into
6645
(partition s1 values less than maxvalue);
6646
select * from t66 order by colint;
6653
reorganize partition s1 into
6654
(partition p0 values less than (hour('18:30')),
6655
partition p1 values less than maxvalue);
6656
select * from t66 order by colint;
6662
-------------------------------------------------------------------------
6663
--- Delete rows and partitions of tables with hour(col1)
6664
-------------------------------------------------------------------------
6665
delete from t1 where col1='14:30';
6666
delete from t2 where col1='14:30';
6667
delete from t3 where col1='14:30';
6668
delete from t4 where col1='14:30';
6669
delete from t5 where col1='14:30';
6670
delete from t6 where col1='14:30';
6671
select * from t1 order by col1;
6674
select * from t2 order by col1;
6678
select * from t3 order by col1;
6682
select * from t4 order by colint;
6688
select * from t5 order by colint;
6694
insert into t1 values ('14:30');
6695
insert into t2 values ('14:30');
6696
insert into t3 values ('14:30');
6697
insert into t4 values (60,'14:30');
6698
insert into t5 values (60,'14:30');
6699
insert into t6 values (60,'14:30');
6700
select * from t1 order by col1;
6704
select * from t2 order by col1;
6709
select * from t3 order by col1;
6714
select * from t4 order by colint;
6721
select * from t5 order by colint;
6728
select * from t6 order by colint;
6735
alter table t1 drop partition p0;
6736
alter table t2 drop partition p0;
6737
alter table t4 drop partition p0;
6738
alter table t5 drop partition p0;
6739
alter table t6 drop partition p0;
6740
select * from t1 order by col1;
6742
select * from t2 order by col1;
6746
select * from t3 order by col1;
6751
select * from t4 order by colint;
6754
select * from t5 order by colint;
6757
select * from t6 order by colint;
6760
-------------------------------------------------------------------------
6761
--- Delete rows and partitions of tables with hour(col1)
6762
-------------------------------------------------------------------------
6763
delete from t11 where col1='14:30';
6764
delete from t22 where col1='14:30';
6765
delete from t33 where col1='14:30';
6766
delete from t44 where col1='14:30';
6767
delete from t55 where col1='14:30';
6768
delete from t66 where col1='14:30';
6769
select * from t11 order by col1;
6772
select * from t22 order by col1;
6776
select * from t33 order by col1;
6780
select * from t44 order by colint;
6786
select * from t55 order by colint;
6792
insert into t11 values ('14:30');
6793
insert into t22 values ('14:30');
6794
insert into t33 values ('14:30');
6795
insert into t44 values (60,'14:30');
6796
insert into t55 values (60,'14:30');
6797
insert into t66 values (60,'14:30');
6798
select * from t11 order by col1;
6802
select * from t22 order by col1;
6807
select * from t33 order by col1;
6812
select * from t44 order by colint;
6819
select * from t55 order by colint;
6826
select * from t66 order by colint;
6833
alter table t11 drop partition p0;
6834
alter table t22 drop partition p0;
6835
alter table t44 drop partition p0;
6836
alter table t55 drop partition p0;
6837
alter table t66 drop partition p0;
6838
select * from t11 order by col1;
6840
select * from t22 order by col1;
6844
select * from t33 order by col1;
6849
select * from t44 order by colint;
6852
select * from t55 order by colint;
6855
select * from t66 order by colint;
6858
-------------------------
6859
---- some alter table end
6860
-------------------------
6861
drop table if exists t1 ;
6862
drop table if exists t2 ;
6863
drop table if exists t3 ;
6864
drop table if exists t4 ;
6865
drop table if exists t5 ;
6866
drop table if exists t6 ;
6867
drop table if exists t11 ;
6868
drop table if exists t22 ;
6869
drop table if exists t33 ;
6870
drop table if exists t44 ;
6871
drop table if exists t55 ;
6872
drop table if exists t66 ;
6873
-------------------------------------------------------------------------
6874
--- microsecond(col1) in partition with coltype time
6875
-------------------------------------------------------------------------
6876
drop table if exists t1 ;
6877
drop table if exists t2 ;
6878
drop table if exists t3 ;
6879
drop table if exists t4 ;
6880
drop table if exists t5 ;
6881
drop table if exists t6 ;
6882
-------------------------------------------------------------------------
6883
--- Create tables with microsecond(col1)
6884
-------------------------------------------------------------------------
6885
create table t1 (col1 time) engine='MYISAM'
6886
partition by range(microsecond(col1))
6887
(partition p0 values less than (15),
6888
partition p1 values less than maxvalue);
6889
create table t2 (col1 time) engine='MYISAM'
6890
partition by list(microsecond(col1))
6891
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6892
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6893
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6894
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6895
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6896
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6898
create table t3 (col1 time) engine='MYISAM'
6899
partition by hash(microsecond(col1));
6900
create table t4 (colint int, col1 time) engine='MYISAM'
6901
partition by range(colint)
6902
subpartition by hash(microsecond(col1)) subpartitions 2
6903
(partition p0 values less than (15),
6904
partition p1 values less than maxvalue);
6905
create table t5 (colint int, col1 time) engine='MYISAM'
6906
partition by list(colint)
6907
subpartition by hash(microsecond(col1)) subpartitions 2
6908
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6909
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6910
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6911
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6912
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6913
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6915
create table t6 (colint int, col1 time) engine='MYISAM'
6916
partition by range(colint)
6917
(partition p0 values less than (microsecond('10:30:10.000010')),
6918
partition p1 values less than maxvalue);
6919
-------------------------------------------------------------------------
6920
--- Access tables with microsecond(col1)
6921
-------------------------------------------------------------------------
6922
insert into t1 values ('09:09:15.000002');
6923
insert into t1 values ('04:30:01.000018');
6924
insert into t2 values ('09:09:15.000002');
6925
insert into t2 values ('04:30:01.000018');
6926
insert into t2 values ('00:59:22.000024');
6927
insert into t3 values ('09:09:15.000002');
6928
insert into t3 values ('04:30:01.000018');
6929
insert into t3 values ('00:59:22.000024');
6930
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t4;
6931
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t5;
6932
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t6;
6933
select microsecond(col1) from t1 order by col1;
6937
select * from t1 order by col1;
6941
select * from t2 order by col1;
6946
select * from t3 order by col1;
6951
select * from t4 order by colint;
6957
select * from t5 order by colint;
6963
select * from t6 order by colint;
6969
update t1 set col1='05:30:34.000037' where col1='09:09:15.000002';
6970
update t2 set col1='05:30:34.000037' where col1='09:09:15.000002';
6971
update t3 set col1='05:30:34.000037' where col1='09:09:15.000002';
6972
update t4 set col1='05:30:34.000037' where col1='09:09:15.000002';
6973
update t5 set col1='05:30:34.000037' where col1='09:09:15.000002';
6974
update t6 set col1='05:30:34.000037' where col1='09:09:15.000002';
6975
select * from t1 order by col1;
6979
select * from t2 order by col1;
6984
select * from t3 order by col1;
6989
select * from t4 order by colint;
6995
select * from t5 order by colint;
7001
select * from t6 order by colint;
7007
-------------------------------------------------------------------------
7008
--- Alter tables with microsecond(col1)
7009
-------------------------------------------------------------------------
7010
drop table if exists t11 ;
7011
drop table if exists t22 ;
7012
drop table if exists t33 ;
7013
drop table if exists t44 ;
7014
drop table if exists t55 ;
7015
drop table if exists t66 ;
7016
create table t11 engine='MYISAM' as select * from t1;
7017
create table t22 engine='MYISAM' as select * from t2;
7018
create table t33 engine='MYISAM' as select * from t3;
7019
create table t44 engine='MYISAM' as select * from t4;
7020
create table t55 engine='MYISAM' as select * from t5;
7021
create table t66 engine='MYISAM' as select * from t6;
7023
partition by range(microsecond(col1))
7024
(partition p0 values less than (15),
7025
partition p1 values less than maxvalue);
7027
partition by list(microsecond(col1))
7028
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7029
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7030
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7031
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7032
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7033
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7036
partition by hash(microsecond(col1));
7038
partition by range(colint)
7039
subpartition by hash(microsecond(col1)) subpartitions 2
7040
(partition p0 values less than (15),
7041
partition p1 values less than maxvalue);
7043
partition by list(colint)
7044
subpartition by hash(microsecond(col1)) subpartitions 2
7045
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7046
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7047
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7048
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7049
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7050
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7053
partition by range(colint)
7054
(partition p0 values less than (microsecond('10:30:10.000010')),
7055
partition p1 values less than maxvalue);
7056
select * from t11 order by col1;
7060
select * from t22 order by col1;
7065
select * from t33 order by col1;
7070
select * from t44 order by colint;
7076
select * from t55 order by colint;
7082
select * from t66 order by colint;
7088
---------------------------
7089
---- some alter table begin
7090
---------------------------
7092
reorganize partition p0,p1 into
7093
(partition s1 values less than maxvalue);
7094
select * from t11 order by col1;
7099
reorganize partition s1 into
7100
(partition p0 values less than (15),
7101
partition p1 values less than maxvalue);
7102
select * from t11 order by col1;
7107
partition by list(colint)
7108
subpartition by hash(microsecond(col1)) subpartitions 5
7109
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7110
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7111
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7112
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7113
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7114
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7116
show create table t55;
7118
t55 CREATE TABLE `t55` (
7119
`colint` int(11) DEFAULT NULL,
7120
`col1` time DEFAULT NULL
7121
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (microsecond(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
7122
select * from t55 order by colint;
7129
reorganize partition p0,p1 into
7130
(partition s1 values less than maxvalue);
7131
select * from t66 order by colint;
7138
reorganize partition s1 into
7139
(partition p0 values less than (microsecond('10:30:10.000010')),
7140
partition p1 values less than maxvalue);
7141
select * from t66 order by colint;
7148
reorganize partition p0,p1 into
7149
(partition s1 values less than maxvalue);
7150
select * from t66 order by colint;
7157
reorganize partition s1 into
7158
(partition p0 values less than (microsecond('10:30:10.000010')),
7159
partition p1 values less than maxvalue);
7160
select * from t66 order by colint;
7166
-------------------------------------------------------------------------
7167
--- Delete rows and partitions of tables with microsecond(col1)
7168
-------------------------------------------------------------------------
7169
delete from t1 where col1='04:30:01.000018';
7170
delete from t2 where col1='04:30:01.000018';
7171
delete from t3 where col1='04:30:01.000018';
7172
delete from t4 where col1='04:30:01.000018';
7173
delete from t5 where col1='04:30:01.000018';
7174
delete from t6 where col1='04:30:01.000018';
7175
select * from t1 order by col1;
7178
select * from t2 order by col1;
7182
select * from t3 order by col1;
7186
select * from t4 order by colint;
7191
select * from t5 order by colint;
7196
insert into t1 values ('04:30:01.000018');
7197
insert into t2 values ('04:30:01.000018');
7198
insert into t3 values ('04:30:01.000018');
7199
insert into t4 values (60,'04:30:01.000018');
7200
insert into t5 values (60,'04:30:01.000018');
7201
insert into t6 values (60,'04:30:01.000018');
7202
select * from t1 order by col1;
7206
select * from t2 order by col1;
7211
select * from t3 order by col1;
7216
select * from t4 order by colint;
7222
select * from t5 order by colint;
7228
select * from t6 order by colint;
7234
alter table t1 drop partition p0;
7235
alter table t2 drop partition p0;
7236
alter table t4 drop partition p0;
7237
alter table t5 drop partition p0;
7238
alter table t6 drop partition p0;
7239
select * from t1 order by col1;
7241
select * from t2 order by col1;
7243
select * from t3 order by col1;
7248
select * from t4 order by colint;
7251
select * from t5 order by colint;
7254
select * from t6 order by colint;
7257
-------------------------------------------------------------------------
7258
--- Delete rows and partitions of tables with microsecond(col1)
7259
-------------------------------------------------------------------------
7260
delete from t11 where col1='04:30:01.000018';
7261
delete from t22 where col1='04:30:01.000018';
7262
delete from t33 where col1='04:30:01.000018';
7263
delete from t44 where col1='04:30:01.000018';
7264
delete from t55 where col1='04:30:01.000018';
7265
delete from t66 where col1='04:30:01.000018';
7266
select * from t11 order by col1;
7269
select * from t22 order by col1;
7273
select * from t33 order by col1;
7277
select * from t44 order by colint;
7282
select * from t55 order by colint;
7287
insert into t11 values ('04:30:01.000018');
7288
insert into t22 values ('04:30:01.000018');
7289
insert into t33 values ('04:30:01.000018');
7290
insert into t44 values (60,'04:30:01.000018');
7291
insert into t55 values (60,'04:30:01.000018');
7292
insert into t66 values (60,'04:30:01.000018');
7293
select * from t11 order by col1;
7297
select * from t22 order by col1;
7302
select * from t33 order by col1;
7307
select * from t44 order by colint;
7313
select * from t55 order by colint;
7319
select * from t66 order by colint;
7325
alter table t11 drop partition p0;
7326
alter table t22 drop partition p0;
7327
alter table t44 drop partition p0;
7328
alter table t55 drop partition p0;
7329
alter table t66 drop partition p0;
7330
select * from t11 order by col1;
7332
select * from t22 order by col1;
7334
select * from t33 order by col1;
7339
select * from t44 order by colint;
7342
select * from t55 order by colint;
7345
select * from t66 order by colint;
7348
-------------------------
7349
---- some alter table end
7350
-------------------------
7351
drop table if exists t1 ;
7352
drop table if exists t2 ;
7353
drop table if exists t3 ;
7354
drop table if exists t4 ;
7355
drop table if exists t5 ;
7356
drop table if exists t6 ;
7357
drop table if exists t11 ;
7358
drop table if exists t22 ;
7359
drop table if exists t33 ;
7360
drop table if exists t44 ;
7361
drop table if exists t55 ;
7362
drop table if exists t66 ;
7363
-------------------------------------------------------------------------
7364
--- minute(col1) in partition with coltype time
7365
-------------------------------------------------------------------------
7366
drop table if exists t1 ;
7367
drop table if exists t2 ;
7368
drop table if exists t3 ;
7369
drop table if exists t4 ;
7370
drop table if exists t5 ;
7371
drop table if exists t6 ;
7372
-------------------------------------------------------------------------
7373
--- Create tables with minute(col1)
7374
-------------------------------------------------------------------------
7375
create table t1 (col1 time) engine='MYISAM'
7376
partition by range(minute(col1))
7377
(partition p0 values less than (15),
7378
partition p1 values less than maxvalue);
7379
create table t2 (col1 time) engine='MYISAM'
7380
partition by list(minute(col1))
7381
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7382
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7383
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7384
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7385
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7386
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7388
create table t3 (col1 time) engine='MYISAM'
7389
partition by hash(minute(col1));
7390
create table t4 (colint int, col1 time) engine='MYISAM'
7391
partition by range(colint)
7392
subpartition by hash(minute(col1)) subpartitions 2
7393
(partition p0 values less than (15),
7394
partition p1 values less than maxvalue);
7395
create table t5 (colint int, col1 time) engine='MYISAM'
7396
partition by list(colint)
7397
subpartition by hash(minute(col1)) subpartitions 2
7398
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7399
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7400
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7401
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7402
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7403
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7405
create table t6 (colint int, col1 time) engine='MYISAM'
7406
partition by range(colint)
7407
(partition p0 values less than (minute('18:30')),
7408
partition p1 values less than maxvalue);
7409
-------------------------------------------------------------------------
7410
--- Access tables with minute(col1)
7411
-------------------------------------------------------------------------
7412
insert into t1 values ('09:09:15');
7413
insert into t1 values ('14:30:45');
7414
insert into t2 values ('09:09:15');
7415
insert into t2 values ('14:30:45');
7416
insert into t2 values ('21:59:22');
7417
insert into t3 values ('09:09:15');
7418
insert into t3 values ('14:30:45');
7419
insert into t3 values ('21:59:22');
7420
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t4;
7421
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t5;
7422
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t6;
7423
select minute(col1) from t1 order by col1;
7427
select * from t1 order by col1;
7431
select * from t2 order by col1;
7436
select * from t3 order by col1;
7441
select * from t4 order by colint;
7447
select * from t5 order by colint;
7453
select * from t6 order by colint;
7459
update t1 set col1='10:24:23' where col1='09:09:15';
7460
update t2 set col1='10:24:23' where col1='09:09:15';
7461
update t3 set col1='10:24:23' where col1='09:09:15';
7462
update t4 set col1='10:24:23' where col1='09:09:15';
7463
update t5 set col1='10:24:23' where col1='09:09:15';
7464
update t6 set col1='10:24:23' where col1='09:09:15';
7465
select * from t1 order by col1;
7469
select * from t2 order by col1;
7474
select * from t3 order by col1;
7479
select * from t4 order by colint;
7485
select * from t5 order by colint;
7491
select * from t6 order by colint;
7497
-------------------------------------------------------------------------
7498
--- Alter tables with minute(col1)
7499
-------------------------------------------------------------------------
7500
drop table if exists t11 ;
7501
drop table if exists t22 ;
7502
drop table if exists t33 ;
7503
drop table if exists t44 ;
7504
drop table if exists t55 ;
7505
drop table if exists t66 ;
7506
create table t11 engine='MYISAM' as select * from t1;
7507
create table t22 engine='MYISAM' as select * from t2;
7508
create table t33 engine='MYISAM' as select * from t3;
7509
create table t44 engine='MYISAM' as select * from t4;
7510
create table t55 engine='MYISAM' as select * from t5;
7511
create table t66 engine='MYISAM' as select * from t6;
7513
partition by range(minute(col1))
7514
(partition p0 values less than (15),
7515
partition p1 values less than maxvalue);
7517
partition by list(minute(col1))
7518
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7519
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7520
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7521
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7522
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7523
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7526
partition by hash(minute(col1));
7528
partition by range(colint)
7529
subpartition by hash(minute(col1)) subpartitions 2
7530
(partition p0 values less than (15),
7531
partition p1 values less than maxvalue);
7533
partition by list(colint)
7534
subpartition by hash(minute(col1)) subpartitions 2
7535
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7536
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7537
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7538
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7539
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7540
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7543
partition by range(colint)
7544
(partition p0 values less than (minute('18:30')),
7545
partition p1 values less than maxvalue);
7546
select * from t11 order by col1;
7550
select * from t22 order by col1;
7555
select * from t33 order by col1;
7560
select * from t44 order by colint;
7566
select * from t55 order by colint;
7572
select * from t66 order by colint;
7578
---------------------------
7579
---- some alter table begin
7580
---------------------------
7582
reorganize partition p0,p1 into
7583
(partition s1 values less than maxvalue);
7584
select * from t11 order by col1;
7589
reorganize partition s1 into
7590
(partition p0 values less than (15),
7591
partition p1 values less than maxvalue);
7592
select * from t11 order by col1;
7597
partition by list(colint)
7598
subpartition by hash(minute(col1)) subpartitions 5
7599
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7600
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7601
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7602
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7603
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7604
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7606
show create table t55;
7608
t55 CREATE TABLE `t55` (
7609
`colint` int(11) DEFAULT NULL,
7610
`col1` time DEFAULT NULL
7611
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (minute(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
7612
select * from t55 order by colint;
7619
reorganize partition p0,p1 into
7620
(partition s1 values less than maxvalue);
7621
select * from t66 order by colint;
7628
reorganize partition s1 into
7629
(partition p0 values less than (minute('18:30')),
7630
partition p1 values less than maxvalue);
7631
select * from t66 order by colint;
7638
reorganize partition p0,p1 into
7639
(partition s1 values less than maxvalue);
7640
select * from t66 order by colint;
7647
reorganize partition s1 into
7648
(partition p0 values less than (minute('18:30')),
7649
partition p1 values less than maxvalue);
7650
select * from t66 order by colint;
7656
-------------------------------------------------------------------------
7657
--- Delete rows and partitions of tables with minute(col1)
7658
-------------------------------------------------------------------------
7659
delete from t1 where col1='14:30:45';
7660
delete from t2 where col1='14:30:45';
7661
delete from t3 where col1='14:30:45';
7662
delete from t4 where col1='14:30:45';
7663
delete from t5 where col1='14:30:45';
7664
delete from t6 where col1='14:30:45';
7665
select * from t1 order by col1;
7668
select * from t2 order by col1;
7672
select * from t3 order by col1;
7676
select * from t4 order by colint;
7682
select * from t5 order by colint;
7688
insert into t1 values ('14:30:45');
7689
insert into t2 values ('14:30:45');
7690
insert into t3 values ('14:30:45');
7691
insert into t4 values (60,'14:30:45');
7692
insert into t5 values (60,'14:30:45');
7693
insert into t6 values (60,'14:30:45');
7694
select * from t1 order by col1;
7698
select * from t2 order by col1;
7703
select * from t3 order by col1;
7708
select * from t4 order by colint;
7715
select * from t5 order by colint;
7722
select * from t6 order by colint;
7729
alter table t1 drop partition p0;
7730
alter table t2 drop partition p0;
7731
alter table t4 drop partition p0;
7732
alter table t5 drop partition p0;
7733
alter table t6 drop partition p0;
7734
select * from t1 order by col1;
7738
select * from t2 order by col1;
7743
select * from t3 order by col1;
7748
select * from t4 order by colint;
7751
select * from t5 order by colint;
7754
select * from t6 order by colint;
7757
-------------------------------------------------------------------------
7758
--- Delete rows and partitions of tables with minute(col1)
7759
-------------------------------------------------------------------------
7760
delete from t11 where col1='14:30:45';
7761
delete from t22 where col1='14:30:45';
7762
delete from t33 where col1='14:30:45';
7763
delete from t44 where col1='14:30:45';
7764
delete from t55 where col1='14:30:45';
7765
delete from t66 where col1='14:30:45';
7766
select * from t11 order by col1;
7769
select * from t22 order by col1;
7773
select * from t33 order by col1;
7777
select * from t44 order by colint;
7783
select * from t55 order by colint;
7789
insert into t11 values ('14:30:45');
7790
insert into t22 values ('14:30:45');
7791
insert into t33 values ('14:30:45');
7792
insert into t44 values (60,'14:30:45');
7793
insert into t55 values (60,'14:30:45');
7794
insert into t66 values (60,'14:30:45');
7795
select * from t11 order by col1;
7799
select * from t22 order by col1;
7804
select * from t33 order by col1;
7809
select * from t44 order by colint;
7816
select * from t55 order by colint;
7823
select * from t66 order by colint;
7830
alter table t11 drop partition p0;
7831
alter table t22 drop partition p0;
7832
alter table t44 drop partition p0;
7833
alter table t55 drop partition p0;
7834
alter table t66 drop partition p0;
7835
select * from t11 order by col1;
7839
select * from t22 order by col1;
7844
select * from t33 order by col1;
7849
select * from t44 order by colint;
7852
select * from t55 order by colint;
7855
select * from t66 order by colint;
7858
-------------------------
7859
---- some alter table end
7860
-------------------------
7861
drop table if exists t1 ;
7862
drop table if exists t2 ;
7863
drop table if exists t3 ;
7864
drop table if exists t4 ;
7865
drop table if exists t5 ;
7866
drop table if exists t6 ;
7867
drop table if exists t11 ;
7868
drop table if exists t22 ;
7869
drop table if exists t33 ;
7870
drop table if exists t44 ;
7871
drop table if exists t55 ;
7872
drop table if exists t66 ;
7873
-------------------------------------------------------------------------
7874
--- second(col1) in partition with coltype time
7875
-------------------------------------------------------------------------
7876
drop table if exists t1 ;
7877
drop table if exists t2 ;
7878
drop table if exists t3 ;
7879
drop table if exists t4 ;
7880
drop table if exists t5 ;
7881
drop table if exists t6 ;
7882
-------------------------------------------------------------------------
7883
--- Create tables with second(col1)
7884
-------------------------------------------------------------------------
7885
create table t1 (col1 time) engine='MYISAM'
7886
partition by range(second(col1))
7887
(partition p0 values less than (15),
7888
partition p1 values less than maxvalue);
7889
create table t2 (col1 time) engine='MYISAM'
7890
partition by list(second(col1))
7891
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7892
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7893
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7894
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7895
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7896
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7898
create table t3 (col1 time) engine='MYISAM'
7899
partition by hash(second(col1));
7900
create table t4 (colint int, col1 time) engine='MYISAM'
7901
partition by range(colint)
7902
subpartition by hash(second(col1)) subpartitions 2
7903
(partition p0 values less than (15),
7904
partition p1 values less than maxvalue);
7905
create table t5 (colint int, col1 time) engine='MYISAM'
7906
partition by list(colint)
7907
subpartition by hash(second(col1)) subpartitions 2
7908
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7909
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7910
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7911
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7912
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7913
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7915
create table t6 (colint int, col1 time) engine='MYISAM'
7916
partition by range(colint)
7917
(partition p0 values less than (second('18:30:14')),
7918
partition p1 values less than maxvalue);
7919
-------------------------------------------------------------------------
7920
--- Access tables with second(col1)
7921
-------------------------------------------------------------------------
7922
insert into t1 values ('09:09:09');
7923
insert into t1 values ('14:30:20');
7924
insert into t2 values ('09:09:09');
7925
insert into t2 values ('14:30:20');
7926
insert into t2 values ('21:59:22');
7927
insert into t3 values ('09:09:09');
7928
insert into t3 values ('14:30:20');
7929
insert into t3 values ('21:59:22');
7930
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t4;
7931
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t5;
7932
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t6;
7933
select second(col1) from t1 order by col1;
7937
select * from t1 order by col1;
7941
select * from t2 order by col1;
7946
select * from t3 order by col1;
7951
select * from t4 order by colint;
7957
select * from t5 order by colint;
7963
select * from t6 order by colint;
7969
update t1 set col1='10:22:33' where col1='09:09:09';
7970
update t2 set col1='10:22:33' where col1='09:09:09';
7971
update t3 set col1='10:22:33' where col1='09:09:09';
7972
update t4 set col1='10:22:33' where col1='09:09:09';
7973
update t5 set col1='10:22:33' where col1='09:09:09';
7974
update t6 set col1='10:22:33' where col1='09:09:09';
7975
select * from t1 order by col1;
7979
select * from t2 order by col1;
7984
select * from t3 order by col1;
7989
select * from t4 order by colint;
7995
select * from t5 order by colint;
8001
select * from t6 order by colint;
8007
-------------------------------------------------------------------------
8008
--- Alter tables with second(col1)
8009
-------------------------------------------------------------------------
8010
drop table if exists t11 ;
8011
drop table if exists t22 ;
8012
drop table if exists t33 ;
8013
drop table if exists t44 ;
8014
drop table if exists t55 ;
8015
drop table if exists t66 ;
8016
create table t11 engine='MYISAM' as select * from t1;
8017
create table t22 engine='MYISAM' as select * from t2;
8018
create table t33 engine='MYISAM' as select * from t3;
8019
create table t44 engine='MYISAM' as select * from t4;
8020
create table t55 engine='MYISAM' as select * from t5;
8021
create table t66 engine='MYISAM' as select * from t6;
8023
partition by range(second(col1))
8024
(partition p0 values less than (15),
8025
partition p1 values less than maxvalue);
8027
partition by list(second(col1))
8028
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8029
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8030
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8031
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8032
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8033
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8036
partition by hash(second(col1));
8038
partition by range(colint)
8039
subpartition by hash(second(col1)) subpartitions 2
8040
(partition p0 values less than (15),
8041
partition p1 values less than maxvalue);
8043
partition by list(colint)
8044
subpartition by hash(second(col1)) subpartitions 2
8045
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8046
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8047
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8048
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8049
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8050
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8053
partition by range(colint)
8054
(partition p0 values less than (second('18:30:14')),
8055
partition p1 values less than maxvalue);
8056
select * from t11 order by col1;
8060
select * from t22 order by col1;
8065
select * from t33 order by col1;
8070
select * from t44 order by colint;
8076
select * from t55 order by colint;
8082
select * from t66 order by colint;
8088
---------------------------
8089
---- some alter table begin
8090
---------------------------
8092
reorganize partition p0,p1 into
8093
(partition s1 values less than maxvalue);
8094
select * from t11 order by col1;
8099
reorganize partition s1 into
8100
(partition p0 values less than (15),
8101
partition p1 values less than maxvalue);
8102
select * from t11 order by col1;
8107
partition by list(colint)
8108
subpartition by hash(second(col1)) subpartitions 5
8109
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8110
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8111
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8112
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8113
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8114
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8116
show create table t55;
8118
t55 CREATE TABLE `t55` (
8119
`colint` int(11) DEFAULT NULL,
8120
`col1` time DEFAULT NULL
8121
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (second(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
8122
select * from t55 order by colint;
8129
reorganize partition p0,p1 into
8130
(partition s1 values less than maxvalue);
8131
select * from t66 order by colint;
8138
reorganize partition s1 into
8139
(partition p0 values less than (second('18:30:14')),
8140
partition p1 values less than maxvalue);
8141
select * from t66 order by colint;
8148
reorganize partition p0,p1 into
8149
(partition s1 values less than maxvalue);
8150
select * from t66 order by colint;
8157
reorganize partition s1 into
8158
(partition p0 values less than (second('18:30:14')),
8159
partition p1 values less than maxvalue);
8160
select * from t66 order by colint;
8166
-------------------------------------------------------------------------
8167
--- Delete rows and partitions of tables with second(col1)
8168
-------------------------------------------------------------------------
8169
delete from t1 where col1='14:30:20';
8170
delete from t2 where col1='14:30:20';
8171
delete from t3 where col1='14:30:20';
8172
delete from t4 where col1='14:30:20';
8173
delete from t5 where col1='14:30:20';
8174
delete from t6 where col1='14:30:20';
8175
select * from t1 order by col1;
8178
select * from t2 order by col1;
8182
select * from t3 order by col1;
8186
select * from t4 order by colint;
8192
select * from t5 order by colint;
8198
insert into t1 values ('14:30:20');
8199
insert into t2 values ('14:30:20');
8200
insert into t3 values ('14:30:20');
8201
insert into t4 values (60,'14:30:20');
8202
insert into t5 values (60,'14:30:20');
8203
insert into t6 values (60,'14:30:20');
8204
select * from t1 order by col1;
8208
select * from t2 order by col1;
8213
select * from t3 order by col1;
8218
select * from t4 order by colint;
8225
select * from t5 order by colint;
8232
select * from t6 order by colint;
8239
alter table t1 drop partition p0;
8240
alter table t2 drop partition p0;
8241
alter table t4 drop partition p0;
8242
alter table t5 drop partition p0;
8243
alter table t6 drop partition p0;
8244
select * from t1 order by col1;
8248
select * from t2 order by col1;
8253
select * from t3 order by col1;
8258
select * from t4 order by colint;
8261
select * from t5 order by colint;
8264
select * from t6 order by colint;
8267
-------------------------------------------------------------------------
8268
--- Delete rows and partitions of tables with second(col1)
8269
-------------------------------------------------------------------------
8270
delete from t11 where col1='14:30:20';
8271
delete from t22 where col1='14:30:20';
8272
delete from t33 where col1='14:30:20';
8273
delete from t44 where col1='14:30:20';
8274
delete from t55 where col1='14:30:20';
8275
delete from t66 where col1='14:30:20';
8276
select * from t11 order by col1;
8279
select * from t22 order by col1;
8283
select * from t33 order by col1;
8287
select * from t44 order by colint;
8293
select * from t55 order by colint;
8299
insert into t11 values ('14:30:20');
8300
insert into t22 values ('14:30:20');
8301
insert into t33 values ('14:30:20');
8302
insert into t44 values (60,'14:30:20');
8303
insert into t55 values (60,'14:30:20');
8304
insert into t66 values (60,'14:30:20');
8305
select * from t11 order by col1;
8309
select * from t22 order by col1;
8314
select * from t33 order by col1;
8319
select * from t44 order by colint;
8326
select * from t55 order by colint;
8333
select * from t66 order by colint;
8340
alter table t11 drop partition p0;
8341
alter table t22 drop partition p0;
8342
alter table t44 drop partition p0;
8343
alter table t55 drop partition p0;
8344
alter table t66 drop partition p0;
8345
select * from t11 order by col1;
8349
select * from t22 order by col1;
8354
select * from t33 order by col1;
8359
select * from t44 order by colint;
8362
select * from t55 order by colint;
8365
select * from t66 order by colint;
8368
-------------------------
8369
---- some alter table end
8370
-------------------------
8371
drop table if exists t1 ;
8372
drop table if exists t2 ;
8373
drop table if exists t3 ;
8374
drop table if exists t4 ;
8375
drop table if exists t5 ;
8376
drop table if exists t6 ;
8377
drop table if exists t11 ;
8378
drop table if exists t22 ;
8379
drop table if exists t33 ;
8380
drop table if exists t44 ;
8381
drop table if exists t55 ;
8382
drop table if exists t66 ;
8383
-------------------------------------------------------------------------
8384
--- second(col1) in partition with coltype char(30)
8385
-------------------------------------------------------------------------
8386
drop table if exists t1 ;
8387
drop table if exists t2 ;
8388
drop table if exists t3 ;
8389
drop table if exists t4 ;
8390
drop table if exists t5 ;
8391
drop table if exists t6 ;
8392
-------------------------------------------------------------------------
8393
--- Create tables with second(col1)
8394
-------------------------------------------------------------------------
8395
create table t1 (col1 char(30)) engine='MYISAM'
8396
partition by range(second(col1))
8397
(partition p0 values less than (15),
8398
partition p1 values less than maxvalue);
8399
create table t2 (col1 char(30)) engine='MYISAM'
8400
partition by list(second(col1))
8401
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8402
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8403
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8404
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8405
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8406
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8408
create table t3 (col1 char(30)) engine='MYISAM'
8409
partition by hash(second(col1));
8410
create table t4 (colint int, col1 char(30)) engine='MYISAM'
8411
partition by range(colint)
8412
subpartition by hash(second(col1)) subpartitions 2
8413
(partition p0 values less than (15),
8414
partition p1 values less than maxvalue);
8415
create table t5 (colint int, col1 char(30)) engine='MYISAM'
8416
partition by list(colint)
8417
subpartition by hash(second(col1)) subpartitions 2
8418
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8419
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8420
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8421
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8422
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8423
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8425
create table t6 (colint int, col1 char(30)) engine='MYISAM'
8426
partition by range(colint)
8427
(partition p0 values less than (second('18:30:14')),
8428
partition p1 values less than maxvalue);
8429
-------------------------------------------------------------------------
8430
--- Access tables with second(col1)
8431
-------------------------------------------------------------------------
8432
insert into t1 values ('09:09:09');
8433
insert into t1 values ('14:30:20');
8434
insert into t2 values ('09:09:09');
8435
insert into t2 values ('14:30:20');
8436
insert into t2 values ('21:59:22');
8437
insert into t3 values ('09:09:09');
8438
insert into t3 values ('14:30:20');
8439
insert into t3 values ('21:59:22');
8440
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t4;
8441
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t5;
8442
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t6;
8443
select second(col1) from t1 order by col1;
8447
select * from t1 order by col1;
8451
select * from t2 order by col1;
8456
select * from t3 order by col1;
8461
select * from t4 order by colint;
8467
select * from t5 order by colint;
8473
select * from t6 order by colint;
8479
update t1 set col1='10:22:33' where col1='09:09:09';
8480
update t2 set col1='10:22:33' where col1='09:09:09';
8481
update t3 set col1='10:22:33' where col1='09:09:09';
8482
update t4 set col1='10:22:33' where col1='09:09:09';
8483
update t5 set col1='10:22:33' where col1='09:09:09';
8484
update t6 set col1='10:22:33' where col1='09:09:09';
8485
select * from t1 order by col1;
8489
select * from t2 order by col1;
8494
select * from t3 order by col1;
8499
select * from t4 order by colint;
8505
select * from t5 order by colint;
8511
select * from t6 order by colint;
8517
-------------------------------------------------------------------------
8518
--- Alter tables with second(col1)
8519
-------------------------------------------------------------------------
8520
drop table if exists t11 ;
8521
drop table if exists t22 ;
8522
drop table if exists t33 ;
8523
drop table if exists t44 ;
8524
drop table if exists t55 ;
8525
drop table if exists t66 ;
8526
create table t11 engine='MYISAM' as select * from t1;
8527
create table t22 engine='MYISAM' as select * from t2;
8528
create table t33 engine='MYISAM' as select * from t3;
8529
create table t44 engine='MYISAM' as select * from t4;
8530
create table t55 engine='MYISAM' as select * from t5;
8531
create table t66 engine='MYISAM' as select * from t6;
8533
partition by range(second(col1))
8534
(partition p0 values less than (15),
8535
partition p1 values less than maxvalue);
8537
partition by list(second(col1))
8538
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8539
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8540
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8541
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8542
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8543
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8546
partition by hash(second(col1));
8548
partition by range(colint)
8549
subpartition by hash(second(col1)) subpartitions 2
8550
(partition p0 values less than (15),
8551
partition p1 values less than maxvalue);
8553
partition by list(colint)
8554
subpartition by hash(second(col1)) subpartitions 2
8555
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8556
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8557
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8558
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8559
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8560
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8563
partition by range(colint)
8564
(partition p0 values less than (second('18:30:14')),
8565
partition p1 values less than maxvalue);
8566
select * from t11 order by col1;
8570
select * from t22 order by col1;
8575
select * from t33 order by col1;
8580
select * from t44 order by colint;
8586
select * from t55 order by colint;
8592
select * from t66 order by colint;
8598
---------------------------
8599
---- some alter table begin
8600
---------------------------
8602
reorganize partition p0,p1 into
8603
(partition s1 values less than maxvalue);
8604
select * from t11 order by col1;
8609
reorganize partition s1 into
8610
(partition p0 values less than (15),
8611
partition p1 values less than maxvalue);
8612
select * from t11 order by col1;
8617
partition by list(colint)
8618
subpartition by hash(second(col1)) subpartitions 5
8619
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8620
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8621
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8622
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8623
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8624
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8626
show create table t55;
8628
t55 CREATE TABLE `t55` (
8629
`colint` int(11) DEFAULT NULL,
8630
`col1` char(30) DEFAULT NULL
8631
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (second(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
8632
select * from t55 order by colint;
8639
reorganize partition p0,p1 into
8640
(partition s1 values less than maxvalue);
8641
select * from t66 order by colint;
8648
reorganize partition s1 into
8649
(partition p0 values less than (second('18:30:14')),
8650
partition p1 values less than maxvalue);
8651
select * from t66 order by colint;
8658
reorganize partition p0,p1 into
8659
(partition s1 values less than maxvalue);
8660
select * from t66 order by colint;
8667
reorganize partition s1 into
8668
(partition p0 values less than (second('18:30:14')),
8669
partition p1 values less than maxvalue);
8670
select * from t66 order by colint;
8676
-------------------------------------------------------------------------
8677
--- Delete rows and partitions of tables with second(col1)
8678
-------------------------------------------------------------------------
8679
delete from t1 where col1='14:30:20';
8680
delete from t2 where col1='14:30:20';
8681
delete from t3 where col1='14:30:20';
8682
delete from t4 where col1='14:30:20';
8683
delete from t5 where col1='14:30:20';
8684
delete from t6 where col1='14:30:20';
8685
select * from t1 order by col1;
8688
select * from t2 order by col1;
8692
select * from t3 order by col1;
8696
select * from t4 order by colint;
8702
select * from t5 order by colint;
8708
insert into t1 values ('14:30:20');
8709
insert into t2 values ('14:30:20');
8710
insert into t3 values ('14:30:20');
8711
insert into t4 values (60,'14:30:20');
8712
insert into t5 values (60,'14:30:20');
8713
insert into t6 values (60,'14:30:20');
8714
select * from t1 order by col1;
8718
select * from t2 order by col1;
8723
select * from t3 order by col1;
8728
select * from t4 order by colint;
8735
select * from t5 order by colint;
8742
select * from t6 order by colint;
8749
alter table t1 drop partition p0;
8750
alter table t2 drop partition p0;
8751
alter table t4 drop partition p0;
8752
alter table t5 drop partition p0;
8753
alter table t6 drop partition p0;
8754
select * from t1 order by col1;
8758
select * from t2 order by col1;
8763
select * from t3 order by col1;
8768
select * from t4 order by colint;
8771
select * from t5 order by colint;
8774
select * from t6 order by colint;
8777
-------------------------------------------------------------------------
8778
--- Delete rows and partitions of tables with second(col1)
8779
-------------------------------------------------------------------------
8780
delete from t11 where col1='14:30:20';
8781
delete from t22 where col1='14:30:20';
8782
delete from t33 where col1='14:30:20';
8783
delete from t44 where col1='14:30:20';
8784
delete from t55 where col1='14:30:20';
8785
delete from t66 where col1='14:30:20';
8786
select * from t11 order by col1;
8789
select * from t22 order by col1;
8793
select * from t33 order by col1;
8797
select * from t44 order by colint;
8803
select * from t55 order by colint;
8809
insert into t11 values ('14:30:20');
8810
insert into t22 values ('14:30:20');
8811
insert into t33 values ('14:30:20');
8812
insert into t44 values (60,'14:30:20');
8813
insert into t55 values (60,'14:30:20');
8814
insert into t66 values (60,'14:30:20');
8815
select * from t11 order by col1;
8819
select * from t22 order by col1;
8824
select * from t33 order by col1;
8829
select * from t44 order by colint;
8836
select * from t55 order by colint;
8843
select * from t66 order by colint;
8850
alter table t11 drop partition p0;
8851
alter table t22 drop partition p0;
8852
alter table t44 drop partition p0;
8853
alter table t55 drop partition p0;
8854
alter table t66 drop partition p0;
8855
select * from t11 order by col1;
8859
select * from t22 order by col1;
8864
select * from t33 order by col1;
8869
select * from t44 order by colint;
8872
select * from t55 order by colint;
8875
select * from t66 order by colint;
8878
-------------------------
8879
---- some alter table end
8880
-------------------------
8881
drop table if exists t1 ;
8882
drop table if exists t2 ;
8883
drop table if exists t3 ;
8884
drop table if exists t4 ;
8885
drop table if exists t5 ;
8886
drop table if exists t6 ;
8887
drop table if exists t11 ;
8888
drop table if exists t22 ;
8889
drop table if exists t33 ;
8890
drop table if exists t44 ;
8891
drop table if exists t55 ;
8892
drop table if exists t66 ;
8893
-------------------------------------------------------------------------
8894
--- month(col1) in partition with coltype date
8895
-------------------------------------------------------------------------
8896
drop table if exists t1 ;
8897
drop table if exists t2 ;
8898
drop table if exists t3 ;
8899
drop table if exists t4 ;
8900
drop table if exists t5 ;
8901
drop table if exists t6 ;
8902
-------------------------------------------------------------------------
8903
--- Create tables with month(col1)
8904
-------------------------------------------------------------------------
8905
create table t1 (col1 date) engine='MYISAM'
8906
partition by range(month(col1))
8907
(partition p0 values less than (15),
8908
partition p1 values less than maxvalue);
8909
create table t2 (col1 date) engine='MYISAM'
8910
partition by list(month(col1))
8911
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8912
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8913
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8914
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8915
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8916
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8918
create table t3 (col1 date) engine='MYISAM'
8919
partition by hash(month(col1));
8920
create table t4 (colint int, col1 date) engine='MYISAM'
8921
partition by range(colint)
8922
subpartition by hash(month(col1)) subpartitions 2
8923
(partition p0 values less than (15),
8924
partition p1 values less than maxvalue);
8925
create table t5 (colint int, col1 date) engine='MYISAM'
8926
partition by list(colint)
8927
subpartition by hash(month(col1)) subpartitions 2
8928
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8929
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8930
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8931
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8932
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8933
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8935
create table t6 (colint int, col1 date) engine='MYISAM'
8936
partition by range(colint)
8937
(partition p0 values less than (month('2006-10-14')),
8938
partition p1 values less than maxvalue);
8939
-------------------------------------------------------------------------
8940
--- Access tables with month(col1)
8941
-------------------------------------------------------------------------
8942
insert into t1 values ('2006-01-03');
8943
insert into t1 values ('2006-12-17');
8944
insert into t2 values ('2006-01-03');
8945
insert into t2 values ('2006-12-17');
8946
insert into t2 values ('2006-05-25');
8947
insert into t3 values ('2006-01-03');
8948
insert into t3 values ('2006-12-17');
8949
insert into t3 values ('2006-05-25');
8950
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
8951
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
8952
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
8953
select month(col1) from t1 order by col1;
8957
select * from t1 order by col1;
8961
select * from t2 order by col1;
8966
select * from t3 order by col1;
8971
select * from t4 order by colint;
8977
select * from t5 order by colint;
8983
select * from t6 order by colint;
8989
update t1 set col1='2006-11-06' where col1='2006-01-03';
8990
update t2 set col1='2006-11-06' where col1='2006-01-03';
8991
update t3 set col1='2006-11-06' where col1='2006-01-03';
8992
update t4 set col1='2006-11-06' where col1='2006-01-03';
8993
update t5 set col1='2006-11-06' where col1='2006-01-03';
8994
update t6 set col1='2006-11-06' where col1='2006-01-03';
8995
select * from t1 order by col1;
8999
select * from t2 order by col1;
9004
select * from t3 order by col1;
9009
select * from t4 order by colint;
9015
select * from t5 order by colint;
9021
select * from t6 order by colint;
9027
-------------------------------------------------------------------------
9028
--- Alter tables with month(col1)
9029
-------------------------------------------------------------------------
9030
drop table if exists t11 ;
9031
drop table if exists t22 ;
9032
drop table if exists t33 ;
9033
drop table if exists t44 ;
9034
drop table if exists t55 ;
9035
drop table if exists t66 ;
9036
create table t11 engine='MYISAM' as select * from t1;
9037
create table t22 engine='MYISAM' as select * from t2;
9038
create table t33 engine='MYISAM' as select * from t3;
9039
create table t44 engine='MYISAM' as select * from t4;
9040
create table t55 engine='MYISAM' as select * from t5;
9041
create table t66 engine='MYISAM' as select * from t6;
9043
partition by range(month(col1))
9044
(partition p0 values less than (15),
9045
partition p1 values less than maxvalue);
9047
partition by list(month(col1))
9048
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9049
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9050
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9051
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9052
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9053
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9056
partition by hash(month(col1));
9058
partition by range(colint)
9059
subpartition by hash(month(col1)) subpartitions 2
9060
(partition p0 values less than (15),
9061
partition p1 values less than maxvalue);
9063
partition by list(colint)
9064
subpartition by hash(month(col1)) subpartitions 2
9065
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9066
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9067
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9068
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9069
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9070
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9073
partition by range(colint)
9074
(partition p0 values less than (month('2006-10-14')),
9075
partition p1 values less than maxvalue);
9076
select * from t11 order by col1;
9080
select * from t22 order by col1;
9085
select * from t33 order by col1;
9090
select * from t44 order by colint;
9096
select * from t55 order by colint;
9102
select * from t66 order by colint;
9108
---------------------------
9109
---- some alter table begin
9110
---------------------------
9112
reorganize partition p0,p1 into
9113
(partition s1 values less than maxvalue);
9114
select * from t11 order by col1;
9119
reorganize partition s1 into
9120
(partition p0 values less than (15),
9121
partition p1 values less than maxvalue);
9122
select * from t11 order by col1;
9127
partition by list(colint)
9128
subpartition by hash(month(col1)) subpartitions 5
9129
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9130
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9131
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9132
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9133
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9134
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9136
show create table t55;
9138
t55 CREATE TABLE `t55` (
9139
`colint` int(11) DEFAULT NULL,
9140
`col1` date DEFAULT NULL
9141
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (month(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
9142
select * from t55 order by colint;
9149
reorganize partition p0,p1 into
9150
(partition s1 values less than maxvalue);
9151
select * from t66 order by colint;
9158
reorganize partition s1 into
9159
(partition p0 values less than (month('2006-10-14')),
9160
partition p1 values less than maxvalue);
9161
select * from t66 order by colint;
9168
reorganize partition p0,p1 into
9169
(partition s1 values less than maxvalue);
9170
select * from t66 order by colint;
9177
reorganize partition s1 into
9178
(partition p0 values less than (month('2006-10-14')),
9179
partition p1 values less than maxvalue);
9180
select * from t66 order by colint;
9186
-------------------------------------------------------------------------
9187
--- Delete rows and partitions of tables with month(col1)
9188
-------------------------------------------------------------------------
9189
delete from t1 where col1='2006-12-17';
9190
delete from t2 where col1='2006-12-17';
9191
delete from t3 where col1='2006-12-17';
9192
delete from t4 where col1='2006-12-17';
9193
delete from t5 where col1='2006-12-17';
9194
delete from t6 where col1='2006-12-17';
9195
select * from t1 order by col1;
9198
select * from t2 order by col1;
9202
select * from t3 order by col1;
9206
select * from t4 order by colint;
9212
select * from t5 order by colint;
9218
insert into t1 values ('2006-12-17');
9219
insert into t2 values ('2006-12-17');
9220
insert into t3 values ('2006-12-17');
9221
insert into t4 values (60,'2006-12-17');
9222
insert into t5 values (60,'2006-12-17');
9223
insert into t6 values (60,'2006-12-17');
9224
select * from t1 order by col1;
9228
select * from t2 order by col1;
9233
select * from t3 order by col1;
9238
select * from t4 order by colint;
9245
select * from t5 order by colint;
9252
select * from t6 order by colint;
9259
alter table t1 drop partition p0;
9260
alter table t2 drop partition p0;
9261
alter table t4 drop partition p0;
9262
alter table t5 drop partition p0;
9263
alter table t6 drop partition p0;
9264
select * from t1 order by col1;
9266
select * from t2 order by col1;
9270
select * from t3 order by col1;
9275
select * from t4 order by colint;
9278
select * from t5 order by colint;
9281
select * from t6 order by colint;
9284
-------------------------------------------------------------------------
9285
--- Delete rows and partitions of tables with month(col1)
9286
-------------------------------------------------------------------------
9287
delete from t11 where col1='2006-12-17';
9288
delete from t22 where col1='2006-12-17';
9289
delete from t33 where col1='2006-12-17';
9290
delete from t44 where col1='2006-12-17';
9291
delete from t55 where col1='2006-12-17';
9292
delete from t66 where col1='2006-12-17';
9293
select * from t11 order by col1;
9296
select * from t22 order by col1;
9300
select * from t33 order by col1;
9304
select * from t44 order by colint;
9310
select * from t55 order by colint;
9316
insert into t11 values ('2006-12-17');
9317
insert into t22 values ('2006-12-17');
9318
insert into t33 values ('2006-12-17');
9319
insert into t44 values (60,'2006-12-17');
9320
insert into t55 values (60,'2006-12-17');
9321
insert into t66 values (60,'2006-12-17');
9322
select * from t11 order by col1;
9326
select * from t22 order by col1;
9331
select * from t33 order by col1;
9336
select * from t44 order by colint;
9343
select * from t55 order by colint;
9350
select * from t66 order by colint;
9357
alter table t11 drop partition p0;
9358
alter table t22 drop partition p0;
9359
alter table t44 drop partition p0;
9360
alter table t55 drop partition p0;
9361
alter table t66 drop partition p0;
9362
select * from t11 order by col1;
9364
select * from t22 order by col1;
9368
select * from t33 order by col1;
9373
select * from t44 order by colint;
9376
select * from t55 order by colint;
9379
select * from t66 order by colint;
9382
-------------------------
9383
---- some alter table end
9384
-------------------------
9385
drop table if exists t1 ;
9386
drop table if exists t2 ;
9387
drop table if exists t3 ;
9388
drop table if exists t4 ;
9389
drop table if exists t5 ;
9390
drop table if exists t6 ;
9391
drop table if exists t11 ;
9392
drop table if exists t22 ;
9393
drop table if exists t33 ;
9394
drop table if exists t44 ;
9395
drop table if exists t55 ;
9396
drop table if exists t66 ;
9397
-------------------------------------------------------------------------
9398
--- quarter(col1) in partition with coltype date
9399
-------------------------------------------------------------------------
9400
drop table if exists t1 ;
9401
drop table if exists t2 ;
9402
drop table if exists t3 ;
9403
drop table if exists t4 ;
9404
drop table if exists t5 ;
9405
drop table if exists t6 ;
9406
-------------------------------------------------------------------------
9407
--- Create tables with quarter(col1)
9408
-------------------------------------------------------------------------
9409
create table t1 (col1 date) engine='MYISAM'
9410
partition by range(quarter(col1))
9411
(partition p0 values less than (15),
9412
partition p1 values less than maxvalue);
9413
create table t2 (col1 date) engine='MYISAM'
9414
partition by list(quarter(col1))
9415
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9416
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9417
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9418
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9419
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9420
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9422
create table t3 (col1 date) engine='MYISAM'
9423
partition by hash(quarter(col1));
9424
create table t4 (colint int, col1 date) engine='MYISAM'
9425
partition by range(colint)
9426
subpartition by hash(quarter(col1)) subpartitions 2
9427
(partition p0 values less than (15),
9428
partition p1 values less than maxvalue);
9429
create table t5 (colint int, col1 date) engine='MYISAM'
9430
partition by list(colint)
9431
subpartition by hash(quarter(col1)) subpartitions 2
9432
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9433
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9434
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9435
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9436
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9437
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9439
create table t6 (colint int, col1 date) engine='MYISAM'
9440
partition by range(colint)
9441
(partition p0 values less than (quarter('2006-10-14')),
9442
partition p1 values less than maxvalue);
9443
-------------------------------------------------------------------------
9444
--- Access tables with quarter(col1)
9445
-------------------------------------------------------------------------
9446
insert into t1 values ('2006-01-03');
9447
insert into t1 values ('2006-12-17');
9448
insert into t2 values ('2006-01-03');
9449
insert into t2 values ('2006-12-17');
9450
insert into t2 values ('2006-09-25');
9451
insert into t3 values ('2006-01-03');
9452
insert into t3 values ('2006-12-17');
9453
insert into t3 values ('2006-09-25');
9454
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
9455
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
9456
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
9457
select quarter(col1) from t1 order by col1;
9461
select * from t1 order by col1;
9465
select * from t2 order by col1;
9470
select * from t3 order by col1;
9475
select * from t4 order by colint;
9481
select * from t5 order by colint;
9487
select * from t6 order by colint;
9493
update t1 set col1='2006-07-30' where col1='2006-01-03';
9494
update t2 set col1='2006-07-30' where col1='2006-01-03';
9495
update t3 set col1='2006-07-30' where col1='2006-01-03';
9496
update t4 set col1='2006-07-30' where col1='2006-01-03';
9497
update t5 set col1='2006-07-30' where col1='2006-01-03';
9498
update t6 set col1='2006-07-30' where col1='2006-01-03';
9499
select * from t1 order by col1;
9503
select * from t2 order by col1;
9508
select * from t3 order by col1;
9513
select * from t4 order by colint;
9519
select * from t5 order by colint;
9525
select * from t6 order by colint;
9531
-------------------------------------------------------------------------
9532
--- Alter tables with quarter(col1)
9533
-------------------------------------------------------------------------
9534
drop table if exists t11 ;
9535
drop table if exists t22 ;
9536
drop table if exists t33 ;
9537
drop table if exists t44 ;
9538
drop table if exists t55 ;
9539
drop table if exists t66 ;
9540
create table t11 engine='MYISAM' as select * from t1;
9541
create table t22 engine='MYISAM' as select * from t2;
9542
create table t33 engine='MYISAM' as select * from t3;
9543
create table t44 engine='MYISAM' as select * from t4;
9544
create table t55 engine='MYISAM' as select * from t5;
9545
create table t66 engine='MYISAM' as select * from t6;
9547
partition by range(quarter(col1))
9548
(partition p0 values less than (15),
9549
partition p1 values less than maxvalue);
9551
partition by list(quarter(col1))
9552
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9553
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9554
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9555
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9556
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9557
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9560
partition by hash(quarter(col1));
9562
partition by range(colint)
9563
subpartition by hash(quarter(col1)) subpartitions 2
9564
(partition p0 values less than (15),
9565
partition p1 values less than maxvalue);
9567
partition by list(colint)
9568
subpartition by hash(quarter(col1)) subpartitions 2
9569
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9570
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9571
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9572
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9573
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9574
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9577
partition by range(colint)
9578
(partition p0 values less than (quarter('2006-10-14')),
9579
partition p1 values less than maxvalue);
9580
select * from t11 order by col1;
9584
select * from t22 order by col1;
9589
select * from t33 order by col1;
9594
select * from t44 order by colint;
9600
select * from t55 order by colint;
9606
select * from t66 order by colint;
9612
---------------------------
9613
---- some alter table begin
9614
---------------------------
9616
reorganize partition p0,p1 into
9617
(partition s1 values less than maxvalue);
9618
select * from t11 order by col1;
9623
reorganize partition s1 into
9624
(partition p0 values less than (15),
9625
partition p1 values less than maxvalue);
9626
select * from t11 order by col1;
9631
partition by list(colint)
9632
subpartition by hash(quarter(col1)) subpartitions 5
9633
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9634
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9635
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9636
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9637
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9638
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9640
show create table t55;
9642
t55 CREATE TABLE `t55` (
9643
`colint` int(11) DEFAULT NULL,
9644
`col1` date DEFAULT NULL
9645
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (quarter(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
9646
select * from t55 order by colint;
9653
reorganize partition p0,p1 into
9654
(partition s1 values less than maxvalue);
9655
select * from t66 order by colint;
9662
reorganize partition s1 into
9663
(partition p0 values less than (quarter('2006-10-14')),
9664
partition p1 values less than maxvalue);
9665
select * from t66 order by colint;
9672
reorganize partition p0,p1 into
9673
(partition s1 values less than maxvalue);
9674
select * from t66 order by colint;
9681
reorganize partition s1 into
9682
(partition p0 values less than (quarter('2006-10-14')),
9683
partition p1 values less than maxvalue);
9684
select * from t66 order by colint;
9690
-------------------------------------------------------------------------
9691
--- Delete rows and partitions of tables with quarter(col1)
9692
-------------------------------------------------------------------------
9693
delete from t1 where col1='2006-12-17';
9694
delete from t2 where col1='2006-12-17';
9695
delete from t3 where col1='2006-12-17';
9696
delete from t4 where col1='2006-12-17';
9697
delete from t5 where col1='2006-12-17';
9698
delete from t6 where col1='2006-12-17';
9699
select * from t1 order by col1;
9702
select * from t2 order by col1;
9706
select * from t3 order by col1;
9710
select * from t4 order by colint;
9716
select * from t5 order by colint;
9722
insert into t1 values ('2006-12-17');
9723
insert into t2 values ('2006-12-17');
9724
insert into t3 values ('2006-12-17');
9725
insert into t4 values (60,'2006-12-17');
9726
insert into t5 values (60,'2006-12-17');
9727
insert into t6 values (60,'2006-12-17');
9728
select * from t1 order by col1;
9732
select * from t2 order by col1;
9737
select * from t3 order by col1;
9742
select * from t4 order by colint;
9749
select * from t5 order by colint;
9756
select * from t6 order by colint;
9763
alter table t1 drop partition p0;
9764
alter table t2 drop partition p0;
9765
alter table t4 drop partition p0;
9766
alter table t5 drop partition p0;
9767
alter table t6 drop partition p0;
9768
select * from t1 order by col1;
9770
select * from t2 order by col1;
9772
select * from t3 order by col1;
9777
select * from t4 order by colint;
9780
select * from t5 order by colint;
9783
select * from t6 order by colint;
9787
-------------------------------------------------------------------------
9788
--- Delete rows and partitions of tables with quarter(col1)
9789
-------------------------------------------------------------------------
9790
delete from t11 where col1='2006-12-17';
9791
delete from t22 where col1='2006-12-17';
9792
delete from t33 where col1='2006-12-17';
9793
delete from t44 where col1='2006-12-17';
9794
delete from t55 where col1='2006-12-17';
9795
delete from t66 where col1='2006-12-17';
9796
select * from t11 order by col1;
9799
select * from t22 order by col1;
9803
select * from t33 order by col1;
9807
select * from t44 order by colint;
9813
select * from t55 order by colint;
9819
insert into t11 values ('2006-12-17');
9820
insert into t22 values ('2006-12-17');
9821
insert into t33 values ('2006-12-17');
9822
insert into t44 values (60,'2006-12-17');
9823
insert into t55 values (60,'2006-12-17');
9824
insert into t66 values (60,'2006-12-17');
9825
select * from t11 order by col1;
9829
select * from t22 order by col1;
9834
select * from t33 order by col1;
9839
select * from t44 order by colint;
9846
select * from t55 order by colint;
9853
select * from t66 order by colint;
9860
alter table t11 drop partition p0;
9861
alter table t22 drop partition p0;
9862
alter table t44 drop partition p0;
9863
alter table t55 drop partition p0;
9864
alter table t66 drop partition p0;
9865
select * from t11 order by col1;
9867
select * from t22 order by col1;
9869
select * from t33 order by col1;
9874
select * from t44 order by colint;
9877
select * from t55 order by colint;
9880
select * from t66 order by colint;
9884
-------------------------
9885
---- some alter table end
9886
-------------------------
9887
drop table if exists t1 ;
9888
drop table if exists t2 ;
9889
drop table if exists t3 ;
9890
drop table if exists t4 ;
9891
drop table if exists t5 ;
9892
drop table if exists t6 ;
9893
drop table if exists t11 ;
9894
drop table if exists t22 ;
9895
drop table if exists t33 ;
9896
drop table if exists t44 ;
9897
drop table if exists t55 ;
9898
drop table if exists t66 ;
9899
-------------------------------------------------------------------------
9900
--- time_to_sec(col1)-(time_to_sec(col1)-20) in partition with coltype time
9901
-------------------------------------------------------------------------
9902
drop table if exists t1 ;
9903
drop table if exists t2 ;
9904
drop table if exists t3 ;
9905
drop table if exists t4 ;
9906
drop table if exists t5 ;
9907
drop table if exists t6 ;
9908
-------------------------------------------------------------------------
9909
--- Create tables with time_to_sec(col1)-(time_to_sec(col1)-20)
9910
-------------------------------------------------------------------------
9911
create table t1 (col1 time) engine='MYISAM'
9912
partition by range(time_to_sec(col1)-(time_to_sec(col1)-20))
9913
(partition p0 values less than (15),
9914
partition p1 values less than maxvalue);
9915
create table t2 (col1 time) engine='MYISAM'
9916
partition by list(time_to_sec(col1)-(time_to_sec(col1)-20))
9917
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9918
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9919
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9920
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9921
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9922
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9924
create table t3 (col1 time) engine='MYISAM'
9925
partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20));
9926
create table t4 (colint int, col1 time) engine='MYISAM'
9927
partition by range(colint)
9928
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
9929
(partition p0 values less than (15),
9930
partition p1 values less than maxvalue);
9931
create table t5 (colint int, col1 time) engine='MYISAM'
9932
partition by list(colint)
9933
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
9934
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9935
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9936
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9937
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9938
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9939
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9941
create table t6 (colint int, col1 time) engine='MYISAM'
9942
partition by range(colint)
9943
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
9944
partition p1 values less than maxvalue);
9945
-------------------------------------------------------------------------
9946
--- Access tables with time_to_sec(col1)-(time_to_sec(col1)-20)
9947
-------------------------------------------------------------------------
9948
insert into t1 values ('09:09:15');
9949
insert into t1 values ('14:30:45');
9950
insert into t2 values ('09:09:15');
9951
insert into t2 values ('14:30:45');
9952
insert into t2 values ('21:59:22');
9953
insert into t3 values ('09:09:15');
9954
insert into t3 values ('14:30:45');
9955
insert into t3 values ('21:59:22');
9956
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t4;
9957
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t5;
9958
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_time.inc' into table t6;
9959
select time_to_sec(col1)-(time_to_sec(col1)-20) from t1 order by col1;
9960
time_to_sec(col1)-(time_to_sec(col1)-20)
9963
select * from t1 order by col1;
9967
select * from t2 order by col1;
9972
select * from t3 order by col1;
9977
select * from t4 order by colint;
9983
select * from t5 order by colint;
9989
select * from t6 order by colint;
9995
update t1 set col1='10:33:11' where col1='09:09:15';
9996
update t2 set col1='10:33:11' where col1='09:09:15';
9997
update t3 set col1='10:33:11' where col1='09:09:15';
9998
update t4 set col1='10:33:11' where col1='09:09:15';
9999
update t5 set col1='10:33:11' where col1='09:09:15';
10000
update t6 set col1='10:33:11' where col1='09:09:15';
10001
select * from t1 order by col1;
10005
select * from t2 order by col1;
10010
select * from t3 order by col1;
10015
select * from t4 order by colint;
10021
select * from t5 order by colint;
10027
select * from t6 order by colint;
10033
-------------------------------------------------------------------------
10034
--- Alter tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10035
-------------------------------------------------------------------------
10036
drop table if exists t11 ;
10037
drop table if exists t22 ;
10038
drop table if exists t33 ;
10039
drop table if exists t44 ;
10040
drop table if exists t55 ;
10041
drop table if exists t66 ;
10042
create table t11 engine='MYISAM' as select * from t1;
10043
create table t22 engine='MYISAM' as select * from t2;
10044
create table t33 engine='MYISAM' as select * from t3;
10045
create table t44 engine='MYISAM' as select * from t4;
10046
create table t55 engine='MYISAM' as select * from t5;
10047
create table t66 engine='MYISAM' as select * from t6;
10049
partition by range(time_to_sec(col1)-(time_to_sec(col1)-20))
10050
(partition p0 values less than (15),
10051
partition p1 values less than maxvalue);
10053
partition by list(time_to_sec(col1)-(time_to_sec(col1)-20))
10054
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10055
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10056
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10057
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10058
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10059
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10062
partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20));
10064
partition by range(colint)
10065
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
10066
(partition p0 values less than (15),
10067
partition p1 values less than maxvalue);
10069
partition by list(colint)
10070
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
10071
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10072
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10073
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10074
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10075
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10076
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10079
partition by range(colint)
10080
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10081
partition p1 values less than maxvalue);
10082
select * from t11 order by col1;
10086
select * from t22 order by col1;
10091
select * from t33 order by col1;
10096
select * from t44 order by colint;
10102
select * from t55 order by colint;
10108
select * from t66 order by colint;
10114
---------------------------
10115
---- some alter table begin
10116
---------------------------
10118
reorganize partition p0,p1 into
10119
(partition s1 values less than maxvalue);
10120
select * from t11 order by col1;
10125
reorganize partition s1 into
10126
(partition p0 values less than (15),
10127
partition p1 values less than maxvalue);
10128
select * from t11 order by col1;
10133
partition by list(colint)
10134
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 5
10135
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10136
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10137
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10138
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10139
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10140
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10142
show create table t55;
10144
t55 CREATE TABLE `t55` (
10145
`colint` int(11) DEFAULT NULL,
10146
`col1` time DEFAULT NULL
10147
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (time_to_sec(col1)-(time_to_sec(col1)-20)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
10148
select * from t55 order by colint;
10155
reorganize partition p0,p1 into
10156
(partition s1 values less than maxvalue);
10157
select * from t66 order by colint;
10164
reorganize partition s1 into
10165
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10166
partition p1 values less than maxvalue);
10167
select * from t66 order by colint;
10174
reorganize partition p0,p1 into
10175
(partition s1 values less than maxvalue);
10176
select * from t66 order by colint;
10183
reorganize partition s1 into
10184
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10185
partition p1 values less than maxvalue);
10186
select * from t66 order by colint;
10192
-------------------------------------------------------------------------
10193
--- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10194
-------------------------------------------------------------------------
10195
delete from t1 where col1='14:30:45';
10196
delete from t2 where col1='14:30:45';
10197
delete from t3 where col1='14:30:45';
10198
delete from t4 where col1='14:30:45';
10199
delete from t5 where col1='14:30:45';
10200
delete from t6 where col1='14:30:45';
10201
select * from t1 order by col1;
10204
select * from t2 order by col1;
10208
select * from t3 order by col1;
10212
select * from t4 order by colint;
10218
select * from t5 order by colint;
10224
insert into t1 values ('14:30:45');
10225
insert into t2 values ('14:30:45');
10226
insert into t3 values ('14:30:45');
10227
insert into t4 values (60,'14:30:45');
10228
insert into t5 values (60,'14:30:45');
10229
insert into t6 values (60,'14:30:45');
10230
select * from t1 order by col1;
10234
select * from t2 order by col1;
10239
select * from t3 order by col1;
10244
select * from t4 order by colint;
10251
select * from t5 order by colint;
10258
select * from t6 order by colint;
10265
alter table t1 drop partition p0;
10266
alter table t2 drop partition p0;
10267
alter table t4 drop partition p0;
10268
alter table t5 drop partition p0;
10269
alter table t6 drop partition p0;
10270
select * from t1 order by col1;
10274
select * from t2 order by col1;
10279
select * from t3 order by col1;
10284
select * from t4 order by colint;
10287
select * from t5 order by colint;
10290
select * from t6 order by colint;
10292
-------------------------------------------------------------------------
10293
--- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10294
-------------------------------------------------------------------------
10295
delete from t11 where col1='14:30:45';
10296
delete from t22 where col1='14:30:45';
10297
delete from t33 where col1='14:30:45';
10298
delete from t44 where col1='14:30:45';
10299
delete from t55 where col1='14:30:45';
10300
delete from t66 where col1='14:30:45';
10301
select * from t11 order by col1;
10304
select * from t22 order by col1;
10308
select * from t33 order by col1;
10312
select * from t44 order by colint;
10318
select * from t55 order by colint;
10324
insert into t11 values ('14:30:45');
10325
insert into t22 values ('14:30:45');
10326
insert into t33 values ('14:30:45');
10327
insert into t44 values (60,'14:30:45');
10328
insert into t55 values (60,'14:30:45');
10329
insert into t66 values (60,'14:30:45');
10330
select * from t11 order by col1;
10334
select * from t22 order by col1;
10339
select * from t33 order by col1;
10344
select * from t44 order by colint;
10351
select * from t55 order by colint;
10358
select * from t66 order by colint;
10365
alter table t11 drop partition p0;
10366
alter table t22 drop partition p0;
10367
alter table t44 drop partition p0;
10368
alter table t55 drop partition p0;
10369
alter table t66 drop partition p0;
10370
select * from t11 order by col1;
10374
select * from t22 order by col1;
10379
select * from t33 order by col1;
10384
select * from t44 order by colint;
10387
select * from t55 order by colint;
10390
select * from t66 order by colint;
10392
-------------------------
10393
---- some alter table end
10394
-------------------------
10395
drop table if exists t1 ;
10396
drop table if exists t2 ;
10397
drop table if exists t3 ;
10398
drop table if exists t4 ;
10399
drop table if exists t5 ;
10400
drop table if exists t6 ;
10401
drop table if exists t11 ;
10402
drop table if exists t22 ;
10403
drop table if exists t33 ;
10404
drop table if exists t44 ;
10405
drop table if exists t55 ;
10406
drop table if exists t66 ;
10407
-------------------------------------------------------------------------
10408
--- to_days(col1)-to_days('2006-01-01') in partition with coltype date
10409
-------------------------------------------------------------------------
10410
drop table if exists t1 ;
10411
drop table if exists t2 ;
10412
drop table if exists t3 ;
10413
drop table if exists t4 ;
10414
drop table if exists t5 ;
10415
drop table if exists t6 ;
10416
-------------------------------------------------------------------------
10417
--- Create tables with to_days(col1)-to_days('2006-01-01')
10418
-------------------------------------------------------------------------
10419
create table t1 (col1 date) engine='MYISAM'
10420
partition by range(to_days(col1)-to_days('2006-01-01'))
10421
(partition p0 values less than (15),
10422
partition p1 values less than maxvalue);
10423
create table t2 (col1 date) engine='MYISAM'
10424
partition by list(to_days(col1)-to_days('2006-01-01'))
10425
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10426
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10427
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10428
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10429
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10430
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10432
create table t3 (col1 date) engine='MYISAM'
10433
partition by hash(to_days(col1)-to_days('2006-01-01'));
10434
create table t4 (colint int, col1 date) engine='MYISAM'
10435
partition by range(colint)
10436
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10437
(partition p0 values less than (15),
10438
partition p1 values less than maxvalue);
10439
create table t5 (colint int, col1 date) engine='MYISAM'
10440
partition by list(colint)
10441
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10442
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10443
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10444
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10445
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10446
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10447
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10449
create table t6 (colint int, col1 date) engine='MYISAM'
10450
partition by range(colint)
10451
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10452
partition p1 values less than maxvalue);
10453
-------------------------------------------------------------------------
10454
--- Access tables with to_days(col1)-to_days('2006-01-01')
10455
-------------------------------------------------------------------------
10456
insert into t1 values ('2006-02-03');
10457
insert into t1 values ('2006-01-17');
10458
insert into t2 values ('2006-02-03');
10459
insert into t2 values ('2006-01-17');
10460
insert into t2 values ('2006-01-25');
10461
insert into t3 values ('2006-02-03');
10462
insert into t3 values ('2006-01-17');
10463
insert into t3 values ('2006-01-25');
10464
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
10465
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
10466
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
10467
select to_days(col1)-to_days('2006-01-01') from t1 order by col1;
10468
to_days(col1)-to_days('2006-01-01')
10471
select * from t1 order by col1;
10475
select * from t2 order by col1;
10480
select * from t3 order by col1;
10485
select * from t4 order by colint;
10491
select * from t5 order by colint;
10497
select * from t6 order by colint;
10503
update t1 set col1='2006-02-06' where col1='2006-02-03';
10504
update t2 set col1='2006-02-06' where col1='2006-02-03';
10505
update t3 set col1='2006-02-06' where col1='2006-02-03';
10506
update t4 set col1='2006-02-06' where col1='2006-02-03';
10507
update t5 set col1='2006-02-06' where col1='2006-02-03';
10508
update t6 set col1='2006-02-06' where col1='2006-02-03';
10509
select * from t1 order by col1;
10513
select * from t2 order by col1;
10518
select * from t3 order by col1;
10523
select * from t4 order by colint;
10529
select * from t5 order by colint;
10535
select * from t6 order by colint;
10541
-------------------------------------------------------------------------
10542
--- Alter tables with to_days(col1)-to_days('2006-01-01')
10543
-------------------------------------------------------------------------
10544
drop table if exists t11 ;
10545
drop table if exists t22 ;
10546
drop table if exists t33 ;
10547
drop table if exists t44 ;
10548
drop table if exists t55 ;
10549
drop table if exists t66 ;
10550
create table t11 engine='MYISAM' as select * from t1;
10551
create table t22 engine='MYISAM' as select * from t2;
10552
create table t33 engine='MYISAM' as select * from t3;
10553
create table t44 engine='MYISAM' as select * from t4;
10554
create table t55 engine='MYISAM' as select * from t5;
10555
create table t66 engine='MYISAM' as select * from t6;
10557
partition by range(to_days(col1)-to_days('2006-01-01'))
10558
(partition p0 values less than (15),
10559
partition p1 values less than maxvalue);
10561
partition by list(to_days(col1)-to_days('2006-01-01'))
10562
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10563
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10564
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10565
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10566
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10567
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10570
partition by hash(to_days(col1)-to_days('2006-01-01'));
10572
partition by range(colint)
10573
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10574
(partition p0 values less than (15),
10575
partition p1 values less than maxvalue);
10577
partition by list(colint)
10578
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10579
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10580
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10581
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10582
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10583
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10584
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10587
partition by range(colint)
10588
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10589
partition p1 values less than maxvalue);
10590
select * from t11 order by col1;
10594
select * from t22 order by col1;
10599
select * from t33 order by col1;
10604
select * from t44 order by colint;
10610
select * from t55 order by colint;
10616
select * from t66 order by colint;
10622
---------------------------
10623
---- some alter table begin
10624
---------------------------
10626
reorganize partition p0,p1 into
10627
(partition s1 values less than maxvalue);
10628
select * from t11 order by col1;
10633
reorganize partition s1 into
10634
(partition p0 values less than (15),
10635
partition p1 values less than maxvalue);
10636
select * from t11 order by col1;
10641
partition by list(colint)
10642
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 5
10643
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10644
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10645
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10646
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10647
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10648
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10650
show create table t55;
10652
t55 CREATE TABLE `t55` (
10653
`colint` int(11) DEFAULT NULL,
10654
`col1` date DEFAULT NULL
10655
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01')) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
10656
select * from t55 order by colint;
10663
reorganize partition p0,p1 into
10664
(partition s1 values less than maxvalue);
10665
select * from t66 order by colint;
10672
reorganize partition s1 into
10673
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10674
partition p1 values less than maxvalue);
10675
select * from t66 order by colint;
10682
reorganize partition p0,p1 into
10683
(partition s1 values less than maxvalue);
10684
select * from t66 order by colint;
10691
reorganize partition s1 into
10692
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10693
partition p1 values less than maxvalue);
10694
select * from t66 order by colint;
10700
-------------------------------------------------------------------------
10701
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
10702
-------------------------------------------------------------------------
10703
delete from t1 where col1='2006-01-17';
10704
delete from t2 where col1='2006-01-17';
10705
delete from t3 where col1='2006-01-17';
10706
delete from t4 where col1='2006-01-17';
10707
delete from t5 where col1='2006-01-17';
10708
delete from t6 where col1='2006-01-17';
10709
select * from t1 order by col1;
10712
select * from t2 order by col1;
10716
select * from t3 order by col1;
10720
select * from t4 order by colint;
10725
select * from t5 order by colint;
10730
insert into t1 values ('2006-01-17');
10731
insert into t2 values ('2006-01-17');
10732
insert into t3 values ('2006-01-17');
10733
insert into t4 values (60,'2006-01-17');
10734
insert into t5 values (60,'2006-01-17');
10735
insert into t6 values (60,'2006-01-17');
10736
select * from t1 order by col1;
10740
select * from t2 order by col1;
10745
select * from t3 order by col1;
10750
select * from t4 order by colint;
10756
select * from t5 order by colint;
10762
select * from t6 order by colint;
10768
alter table t1 drop partition p0;
10769
alter table t2 drop partition p0;
10770
alter table t4 drop partition p0;
10771
alter table t5 drop partition p0;
10772
alter table t6 drop partition p0;
10773
select * from t1 order by col1;
10777
select * from t2 order by col1;
10782
select * from t3 order by col1;
10787
select * from t4 order by colint;
10790
select * from t5 order by colint;
10793
select * from t6 order by colint;
10796
-------------------------------------------------------------------------
10797
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
10798
-------------------------------------------------------------------------
10799
delete from t11 where col1='2006-01-17';
10800
delete from t22 where col1='2006-01-17';
10801
delete from t33 where col1='2006-01-17';
10802
delete from t44 where col1='2006-01-17';
10803
delete from t55 where col1='2006-01-17';
10804
delete from t66 where col1='2006-01-17';
10805
select * from t11 order by col1;
10808
select * from t22 order by col1;
10812
select * from t33 order by col1;
10816
select * from t44 order by colint;
10821
select * from t55 order by colint;
10826
insert into t11 values ('2006-01-17');
10827
insert into t22 values ('2006-01-17');
10828
insert into t33 values ('2006-01-17');
10829
insert into t44 values (60,'2006-01-17');
10830
insert into t55 values (60,'2006-01-17');
10831
insert into t66 values (60,'2006-01-17');
10832
select * from t11 order by col1;
10836
select * from t22 order by col1;
10841
select * from t33 order by col1;
10846
select * from t44 order by colint;
10852
select * from t55 order by colint;
10858
select * from t66 order by colint;
10864
alter table t11 drop partition p0;
10865
alter table t22 drop partition p0;
10866
alter table t44 drop partition p0;
10867
alter table t55 drop partition p0;
10868
alter table t66 drop partition p0;
10869
select * from t11 order by col1;
10873
select * from t22 order by col1;
10878
select * from t33 order by col1;
10883
select * from t44 order by colint;
10886
select * from t55 order by colint;
10889
select * from t66 order by colint;
10892
-------------------------
10893
---- some alter table end
10894
-------------------------
10895
drop table if exists t1 ;
10896
drop table if exists t2 ;
10897
drop table if exists t3 ;
10898
drop table if exists t4 ;
10899
drop table if exists t5 ;
10900
drop table if exists t6 ;
10901
drop table if exists t11 ;
10902
drop table if exists t22 ;
10903
drop table if exists t33 ;
10904
drop table if exists t44 ;
10905
drop table if exists t55 ;
10906
drop table if exists t66 ;
10907
-------------------------------------------------------------------------
10908
--- datediff(col1, '2006-01-01') in partition with coltype date
10909
-------------------------------------------------------------------------
10910
drop table if exists t1 ;
10911
drop table if exists t2 ;
10912
drop table if exists t3 ;
10913
drop table if exists t4 ;
10914
drop table if exists t5 ;
10915
drop table if exists t6 ;
10916
-------------------------------------------------------------------------
10917
--- Create tables with datediff(col1, '2006-01-01')
10918
-------------------------------------------------------------------------
10919
create table t1 (col1 date) engine='MYISAM'
10920
partition by range(datediff(col1, '2006-01-01'))
10921
(partition p0 values less than (15),
10922
partition p1 values less than maxvalue);
10923
create table t2 (col1 date) engine='MYISAM'
10924
partition by list(datediff(col1, '2006-01-01'))
10925
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10926
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10927
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10928
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10929
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10930
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10932
create table t3 (col1 date) engine='MYISAM'
10933
partition by hash(datediff(col1, '2006-01-01'));
10934
create table t4 (colint int, col1 date) engine='MYISAM'
10935
partition by range(colint)
10936
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
10937
(partition p0 values less than (15),
10938
partition p1 values less than maxvalue);
10939
create table t5 (colint int, col1 date) engine='MYISAM'
10940
partition by list(colint)
10941
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
10942
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10943
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10944
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10945
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10946
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10947
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10949
create table t6 (colint int, col1 date) engine='MYISAM'
10950
partition by range(colint)
10951
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
10952
partition p1 values less than maxvalue);
10953
-------------------------------------------------------------------------
10954
--- Access tables with datediff(col1, '2006-01-01')
10955
-------------------------------------------------------------------------
10956
insert into t1 values ('2006-02-03');
10957
insert into t1 values ('2006-01-17');
10958
insert into t2 values ('2006-02-03');
10959
insert into t2 values ('2006-01-17');
10960
insert into t2 values ('2006-01-25');
10961
insert into t3 values ('2006-02-03');
10962
insert into t3 values ('2006-01-17');
10963
insert into t3 values ('2006-01-25');
10964
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
10965
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
10966
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
10967
select datediff(col1, '2006-01-01') from t1 order by col1;
10968
datediff(col1, '2006-01-01')
10971
select * from t1 order by col1;
10975
select * from t2 order by col1;
10980
select * from t3 order by col1;
10985
select * from t4 order by colint;
10991
select * from t5 order by colint;
10997
select * from t6 order by colint;
11003
update t1 set col1='2006-02-06' where col1='2006-02-03';
11004
update t2 set col1='2006-02-06' where col1='2006-02-03';
11005
update t3 set col1='2006-02-06' where col1='2006-02-03';
11006
update t4 set col1='2006-02-06' where col1='2006-02-03';
11007
update t5 set col1='2006-02-06' where col1='2006-02-03';
11008
update t6 set col1='2006-02-06' where col1='2006-02-03';
11009
select * from t1 order by col1;
11013
select * from t2 order by col1;
11018
select * from t3 order by col1;
11023
select * from t4 order by colint;
11029
select * from t5 order by colint;
11035
select * from t6 order by colint;
11041
-------------------------------------------------------------------------
11042
--- Alter tables with datediff(col1, '2006-01-01')
11043
-------------------------------------------------------------------------
11044
drop table if exists t11 ;
11045
drop table if exists t22 ;
11046
drop table if exists t33 ;
11047
drop table if exists t44 ;
11048
drop table if exists t55 ;
11049
drop table if exists t66 ;
11050
create table t11 engine='MYISAM' as select * from t1;
11051
create table t22 engine='MYISAM' as select * from t2;
11052
create table t33 engine='MYISAM' as select * from t3;
11053
create table t44 engine='MYISAM' as select * from t4;
11054
create table t55 engine='MYISAM' as select * from t5;
11055
create table t66 engine='MYISAM' as select * from t6;
11057
partition by range(datediff(col1, '2006-01-01'))
11058
(partition p0 values less than (15),
11059
partition p1 values less than maxvalue);
11061
partition by list(datediff(col1, '2006-01-01'))
11062
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11063
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11064
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11065
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11066
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11067
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11070
partition by hash(datediff(col1, '2006-01-01'));
11072
partition by range(colint)
11073
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11074
(partition p0 values less than (15),
11075
partition p1 values less than maxvalue);
11077
partition by list(colint)
11078
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11079
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11080
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11081
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11082
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11083
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11084
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11087
partition by range(colint)
11088
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11089
partition p1 values less than maxvalue);
11090
select * from t11 order by col1;
11094
select * from t22 order by col1;
11099
select * from t33 order by col1;
11104
select * from t44 order by colint;
11110
select * from t55 order by colint;
11116
select * from t66 order by colint;
11122
---------------------------
11123
---- some alter table begin
11124
---------------------------
11126
reorganize partition p0,p1 into
11127
(partition s1 values less than maxvalue);
11128
select * from t11 order by col1;
11133
reorganize partition s1 into
11134
(partition p0 values less than (15),
11135
partition p1 values less than maxvalue);
11136
select * from t11 order by col1;
11141
partition by list(colint)
11142
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 5
11143
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11144
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11145
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11146
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11147
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11148
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11150
show create table t55;
11152
t55 CREATE TABLE `t55` (
11153
`colint` int(11) DEFAULT NULL,
11154
`col1` date DEFAULT NULL
11155
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (datediff(col1, '2006-01-01')) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
11156
select * from t55 order by colint;
11163
reorganize partition p0,p1 into
11164
(partition s1 values less than maxvalue);
11165
select * from t66 order by colint;
11172
reorganize partition s1 into
11173
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11174
partition p1 values less than maxvalue);
11175
select * from t66 order by colint;
11182
reorganize partition p0,p1 into
11183
(partition s1 values less than maxvalue);
11184
select * from t66 order by colint;
11191
reorganize partition s1 into
11192
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11193
partition p1 values less than maxvalue);
11194
select * from t66 order by colint;
11200
-------------------------------------------------------------------------
11201
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
11202
-------------------------------------------------------------------------
11203
delete from t1 where col1='2006-01-17';
11204
delete from t2 where col1='2006-01-17';
11205
delete from t3 where col1='2006-01-17';
11206
delete from t4 where col1='2006-01-17';
11207
delete from t5 where col1='2006-01-17';
11208
delete from t6 where col1='2006-01-17';
11209
select * from t1 order by col1;
11212
select * from t2 order by col1;
11216
select * from t3 order by col1;
11220
select * from t4 order by colint;
11225
select * from t5 order by colint;
11230
insert into t1 values ('2006-01-17');
11231
insert into t2 values ('2006-01-17');
11232
insert into t3 values ('2006-01-17');
11233
insert into t4 values (60,'2006-01-17');
11234
insert into t5 values (60,'2006-01-17');
11235
insert into t6 values (60,'2006-01-17');
11236
select * from t1 order by col1;
11240
select * from t2 order by col1;
11245
select * from t3 order by col1;
11250
select * from t4 order by colint;
11256
select * from t5 order by colint;
11262
select * from t6 order by colint;
11268
alter table t1 drop partition p0;
11269
alter table t2 drop partition p0;
11270
alter table t4 drop partition p0;
11271
alter table t5 drop partition p0;
11272
alter table t6 drop partition p0;
11273
select * from t1 order by col1;
11277
select * from t2 order by col1;
11282
select * from t3 order by col1;
11287
select * from t4 order by colint;
11290
select * from t5 order by colint;
11293
select * from t6 order by colint;
11296
-------------------------------------------------------------------------
11297
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
11298
-------------------------------------------------------------------------
11299
delete from t11 where col1='2006-01-17';
11300
delete from t22 where col1='2006-01-17';
11301
delete from t33 where col1='2006-01-17';
11302
delete from t44 where col1='2006-01-17';
11303
delete from t55 where col1='2006-01-17';
11304
delete from t66 where col1='2006-01-17';
11305
select * from t11 order by col1;
11308
select * from t22 order by col1;
11312
select * from t33 order by col1;
11316
select * from t44 order by colint;
11321
select * from t55 order by colint;
11326
insert into t11 values ('2006-01-17');
11327
insert into t22 values ('2006-01-17');
11328
insert into t33 values ('2006-01-17');
11329
insert into t44 values (60,'2006-01-17');
11330
insert into t55 values (60,'2006-01-17');
11331
insert into t66 values (60,'2006-01-17');
11332
select * from t11 order by col1;
11336
select * from t22 order by col1;
11341
select * from t33 order by col1;
11346
select * from t44 order by colint;
11352
select * from t55 order by colint;
11358
select * from t66 order by colint;
11364
alter table t11 drop partition p0;
11365
alter table t22 drop partition p0;
11366
alter table t44 drop partition p0;
11367
alter table t55 drop partition p0;
11368
alter table t66 drop partition p0;
11369
select * from t11 order by col1;
11373
select * from t22 order by col1;
11378
select * from t33 order by col1;
11383
select * from t44 order by colint;
11386
select * from t55 order by colint;
11389
select * from t66 order by colint;
11392
-------------------------
11393
---- some alter table end
11394
-------------------------
11395
drop table if exists t1 ;
11396
drop table if exists t2 ;
11397
drop table if exists t3 ;
11398
drop table if exists t4 ;
11399
drop table if exists t5 ;
11400
drop table if exists t6 ;
11401
drop table if exists t11 ;
11402
drop table if exists t22 ;
11403
drop table if exists t33 ;
11404
drop table if exists t44 ;
11405
drop table if exists t55 ;
11406
drop table if exists t66 ;
11407
-------------------------------------------------------------------------
11408
--- weekday(col1) in partition with coltype date
11409
-------------------------------------------------------------------------
11410
drop table if exists t1 ;
11411
drop table if exists t2 ;
11412
drop table if exists t3 ;
11413
drop table if exists t4 ;
11414
drop table if exists t5 ;
11415
drop table if exists t6 ;
11416
-------------------------------------------------------------------------
11417
--- Create tables with weekday(col1)
11418
-------------------------------------------------------------------------
11419
create table t1 (col1 date) engine='MYISAM'
11420
partition by range(weekday(col1))
11421
(partition p0 values less than (15),
11422
partition p1 values less than maxvalue);
11423
create table t2 (col1 date) engine='MYISAM'
11424
partition by list(weekday(col1))
11425
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11426
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11427
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11428
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11429
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11430
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11432
create table t3 (col1 date) engine='MYISAM'
11433
partition by hash(weekday(col1));
11434
create table t4 (colint int, col1 date) engine='MYISAM'
11435
partition by range(colint)
11436
subpartition by hash(weekday(col1)) subpartitions 2
11437
(partition p0 values less than (15),
11438
partition p1 values less than maxvalue);
11439
create table t5 (colint int, col1 date) engine='MYISAM'
11440
partition by list(colint)
11441
subpartition by hash(weekday(col1)) subpartitions 2
11442
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11443
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11444
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11445
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11446
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11447
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11449
create table t6 (colint int, col1 date) engine='MYISAM'
11450
partition by range(colint)
11451
(partition p0 values less than (weekday('2006-10-14')),
11452
partition p1 values less than maxvalue);
11453
-------------------------------------------------------------------------
11454
--- Access tables with weekday(col1)
11455
-------------------------------------------------------------------------
11456
insert into t1 values ('2006-12-03');
11457
insert into t1 values ('2006-11-17');
11458
insert into t2 values ('2006-12-03');
11459
insert into t2 values ('2006-11-17');
11460
insert into t2 values ('2006-05-25');
11461
insert into t3 values ('2006-12-03');
11462
insert into t3 values ('2006-11-17');
11463
insert into t3 values ('2006-05-25');
11464
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
11465
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
11466
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
11467
select weekday(col1) from t1 order by col1;
11471
select * from t1 order by col1;
11475
select * from t2 order by col1;
11480
select * from t3 order by col1;
11485
select * from t4 order by colint;
11491
select * from t5 order by colint;
11497
select * from t6 order by colint;
11503
update t1 set col1='2006-02-06' where col1='2006-12-03';
11504
update t2 set col1='2006-02-06' where col1='2006-12-03';
11505
update t3 set col1='2006-02-06' where col1='2006-12-03';
11506
update t4 set col1='2006-02-06' where col1='2006-12-03';
11507
update t5 set col1='2006-02-06' where col1='2006-12-03';
11508
update t6 set col1='2006-02-06' where col1='2006-12-03';
11509
select * from t1 order by col1;
11513
select * from t2 order by col1;
11518
select * from t3 order by col1;
11523
select * from t4 order by colint;
11529
select * from t5 order by colint;
11535
select * from t6 order by colint;
11541
-------------------------------------------------------------------------
11542
--- Alter tables with weekday(col1)
11543
-------------------------------------------------------------------------
11544
drop table if exists t11 ;
11545
drop table if exists t22 ;
11546
drop table if exists t33 ;
11547
drop table if exists t44 ;
11548
drop table if exists t55 ;
11549
drop table if exists t66 ;
11550
create table t11 engine='MYISAM' as select * from t1;
11551
create table t22 engine='MYISAM' as select * from t2;
11552
create table t33 engine='MYISAM' as select * from t3;
11553
create table t44 engine='MYISAM' as select * from t4;
11554
create table t55 engine='MYISAM' as select * from t5;
11555
create table t66 engine='MYISAM' as select * from t6;
11557
partition by range(weekday(col1))
11558
(partition p0 values less than (15),
11559
partition p1 values less than maxvalue);
11561
partition by list(weekday(col1))
11562
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11563
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11564
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11565
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11566
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11567
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11570
partition by hash(weekday(col1));
11572
partition by range(colint)
11573
subpartition by hash(weekday(col1)) subpartitions 2
11574
(partition p0 values less than (15),
11575
partition p1 values less than maxvalue);
11577
partition by list(colint)
11578
subpartition by hash(weekday(col1)) subpartitions 2
11579
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11580
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11581
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11582
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11583
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11584
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11587
partition by range(colint)
11588
(partition p0 values less than (weekday('2006-10-14')),
11589
partition p1 values less than maxvalue);
11590
select * from t11 order by col1;
11594
select * from t22 order by col1;
11599
select * from t33 order by col1;
11604
select * from t44 order by colint;
11610
select * from t55 order by colint;
11616
select * from t66 order by colint;
11622
---------------------------
11623
---- some alter table begin
11624
---------------------------
11626
reorganize partition p0,p1 into
11627
(partition s1 values less than maxvalue);
11628
select * from t11 order by col1;
11633
reorganize partition s1 into
11634
(partition p0 values less than (15),
11635
partition p1 values less than maxvalue);
11636
select * from t11 order by col1;
11641
partition by list(colint)
11642
subpartition by hash(weekday(col1)) subpartitions 5
11643
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11644
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11645
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11646
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11647
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11648
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11650
show create table t55;
11652
t55 CREATE TABLE `t55` (
11653
`colint` int(11) DEFAULT NULL,
11654
`col1` date DEFAULT NULL
11655
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (weekday(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
11656
select * from t55 order by colint;
11663
reorganize partition p0,p1 into
11664
(partition s1 values less than maxvalue);
11665
select * from t66 order by colint;
11672
reorganize partition s1 into
11673
(partition p0 values less than (weekday('2006-10-14')),
11674
partition p1 values less than maxvalue);
11675
select * from t66 order by colint;
11682
reorganize partition p0,p1 into
11683
(partition s1 values less than maxvalue);
11684
select * from t66 order by colint;
11691
reorganize partition s1 into
11692
(partition p0 values less than (weekday('2006-10-14')),
11693
partition p1 values less than maxvalue);
11694
select * from t66 order by colint;
11700
-------------------------------------------------------------------------
11701
--- Delete rows and partitions of tables with weekday(col1)
11702
-------------------------------------------------------------------------
11703
delete from t1 where col1='2006-11-17';
11704
delete from t2 where col1='2006-11-17';
11705
delete from t3 where col1='2006-11-17';
11706
delete from t4 where col1='2006-11-17';
11707
delete from t5 where col1='2006-11-17';
11708
delete from t6 where col1='2006-11-17';
11709
select * from t1 order by col1;
11712
select * from t2 order by col1;
11716
select * from t3 order by col1;
11720
select * from t4 order by colint;
11726
select * from t5 order by colint;
11732
insert into t1 values ('2006-11-17');
11733
insert into t2 values ('2006-11-17');
11734
insert into t3 values ('2006-11-17');
11735
insert into t4 values (60,'2006-11-17');
11736
insert into t5 values (60,'2006-11-17');
11737
insert into t6 values (60,'2006-11-17');
11738
select * from t1 order by col1;
11742
select * from t2 order by col1;
11747
select * from t3 order by col1;
11752
select * from t4 order by colint;
11759
select * from t5 order by colint;
11766
select * from t6 order by colint;
11773
alter table t1 drop partition p0;
11774
alter table t2 drop partition p0;
11775
alter table t4 drop partition p0;
11776
alter table t5 drop partition p0;
11777
alter table t6 drop partition p0;
11778
select * from t1 order by col1;
11780
select * from t2 order by col1;
11782
select * from t3 order by col1;
11787
select * from t4 order by colint;
11790
select * from t5 order by colint;
11793
select * from t6 order by colint;
11796
-------------------------------------------------------------------------
11797
--- Delete rows and partitions of tables with weekday(col1)
11798
-------------------------------------------------------------------------
11799
delete from t11 where col1='2006-11-17';
11800
delete from t22 where col1='2006-11-17';
11801
delete from t33 where col1='2006-11-17';
11802
delete from t44 where col1='2006-11-17';
11803
delete from t55 where col1='2006-11-17';
11804
delete from t66 where col1='2006-11-17';
11805
select * from t11 order by col1;
11808
select * from t22 order by col1;
11812
select * from t33 order by col1;
11816
select * from t44 order by colint;
11822
select * from t55 order by colint;
11828
insert into t11 values ('2006-11-17');
11829
insert into t22 values ('2006-11-17');
11830
insert into t33 values ('2006-11-17');
11831
insert into t44 values (60,'2006-11-17');
11832
insert into t55 values (60,'2006-11-17');
11833
insert into t66 values (60,'2006-11-17');
11834
select * from t11 order by col1;
11838
select * from t22 order by col1;
11843
select * from t33 order by col1;
11848
select * from t44 order by colint;
11855
select * from t55 order by colint;
11862
select * from t66 order by colint;
11869
alter table t11 drop partition p0;
11870
alter table t22 drop partition p0;
11871
alter table t44 drop partition p0;
11872
alter table t55 drop partition p0;
11873
alter table t66 drop partition p0;
11874
select * from t11 order by col1;
11876
select * from t22 order by col1;
11878
select * from t33 order by col1;
11883
select * from t44 order by colint;
11886
select * from t55 order by colint;
11889
select * from t66 order by colint;
11892
-------------------------
11893
---- some alter table end
11894
-------------------------
11895
drop table if exists t1 ;
11896
drop table if exists t2 ;
11897
drop table if exists t3 ;
11898
drop table if exists t4 ;
11899
drop table if exists t5 ;
11900
drop table if exists t6 ;
11901
drop table if exists t11 ;
11902
drop table if exists t22 ;
11903
drop table if exists t33 ;
11904
drop table if exists t44 ;
11905
drop table if exists t55 ;
11906
drop table if exists t66 ;
11907
-------------------------------------------------------------------------
11908
--- year(col1)-1990 in partition with coltype date
11909
-------------------------------------------------------------------------
11910
drop table if exists t1 ;
11911
drop table if exists t2 ;
11912
drop table if exists t3 ;
11913
drop table if exists t4 ;
11914
drop table if exists t5 ;
11915
drop table if exists t6 ;
11916
-------------------------------------------------------------------------
11917
--- Create tables with year(col1)-1990
11918
-------------------------------------------------------------------------
11919
create table t1 (col1 date) engine='MYISAM'
11920
partition by range(year(col1)-1990)
11921
(partition p0 values less than (15),
11922
partition p1 values less than maxvalue);
11923
create table t2 (col1 date) engine='MYISAM'
11924
partition by list(year(col1)-1990)
11925
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11926
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11927
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11928
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11929
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11930
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11932
create table t3 (col1 date) engine='MYISAM'
11933
partition by hash(year(col1)-1990);
11934
create table t4 (colint int, col1 date) engine='MYISAM'
11935
partition by range(colint)
11936
subpartition by hash(year(col1)-1990) subpartitions 2
11937
(partition p0 values less than (15),
11938
partition p1 values less than maxvalue);
11939
create table t5 (colint int, col1 date) engine='MYISAM'
11940
partition by list(colint)
11941
subpartition by hash(year(col1)-1990) subpartitions 2
11942
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11943
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11944
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11945
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11946
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11947
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11949
create table t6 (colint int, col1 date) engine='MYISAM'
11950
partition by range(colint)
11951
(partition p0 values less than (year('2005-10-14')-1990),
11952
partition p1 values less than maxvalue);
11953
-------------------------------------------------------------------------
11954
--- Access tables with year(col1)-1990
11955
-------------------------------------------------------------------------
11956
insert into t1 values ('1996-01-03');
11957
insert into t1 values ('2000-02-17');
11958
insert into t2 values ('1996-01-03');
11959
insert into t2 values ('2000-02-17');
11960
insert into t2 values ('2004-05-25');
11961
insert into t3 values ('1996-01-03');
11962
insert into t3 values ('2000-02-17');
11963
insert into t3 values ('2004-05-25');
11964
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
11965
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
11966
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
11967
select year(col1)-1990 from t1 order by col1;
11971
select * from t1 order by col1;
11975
select * from t2 order by col1;
11980
select * from t3 order by col1;
11985
select * from t4 order by colint;
11991
select * from t5 order by colint;
11997
select * from t6 order by colint;
12003
update t1 set col1='2002-02-15' where col1='1996-01-03';
12004
update t2 set col1='2002-02-15' where col1='1996-01-03';
12005
update t3 set col1='2002-02-15' where col1='1996-01-03';
12006
update t4 set col1='2002-02-15' where col1='1996-01-03';
12007
update t5 set col1='2002-02-15' where col1='1996-01-03';
12008
update t6 set col1='2002-02-15' where col1='1996-01-03';
12009
select * from t1 order by col1;
12013
select * from t2 order by col1;
12018
select * from t3 order by col1;
12023
select * from t4 order by colint;
12029
select * from t5 order by colint;
12035
select * from t6 order by colint;
12041
-------------------------------------------------------------------------
12042
--- Alter tables with year(col1)-1990
12043
-------------------------------------------------------------------------
12044
drop table if exists t11 ;
12045
drop table if exists t22 ;
12046
drop table if exists t33 ;
12047
drop table if exists t44 ;
12048
drop table if exists t55 ;
12049
drop table if exists t66 ;
12050
create table t11 engine='MYISAM' as select * from t1;
12051
create table t22 engine='MYISAM' as select * from t2;
12052
create table t33 engine='MYISAM' as select * from t3;
12053
create table t44 engine='MYISAM' as select * from t4;
12054
create table t55 engine='MYISAM' as select * from t5;
12055
create table t66 engine='MYISAM' as select * from t6;
12057
partition by range(year(col1)-1990)
12058
(partition p0 values less than (15),
12059
partition p1 values less than maxvalue);
12061
partition by list(year(col1)-1990)
12062
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12063
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12064
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12065
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12066
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12067
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12070
partition by hash(year(col1)-1990);
12072
partition by range(colint)
12073
subpartition by hash(year(col1)-1990) subpartitions 2
12074
(partition p0 values less than (15),
12075
partition p1 values less than maxvalue);
12077
partition by list(colint)
12078
subpartition by hash(year(col1)-1990) subpartitions 2
12079
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12080
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12081
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12082
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12083
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12084
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12087
partition by range(colint)
12088
(partition p0 values less than (year('2005-10-14')-1990),
12089
partition p1 values less than maxvalue);
12090
select * from t11 order by col1;
12094
select * from t22 order by col1;
12099
select * from t33 order by col1;
12104
select * from t44 order by colint;
12110
select * from t55 order by colint;
12116
select * from t66 order by colint;
12122
---------------------------
12123
---- some alter table begin
12124
---------------------------
12126
reorganize partition p0,p1 into
12127
(partition s1 values less than maxvalue);
12128
select * from t11 order by col1;
12133
reorganize partition s1 into
12134
(partition p0 values less than (15),
12135
partition p1 values less than maxvalue);
12136
select * from t11 order by col1;
12141
partition by list(colint)
12142
subpartition by hash(year(col1)-1990) subpartitions 5
12143
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12144
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12145
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12146
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12147
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12148
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12150
show create table t55;
12152
t55 CREATE TABLE `t55` (
12153
`colint` int(11) DEFAULT NULL,
12154
`col1` date DEFAULT NULL
12155
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (year(col1)-1990) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
12156
select * from t55 order by colint;
12163
reorganize partition p0,p1 into
12164
(partition s1 values less than maxvalue);
12165
select * from t66 order by colint;
12172
reorganize partition s1 into
12173
(partition p0 values less than (year('2005-10-14')-1990),
12174
partition p1 values less than maxvalue);
12175
select * from t66 order by colint;
12182
reorganize partition p0,p1 into
12183
(partition s1 values less than maxvalue);
12184
select * from t66 order by colint;
12191
reorganize partition s1 into
12192
(partition p0 values less than (year('2005-10-14')-1990),
12193
partition p1 values less than maxvalue);
12194
select * from t66 order by colint;
12200
-------------------------------------------------------------------------
12201
--- Delete rows and partitions of tables with year(col1)-1990
12202
-------------------------------------------------------------------------
12203
delete from t1 where col1='2000-02-17';
12204
delete from t2 where col1='2000-02-17';
12205
delete from t3 where col1='2000-02-17';
12206
delete from t4 where col1='2000-02-17';
12207
delete from t5 where col1='2000-02-17';
12208
delete from t6 where col1='2000-02-17';
12209
select * from t1 order by col1;
12212
select * from t2 order by col1;
12216
select * from t3 order by col1;
12220
select * from t4 order by colint;
12226
select * from t5 order by colint;
12232
insert into t1 values ('2000-02-17');
12233
insert into t2 values ('2000-02-17');
12234
insert into t3 values ('2000-02-17');
12235
insert into t4 values (60,'2000-02-17');
12236
insert into t5 values (60,'2000-02-17');
12237
insert into t6 values (60,'2000-02-17');
12238
select * from t1 order by col1;
12242
select * from t2 order by col1;
12247
select * from t3 order by col1;
12252
select * from t4 order by colint;
12259
select * from t5 order by colint;
12266
select * from t6 order by colint;
12273
alter table t1 drop partition p0;
12274
alter table t2 drop partition p0;
12275
alter table t4 drop partition p0;
12276
alter table t5 drop partition p0;
12277
alter table t6 drop partition p0;
12278
select * from t1 order by col1;
12280
select * from t2 order by col1;
12284
select * from t3 order by col1;
12289
select * from t4 order by colint;
12292
select * from t5 order by colint;
12295
select * from t6 order by colint;
12298
-------------------------------------------------------------------------
12299
--- Delete rows and partitions of tables with year(col1)-1990
12300
-------------------------------------------------------------------------
12301
delete from t11 where col1='2000-02-17';
12302
delete from t22 where col1='2000-02-17';
12303
delete from t33 where col1='2000-02-17';
12304
delete from t44 where col1='2000-02-17';
12305
delete from t55 where col1='2000-02-17';
12306
delete from t66 where col1='2000-02-17';
12307
select * from t11 order by col1;
12310
select * from t22 order by col1;
12314
select * from t33 order by col1;
12318
select * from t44 order by colint;
12324
select * from t55 order by colint;
12330
insert into t11 values ('2000-02-17');
12331
insert into t22 values ('2000-02-17');
12332
insert into t33 values ('2000-02-17');
12333
insert into t44 values (60,'2000-02-17');
12334
insert into t55 values (60,'2000-02-17');
12335
insert into t66 values (60,'2000-02-17');
12336
select * from t11 order by col1;
12340
select * from t22 order by col1;
12345
select * from t33 order by col1;
12350
select * from t44 order by colint;
12357
select * from t55 order by colint;
12364
select * from t66 order by colint;
12371
alter table t11 drop partition p0;
12372
alter table t22 drop partition p0;
12373
alter table t44 drop partition p0;
12374
alter table t55 drop partition p0;
12375
alter table t66 drop partition p0;
12376
select * from t11 order by col1;
12378
select * from t22 order by col1;
12382
select * from t33 order by col1;
12387
select * from t44 order by colint;
12390
select * from t55 order by colint;
12393
select * from t66 order by colint;
12396
-------------------------
12397
---- some alter table end
12398
-------------------------
12399
drop table if exists t1 ;
12400
drop table if exists t2 ;
12401
drop table if exists t3 ;
12402
drop table if exists t4 ;
12403
drop table if exists t5 ;
12404
drop table if exists t6 ;
12405
drop table if exists t11 ;
12406
drop table if exists t22 ;
12407
drop table if exists t33 ;
12408
drop table if exists t44 ;
12409
drop table if exists t55 ;
12410
drop table if exists t66 ;
12411
-------------------------------------------------------------------------
12412
--- yearweek(col1)-200600 in partition with coltype date
12413
-------------------------------------------------------------------------
12414
drop table if exists t1 ;
12415
drop table if exists t2 ;
12416
drop table if exists t3 ;
12417
drop table if exists t4 ;
12418
drop table if exists t5 ;
12419
drop table if exists t6 ;
12420
-------------------------------------------------------------------------
12421
--- Create tables with yearweek(col1)-200600
12422
-------------------------------------------------------------------------
12423
create table t1 (col1 date) engine='MYISAM'
12424
partition by range(yearweek(col1)-200600)
12425
(partition p0 values less than (15),
12426
partition p1 values less than maxvalue);
12427
create table t2 (col1 date) engine='MYISAM'
12428
partition by list(yearweek(col1)-200600)
12429
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12430
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12431
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12432
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12433
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12434
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12436
create table t3 (col1 date) engine='MYISAM'
12437
partition by hash(yearweek(col1)-200600);
12438
create table t4 (colint int, col1 date) engine='MYISAM'
12439
partition by range(colint)
12440
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12441
(partition p0 values less than (15),
12442
partition p1 values less than maxvalue);
12443
create table t5 (colint int, col1 date) engine='MYISAM'
12444
partition by list(colint)
12445
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12446
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12447
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12448
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12449
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12450
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12451
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12453
create table t6 (colint int, col1 date) engine='MYISAM'
12454
partition by range(colint)
12455
(partition p0 values less than (yearweek('2006-10-14')-200600),
12456
partition p1 values less than maxvalue);
12457
-------------------------------------------------------------------------
12458
--- Access tables with yearweek(col1)-200600
12459
-------------------------------------------------------------------------
12460
insert into t1 values ('2006-01-03');
12461
insert into t1 values ('2006-08-17');
12462
insert into t2 values ('2006-01-03');
12463
insert into t2 values ('2006-08-17');
12464
insert into t2 values ('2006-03-25');
12465
insert into t3 values ('2006-01-03');
12466
insert into t3 values ('2006-08-17');
12467
insert into t3 values ('2006-03-25');
12468
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
12469
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
12470
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
12471
select yearweek(col1)-200600 from t1 order by col1;
12472
yearweek(col1)-200600
12475
select * from t1 order by col1;
12479
select * from t2 order by col1;
12484
select * from t3 order by col1;
12489
select * from t4 order by colint;
12495
select * from t5 order by colint;
12501
select * from t6 order by colint;
12507
update t1 set col1='2006-11-15' where col1='2006-01-03';
12508
update t2 set col1='2006-11-15' where col1='2006-01-03';
12509
update t3 set col1='2006-11-15' where col1='2006-01-03';
12510
update t4 set col1='2006-11-15' where col1='2006-01-03';
12511
update t5 set col1='2006-11-15' where col1='2006-01-03';
12512
update t6 set col1='2006-11-15' where col1='2006-01-03';
12513
select * from t1 order by col1;
12517
select * from t2 order by col1;
12522
select * from t3 order by col1;
12527
select * from t4 order by colint;
12533
select * from t5 order by colint;
12539
select * from t6 order by colint;
12545
-------------------------------------------------------------------------
12546
--- Alter tables with yearweek(col1)-200600
12547
-------------------------------------------------------------------------
12548
drop table if exists t11 ;
12549
drop table if exists t22 ;
12550
drop table if exists t33 ;
12551
drop table if exists t44 ;
12552
drop table if exists t55 ;
12553
drop table if exists t66 ;
12554
create table t11 engine='MYISAM' as select * from t1;
12555
create table t22 engine='MYISAM' as select * from t2;
12556
create table t33 engine='MYISAM' as select * from t3;
12557
create table t44 engine='MYISAM' as select * from t4;
12558
create table t55 engine='MYISAM' as select * from t5;
12559
create table t66 engine='MYISAM' as select * from t6;
12561
partition by range(yearweek(col1)-200600)
12562
(partition p0 values less than (15),
12563
partition p1 values less than maxvalue);
12565
partition by list(yearweek(col1)-200600)
12566
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12567
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12568
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12569
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12570
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12571
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12574
partition by hash(yearweek(col1)-200600);
12576
partition by range(colint)
12577
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12578
(partition p0 values less than (15),
12579
partition p1 values less than maxvalue);
12581
partition by list(colint)
12582
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12583
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12584
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12585
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12586
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12587
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12588
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12591
partition by range(colint)
12592
(partition p0 values less than (yearweek('2006-10-14')-200600),
12593
partition p1 values less than maxvalue);
12594
select * from t11 order by col1;
12598
select * from t22 order by col1;
12603
select * from t33 order by col1;
12608
select * from t44 order by colint;
12614
select * from t55 order by colint;
12620
select * from t66 order by colint;
12626
---------------------------
12627
---- some alter table begin
12628
---------------------------
12630
reorganize partition p0,p1 into
12631
(partition s1 values less than maxvalue);
12632
select * from t11 order by col1;
12637
reorganize partition s1 into
12638
(partition p0 values less than (15),
12639
partition p1 values less than maxvalue);
12640
select * from t11 order by col1;
12645
partition by list(colint)
12646
subpartition by hash(yearweek(col1)-200600) subpartitions 5
12647
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12648
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12649
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12650
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12651
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12652
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12654
show create table t55;
12656
t55 CREATE TABLE `t55` (
12657
`colint` int(11) DEFAULT NULL,
12658
`col1` date DEFAULT NULL
12659
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (yearweek(col1)-200600) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
12660
select * from t55 order by colint;
12667
reorganize partition p0,p1 into
12668
(partition s1 values less than maxvalue);
12669
select * from t66 order by colint;
12676
reorganize partition s1 into
12677
(partition p0 values less than (yearweek('2006-10-14')-200600),
12678
partition p1 values less than maxvalue);
12679
select * from t66 order by colint;
12686
reorganize partition p0,p1 into
12687
(partition s1 values less than maxvalue);
12688
select * from t66 order by colint;
12695
reorganize partition s1 into
12696
(partition p0 values less than (yearweek('2006-10-14')-200600),
12697
partition p1 values less than maxvalue);
12698
select * from t66 order by colint;
12704
-------------------------------------------------------------------------
12705
--- Delete rows and partitions of tables with yearweek(col1)-200600
12706
-------------------------------------------------------------------------
12707
delete from t1 where col1='2006-08-17';
12708
delete from t2 where col1='2006-08-17';
12709
delete from t3 where col1='2006-08-17';
12710
delete from t4 where col1='2006-08-17';
12711
delete from t5 where col1='2006-08-17';
12712
delete from t6 where col1='2006-08-17';
12713
select * from t1 order by col1;
12716
select * from t2 order by col1;
12720
select * from t3 order by col1;
12724
select * from t4 order by colint;
12730
select * from t5 order by colint;
12736
insert into t1 values ('2006-08-17');
12737
insert into t2 values ('2006-08-17');
12738
insert into t3 values ('2006-08-17');
12739
insert into t4 values (60,'2006-08-17');
12740
insert into t5 values (60,'2006-08-17');
12741
insert into t6 values (60,'2006-08-17');
12742
select * from t1 order by col1;
12746
select * from t2 order by col1;
12751
select * from t3 order by col1;
12756
select * from t4 order by colint;
12763
select * from t5 order by colint;
12770
select * from t6 order by colint;
12777
alter table t1 drop partition p0;
12778
alter table t2 drop partition p0;
12779
alter table t4 drop partition p0;
12780
alter table t5 drop partition p0;
12781
alter table t6 drop partition p0;
12782
select * from t1 order by col1;
12786
select * from t2 order by col1;
12791
select * from t3 order by col1;
12796
select * from t4 order by colint;
12799
select * from t5 order by colint;
12802
select * from t6 order by colint;
12805
-------------------------------------------------------------------------
12806
--- Delete rows and partitions of tables with yearweek(col1)-200600
12807
-------------------------------------------------------------------------
12808
delete from t11 where col1='2006-08-17';
12809
delete from t22 where col1='2006-08-17';
12810
delete from t33 where col1='2006-08-17';
12811
delete from t44 where col1='2006-08-17';
12812
delete from t55 where col1='2006-08-17';
12813
delete from t66 where col1='2006-08-17';
12814
select * from t11 order by col1;
12817
select * from t22 order by col1;
12821
select * from t33 order by col1;
12825
select * from t44 order by colint;
12831
select * from t55 order by colint;
12837
insert into t11 values ('2006-08-17');
12838
insert into t22 values ('2006-08-17');
12839
insert into t33 values ('2006-08-17');
12840
insert into t44 values (60,'2006-08-17');
12841
insert into t55 values (60,'2006-08-17');
12842
insert into t66 values (60,'2006-08-17');
12843
select * from t11 order by col1;
12847
select * from t22 order by col1;
12852
select * from t33 order by col1;
12857
select * from t44 order by colint;
12864
select * from t55 order by colint;
12871
select * from t66 order by colint;
12878
alter table t11 drop partition p0;
12879
alter table t22 drop partition p0;
12880
alter table t44 drop partition p0;
12881
alter table t55 drop partition p0;
12882
alter table t66 drop partition p0;
12883
select * from t11 order by col1;
12887
select * from t22 order by col1;
12892
select * from t33 order by col1;
12897
select * from t44 order by colint;
12900
select * from t55 order by colint;
12903
select * from t66 order by colint;
12906
-------------------------
12907
---- some alter table end
12908
-------------------------
12909
drop table if exists t1 ;
12910
drop table if exists t2 ;
12911
drop table if exists t3 ;
12912
drop table if exists t4 ;
12913
drop table if exists t5 ;
12914
drop table if exists t6 ;
12915
drop table if exists t11 ;
12916
drop table if exists t22 ;
12917
drop table if exists t33 ;
12918
drop table if exists t44 ;
12919
drop table if exists t55 ;
12920
drop table if exists t66 ;
added
removed
mysql-test/suite/parts/r/part_supported_sql_func_myisam.result
