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='INNODB'
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='INNODB'
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='INNODB'
27
partition by hash(abs(col1));
28
create table t4 (colint int, col1 int) engine='INNODB'
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='INNODB'
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='INNODB'
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 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t4;
59
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t5;
60
load data infile 'MYSQLTEST_VARDIR/std_data/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='INNODB' as select * from t1;
391
create table t22 engine='INNODB' as select * from t2;
392
create table t33 engine='INNODB' as select * from t3;
393
create table t44 engine='INNODB' as select * from t4;
394
create table t55 engine='INNODB' as select * from t5;
395
create table t66 engine='INNODB' 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=InnoDB DEFAULT CHARSET=latin1
619
/*!50100 PARTITION BY LIST (colint)
620
SUBPARTITION BY HASH (abs(col1))
622
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
623
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
624
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
625
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
626
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
627
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
628
select * from t55 order by colint;
676
reorganize partition p0,p1 into
677
(partition s1 values less than maxvalue);
678
select * from t66 order by colint;
726
reorganize partition s1 into
727
(partition p0 values less than (abs(15)),
728
partition p1 values less than maxvalue);
729
select * from t66 order by colint;
777
reorganize partition p0,p1 into
778
(partition s1 values less than maxvalue);
779
select * from t66 order by colint;
827
reorganize partition s1 into
828
(partition p0 values less than (abs(15)),
829
partition p1 values less than maxvalue);
830
select * from t66 order by colint;
877
-------------------------------------------------------------------------
878
--- Delete rows and partitions of tables with abs(col1)
879
-------------------------------------------------------------------------
880
delete from t1 where col1=13 ;
881
delete from t2 where col1=13 ;
882
delete from t3 where col1=13 ;
883
delete from t4 where col1=13 ;
884
delete from t5 where col1=13 ;
885
delete from t6 where col1=13 ;
886
select * from t1 order by col1;
889
select * from t2 order by col1;
893
select * from t3 order by col1;
897
select * from t4 order by colint;
943
select * from t5 order by colint;
989
insert into t1 values (13 );
990
insert into t2 values (13 );
991
insert into t3 values (13 );
992
insert into t4 values (60,13 );
993
insert into t5 values (60,13 );
994
insert into t6 values (60,13 );
995
select * from t1 order by col1;
999
select * from t2 order by col1;
1004
select * from t3 order by col1;
1009
select * from t4 order by colint;
1056
select * from t5 order by colint;
1103
select * from t6 order by colint;
1150
alter table t1 drop partition p0;
1151
alter table t2 drop partition p0;
1152
alter table t4 drop partition p0;
1153
alter table t5 drop partition p0;
1154
alter table t6 drop partition p0;
1155
select * from t1 order by col1;
1158
select * from t2 order by col1;
1163
select * from t3 order by col1;
1168
select * from t4 order by colint;
1202
select * from t5 order by colint;
1240
select * from t6 order by colint;
1274
-------------------------------------------------------------------------
1275
--- Delete rows and partitions of tables with abs(col1)
1276
-------------------------------------------------------------------------
1277
delete from t11 where col1=13 ;
1278
delete from t22 where col1=13 ;
1279
delete from t33 where col1=13 ;
1280
delete from t44 where col1=13 ;
1281
delete from t55 where col1=13 ;
1282
delete from t66 where col1=13 ;
1283
select * from t11 order by col1;
1286
select * from t22 order by col1;
1290
select * from t33 order by col1;
1294
select * from t44 order by colint;
1340
select * from t55 order by colint;
1386
insert into t11 values (13 );
1387
insert into t22 values (13 );
1388
insert into t33 values (13 );
1389
insert into t44 values (60,13 );
1390
insert into t55 values (60,13 );
1391
insert into t66 values (60,13 );
1392
select * from t11 order by col1;
1396
select * from t22 order by col1;
1401
select * from t33 order by col1;
1406
select * from t44 order by colint;
1453
select * from t55 order by colint;
1500
select * from t66 order by colint;
1547
alter table t11 drop partition p0;
1548
alter table t22 drop partition p0;
1549
alter table t44 drop partition p0;
1550
alter table t55 drop partition p0;
1551
alter table t66 drop partition p0;
1552
select * from t11 order by col1;
1555
select * from t22 order by col1;
1560
select * from t33 order by col1;
1565
select * from t44 order by colint;
1599
select * from t55 order by colint;
1637
select * from t66 order by colint;
1671
-------------------------
1672
---- some alter table end
1673
-------------------------
1674
drop table if exists t1 ;
1675
drop table if exists t2 ;
1676
drop table if exists t3 ;
1677
drop table if exists t4 ;
1678
drop table if exists t5 ;
1679
drop table if exists t6 ;
1680
drop table if exists t11 ;
1681
drop table if exists t22 ;
1682
drop table if exists t33 ;
1683
drop table if exists t44 ;
1684
drop table if exists t55 ;
1685
drop table if exists t66 ;
1686
-------------------------------------------------------------------------
1687
--- mod(col1,10) in partition with coltype int
1688
-------------------------------------------------------------------------
1689
drop table if exists t1 ;
1690
drop table if exists t2 ;
1691
drop table if exists t3 ;
1692
drop table if exists t4 ;
1693
drop table if exists t5 ;
1694
drop table if exists t6 ;
1695
-------------------------------------------------------------------------
1696
--- Create tables with mod(col1,10)
1697
-------------------------------------------------------------------------
1698
create table t1 (col1 int) engine='INNODB'
1699
partition by range(mod(col1,10))
1700
(partition p0 values less than (15),
1701
partition p1 values less than maxvalue);
1702
create table t2 (col1 int) engine='INNODB'
1703
partition by list(mod(col1,10))
1704
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1705
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1706
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1707
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1708
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1709
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1711
create table t3 (col1 int) engine='INNODB'
1712
partition by hash(mod(col1,10));
1713
create table t4 (colint int, col1 int) engine='INNODB'
1714
partition by range(colint)
1715
subpartition by hash(mod(col1,10)) subpartitions 2
1716
(partition p0 values less than (15),
1717
partition p1 values less than maxvalue);
1718
create table t5 (colint int, col1 int) engine='INNODB'
1719
partition by list(colint)
1720
subpartition by hash(mod(col1,10)) subpartitions 2
1721
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
1722
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1723
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1724
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1725
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1726
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1728
create table t6 (colint int, col1 int) engine='INNODB'
1729
partition by range(colint)
1730
(partition p0 values less than (mod(15,10)),
1731
partition p1 values less than maxvalue);
1732
-------------------------------------------------------------------------
1733
--- Access tables with mod(col1,10)
1734
-------------------------------------------------------------------------
1735
insert into t1 values (5);
1736
insert into t1 values (19);
1737
insert into t2 values (5);
1738
insert into t2 values (19);
1739
insert into t2 values (17);
1740
insert into t3 values (5);
1741
insert into t3 values (19);
1742
insert into t3 values (17);
1743
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t4;
1744
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t5;
1745
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t6;
1746
select mod(col1,10) from t1 order by col1;
1750
select * from t1 order by col1;
1754
select * from t2 order by col1;
1759
select * from t3 order by col1;
1764
select * from t4 order by colint;
1811
select * from t5 order by colint;
1858
select * from t6 order by colint;
1905
update t1 set col1=15 where col1=5;
1906
update t2 set col1=15 where col1=5;
1907
update t3 set col1=15 where col1=5;
1908
update t4 set col1=15 where col1=5;
1909
update t5 set col1=15 where col1=5;
1910
update t6 set col1=15 where col1=5;
1911
select * from t1 order by col1;
1915
select * from t2 order by col1;
1920
select * from t3 order by col1;
1925
select * from t4 order by colint;
1972
select * from t5 order by colint;
2019
select * from t6 order by colint;
2066
-------------------------------------------------------------------------
2067
--- Alter tables with mod(col1,10)
2068
-------------------------------------------------------------------------
2069
drop table if exists t11 ;
2070
drop table if exists t22 ;
2071
drop table if exists t33 ;
2072
drop table if exists t44 ;
2073
drop table if exists t55 ;
2074
drop table if exists t66 ;
2075
create table t11 engine='INNODB' as select * from t1;
2076
create table t22 engine='INNODB' as select * from t2;
2077
create table t33 engine='INNODB' as select * from t3;
2078
create table t44 engine='INNODB' as select * from t4;
2079
create table t55 engine='INNODB' as select * from t5;
2080
create table t66 engine='INNODB' as select * from t6;
2082
partition by range(mod(col1,10))
2083
(partition p0 values less than (15),
2084
partition p1 values less than maxvalue);
2086
partition by list(mod(col1,10))
2087
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2088
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2089
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2090
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2091
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2092
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2095
partition by hash(mod(col1,10));
2097
partition by range(colint)
2098
subpartition by hash(mod(col1,10)) subpartitions 2
2099
(partition p0 values less than (15),
2100
partition p1 values less than maxvalue);
2102
partition by list(colint)
2103
subpartition by hash(mod(col1,10)) subpartitions 2
2104
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2105
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2106
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2107
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2108
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2109
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2112
partition by range(colint)
2113
(partition p0 values less than (mod(15,10)),
2114
partition p1 values less than maxvalue);
2115
select * from t11 order by col1;
2119
select * from t22 order by col1;
2124
select * from t33 order by col1;
2129
select * from t44 order by colint;
2176
select * from t55 order by colint;
2223
select * from t66 order by colint;
2270
---------------------------
2271
---- some alter table begin
2272
---------------------------
2274
reorganize partition p0,p1 into
2275
(partition s1 values less than maxvalue);
2276
select * from t11 order by col1;
2281
reorganize partition s1 into
2282
(partition p0 values less than (15),
2283
partition p1 values less than maxvalue);
2284
select * from t11 order by col1;
2289
partition by list(colint)
2290
subpartition by hash(mod(col1,10)) subpartitions 5
2291
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2292
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2293
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2294
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2295
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2296
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2298
show create table t55;
2300
t55 CREATE TABLE `t55` (
2301
`colint` int(11) DEFAULT NULL,
2302
`col1` int(11) DEFAULT NULL
2303
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2304
/*!50100 PARTITION BY LIST (colint)
2305
SUBPARTITION BY HASH (mod(col1,10))
2307
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
2308
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
2309
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
2310
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
2311
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
2312
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
2313
select * from t55 order by colint;
2361
reorganize partition p0,p1 into
2362
(partition s1 values less than maxvalue);
2363
select * from t66 order by colint;
2411
reorganize partition s1 into
2412
(partition p0 values less than (mod(15,10)),
2413
partition p1 values less than maxvalue);
2414
select * from t66 order by colint;
2462
reorganize partition p0,p1 into
2463
(partition s1 values less than maxvalue);
2464
select * from t66 order by colint;
2512
reorganize partition s1 into
2513
(partition p0 values less than (mod(15,10)),
2514
partition p1 values less than maxvalue);
2515
select * from t66 order by colint;
2562
-------------------------------------------------------------------------
2563
--- Delete rows and partitions of tables with mod(col1,10)
2564
-------------------------------------------------------------------------
2565
delete from t1 where col1=19;
2566
delete from t2 where col1=19;
2567
delete from t3 where col1=19;
2568
delete from t4 where col1=19;
2569
delete from t5 where col1=19;
2570
delete from t6 where col1=19;
2571
select * from t1 order by col1;
2574
select * from t2 order by col1;
2578
select * from t3 order by col1;
2582
select * from t4 order by colint;
2629
select * from t5 order by colint;
2676
insert into t1 values (19);
2677
insert into t2 values (19);
2678
insert into t3 values (19);
2679
insert into t4 values (60,19);
2680
insert into t5 values (60,19);
2681
insert into t6 values (60,19);
2682
select * from t1 order by col1;
2686
select * from t2 order by col1;
2691
select * from t3 order by col1;
2696
select * from t4 order by colint;
2744
select * from t5 order by colint;
2792
select * from t6 order by colint;
2840
alter table t1 drop partition p0;
2841
alter table t2 drop partition p0;
2842
alter table t4 drop partition p0;
2843
alter table t5 drop partition p0;
2844
alter table t6 drop partition p0;
2845
select * from t1 order by col1;
2847
select * from t2 order by col1;
2849
select * from t3 order by col1;
2854
select * from t4 order by colint;
2888
select * from t5 order by colint;
2926
select * from t6 order by colint;
2970
-------------------------------------------------------------------------
2971
--- Delete rows and partitions of tables with mod(col1,10)
2972
-------------------------------------------------------------------------
2973
delete from t11 where col1=19;
2974
delete from t22 where col1=19;
2975
delete from t33 where col1=19;
2976
delete from t44 where col1=19;
2977
delete from t55 where col1=19;
2978
delete from t66 where col1=19;
2979
select * from t11 order by col1;
2982
select * from t22 order by col1;
2986
select * from t33 order by col1;
2990
select * from t44 order by colint;
3037
select * from t55 order by colint;
3084
insert into t11 values (19);
3085
insert into t22 values (19);
3086
insert into t33 values (19);
3087
insert into t44 values (60,19);
3088
insert into t55 values (60,19);
3089
insert into t66 values (60,19);
3090
select * from t11 order by col1;
3094
select * from t22 order by col1;
3099
select * from t33 order by col1;
3104
select * from t44 order by colint;
3152
select * from t55 order by colint;
3200
select * from t66 order by colint;
3248
alter table t11 drop partition p0;
3249
alter table t22 drop partition p0;
3250
alter table t44 drop partition p0;
3251
alter table t55 drop partition p0;
3252
alter table t66 drop partition p0;
3253
select * from t11 order by col1;
3255
select * from t22 order by col1;
3257
select * from t33 order by col1;
3262
select * from t44 order by colint;
3296
select * from t55 order by colint;
3334
select * from t66 order by colint;
3378
-------------------------
3379
---- some alter table end
3380
-------------------------
3381
drop table if exists t1 ;
3382
drop table if exists t2 ;
3383
drop table if exists t3 ;
3384
drop table if exists t4 ;
3385
drop table if exists t5 ;
3386
drop table if exists t6 ;
3387
drop table if exists t11 ;
3388
drop table if exists t22 ;
3389
drop table if exists t33 ;
3390
drop table if exists t44 ;
3391
drop table if exists t55 ;
3392
drop table if exists t66 ;
3393
-------------------------------------------------------------------------
3394
--- day(col1) in partition with coltype date
3395
-------------------------------------------------------------------------
3396
drop table if exists t1 ;
3397
drop table if exists t2 ;
3398
drop table if exists t3 ;
3399
drop table if exists t4 ;
3400
drop table if exists t5 ;
3401
drop table if exists t6 ;
3402
-------------------------------------------------------------------------
3403
--- Create tables with day(col1)
3404
-------------------------------------------------------------------------
3405
create table t1 (col1 date) engine='INNODB'
3406
partition by range(day(col1))
3407
(partition p0 values less than (15),
3408
partition p1 values less than maxvalue);
3409
create table t2 (col1 date) engine='INNODB'
3410
partition by list(day(col1))
3411
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3412
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3413
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3414
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3415
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3416
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3418
create table t3 (col1 date) engine='INNODB'
3419
partition by hash(day(col1));
3420
create table t4 (colint int, col1 date) engine='INNODB'
3421
partition by range(colint)
3422
subpartition by hash(day(col1)) subpartitions 2
3423
(partition p0 values less than (15),
3424
partition p1 values less than maxvalue);
3425
create table t5 (colint int, col1 date) engine='INNODB'
3426
partition by list(colint)
3427
subpartition by hash(day(col1)) subpartitions 2
3428
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3429
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3430
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3431
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3432
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3433
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3435
create table t6 (colint int, col1 date) engine='INNODB'
3436
partition by range(colint)
3437
(partition p0 values less than (day('2006-12-21')),
3438
partition p1 values less than maxvalue);
3439
-------------------------------------------------------------------------
3440
--- Access tables with day(col1)
3441
-------------------------------------------------------------------------
3442
insert into t1 values ('2006-02-03');
3443
insert into t1 values ('2006-01-17');
3444
insert into t2 values ('2006-02-03');
3445
insert into t2 values ('2006-01-17');
3446
insert into t2 values ('2006-01-25');
3447
insert into t3 values ('2006-02-03');
3448
insert into t3 values ('2006-01-17');
3449
insert into t3 values ('2006-01-25');
3450
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
3451
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
3452
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
3453
select day(col1) from t1 order by col1;
3457
select * from t1 order by col1;
3461
select * from t2 order by col1;
3466
select * from t3 order by col1;
3471
select * from t4 order by colint;
3477
select * from t5 order by colint;
3483
select * from t6 order by colint;
3489
update t1 set col1='2006-02-05' where col1='2006-02-03';
3490
update t2 set col1='2006-02-05' where col1='2006-02-03';
3491
update t3 set col1='2006-02-05' where col1='2006-02-03';
3492
update t4 set col1='2006-02-05' where col1='2006-02-03';
3493
update t5 set col1='2006-02-05' where col1='2006-02-03';
3494
update t6 set col1='2006-02-05' where col1='2006-02-03';
3495
select * from t1 order by col1;
3499
select * from t2 order by col1;
3504
select * from t3 order by col1;
3509
select * from t4 order by colint;
3515
select * from t5 order by colint;
3521
select * from t6 order by colint;
3527
-------------------------------------------------------------------------
3528
--- Alter tables with day(col1)
3529
-------------------------------------------------------------------------
3530
drop table if exists t11 ;
3531
drop table if exists t22 ;
3532
drop table if exists t33 ;
3533
drop table if exists t44 ;
3534
drop table if exists t55 ;
3535
drop table if exists t66 ;
3536
create table t11 engine='INNODB' as select * from t1;
3537
create table t22 engine='INNODB' as select * from t2;
3538
create table t33 engine='INNODB' as select * from t3;
3539
create table t44 engine='INNODB' as select * from t4;
3540
create table t55 engine='INNODB' as select * from t5;
3541
create table t66 engine='INNODB' as select * from t6;
3543
partition by range(day(col1))
3544
(partition p0 values less than (15),
3545
partition p1 values less than maxvalue);
3547
partition by list(day(col1))
3548
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3549
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3550
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3551
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3552
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3553
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3556
partition by hash(day(col1));
3558
partition by range(colint)
3559
subpartition by hash(day(col1)) subpartitions 2
3560
(partition p0 values less than (15),
3561
partition p1 values less than maxvalue);
3563
partition by list(colint)
3564
subpartition by hash(day(col1)) subpartitions 2
3565
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3566
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3567
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3568
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3569
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3570
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3573
partition by range(colint)
3574
(partition p0 values less than (day('2006-12-21')),
3575
partition p1 values less than maxvalue);
3576
select * from t11 order by col1;
3580
select * from t22 order by col1;
3585
select * from t33 order by col1;
3590
select * from t44 order by colint;
3596
select * from t55 order by colint;
3602
select * from t66 order by colint;
3608
---------------------------
3609
---- some alter table begin
3610
---------------------------
3612
reorganize partition p0,p1 into
3613
(partition s1 values less than maxvalue);
3614
select * from t11 order by col1;
3619
reorganize partition s1 into
3620
(partition p0 values less than (15),
3621
partition p1 values less than maxvalue);
3622
select * from t11 order by col1;
3627
partition by list(colint)
3628
subpartition by hash(day(col1)) subpartitions 5
3629
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3630
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3631
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3632
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3633
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3634
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3636
show create table t55;
3638
t55 CREATE TABLE `t55` (
3639
`colint` int(11) DEFAULT NULL,
3640
`col1` date DEFAULT NULL
3641
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3642
/*!50100 PARTITION BY LIST (colint)
3643
SUBPARTITION BY HASH (day(col1))
3645
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
3646
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
3647
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
3648
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
3649
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
3650
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
3651
select * from t55 order by colint;
3658
reorganize partition p0,p1 into
3659
(partition s1 values less than maxvalue);
3660
select * from t66 order by colint;
3667
reorganize partition s1 into
3668
(partition p0 values less than (day('2006-12-21')),
3669
partition p1 values less than maxvalue);
3670
select * from t66 order by colint;
3677
reorganize partition p0,p1 into
3678
(partition s1 values less than maxvalue);
3679
select * from t66 order by colint;
3686
reorganize partition s1 into
3687
(partition p0 values less than (day('2006-12-21')),
3688
partition p1 values less than maxvalue);
3689
select * from t66 order by colint;
3695
-------------------------------------------------------------------------
3696
--- Delete rows and partitions of tables with day(col1)
3697
-------------------------------------------------------------------------
3698
delete from t1 where col1='2006-01-17';
3699
delete from t2 where col1='2006-01-17';
3700
delete from t3 where col1='2006-01-17';
3701
delete from t4 where col1='2006-01-17';
3702
delete from t5 where col1='2006-01-17';
3703
delete from t6 where col1='2006-01-17';
3704
select * from t1 order by col1;
3707
select * from t2 order by col1;
3711
select * from t3 order by col1;
3715
select * from t4 order by colint;
3720
select * from t5 order by colint;
3725
insert into t1 values ('2006-01-17');
3726
insert into t2 values ('2006-01-17');
3727
insert into t3 values ('2006-01-17');
3728
insert into t4 values (60,'2006-01-17');
3729
insert into t5 values (60,'2006-01-17');
3730
insert into t6 values (60,'2006-01-17');
3731
select * from t1 order by col1;
3735
select * from t2 order by col1;
3740
select * from t3 order by col1;
3745
select * from t4 order by colint;
3751
select * from t5 order by colint;
3757
select * from t6 order by colint;
3763
alter table t1 drop partition p0;
3764
alter table t2 drop partition p0;
3765
alter table t4 drop partition p0;
3766
alter table t5 drop partition p0;
3767
alter table t6 drop partition p0;
3768
select * from t1 order by col1;
3771
select * from t2 order by col1;
3775
select * from t3 order by col1;
3780
select * from t4 order by colint;
3783
select * from t5 order by colint;
3786
select * from t6 order by colint;
3789
-------------------------------------------------------------------------
3790
--- Delete rows and partitions of tables with day(col1)
3791
-------------------------------------------------------------------------
3792
delete from t11 where col1='2006-01-17';
3793
delete from t22 where col1='2006-01-17';
3794
delete from t33 where col1='2006-01-17';
3795
delete from t44 where col1='2006-01-17';
3796
delete from t55 where col1='2006-01-17';
3797
delete from t66 where col1='2006-01-17';
3798
select * from t11 order by col1;
3801
select * from t22 order by col1;
3805
select * from t33 order by col1;
3809
select * from t44 order by colint;
3814
select * from t55 order by colint;
3819
insert into t11 values ('2006-01-17');
3820
insert into t22 values ('2006-01-17');
3821
insert into t33 values ('2006-01-17');
3822
insert into t44 values (60,'2006-01-17');
3823
insert into t55 values (60,'2006-01-17');
3824
insert into t66 values (60,'2006-01-17');
3825
select * from t11 order by col1;
3829
select * from t22 order by col1;
3834
select * from t33 order by col1;
3839
select * from t44 order by colint;
3845
select * from t55 order by colint;
3851
select * from t66 order by colint;
3857
alter table t11 drop partition p0;
3858
alter table t22 drop partition p0;
3859
alter table t44 drop partition p0;
3860
alter table t55 drop partition p0;
3861
alter table t66 drop partition p0;
3862
select * from t11 order by col1;
3865
select * from t22 order by col1;
3869
select * from t33 order by col1;
3874
select * from t44 order by colint;
3877
select * from t55 order by colint;
3880
select * from t66 order by colint;
3883
-------------------------
3884
---- some alter table end
3885
-------------------------
3886
drop table if exists t1 ;
3887
drop table if exists t2 ;
3888
drop table if exists t3 ;
3889
drop table if exists t4 ;
3890
drop table if exists t5 ;
3891
drop table if exists t6 ;
3892
drop table if exists t11 ;
3893
drop table if exists t22 ;
3894
drop table if exists t33 ;
3895
drop table if exists t44 ;
3896
drop table if exists t55 ;
3897
drop table if exists t66 ;
3898
-------------------------------------------------------------------------
3899
--- dayofmonth(col1) in partition with coltype date
3900
-------------------------------------------------------------------------
3901
drop table if exists t1 ;
3902
drop table if exists t2 ;
3903
drop table if exists t3 ;
3904
drop table if exists t4 ;
3905
drop table if exists t5 ;
3906
drop table if exists t6 ;
3907
-------------------------------------------------------------------------
3908
--- Create tables with dayofmonth(col1)
3909
-------------------------------------------------------------------------
3910
create table t1 (col1 date) engine='INNODB'
3911
partition by range(dayofmonth(col1))
3912
(partition p0 values less than (15),
3913
partition p1 values less than maxvalue);
3914
create table t2 (col1 date) engine='INNODB'
3915
partition by list(dayofmonth(col1))
3916
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3917
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3918
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3919
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3920
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3921
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3923
create table t3 (col1 date) engine='INNODB'
3924
partition by hash(dayofmonth(col1));
3925
create table t4 (colint int, col1 date) engine='INNODB'
3926
partition by range(colint)
3927
subpartition by hash(dayofmonth(col1)) subpartitions 2
3928
(partition p0 values less than (15),
3929
partition p1 values less than maxvalue);
3930
create table t5 (colint int, col1 date) engine='INNODB'
3931
partition by list(colint)
3932
subpartition by hash(dayofmonth(col1)) subpartitions 2
3933
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3934
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3935
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3936
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3937
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3938
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3940
create table t6 (colint int, col1 date) engine='INNODB'
3941
partition by range(colint)
3942
(partition p0 values less than (dayofmonth('2006-12-24')),
3943
partition p1 values less than maxvalue);
3944
-------------------------------------------------------------------------
3945
--- Access tables with dayofmonth(col1)
3946
-------------------------------------------------------------------------
3947
insert into t1 values ('2006-02-03');
3948
insert into t1 values ('2006-01-17');
3949
insert into t2 values ('2006-02-03');
3950
insert into t2 values ('2006-01-17');
3951
insert into t2 values ('2006-01-25');
3952
insert into t3 values ('2006-02-03');
3953
insert into t3 values ('2006-01-17');
3954
insert into t3 values ('2006-01-25');
3955
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
3956
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
3957
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
3958
select dayofmonth(col1) from t1 order by col1;
3962
select * from t1 order by col1;
3966
select * from t2 order by col1;
3971
select * from t3 order by col1;
3976
select * from t4 order by colint;
3982
select * from t5 order by colint;
3988
select * from t6 order by colint;
3994
update t1 set col1='2006-02-05' where col1='2006-02-03';
3995
update t2 set col1='2006-02-05' where col1='2006-02-03';
3996
update t3 set col1='2006-02-05' where col1='2006-02-03';
3997
update t4 set col1='2006-02-05' where col1='2006-02-03';
3998
update t5 set col1='2006-02-05' where col1='2006-02-03';
3999
update t6 set col1='2006-02-05' where col1='2006-02-03';
4000
select * from t1 order by col1;
4004
select * from t2 order by col1;
4009
select * from t3 order by col1;
4014
select * from t4 order by colint;
4020
select * from t5 order by colint;
4026
select * from t6 order by colint;
4032
-------------------------------------------------------------------------
4033
--- Alter tables with dayofmonth(col1)
4034
-------------------------------------------------------------------------
4035
drop table if exists t11 ;
4036
drop table if exists t22 ;
4037
drop table if exists t33 ;
4038
drop table if exists t44 ;
4039
drop table if exists t55 ;
4040
drop table if exists t66 ;
4041
create table t11 engine='INNODB' as select * from t1;
4042
create table t22 engine='INNODB' as select * from t2;
4043
create table t33 engine='INNODB' as select * from t3;
4044
create table t44 engine='INNODB' as select * from t4;
4045
create table t55 engine='INNODB' as select * from t5;
4046
create table t66 engine='INNODB' as select * from t6;
4048
partition by range(dayofmonth(col1))
4049
(partition p0 values less than (15),
4050
partition p1 values less than maxvalue);
4052
partition by list(dayofmonth(col1))
4053
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4054
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4055
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4056
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4057
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4058
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4061
partition by hash(dayofmonth(col1));
4063
partition by range(colint)
4064
subpartition by hash(dayofmonth(col1)) subpartitions 2
4065
(partition p0 values less than (15),
4066
partition p1 values less than maxvalue);
4068
partition by list(colint)
4069
subpartition by hash(dayofmonth(col1)) subpartitions 2
4070
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4071
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4072
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4073
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4074
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4075
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4078
partition by range(colint)
4079
(partition p0 values less than (dayofmonth('2006-12-24')),
4080
partition p1 values less than maxvalue);
4081
select * from t11 order by col1;
4085
select * from t22 order by col1;
4090
select * from t33 order by col1;
4095
select * from t44 order by colint;
4101
select * from t55 order by colint;
4107
select * from t66 order by colint;
4113
---------------------------
4114
---- some alter table begin
4115
---------------------------
4117
reorganize partition p0,p1 into
4118
(partition s1 values less than maxvalue);
4119
select * from t11 order by col1;
4124
reorganize partition s1 into
4125
(partition p0 values less than (15),
4126
partition p1 values less than maxvalue);
4127
select * from t11 order by col1;
4132
partition by list(colint)
4133
subpartition by hash(dayofmonth(col1)) subpartitions 5
4134
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4135
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4136
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4137
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4138
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4139
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4141
show create table t55;
4143
t55 CREATE TABLE `t55` (
4144
`colint` int(11) DEFAULT NULL,
4145
`col1` date DEFAULT NULL
4146
) ENGINE=InnoDB DEFAULT CHARSET=latin1
4147
/*!50100 PARTITION BY LIST (colint)
4148
SUBPARTITION BY HASH (dayofmonth(col1))
4150
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
4151
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
4152
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
4153
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
4154
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
4155
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
4156
select * from t55 order by colint;
4163
reorganize partition p0,p1 into
4164
(partition s1 values less than maxvalue);
4165
select * from t66 order by colint;
4172
reorganize partition s1 into
4173
(partition p0 values less than (dayofmonth('2006-12-24')),
4174
partition p1 values less than maxvalue);
4175
select * from t66 order by colint;
4182
reorganize partition p0,p1 into
4183
(partition s1 values less than maxvalue);
4184
select * from t66 order by colint;
4191
reorganize partition s1 into
4192
(partition p0 values less than (dayofmonth('2006-12-24')),
4193
partition p1 values less than maxvalue);
4194
select * from t66 order by colint;
4200
-------------------------------------------------------------------------
4201
--- Delete rows and partitions of tables with dayofmonth(col1)
4202
-------------------------------------------------------------------------
4203
delete from t1 where col1='2006-01-17';
4204
delete from t2 where col1='2006-01-17';
4205
delete from t3 where col1='2006-01-17';
4206
delete from t4 where col1='2006-01-17';
4207
delete from t5 where col1='2006-01-17';
4208
delete from t6 where col1='2006-01-17';
4209
select * from t1 order by col1;
4212
select * from t2 order by col1;
4216
select * from t3 order by col1;
4220
select * from t4 order by colint;
4225
select * from t5 order by colint;
4230
insert into t1 values ('2006-01-17');
4231
insert into t2 values ('2006-01-17');
4232
insert into t3 values ('2006-01-17');
4233
insert into t4 values (60,'2006-01-17');
4234
insert into t5 values (60,'2006-01-17');
4235
insert into t6 values (60,'2006-01-17');
4236
select * from t1 order by col1;
4240
select * from t2 order by col1;
4245
select * from t3 order by col1;
4250
select * from t4 order by colint;
4256
select * from t5 order by colint;
4262
select * from t6 order by colint;
4268
alter table t1 drop partition p0;
4269
alter table t2 drop partition p0;
4270
alter table t4 drop partition p0;
4271
alter table t5 drop partition p0;
4272
alter table t6 drop partition p0;
4273
select * from t1 order by col1;
4276
select * from t2 order by col1;
4280
select * from t3 order by col1;
4285
select * from t4 order by colint;
4288
select * from t5 order by colint;
4291
select * from t6 order by colint;
4294
-------------------------------------------------------------------------
4295
--- Delete rows and partitions of tables with dayofmonth(col1)
4296
-------------------------------------------------------------------------
4297
delete from t11 where col1='2006-01-17';
4298
delete from t22 where col1='2006-01-17';
4299
delete from t33 where col1='2006-01-17';
4300
delete from t44 where col1='2006-01-17';
4301
delete from t55 where col1='2006-01-17';
4302
delete from t66 where col1='2006-01-17';
4303
select * from t11 order by col1;
4306
select * from t22 order by col1;
4310
select * from t33 order by col1;
4314
select * from t44 order by colint;
4319
select * from t55 order by colint;
4324
insert into t11 values ('2006-01-17');
4325
insert into t22 values ('2006-01-17');
4326
insert into t33 values ('2006-01-17');
4327
insert into t44 values (60,'2006-01-17');
4328
insert into t55 values (60,'2006-01-17');
4329
insert into t66 values (60,'2006-01-17');
4330
select * from t11 order by col1;
4334
select * from t22 order by col1;
4339
select * from t33 order by col1;
4344
select * from t44 order by colint;
4350
select * from t55 order by colint;
4356
select * from t66 order by colint;
4362
alter table t11 drop partition p0;
4363
alter table t22 drop partition p0;
4364
alter table t44 drop partition p0;
4365
alter table t55 drop partition p0;
4366
alter table t66 drop partition p0;
4367
select * from t11 order by col1;
4370
select * from t22 order by col1;
4374
select * from t33 order by col1;
4379
select * from t44 order by colint;
4382
select * from t55 order by colint;
4385
select * from t66 order by colint;
4388
-------------------------
4389
---- some alter table end
4390
-------------------------
4391
drop table if exists t1 ;
4392
drop table if exists t2 ;
4393
drop table if exists t3 ;
4394
drop table if exists t4 ;
4395
drop table if exists t5 ;
4396
drop table if exists t6 ;
4397
drop table if exists t11 ;
4398
drop table if exists t22 ;
4399
drop table if exists t33 ;
4400
drop table if exists t44 ;
4401
drop table if exists t55 ;
4402
drop table if exists t66 ;
4403
-------------------------------------------------------------------------
4404
--- dayofweek(col1) in partition with coltype date
4405
-------------------------------------------------------------------------
4406
drop table if exists t1 ;
4407
drop table if exists t2 ;
4408
drop table if exists t3 ;
4409
drop table if exists t4 ;
4410
drop table if exists t5 ;
4411
drop table if exists t6 ;
4412
-------------------------------------------------------------------------
4413
--- Create tables with dayofweek(col1)
4414
-------------------------------------------------------------------------
4415
create table t1 (col1 date) engine='INNODB'
4416
partition by range(dayofweek(col1))
4417
(partition p0 values less than (15),
4418
partition p1 values less than maxvalue);
4419
create table t2 (col1 date) engine='INNODB'
4420
partition by list(dayofweek(col1))
4421
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4422
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4423
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4424
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4425
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4426
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4428
create table t3 (col1 date) engine='INNODB'
4429
partition by hash(dayofweek(col1));
4430
create table t4 (colint int, col1 date) engine='INNODB'
4431
partition by range(colint)
4432
subpartition by hash(dayofweek(col1)) subpartitions 2
4433
(partition p0 values less than (15),
4434
partition p1 values less than maxvalue);
4435
create table t5 (colint int, col1 date) engine='INNODB'
4436
partition by list(colint)
4437
subpartition by hash(dayofweek(col1)) subpartitions 2
4438
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4439
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4440
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4441
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4442
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4443
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4445
create table t6 (colint int, col1 date) engine='INNODB'
4446
partition by range(colint)
4447
(partition p0 values less than (dayofweek('2006-12-24')),
4448
partition p1 values less than maxvalue);
4449
-------------------------------------------------------------------------
4450
--- Access tables with dayofweek(col1)
4451
-------------------------------------------------------------------------
4452
insert into t1 values ('2006-01-03');
4453
insert into t1 values ('2006-02-17');
4454
insert into t2 values ('2006-01-03');
4455
insert into t2 values ('2006-02-17');
4456
insert into t2 values ('2006-01-25');
4457
insert into t3 values ('2006-01-03');
4458
insert into t3 values ('2006-02-17');
4459
insert into t3 values ('2006-01-25');
4460
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
4461
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
4462
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
4463
select dayofweek(col1) from t1 order by col1;
4467
select * from t1 order by col1;
4471
select * from t2 order by col1;
4476
select * from t3 order by col1;
4481
select * from t4 order by colint;
4487
select * from t5 order by colint;
4493
select * from t6 order by colint;
4499
update t1 set col1='2006-02-05' where col1='2006-01-03';
4500
update t2 set col1='2006-02-05' where col1='2006-01-03';
4501
update t3 set col1='2006-02-05' where col1='2006-01-03';
4502
update t4 set col1='2006-02-05' where col1='2006-01-03';
4503
update t5 set col1='2006-02-05' where col1='2006-01-03';
4504
update t6 set col1='2006-02-05' where col1='2006-01-03';
4505
select * from t1 order by col1;
4509
select * from t2 order by col1;
4514
select * from t3 order by col1;
4519
select * from t4 order by colint;
4525
select * from t5 order by colint;
4531
select * from t6 order by colint;
4537
-------------------------------------------------------------------------
4538
--- Alter tables with dayofweek(col1)
4539
-------------------------------------------------------------------------
4540
drop table if exists t11 ;
4541
drop table if exists t22 ;
4542
drop table if exists t33 ;
4543
drop table if exists t44 ;
4544
drop table if exists t55 ;
4545
drop table if exists t66 ;
4546
create table t11 engine='INNODB' as select * from t1;
4547
create table t22 engine='INNODB' as select * from t2;
4548
create table t33 engine='INNODB' as select * from t3;
4549
create table t44 engine='INNODB' as select * from t4;
4550
create table t55 engine='INNODB' as select * from t5;
4551
create table t66 engine='INNODB' as select * from t6;
4553
partition by range(dayofweek(col1))
4554
(partition p0 values less than (15),
4555
partition p1 values less than maxvalue);
4557
partition by list(dayofweek(col1))
4558
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4559
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4560
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4561
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4562
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4563
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4566
partition by hash(dayofweek(col1));
4568
partition by range(colint)
4569
subpartition by hash(dayofweek(col1)) subpartitions 2
4570
(partition p0 values less than (15),
4571
partition p1 values less than maxvalue);
4573
partition by list(colint)
4574
subpartition by hash(dayofweek(col1)) subpartitions 2
4575
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4576
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4577
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4578
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4579
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4580
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4583
partition by range(colint)
4584
(partition p0 values less than (dayofweek('2006-12-24')),
4585
partition p1 values less than maxvalue);
4586
select * from t11 order by col1;
4590
select * from t22 order by col1;
4595
select * from t33 order by col1;
4600
select * from t44 order by colint;
4606
select * from t55 order by colint;
4612
select * from t66 order by colint;
4618
---------------------------
4619
---- some alter table begin
4620
---------------------------
4622
reorganize partition p0,p1 into
4623
(partition s1 values less than maxvalue);
4624
select * from t11 order by col1;
4629
reorganize partition s1 into
4630
(partition p0 values less than (15),
4631
partition p1 values less than maxvalue);
4632
select * from t11 order by col1;
4637
partition by list(colint)
4638
subpartition by hash(dayofweek(col1)) subpartitions 5
4639
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4640
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4641
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4642
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4643
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4644
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4646
show create table t55;
4648
t55 CREATE TABLE `t55` (
4649
`colint` int(11) DEFAULT NULL,
4650
`col1` date DEFAULT NULL
4651
) ENGINE=InnoDB DEFAULT CHARSET=latin1
4652
/*!50100 PARTITION BY LIST (colint)
4653
SUBPARTITION BY HASH (dayofweek(col1))
4655
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
4656
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
4657
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
4658
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
4659
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
4660
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
4661
select * from t55 order by colint;
4668
reorganize partition p0,p1 into
4669
(partition s1 values less than maxvalue);
4670
select * from t66 order by colint;
4677
reorganize partition s1 into
4678
(partition p0 values less than (dayofweek('2006-12-24')),
4679
partition p1 values less than maxvalue);
4680
select * from t66 order by colint;
4687
reorganize partition p0,p1 into
4688
(partition s1 values less than maxvalue);
4689
select * from t66 order by colint;
4696
reorganize partition s1 into
4697
(partition p0 values less than (dayofweek('2006-12-24')),
4698
partition p1 values less than maxvalue);
4699
select * from t66 order by colint;
4705
-------------------------------------------------------------------------
4706
--- Delete rows and partitions of tables with dayofweek(col1)
4707
-------------------------------------------------------------------------
4708
delete from t1 where col1='2006-02-17';
4709
delete from t2 where col1='2006-02-17';
4710
delete from t3 where col1='2006-02-17';
4711
delete from t4 where col1='2006-02-17';
4712
delete from t5 where col1='2006-02-17';
4713
delete from t6 where col1='2006-02-17';
4714
select * from t1 order by col1;
4717
select * from t2 order by col1;
4721
select * from t3 order by col1;
4725
select * from t4 order by colint;
4731
select * from t5 order by colint;
4737
insert into t1 values ('2006-02-17');
4738
insert into t2 values ('2006-02-17');
4739
insert into t3 values ('2006-02-17');
4740
insert into t4 values (60,'2006-02-17');
4741
insert into t5 values (60,'2006-02-17');
4742
insert into t6 values (60,'2006-02-17');
4743
select * from t1 order by col1;
4747
select * from t2 order by col1;
4752
select * from t3 order by col1;
4757
select * from t4 order by colint;
4764
select * from t5 order by colint;
4771
select * from t6 order by colint;
4778
alter table t1 drop partition p0;
4779
alter table t2 drop partition p0;
4780
alter table t4 drop partition p0;
4781
alter table t5 drop partition p0;
4782
alter table t6 drop partition p0;
4783
select * from t1 order by col1;
4785
select * from t2 order by col1;
4787
select * from t3 order by col1;
4792
select * from t4 order by colint;
4795
select * from t5 order by colint;
4798
select * from t6 order by colint;
4805
-------------------------------------------------------------------------
4806
--- Delete rows and partitions of tables with dayofweek(col1)
4807
-------------------------------------------------------------------------
4808
delete from t11 where col1='2006-02-17';
4809
delete from t22 where col1='2006-02-17';
4810
delete from t33 where col1='2006-02-17';
4811
delete from t44 where col1='2006-02-17';
4812
delete from t55 where col1='2006-02-17';
4813
delete from t66 where col1='2006-02-17';
4814
select * from t11 order by col1;
4817
select * from t22 order by col1;
4821
select * from t33 order by col1;
4825
select * from t44 order by colint;
4831
select * from t55 order by colint;
4837
insert into t11 values ('2006-02-17');
4838
insert into t22 values ('2006-02-17');
4839
insert into t33 values ('2006-02-17');
4840
insert into t44 values (60,'2006-02-17');
4841
insert into t55 values (60,'2006-02-17');
4842
insert into t66 values (60,'2006-02-17');
4843
select * from t11 order by col1;
4847
select * from t22 order by col1;
4852
select * from t33 order by col1;
4857
select * from t44 order by colint;
4864
select * from t55 order by colint;
4871
select * from t66 order by colint;
4878
alter table t11 drop partition p0;
4879
alter table t22 drop partition p0;
4880
alter table t44 drop partition p0;
4881
alter table t55 drop partition p0;
4882
alter table t66 drop partition p0;
4883
select * from t11 order by col1;
4885
select * from t22 order by col1;
4887
select * from t33 order by col1;
4892
select * from t44 order by colint;
4895
select * from t55 order by colint;
4898
select * from t66 order by colint;
4905
-------------------------
4906
---- some alter table end
4907
-------------------------
4908
drop table if exists t1 ;
4909
drop table if exists t2 ;
4910
drop table if exists t3 ;
4911
drop table if exists t4 ;
4912
drop table if exists t5 ;
4913
drop table if exists t6 ;
4914
drop table if exists t11 ;
4915
drop table if exists t22 ;
4916
drop table if exists t33 ;
4917
drop table if exists t44 ;
4918
drop table if exists t55 ;
4919
drop table if exists t66 ;
4920
-------------------------------------------------------------------------
4921
--- dayofyear(col1) in partition with coltype date
4922
-------------------------------------------------------------------------
4923
drop table if exists t1 ;
4924
drop table if exists t2 ;
4925
drop table if exists t3 ;
4926
drop table if exists t4 ;
4927
drop table if exists t5 ;
4928
drop table if exists t6 ;
4929
-------------------------------------------------------------------------
4930
--- Create tables with dayofyear(col1)
4931
-------------------------------------------------------------------------
4932
create table t1 (col1 date) engine='INNODB'
4933
partition by range(dayofyear(col1))
4934
(partition p0 values less than (15),
4935
partition p1 values less than maxvalue);
4936
create table t2 (col1 date) engine='INNODB'
4937
partition by list(dayofyear(col1))
4938
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4939
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4940
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4941
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4942
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4943
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4945
create table t3 (col1 date) engine='INNODB'
4946
partition by hash(dayofyear(col1));
4947
create table t4 (colint int, col1 date) engine='INNODB'
4948
partition by range(colint)
4949
subpartition by hash(dayofyear(col1)) subpartitions 2
4950
(partition p0 values less than (15),
4951
partition p1 values less than maxvalue);
4952
create table t5 (colint int, col1 date) engine='INNODB'
4953
partition by list(colint)
4954
subpartition by hash(dayofyear(col1)) subpartitions 2
4955
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
4956
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4957
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4958
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4959
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4960
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4962
create table t6 (colint int, col1 date) engine='INNODB'
4963
partition by range(colint)
4964
(partition p0 values less than (dayofyear('2006-12-25')),
4965
partition p1 values less than maxvalue);
4966
-------------------------------------------------------------------------
4967
--- Access tables with dayofyear(col1)
4968
-------------------------------------------------------------------------
4969
insert into t1 values ('2006-01-03');
4970
insert into t1 values ('2006-01-17');
4971
insert into t2 values ('2006-01-03');
4972
insert into t2 values ('2006-01-17');
4973
insert into t2 values ('2006-02-25');
4974
insert into t3 values ('2006-01-03');
4975
insert into t3 values ('2006-01-17');
4976
insert into t3 values ('2006-02-25');
4977
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
4978
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
4979
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
4980
select dayofyear(col1) from t1 order by col1;
4984
select * from t1 order by col1;
4988
select * from t2 order by col1;
4993
select * from t3 order by col1;
4998
select * from t4 order by colint;
5004
select * from t5 order by colint;
5010
select * from t6 order by colint;
5016
update t1 set col1='2006-02-05' where col1='2006-01-03';
5017
update t2 set col1='2006-02-05' where col1='2006-01-03';
5018
update t3 set col1='2006-02-05' where col1='2006-01-03';
5019
update t4 set col1='2006-02-05' where col1='2006-01-03';
5020
update t5 set col1='2006-02-05' where col1='2006-01-03';
5021
update t6 set col1='2006-02-05' where col1='2006-01-03';
5022
select * from t1 order by col1;
5026
select * from t2 order by col1;
5031
select * from t3 order by col1;
5036
select * from t4 order by colint;
5042
select * from t5 order by colint;
5048
select * from t6 order by colint;
5054
-------------------------------------------------------------------------
5055
--- Alter tables with dayofyear(col1)
5056
-------------------------------------------------------------------------
5057
drop table if exists t11 ;
5058
drop table if exists t22 ;
5059
drop table if exists t33 ;
5060
drop table if exists t44 ;
5061
drop table if exists t55 ;
5062
drop table if exists t66 ;
5063
create table t11 engine='INNODB' as select * from t1;
5064
create table t22 engine='INNODB' as select * from t2;
5065
create table t33 engine='INNODB' as select * from t3;
5066
create table t44 engine='INNODB' as select * from t4;
5067
create table t55 engine='INNODB' as select * from t5;
5068
create table t66 engine='INNODB' as select * from t6;
5070
partition by range(dayofyear(col1))
5071
(partition p0 values less than (15),
5072
partition p1 values less than maxvalue);
5074
partition by list(dayofyear(col1))
5075
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5076
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5077
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5078
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5079
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5080
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5083
partition by hash(dayofyear(col1));
5085
partition by range(colint)
5086
subpartition by hash(dayofyear(col1)) subpartitions 2
5087
(partition p0 values less than (15),
5088
partition p1 values less than maxvalue);
5090
partition by list(colint)
5091
subpartition by hash(dayofyear(col1)) subpartitions 2
5092
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5093
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5094
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5095
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5096
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5097
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5100
partition by range(colint)
5101
(partition p0 values less than (dayofyear('2006-12-25')),
5102
partition p1 values less than maxvalue);
5103
select * from t11 order by col1;
5107
select * from t22 order by col1;
5112
select * from t33 order by col1;
5117
select * from t44 order by colint;
5123
select * from t55 order by colint;
5129
select * from t66 order by colint;
5135
---------------------------
5136
---- some alter table begin
5137
---------------------------
5139
reorganize partition p0,p1 into
5140
(partition s1 values less than maxvalue);
5141
select * from t11 order by col1;
5146
reorganize partition s1 into
5147
(partition p0 values less than (15),
5148
partition p1 values less than maxvalue);
5149
select * from t11 order by col1;
5154
partition by list(colint)
5155
subpartition by hash(dayofyear(col1)) subpartitions 5
5156
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5157
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5158
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5159
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5160
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5161
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5163
show create table t55;
5165
t55 CREATE TABLE `t55` (
5166
`colint` int(11) DEFAULT NULL,
5167
`col1` date DEFAULT NULL
5168
) ENGINE=InnoDB DEFAULT CHARSET=latin1
5169
/*!50100 PARTITION BY LIST (colint)
5170
SUBPARTITION BY HASH (dayofyear(col1))
5172
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
5173
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
5174
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
5175
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
5176
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
5177
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
5178
select * from t55 order by colint;
5185
reorganize partition p0,p1 into
5186
(partition s1 values less than maxvalue);
5187
select * from t66 order by colint;
5194
reorganize partition s1 into
5195
(partition p0 values less than (dayofyear('2006-12-25')),
5196
partition p1 values less than maxvalue);
5197
select * from t66 order by colint;
5204
reorganize partition p0,p1 into
5205
(partition s1 values less than maxvalue);
5206
select * from t66 order by colint;
5213
reorganize partition s1 into
5214
(partition p0 values less than (dayofyear('2006-12-25')),
5215
partition p1 values less than maxvalue);
5216
select * from t66 order by colint;
5222
-------------------------------------------------------------------------
5223
--- Delete rows and partitions of tables with dayofyear(col1)
5224
-------------------------------------------------------------------------
5225
delete from t1 where col1='2006-01-17';
5226
delete from t2 where col1='2006-01-17';
5227
delete from t3 where col1='2006-01-17';
5228
delete from t4 where col1='2006-01-17';
5229
delete from t5 where col1='2006-01-17';
5230
delete from t6 where col1='2006-01-17';
5231
select * from t1 order by col1;
5234
select * from t2 order by col1;
5238
select * from t3 order by col1;
5242
select * from t4 order by colint;
5247
select * from t5 order by colint;
5252
insert into t1 values ('2006-01-17');
5253
insert into t2 values ('2006-01-17');
5254
insert into t3 values ('2006-01-17');
5255
insert into t4 values (60,'2006-01-17');
5256
insert into t5 values (60,'2006-01-17');
5257
insert into t6 values (60,'2006-01-17');
5258
select * from t1 order by col1;
5262
select * from t2 order by col1;
5267
select * from t3 order by col1;
5272
select * from t4 order by colint;
5278
select * from t5 order by colint;
5284
select * from t6 order by colint;
5290
alter table t1 drop partition p0;
5291
alter table t2 drop partition p0;
5292
alter table t4 drop partition p0;
5293
alter table t5 drop partition p0;
5294
alter table t6 drop partition p0;
5295
select * from t1 order by col1;
5299
select * from t2 order by col1;
5304
select * from t3 order by col1;
5309
select * from t4 order by colint;
5312
select * from t5 order by colint;
5315
select * from t6 order by colint;
5317
-------------------------------------------------------------------------
5318
--- Delete rows and partitions of tables with dayofyear(col1)
5319
-------------------------------------------------------------------------
5320
delete from t11 where col1='2006-01-17';
5321
delete from t22 where col1='2006-01-17';
5322
delete from t33 where col1='2006-01-17';
5323
delete from t44 where col1='2006-01-17';
5324
delete from t55 where col1='2006-01-17';
5325
delete from t66 where col1='2006-01-17';
5326
select * from t11 order by col1;
5329
select * from t22 order by col1;
5333
select * from t33 order by col1;
5337
select * from t44 order by colint;
5342
select * from t55 order by colint;
5347
insert into t11 values ('2006-01-17');
5348
insert into t22 values ('2006-01-17');
5349
insert into t33 values ('2006-01-17');
5350
insert into t44 values (60,'2006-01-17');
5351
insert into t55 values (60,'2006-01-17');
5352
insert into t66 values (60,'2006-01-17');
5353
select * from t11 order by col1;
5357
select * from t22 order by col1;
5362
select * from t33 order by col1;
5367
select * from t44 order by colint;
5373
select * from t55 order by colint;
5379
select * from t66 order by colint;
5385
alter table t11 drop partition p0;
5386
alter table t22 drop partition p0;
5387
alter table t44 drop partition p0;
5388
alter table t55 drop partition p0;
5389
alter table t66 drop partition p0;
5390
select * from t11 order by col1;
5394
select * from t22 order by col1;
5399
select * from t33 order by col1;
5404
select * from t44 order by colint;
5407
select * from t55 order by colint;
5410
select * from t66 order by colint;
5412
-------------------------
5413
---- some alter table end
5414
-------------------------
5415
drop table if exists t1 ;
5416
drop table if exists t2 ;
5417
drop table if exists t3 ;
5418
drop table if exists t4 ;
5419
drop table if exists t5 ;
5420
drop table if exists t6 ;
5421
drop table if exists t11 ;
5422
drop table if exists t22 ;
5423
drop table if exists t33 ;
5424
drop table if exists t44 ;
5425
drop table if exists t55 ;
5426
drop table if exists t66 ;
5427
-------------------------------------------------------------------------
5428
--- dayofyear(col1) in partition with coltype char(30)
5429
-------------------------------------------------------------------------
5430
drop table if exists t1 ;
5431
drop table if exists t2 ;
5432
drop table if exists t3 ;
5433
drop table if exists t4 ;
5434
drop table if exists t5 ;
5435
drop table if exists t6 ;
5436
-------------------------------------------------------------------------
5437
--- Create tables with dayofyear(col1)
5438
-------------------------------------------------------------------------
5439
create table t1 (col1 char(30)) engine='INNODB'
5440
partition by range(dayofyear(col1))
5441
(partition p0 values less than (15),
5442
partition p1 values less than maxvalue);
5443
create table t2 (col1 char(30)) engine='INNODB'
5444
partition by list(dayofyear(col1))
5445
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5446
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5447
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5448
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5449
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5450
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5452
create table t3 (col1 char(30)) engine='INNODB'
5453
partition by hash(dayofyear(col1));
5454
create table t4 (colint int, col1 char(30)) engine='INNODB'
5455
partition by range(colint)
5456
subpartition by hash(dayofyear(col1)) subpartitions 2
5457
(partition p0 values less than (15),
5458
partition p1 values less than maxvalue);
5459
create table t5 (colint int, col1 char(30)) engine='INNODB'
5460
partition by list(colint)
5461
subpartition by hash(dayofyear(col1)) subpartitions 2
5462
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5463
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5464
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5465
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5466
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5467
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5469
create table t6 (colint int, col1 char(30)) engine='INNODB'
5470
partition by range(colint)
5471
(partition p0 values less than (dayofyear('2006-12-25')),
5472
partition p1 values less than maxvalue);
5473
-------------------------------------------------------------------------
5474
--- Access tables with dayofyear(col1)
5475
-------------------------------------------------------------------------
5476
insert into t1 values ('2006-01-03');
5477
insert into t1 values ('2006-01-17');
5478
insert into t2 values ('2006-01-03');
5479
insert into t2 values ('2006-01-17');
5480
insert into t2 values ('2006-02-25');
5481
insert into t3 values ('2006-01-03');
5482
insert into t3 values ('2006-01-17');
5483
insert into t3 values ('2006-02-25');
5484
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
5485
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
5486
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
5487
select dayofyear(col1) from t1 order by col1;
5491
select * from t1 order by col1;
5495
select * from t2 order by col1;
5500
select * from t3 order by col1;
5505
select * from t4 order by colint;
5511
select * from t5 order by colint;
5517
select * from t6 order by colint;
5523
update t1 set col1='2006-02-05' where col1='2006-01-03';
5524
update t2 set col1='2006-02-05' where col1='2006-01-03';
5525
update t3 set col1='2006-02-05' where col1='2006-01-03';
5526
update t4 set col1='2006-02-05' where col1='2006-01-03';
5527
update t5 set col1='2006-02-05' where col1='2006-01-03';
5528
update t6 set col1='2006-02-05' where col1='2006-01-03';
5529
select * from t1 order by col1;
5533
select * from t2 order by col1;
5538
select * from t3 order by col1;
5543
select * from t4 order by colint;
5549
select * from t5 order by colint;
5555
select * from t6 order by colint;
5561
-------------------------------------------------------------------------
5562
--- Alter tables with dayofyear(col1)
5563
-------------------------------------------------------------------------
5564
drop table if exists t11 ;
5565
drop table if exists t22 ;
5566
drop table if exists t33 ;
5567
drop table if exists t44 ;
5568
drop table if exists t55 ;
5569
drop table if exists t66 ;
5570
create table t11 engine='INNODB' as select * from t1;
5571
create table t22 engine='INNODB' as select * from t2;
5572
create table t33 engine='INNODB' as select * from t3;
5573
create table t44 engine='INNODB' as select * from t4;
5574
create table t55 engine='INNODB' as select * from t5;
5575
create table t66 engine='INNODB' as select * from t6;
5577
partition by range(dayofyear(col1))
5578
(partition p0 values less than (15),
5579
partition p1 values less than maxvalue);
5581
partition by list(dayofyear(col1))
5582
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5583
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5584
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5585
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5586
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5587
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5590
partition by hash(dayofyear(col1));
5592
partition by range(colint)
5593
subpartition by hash(dayofyear(col1)) subpartitions 2
5594
(partition p0 values less than (15),
5595
partition p1 values less than maxvalue);
5597
partition by list(colint)
5598
subpartition by hash(dayofyear(col1)) subpartitions 2
5599
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5600
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5601
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5602
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5603
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5604
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5607
partition by range(colint)
5608
(partition p0 values less than (dayofyear('2006-12-25')),
5609
partition p1 values less than maxvalue);
5610
select * from t11 order by col1;
5614
select * from t22 order by col1;
5619
select * from t33 order by col1;
5624
select * from t44 order by colint;
5630
select * from t55 order by colint;
5636
select * from t66 order by colint;
5642
---------------------------
5643
---- some alter table begin
5644
---------------------------
5646
reorganize partition p0,p1 into
5647
(partition s1 values less than maxvalue);
5648
select * from t11 order by col1;
5653
reorganize partition s1 into
5654
(partition p0 values less than (15),
5655
partition p1 values less than maxvalue);
5656
select * from t11 order by col1;
5661
partition by list(colint)
5662
subpartition by hash(dayofyear(col1)) subpartitions 5
5663
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5664
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5665
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5666
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5667
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5668
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5670
show create table t55;
5672
t55 CREATE TABLE `t55` (
5673
`colint` int(11) DEFAULT NULL,
5674
`col1` char(30) DEFAULT NULL
5675
) ENGINE=InnoDB DEFAULT CHARSET=latin1
5676
/*!50100 PARTITION BY LIST (colint)
5677
SUBPARTITION BY HASH (dayofyear(col1))
5679
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
5680
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
5681
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
5682
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
5683
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
5684
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
5685
select * from t55 order by colint;
5692
reorganize partition p0,p1 into
5693
(partition s1 values less than maxvalue);
5694
select * from t66 order by colint;
5701
reorganize partition s1 into
5702
(partition p0 values less than (dayofyear('2006-12-25')),
5703
partition p1 values less than maxvalue);
5704
select * from t66 order by colint;
5711
reorganize partition p0,p1 into
5712
(partition s1 values less than maxvalue);
5713
select * from t66 order by colint;
5720
reorganize partition s1 into
5721
(partition p0 values less than (dayofyear('2006-12-25')),
5722
partition p1 values less than maxvalue);
5723
select * from t66 order by colint;
5729
-------------------------------------------------------------------------
5730
--- Delete rows and partitions of tables with dayofyear(col1)
5731
-------------------------------------------------------------------------
5732
delete from t1 where col1='2006-01-17';
5733
delete from t2 where col1='2006-01-17';
5734
delete from t3 where col1='2006-01-17';
5735
delete from t4 where col1='2006-01-17';
5736
delete from t5 where col1='2006-01-17';
5737
delete from t6 where col1='2006-01-17';
5738
select * from t1 order by col1;
5741
select * from t2 order by col1;
5745
select * from t3 order by col1;
5749
select * from t4 order by colint;
5754
select * from t5 order by colint;
5759
insert into t1 values ('2006-01-17');
5760
insert into t2 values ('2006-01-17');
5761
insert into t3 values ('2006-01-17');
5762
insert into t4 values (60,'2006-01-17');
5763
insert into t5 values (60,'2006-01-17');
5764
insert into t6 values (60,'2006-01-17');
5765
select * from t1 order by col1;
5769
select * from t2 order by col1;
5774
select * from t3 order by col1;
5779
select * from t4 order by colint;
5785
select * from t5 order by colint;
5791
select * from t6 order by colint;
5797
alter table t1 drop partition p0;
5798
alter table t2 drop partition p0;
5799
alter table t4 drop partition p0;
5800
alter table t5 drop partition p0;
5801
alter table t6 drop partition p0;
5802
select * from t1 order by col1;
5806
select * from t2 order by col1;
5811
select * from t3 order by col1;
5816
select * from t4 order by colint;
5819
select * from t5 order by colint;
5822
select * from t6 order by colint;
5824
-------------------------------------------------------------------------
5825
--- Delete rows and partitions of tables with dayofyear(col1)
5826
-------------------------------------------------------------------------
5827
delete from t11 where col1='2006-01-17';
5828
delete from t22 where col1='2006-01-17';
5829
delete from t33 where col1='2006-01-17';
5830
delete from t44 where col1='2006-01-17';
5831
delete from t55 where col1='2006-01-17';
5832
delete from t66 where col1='2006-01-17';
5833
select * from t11 order by col1;
5836
select * from t22 order by col1;
5840
select * from t33 order by col1;
5844
select * from t44 order by colint;
5849
select * from t55 order by colint;
5854
insert into t11 values ('2006-01-17');
5855
insert into t22 values ('2006-01-17');
5856
insert into t33 values ('2006-01-17');
5857
insert into t44 values (60,'2006-01-17');
5858
insert into t55 values (60,'2006-01-17');
5859
insert into t66 values (60,'2006-01-17');
5860
select * from t11 order by col1;
5864
select * from t22 order by col1;
5869
select * from t33 order by col1;
5874
select * from t44 order by colint;
5880
select * from t55 order by colint;
5886
select * from t66 order by colint;
5892
alter table t11 drop partition p0;
5893
alter table t22 drop partition p0;
5894
alter table t44 drop partition p0;
5895
alter table t55 drop partition p0;
5896
alter table t66 drop partition p0;
5897
select * from t11 order by col1;
5901
select * from t22 order by col1;
5906
select * from t33 order by col1;
5911
select * from t44 order by colint;
5914
select * from t55 order by colint;
5917
select * from t66 order by colint;
5919
-------------------------
5920
---- some alter table end
5921
-------------------------
5922
drop table if exists t1 ;
5923
drop table if exists t2 ;
5924
drop table if exists t3 ;
5925
drop table if exists t4 ;
5926
drop table if exists t5 ;
5927
drop table if exists t6 ;
5928
drop table if exists t11 ;
5929
drop table if exists t22 ;
5930
drop table if exists t33 ;
5931
drop table if exists t44 ;
5932
drop table if exists t55 ;
5933
drop table if exists t66 ;
5934
-------------------------------------------------------------------------
5935
--- extract(month from col1) in partition with coltype date
5936
-------------------------------------------------------------------------
5937
drop table if exists t1 ;
5938
drop table if exists t2 ;
5939
drop table if exists t3 ;
5940
drop table if exists t4 ;
5941
drop table if exists t5 ;
5942
drop table if exists t6 ;
5943
-------------------------------------------------------------------------
5944
--- Create tables with extract(month from col1)
5945
-------------------------------------------------------------------------
5946
create table t1 (col1 date) engine='INNODB'
5947
partition by range(extract(month from col1))
5948
(partition p0 values less than (15),
5949
partition p1 values less than maxvalue);
5950
create table t2 (col1 date) engine='INNODB'
5951
partition by list(extract(month from col1))
5952
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5953
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5954
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5955
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5956
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5957
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5959
create table t3 (col1 date) engine='INNODB'
5960
partition by hash(extract(month from col1));
5961
create table t4 (colint int, col1 date) engine='INNODB'
5962
partition by range(colint)
5963
subpartition by hash(extract(month from col1)) subpartitions 2
5964
(partition p0 values less than (15),
5965
partition p1 values less than maxvalue);
5966
create table t5 (colint int, col1 date) engine='INNODB'
5967
partition by list(colint)
5968
subpartition by hash(extract(month from col1)) subpartitions 2
5969
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5970
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5971
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5972
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5973
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5974
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5976
create table t6 (colint int, col1 date) engine='INNODB'
5977
partition by range(colint)
5978
(partition p0 values less than (extract(year from '1998-11-23')),
5979
partition p1 values less than maxvalue);
5980
-------------------------------------------------------------------------
5981
--- Access tables with extract(month from col1)
5982
-------------------------------------------------------------------------
5983
insert into t1 values ('2006-01-03');
5984
insert into t1 values ('2006-02-17');
5985
insert into t2 values ('2006-01-03');
5986
insert into t2 values ('2006-02-17');
5987
insert into t2 values ('2006-01-25');
5988
insert into t3 values ('2006-01-03');
5989
insert into t3 values ('2006-02-17');
5990
insert into t3 values ('2006-01-25');
5991
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
5992
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
5993
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
5994
select extract(month from col1) from t1 order by col1;
5995
extract(month from col1)
5998
select * from t1 order by col1;
6002
select * from t2 order by col1;
6007
select * from t3 order by col1;
6012
select * from t4 order by colint;
6018
select * from t5 order by colint;
6024
select * from t6 order by colint;
6030
update t1 set col1='2006-02-05' where col1='2006-01-03';
6031
update t2 set col1='2006-02-05' where col1='2006-01-03';
6032
update t3 set col1='2006-02-05' where col1='2006-01-03';
6033
update t4 set col1='2006-02-05' where col1='2006-01-03';
6034
update t5 set col1='2006-02-05' where col1='2006-01-03';
6035
update t6 set col1='2006-02-05' where col1='2006-01-03';
6036
select * from t1 order by col1;
6040
select * from t2 order by col1;
6045
select * from t3 order by col1;
6050
select * from t4 order by colint;
6056
select * from t5 order by colint;
6062
select * from t6 order by colint;
6068
-------------------------------------------------------------------------
6069
--- Alter tables with extract(month from col1)
6070
-------------------------------------------------------------------------
6071
drop table if exists t11 ;
6072
drop table if exists t22 ;
6073
drop table if exists t33 ;
6074
drop table if exists t44 ;
6075
drop table if exists t55 ;
6076
drop table if exists t66 ;
6077
create table t11 engine='INNODB' as select * from t1;
6078
create table t22 engine='INNODB' as select * from t2;
6079
create table t33 engine='INNODB' as select * from t3;
6080
create table t44 engine='INNODB' as select * from t4;
6081
create table t55 engine='INNODB' as select * from t5;
6082
create table t66 engine='INNODB' as select * from t6;
6084
partition by range(extract(month from col1))
6085
(partition p0 values less than (15),
6086
partition p1 values less than maxvalue);
6088
partition by list(extract(month from col1))
6089
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6090
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6091
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6092
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6093
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6094
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6097
partition by hash(extract(month from col1));
6099
partition by range(colint)
6100
subpartition by hash(extract(month from col1)) subpartitions 2
6101
(partition p0 values less than (15),
6102
partition p1 values less than maxvalue);
6104
partition by list(colint)
6105
subpartition by hash(extract(month from col1)) subpartitions 2
6106
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6107
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6108
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6109
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6110
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6111
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6114
partition by range(colint)
6115
(partition p0 values less than (extract(year from '1998-11-23')),
6116
partition p1 values less than maxvalue);
6117
select * from t11 order by col1;
6121
select * from t22 order by col1;
6126
select * from t33 order by col1;
6131
select * from t44 order by colint;
6137
select * from t55 order by colint;
6143
select * from t66 order by colint;
6149
---------------------------
6150
---- some alter table begin
6151
---------------------------
6153
reorganize partition p0,p1 into
6154
(partition s1 values less than maxvalue);
6155
select * from t11 order by col1;
6160
reorganize partition s1 into
6161
(partition p0 values less than (15),
6162
partition p1 values less than maxvalue);
6163
select * from t11 order by col1;
6168
partition by list(colint)
6169
subpartition by hash(extract(month from col1)) subpartitions 5
6170
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6171
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6172
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6173
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6174
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6175
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6177
show create table t55;
6179
t55 CREATE TABLE `t55` (
6180
`colint` int(11) DEFAULT NULL,
6181
`col1` date DEFAULT NULL
6182
) ENGINE=InnoDB DEFAULT CHARSET=latin1
6183
/*!50100 PARTITION BY LIST (colint)
6184
SUBPARTITION BY HASH (extract(month from col1))
6186
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
6187
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
6188
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
6189
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
6190
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
6191
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
6192
select * from t55 order by colint;
6199
reorganize partition p0,p1 into
6200
(partition s1 values less than maxvalue);
6201
select * from t66 order by colint;
6208
reorganize partition s1 into
6209
(partition p0 values less than (extract(year from '1998-11-23')),
6210
partition p1 values less than maxvalue);
6211
select * from t66 order by colint;
6218
reorganize partition p0,p1 into
6219
(partition s1 values less than maxvalue);
6220
select * from t66 order by colint;
6227
reorganize partition s1 into
6228
(partition p0 values less than (extract(year from '1998-11-23')),
6229
partition p1 values less than maxvalue);
6230
select * from t66 order by colint;
6236
-------------------------------------------------------------------------
6237
--- Delete rows and partitions of tables with extract(month from col1)
6238
-------------------------------------------------------------------------
6239
delete from t1 where col1='2006-02-17';
6240
delete from t2 where col1='2006-02-17';
6241
delete from t3 where col1='2006-02-17';
6242
delete from t4 where col1='2006-02-17';
6243
delete from t5 where col1='2006-02-17';
6244
delete from t6 where col1='2006-02-17';
6245
select * from t1 order by col1;
6248
select * from t2 order by col1;
6252
select * from t3 order by col1;
6256
select * from t4 order by colint;
6262
select * from t5 order by colint;
6268
insert into t1 values ('2006-02-17');
6269
insert into t2 values ('2006-02-17');
6270
insert into t3 values ('2006-02-17');
6271
insert into t4 values (60,'2006-02-17');
6272
insert into t5 values (60,'2006-02-17');
6273
insert into t6 values (60,'2006-02-17');
6274
select * from t1 order by col1;
6278
select * from t2 order by col1;
6283
select * from t3 order by col1;
6288
select * from t4 order by colint;
6295
select * from t5 order by colint;
6302
select * from t6 order by colint;
6309
alter table t1 drop partition p0;
6310
alter table t2 drop partition p0;
6311
alter table t4 drop partition p0;
6312
alter table t5 drop partition p0;
6313
alter table t6 drop partition p0;
6314
select * from t1 order by col1;
6316
select * from t2 order by col1;
6318
select * from t3 order by col1;
6323
select * from t4 order by colint;
6326
select * from t5 order by colint;
6329
select * from t6 order by colint;
6331
-------------------------------------------------------------------------
6332
--- Delete rows and partitions of tables with extract(month from col1)
6333
-------------------------------------------------------------------------
6334
delete from t11 where col1='2006-02-17';
6335
delete from t22 where col1='2006-02-17';
6336
delete from t33 where col1='2006-02-17';
6337
delete from t44 where col1='2006-02-17';
6338
delete from t55 where col1='2006-02-17';
6339
delete from t66 where col1='2006-02-17';
6340
select * from t11 order by col1;
6343
select * from t22 order by col1;
6347
select * from t33 order by col1;
6351
select * from t44 order by colint;
6357
select * from t55 order by colint;
6363
insert into t11 values ('2006-02-17');
6364
insert into t22 values ('2006-02-17');
6365
insert into t33 values ('2006-02-17');
6366
insert into t44 values (60,'2006-02-17');
6367
insert into t55 values (60,'2006-02-17');
6368
insert into t66 values (60,'2006-02-17');
6369
select * from t11 order by col1;
6373
select * from t22 order by col1;
6378
select * from t33 order by col1;
6383
select * from t44 order by colint;
6390
select * from t55 order by colint;
6397
select * from t66 order by colint;
6404
alter table t11 drop partition p0;
6405
alter table t22 drop partition p0;
6406
alter table t44 drop partition p0;
6407
alter table t55 drop partition p0;
6408
alter table t66 drop partition p0;
6409
select * from t11 order by col1;
6411
select * from t22 order by col1;
6413
select * from t33 order by col1;
6418
select * from t44 order by colint;
6421
select * from t55 order by colint;
6424
select * from t66 order by colint;
6426
-------------------------
6427
---- some alter table end
6428
-------------------------
6429
drop table if exists t1 ;
6430
drop table if exists t2 ;
6431
drop table if exists t3 ;
6432
drop table if exists t4 ;
6433
drop table if exists t5 ;
6434
drop table if exists t6 ;
6435
drop table if exists t11 ;
6436
drop table if exists t22 ;
6437
drop table if exists t33 ;
6438
drop table if exists t44 ;
6439
drop table if exists t55 ;
6440
drop table if exists t66 ;
6441
-------------------------------------------------------------------------
6442
--- hour(col1) in partition with coltype time
6443
-------------------------------------------------------------------------
6444
drop table if exists t1 ;
6445
drop table if exists t2 ;
6446
drop table if exists t3 ;
6447
drop table if exists t4 ;
6448
drop table if exists t5 ;
6449
drop table if exists t6 ;
6450
-------------------------------------------------------------------------
6451
--- Create tables with hour(col1)
6452
-------------------------------------------------------------------------
6453
create table t1 (col1 time) engine='INNODB'
6454
partition by range(hour(col1))
6455
(partition p0 values less than (15),
6456
partition p1 values less than maxvalue);
6457
create table t2 (col1 time) engine='INNODB'
6458
partition by list(hour(col1))
6459
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6460
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6461
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6462
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6463
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6464
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6466
create table t3 (col1 time) engine='INNODB'
6467
partition by hash(hour(col1));
6468
create table t4 (colint int, col1 time) engine='INNODB'
6469
partition by range(colint)
6470
subpartition by hash(hour(col1)) subpartitions 2
6471
(partition p0 values less than (15),
6472
partition p1 values less than maxvalue);
6473
create table t5 (colint int, col1 time) engine='INNODB'
6474
partition by list(colint)
6475
subpartition by hash(hour(col1)) subpartitions 2
6476
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6477
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6478
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6479
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6480
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6481
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6483
create table t6 (colint int, col1 time) engine='INNODB'
6484
partition by range(colint)
6485
(partition p0 values less than (hour('18:30')),
6486
partition p1 values less than maxvalue);
6487
-------------------------------------------------------------------------
6488
--- Access tables with hour(col1)
6489
-------------------------------------------------------------------------
6490
insert into t1 values ('09:09');
6491
insert into t1 values ('14:30');
6492
insert into t2 values ('09:09');
6493
insert into t2 values ('14:30');
6494
insert into t2 values ('21:59');
6495
insert into t3 values ('09:09');
6496
insert into t3 values ('14:30');
6497
insert into t3 values ('21:59');
6498
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
6499
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
6500
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
6501
select hour(col1) from t1 order by col1;
6505
select * from t1 order by col1;
6509
select * from t2 order by col1;
6514
select * from t3 order by col1;
6519
select * from t4 order by colint;
6525
select * from t5 order by colint;
6531
select * from t6 order by colint;
6537
update t1 set col1='10:30' where col1='09:09';
6538
update t2 set col1='10:30' where col1='09:09';
6539
update t3 set col1='10:30' where col1='09:09';
6540
update t4 set col1='10:30' where col1='09:09';
6541
update t5 set col1='10:30' where col1='09:09';
6542
update t6 set col1='10:30' where col1='09:09';
6543
select * from t1 order by col1;
6547
select * from t2 order by col1;
6552
select * from t3 order by col1;
6557
select * from t4 order by colint;
6563
select * from t5 order by colint;
6569
select * from t6 order by colint;
6575
-------------------------------------------------------------------------
6576
--- Alter tables with hour(col1)
6577
-------------------------------------------------------------------------
6578
drop table if exists t11 ;
6579
drop table if exists t22 ;
6580
drop table if exists t33 ;
6581
drop table if exists t44 ;
6582
drop table if exists t55 ;
6583
drop table if exists t66 ;
6584
create table t11 engine='INNODB' as select * from t1;
6585
create table t22 engine='INNODB' as select * from t2;
6586
create table t33 engine='INNODB' as select * from t3;
6587
create table t44 engine='INNODB' as select * from t4;
6588
create table t55 engine='INNODB' as select * from t5;
6589
create table t66 engine='INNODB' as select * from t6;
6591
partition by range(hour(col1))
6592
(partition p0 values less than (15),
6593
partition p1 values less than maxvalue);
6595
partition by list(hour(col1))
6596
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6597
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6598
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6599
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6600
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6601
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6604
partition by hash(hour(col1));
6606
partition by range(colint)
6607
subpartition by hash(hour(col1)) subpartitions 2
6608
(partition p0 values less than (15),
6609
partition p1 values less than maxvalue);
6611
partition by list(colint)
6612
subpartition by hash(hour(col1)) subpartitions 2
6613
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6614
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6615
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6616
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6617
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6618
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6621
partition by range(colint)
6622
(partition p0 values less than (hour('18:30')),
6623
partition p1 values less than maxvalue);
6624
select * from t11 order by col1;
6628
select * from t22 order by col1;
6633
select * from t33 order by col1;
6638
select * from t44 order by colint;
6644
select * from t55 order by colint;
6650
select * from t66 order by colint;
6656
---------------------------
6657
---- some alter table begin
6658
---------------------------
6660
reorganize partition p0,p1 into
6661
(partition s1 values less than maxvalue);
6662
select * from t11 order by col1;
6667
reorganize partition s1 into
6668
(partition p0 values less than (15),
6669
partition p1 values less than maxvalue);
6670
select * from t11 order by col1;
6675
partition by list(colint)
6676
subpartition by hash(hour(col1)) subpartitions 5
6677
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6678
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6679
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6680
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6681
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6682
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6684
show create table t55;
6686
t55 CREATE TABLE `t55` (
6687
`colint` int(11) DEFAULT NULL,
6688
`col1` time DEFAULT NULL
6689
) ENGINE=InnoDB DEFAULT CHARSET=latin1
6690
/*!50100 PARTITION BY LIST (colint)
6691
SUBPARTITION BY HASH (hour(col1))
6693
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
6694
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
6695
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
6696
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
6697
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
6698
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
6699
select * from t55 order by colint;
6706
reorganize partition p0,p1 into
6707
(partition s1 values less than maxvalue);
6708
select * from t66 order by colint;
6715
reorganize partition s1 into
6716
(partition p0 values less than (hour('18:30')),
6717
partition p1 values less than maxvalue);
6718
select * from t66 order by colint;
6725
reorganize partition p0,p1 into
6726
(partition s1 values less than maxvalue);
6727
select * from t66 order by colint;
6734
reorganize partition s1 into
6735
(partition p0 values less than (hour('18:30')),
6736
partition p1 values less than maxvalue);
6737
select * from t66 order by colint;
6743
-------------------------------------------------------------------------
6744
--- Delete rows and partitions of tables with hour(col1)
6745
-------------------------------------------------------------------------
6746
delete from t1 where col1='14:30';
6747
delete from t2 where col1='14:30';
6748
delete from t3 where col1='14:30';
6749
delete from t4 where col1='14:30';
6750
delete from t5 where col1='14:30';
6751
delete from t6 where col1='14:30';
6752
select * from t1 order by col1;
6755
select * from t2 order by col1;
6759
select * from t3 order by col1;
6763
select * from t4 order by colint;
6769
select * from t5 order by colint;
6775
insert into t1 values ('14:30');
6776
insert into t2 values ('14:30');
6777
insert into t3 values ('14:30');
6778
insert into t4 values (60,'14:30');
6779
insert into t5 values (60,'14:30');
6780
insert into t6 values (60,'14:30');
6781
select * from t1 order by col1;
6785
select * from t2 order by col1;
6790
select * from t3 order by col1;
6795
select * from t4 order by colint;
6802
select * from t5 order by colint;
6809
select * from t6 order by colint;
6816
alter table t1 drop partition p0;
6817
alter table t2 drop partition p0;
6818
alter table t4 drop partition p0;
6819
alter table t5 drop partition p0;
6820
alter table t6 drop partition p0;
6821
select * from t1 order by col1;
6823
select * from t2 order by col1;
6827
select * from t3 order by col1;
6832
select * from t4 order by colint;
6835
select * from t5 order by colint;
6838
select * from t6 order by colint;
6841
-------------------------------------------------------------------------
6842
--- Delete rows and partitions of tables with hour(col1)
6843
-------------------------------------------------------------------------
6844
delete from t11 where col1='14:30';
6845
delete from t22 where col1='14:30';
6846
delete from t33 where col1='14:30';
6847
delete from t44 where col1='14:30';
6848
delete from t55 where col1='14:30';
6849
delete from t66 where col1='14:30';
6850
select * from t11 order by col1;
6853
select * from t22 order by col1;
6857
select * from t33 order by col1;
6861
select * from t44 order by colint;
6867
select * from t55 order by colint;
6873
insert into t11 values ('14:30');
6874
insert into t22 values ('14:30');
6875
insert into t33 values ('14:30');
6876
insert into t44 values (60,'14:30');
6877
insert into t55 values (60,'14:30');
6878
insert into t66 values (60,'14:30');
6879
select * from t11 order by col1;
6883
select * from t22 order by col1;
6888
select * from t33 order by col1;
6893
select * from t44 order by colint;
6900
select * from t55 order by colint;
6907
select * from t66 order by colint;
6914
alter table t11 drop partition p0;
6915
alter table t22 drop partition p0;
6916
alter table t44 drop partition p0;
6917
alter table t55 drop partition p0;
6918
alter table t66 drop partition p0;
6919
select * from t11 order by col1;
6921
select * from t22 order by col1;
6925
select * from t33 order by col1;
6930
select * from t44 order by colint;
6933
select * from t55 order by colint;
6936
select * from t66 order by colint;
6939
-------------------------
6940
---- some alter table end
6941
-------------------------
6942
drop table if exists t1 ;
6943
drop table if exists t2 ;
6944
drop table if exists t3 ;
6945
drop table if exists t4 ;
6946
drop table if exists t5 ;
6947
drop table if exists t6 ;
6948
drop table if exists t11 ;
6949
drop table if exists t22 ;
6950
drop table if exists t33 ;
6951
drop table if exists t44 ;
6952
drop table if exists t55 ;
6953
drop table if exists t66 ;
6954
-------------------------------------------------------------------------
6955
--- microsecond(col1) in partition with coltype time
6956
-------------------------------------------------------------------------
6957
drop table if exists t1 ;
6958
drop table if exists t2 ;
6959
drop table if exists t3 ;
6960
drop table if exists t4 ;
6961
drop table if exists t5 ;
6962
drop table if exists t6 ;
6963
-------------------------------------------------------------------------
6964
--- Create tables with microsecond(col1)
6965
-------------------------------------------------------------------------
6966
create table t1 (col1 time) engine='INNODB'
6967
partition by range(microsecond(col1))
6968
(partition p0 values less than (15),
6969
partition p1 values less than maxvalue);
6970
create table t2 (col1 time) engine='INNODB'
6971
partition by list(microsecond(col1))
6972
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6973
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6974
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6975
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6976
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6977
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6979
create table t3 (col1 time) engine='INNODB'
6980
partition by hash(microsecond(col1));
6981
create table t4 (colint int, col1 time) engine='INNODB'
6982
partition by range(colint)
6983
subpartition by hash(microsecond(col1)) subpartitions 2
6984
(partition p0 values less than (15),
6985
partition p1 values less than maxvalue);
6986
create table t5 (colint int, col1 time) engine='INNODB'
6987
partition by list(colint)
6988
subpartition by hash(microsecond(col1)) subpartitions 2
6989
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
6990
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6991
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6992
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6993
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6994
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6996
create table t6 (colint int, col1 time) engine='INNODB'
6997
partition by range(colint)
6998
(partition p0 values less than (microsecond('10:30:10.000010')),
6999
partition p1 values less than maxvalue);
7000
-------------------------------------------------------------------------
7001
--- Access tables with microsecond(col1)
7002
-------------------------------------------------------------------------
7003
insert into t1 values ('09:09:15.000002');
7004
insert into t1 values ('04:30:01.000018');
7005
insert into t2 values ('09:09:15.000002');
7006
insert into t2 values ('04:30:01.000018');
7007
insert into t2 values ('00:59:22.000024');
7008
insert into t3 values ('09:09:15.000002');
7009
insert into t3 values ('04:30:01.000018');
7010
insert into t3 values ('00:59:22.000024');
7011
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
7012
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
7013
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
7014
select microsecond(col1) from t1 order by col1;
7018
select * from t1 order by col1;
7022
select * from t2 order by col1;
7027
select * from t3 order by col1;
7032
select * from t4 order by colint;
7038
select * from t5 order by colint;
7044
select * from t6 order by colint;
7050
update t1 set col1='05:30:34.000037' where col1='09:09:15.000002';
7051
update t2 set col1='05:30:34.000037' where col1='09:09:15.000002';
7052
update t3 set col1='05:30:34.000037' where col1='09:09:15.000002';
7053
update t4 set col1='05:30:34.000037' where col1='09:09:15.000002';
7054
update t5 set col1='05:30:34.000037' where col1='09:09:15.000002';
7055
update t6 set col1='05:30:34.000037' where col1='09:09:15.000002';
7056
select * from t1 order by col1;
7060
select * from t2 order by col1;
7065
select * from t3 order by col1;
7070
select * from t4 order by colint;
7076
select * from t5 order by colint;
7082
select * from t6 order by colint;
7088
-------------------------------------------------------------------------
7089
--- Alter tables with microsecond(col1)
7090
-------------------------------------------------------------------------
7091
drop table if exists t11 ;
7092
drop table if exists t22 ;
7093
drop table if exists t33 ;
7094
drop table if exists t44 ;
7095
drop table if exists t55 ;
7096
drop table if exists t66 ;
7097
create table t11 engine='INNODB' as select * from t1;
7098
create table t22 engine='INNODB' as select * from t2;
7099
create table t33 engine='INNODB' as select * from t3;
7100
create table t44 engine='INNODB' as select * from t4;
7101
create table t55 engine='INNODB' as select * from t5;
7102
create table t66 engine='INNODB' as select * from t6;
7104
partition by range(microsecond(col1))
7105
(partition p0 values less than (15),
7106
partition p1 values less than maxvalue);
7108
partition by list(microsecond(col1))
7109
(partition p0 values in (0,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)
7117
partition by hash(microsecond(col1));
7119
partition by range(colint)
7120
subpartition by hash(microsecond(col1)) subpartitions 2
7121
(partition p0 values less than (15),
7122
partition p1 values less than maxvalue);
7124
partition by list(colint)
7125
subpartition by hash(microsecond(col1)) subpartitions 2
7126
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7127
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7128
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7129
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7130
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7131
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7134
partition by range(colint)
7135
(partition p0 values less than (microsecond('10:30:10.000010')),
7136
partition p1 values less than maxvalue);
7137
select * from t11 order by col1;
7141
select * from t22 order by col1;
7146
select * from t33 order by col1;
7151
select * from t44 order by colint;
7157
select * from t55 order by colint;
7163
select * from t66 order by colint;
7169
---------------------------
7170
---- some alter table begin
7171
---------------------------
7173
reorganize partition p0,p1 into
7174
(partition s1 values less than maxvalue);
7175
select * from t11 order by col1;
7180
reorganize partition s1 into
7181
(partition p0 values less than (15),
7182
partition p1 values less than maxvalue);
7183
select * from t11 order by col1;
7188
partition by list(colint)
7189
subpartition by hash(microsecond(col1)) subpartitions 5
7190
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7191
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7192
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7193
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7194
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7195
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7197
show create table t55;
7199
t55 CREATE TABLE `t55` (
7200
`colint` int(11) DEFAULT NULL,
7201
`col1` time DEFAULT NULL
7202
) ENGINE=InnoDB DEFAULT CHARSET=latin1
7203
/*!50100 PARTITION BY LIST (colint)
7204
SUBPARTITION BY HASH (microsecond(col1))
7206
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
7207
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
7208
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
7209
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
7210
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
7211
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
7212
select * from t55 order by colint;
7219
reorganize partition p0,p1 into
7220
(partition s1 values less than maxvalue);
7221
select * from t66 order by colint;
7228
reorganize partition s1 into
7229
(partition p0 values less than (microsecond('10:30:10.000010')),
7230
partition p1 values less than maxvalue);
7231
select * from t66 order by colint;
7238
reorganize partition p0,p1 into
7239
(partition s1 values less than maxvalue);
7240
select * from t66 order by colint;
7247
reorganize partition s1 into
7248
(partition p0 values less than (microsecond('10:30:10.000010')),
7249
partition p1 values less than maxvalue);
7250
select * from t66 order by colint;
7256
-------------------------------------------------------------------------
7257
--- Delete rows and partitions of tables with microsecond(col1)
7258
-------------------------------------------------------------------------
7259
delete from t1 where col1='04:30:01.000018';
7260
delete from t2 where col1='04:30:01.000018';
7261
delete from t3 where col1='04:30:01.000018';
7262
delete from t4 where col1='04:30:01.000018';
7263
delete from t5 where col1='04:30:01.000018';
7264
delete from t6 where col1='04:30:01.000018';
7265
select * from t1 order by col1;
7268
select * from t2 order by col1;
7272
select * from t3 order by col1;
7276
select * from t4 order by colint;
7281
select * from t5 order by colint;
7286
insert into t1 values ('04:30:01.000018');
7287
insert into t2 values ('04:30:01.000018');
7288
insert into t3 values ('04:30:01.000018');
7289
insert into t4 values (60,'04:30:01.000018');
7290
insert into t5 values (60,'04:30:01.000018');
7291
insert into t6 values (60,'04:30:01.000018');
7292
select * from t1 order by col1;
7296
select * from t2 order by col1;
7301
select * from t3 order by col1;
7306
select * from t4 order by colint;
7312
select * from t5 order by colint;
7318
select * from t6 order by colint;
7324
alter table t1 drop partition p0;
7325
alter table t2 drop partition p0;
7326
alter table t4 drop partition p0;
7327
alter table t5 drop partition p0;
7328
alter table t6 drop partition p0;
7329
select * from t1 order by col1;
7331
select * from t2 order by col1;
7333
select * from t3 order by col1;
7338
select * from t4 order by colint;
7341
select * from t5 order by colint;
7344
select * from t6 order by colint;
7347
-------------------------------------------------------------------------
7348
--- Delete rows and partitions of tables with microsecond(col1)
7349
-------------------------------------------------------------------------
7350
delete from t11 where col1='04:30:01.000018';
7351
delete from t22 where col1='04:30:01.000018';
7352
delete from t33 where col1='04:30:01.000018';
7353
delete from t44 where col1='04:30:01.000018';
7354
delete from t55 where col1='04:30:01.000018';
7355
delete from t66 where col1='04:30:01.000018';
7356
select * from t11 order by col1;
7359
select * from t22 order by col1;
7363
select * from t33 order by col1;
7367
select * from t44 order by colint;
7372
select * from t55 order by colint;
7377
insert into t11 values ('04:30:01.000018');
7378
insert into t22 values ('04:30:01.000018');
7379
insert into t33 values ('04:30:01.000018');
7380
insert into t44 values (60,'04:30:01.000018');
7381
insert into t55 values (60,'04:30:01.000018');
7382
insert into t66 values (60,'04:30:01.000018');
7383
select * from t11 order by col1;
7387
select * from t22 order by col1;
7392
select * from t33 order by col1;
7397
select * from t44 order by colint;
7403
select * from t55 order by colint;
7409
select * from t66 order by colint;
7415
alter table t11 drop partition p0;
7416
alter table t22 drop partition p0;
7417
alter table t44 drop partition p0;
7418
alter table t55 drop partition p0;
7419
alter table t66 drop partition p0;
7420
select * from t11 order by col1;
7422
select * from t22 order by col1;
7424
select * from t33 order by col1;
7429
select * from t44 order by colint;
7432
select * from t55 order by colint;
7435
select * from t66 order by colint;
7438
-------------------------
7439
---- some alter table end
7440
-------------------------
7441
drop table if exists t1 ;
7442
drop table if exists t2 ;
7443
drop table if exists t3 ;
7444
drop table if exists t4 ;
7445
drop table if exists t5 ;
7446
drop table if exists t6 ;
7447
drop table if exists t11 ;
7448
drop table if exists t22 ;
7449
drop table if exists t33 ;
7450
drop table if exists t44 ;
7451
drop table if exists t55 ;
7452
drop table if exists t66 ;
7453
-------------------------------------------------------------------------
7454
--- minute(col1) in partition with coltype time
7455
-------------------------------------------------------------------------
7456
drop table if exists t1 ;
7457
drop table if exists t2 ;
7458
drop table if exists t3 ;
7459
drop table if exists t4 ;
7460
drop table if exists t5 ;
7461
drop table if exists t6 ;
7462
-------------------------------------------------------------------------
7463
--- Create tables with minute(col1)
7464
-------------------------------------------------------------------------
7465
create table t1 (col1 time) engine='INNODB'
7466
partition by range(minute(col1))
7467
(partition p0 values less than (15),
7468
partition p1 values less than maxvalue);
7469
create table t2 (col1 time) engine='INNODB'
7470
partition by list(minute(col1))
7471
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7472
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7473
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7474
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7475
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7476
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7478
create table t3 (col1 time) engine='INNODB'
7479
partition by hash(minute(col1));
7480
create table t4 (colint int, col1 time) engine='INNODB'
7481
partition by range(colint)
7482
subpartition by hash(minute(col1)) subpartitions 2
7483
(partition p0 values less than (15),
7484
partition p1 values less than maxvalue);
7485
create table t5 (colint int, col1 time) engine='INNODB'
7486
partition by list(colint)
7487
subpartition by hash(minute(col1)) subpartitions 2
7488
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7489
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7490
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7491
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7492
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7493
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7495
create table t6 (colint int, col1 time) engine='INNODB'
7496
partition by range(colint)
7497
(partition p0 values less than (minute('18:30')),
7498
partition p1 values less than maxvalue);
7499
-------------------------------------------------------------------------
7500
--- Access tables with minute(col1)
7501
-------------------------------------------------------------------------
7502
insert into t1 values ('09:09:15');
7503
insert into t1 values ('14:30:45');
7504
insert into t2 values ('09:09:15');
7505
insert into t2 values ('14:30:45');
7506
insert into t2 values ('21:59:22');
7507
insert into t3 values ('09:09:15');
7508
insert into t3 values ('14:30:45');
7509
insert into t3 values ('21:59:22');
7510
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
7511
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
7512
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
7513
select minute(col1) from t1 order by col1;
7517
select * from t1 order by col1;
7521
select * from t2 order by col1;
7526
select * from t3 order by col1;
7531
select * from t4 order by colint;
7537
select * from t5 order by colint;
7543
select * from t6 order by colint;
7549
update t1 set col1='10:24:23' where col1='09:09:15';
7550
update t2 set col1='10:24:23' where col1='09:09:15';
7551
update t3 set col1='10:24:23' where col1='09:09:15';
7552
update t4 set col1='10:24:23' where col1='09:09:15';
7553
update t5 set col1='10:24:23' where col1='09:09:15';
7554
update t6 set col1='10:24:23' where col1='09:09:15';
7555
select * from t1 order by col1;
7559
select * from t2 order by col1;
7564
select * from t3 order by col1;
7569
select * from t4 order by colint;
7575
select * from t5 order by colint;
7581
select * from t6 order by colint;
7587
-------------------------------------------------------------------------
7588
--- Alter tables with minute(col1)
7589
-------------------------------------------------------------------------
7590
drop table if exists t11 ;
7591
drop table if exists t22 ;
7592
drop table if exists t33 ;
7593
drop table if exists t44 ;
7594
drop table if exists t55 ;
7595
drop table if exists t66 ;
7596
create table t11 engine='INNODB' as select * from t1;
7597
create table t22 engine='INNODB' as select * from t2;
7598
create table t33 engine='INNODB' as select * from t3;
7599
create table t44 engine='INNODB' as select * from t4;
7600
create table t55 engine='INNODB' as select * from t5;
7601
create table t66 engine='INNODB' as select * from t6;
7603
partition by range(minute(col1))
7604
(partition p0 values less than (15),
7605
partition p1 values less than maxvalue);
7607
partition by list(minute(col1))
7608
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7609
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7610
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7611
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7612
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7613
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7616
partition by hash(minute(col1));
7618
partition by range(colint)
7619
subpartition by hash(minute(col1)) subpartitions 2
7620
(partition p0 values less than (15),
7621
partition p1 values less than maxvalue);
7623
partition by list(colint)
7624
subpartition by hash(minute(col1)) subpartitions 2
7625
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7626
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7627
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7628
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7629
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7630
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7633
partition by range(colint)
7634
(partition p0 values less than (minute('18:30')),
7635
partition p1 values less than maxvalue);
7636
select * from t11 order by col1;
7640
select * from t22 order by col1;
7645
select * from t33 order by col1;
7650
select * from t44 order by colint;
7656
select * from t55 order by colint;
7662
select * from t66 order by colint;
7668
---------------------------
7669
---- some alter table begin
7670
---------------------------
7672
reorganize partition p0,p1 into
7673
(partition s1 values less than maxvalue);
7674
select * from t11 order by col1;
7679
reorganize partition s1 into
7680
(partition p0 values less than (15),
7681
partition p1 values less than maxvalue);
7682
select * from t11 order by col1;
7687
partition by list(colint)
7688
subpartition by hash(minute(col1)) subpartitions 5
7689
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
7690
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7691
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7692
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7693
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7694
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7696
show create table t55;
7698
t55 CREATE TABLE `t55` (
7699
`colint` int(11) DEFAULT NULL,
7700
`col1` time DEFAULT NULL
7701
) ENGINE=InnoDB DEFAULT CHARSET=latin1
7702
/*!50100 PARTITION BY LIST (colint)
7703
SUBPARTITION BY HASH (minute(col1))
7705
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
7706
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
7707
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
7708
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
7709
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
7710
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
7711
select * from t55 order by colint;
7718
reorganize partition p0,p1 into
7719
(partition s1 values less than maxvalue);
7720
select * from t66 order by colint;
7727
reorganize partition s1 into
7728
(partition p0 values less than (minute('18:30')),
7729
partition p1 values less than maxvalue);
7730
select * from t66 order by colint;
7737
reorganize partition p0,p1 into
7738
(partition s1 values less than maxvalue);
7739
select * from t66 order by colint;
7746
reorganize partition s1 into
7747
(partition p0 values less than (minute('18:30')),
7748
partition p1 values less than maxvalue);
7749
select * from t66 order by colint;
7755
-------------------------------------------------------------------------
7756
--- Delete rows and partitions of tables with minute(col1)
7757
-------------------------------------------------------------------------
7758
delete from t1 where col1='14:30:45';
7759
delete from t2 where col1='14:30:45';
7760
delete from t3 where col1='14:30:45';
7761
delete from t4 where col1='14:30:45';
7762
delete from t5 where col1='14:30:45';
7763
delete from t6 where col1='14:30:45';
7764
select * from t1 order by col1;
7767
select * from t2 order by col1;
7771
select * from t3 order by col1;
7775
select * from t4 order by colint;
7781
select * from t5 order by colint;
7787
insert into t1 values ('14:30:45');
7788
insert into t2 values ('14:30:45');
7789
insert into t3 values ('14:30:45');
7790
insert into t4 values (60,'14:30:45');
7791
insert into t5 values (60,'14:30:45');
7792
insert into t6 values (60,'14:30:45');
7793
select * from t1 order by col1;
7797
select * from t2 order by col1;
7802
select * from t3 order by col1;
7807
select * from t4 order by colint;
7814
select * from t5 order by colint;
7821
select * from t6 order by colint;
7828
alter table t1 drop partition p0;
7829
alter table t2 drop partition p0;
7830
alter table t4 drop partition p0;
7831
alter table t5 drop partition p0;
7832
alter table t6 drop partition p0;
7833
select * from t1 order by col1;
7837
select * from t2 order by col1;
7842
select * from t3 order by col1;
7847
select * from t4 order by colint;
7850
select * from t5 order by colint;
7853
select * from t6 order by colint;
7856
-------------------------------------------------------------------------
7857
--- Delete rows and partitions of tables with minute(col1)
7858
-------------------------------------------------------------------------
7859
delete from t11 where col1='14:30:45';
7860
delete from t22 where col1='14:30:45';
7861
delete from t33 where col1='14:30:45';
7862
delete from t44 where col1='14:30:45';
7863
delete from t55 where col1='14:30:45';
7864
delete from t66 where col1='14:30:45';
7865
select * from t11 order by col1;
7868
select * from t22 order by col1;
7872
select * from t33 order by col1;
7876
select * from t44 order by colint;
7882
select * from t55 order by colint;
7888
insert into t11 values ('14:30:45');
7889
insert into t22 values ('14:30:45');
7890
insert into t33 values ('14:30:45');
7891
insert into t44 values (60,'14:30:45');
7892
insert into t55 values (60,'14:30:45');
7893
insert into t66 values (60,'14:30:45');
7894
select * from t11 order by col1;
7898
select * from t22 order by col1;
7903
select * from t33 order by col1;
7908
select * from t44 order by colint;
7915
select * from t55 order by colint;
7922
select * from t66 order by colint;
7929
alter table t11 drop partition p0;
7930
alter table t22 drop partition p0;
7931
alter table t44 drop partition p0;
7932
alter table t55 drop partition p0;
7933
alter table t66 drop partition p0;
7934
select * from t11 order by col1;
7938
select * from t22 order by col1;
7943
select * from t33 order by col1;
7948
select * from t44 order by colint;
7951
select * from t55 order by colint;
7954
select * from t66 order by colint;
7957
-------------------------
7958
---- some alter table end
7959
-------------------------
7960
drop table if exists t1 ;
7961
drop table if exists t2 ;
7962
drop table if exists t3 ;
7963
drop table if exists t4 ;
7964
drop table if exists t5 ;
7965
drop table if exists t6 ;
7966
drop table if exists t11 ;
7967
drop table if exists t22 ;
7968
drop table if exists t33 ;
7969
drop table if exists t44 ;
7970
drop table if exists t55 ;
7971
drop table if exists t66 ;
7972
-------------------------------------------------------------------------
7973
--- second(col1) in partition with coltype time
7974
-------------------------------------------------------------------------
7975
drop table if exists t1 ;
7976
drop table if exists t2 ;
7977
drop table if exists t3 ;
7978
drop table if exists t4 ;
7979
drop table if exists t5 ;
7980
drop table if exists t6 ;
7981
-------------------------------------------------------------------------
7982
--- Create tables with second(col1)
7983
-------------------------------------------------------------------------
7984
create table t1 (col1 time) engine='INNODB'
7985
partition by range(second(col1))
7986
(partition p0 values less than (15),
7987
partition p1 values less than maxvalue);
7988
create table t2 (col1 time) engine='INNODB'
7989
partition by list(second(col1))
7990
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7991
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7992
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7993
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7994
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7995
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7997
create table t3 (col1 time) engine='INNODB'
7998
partition by hash(second(col1));
7999
create table t4 (colint int, col1 time) engine='INNODB'
8000
partition by range(colint)
8001
subpartition by hash(second(col1)) subpartitions 2
8002
(partition p0 values less than (15),
8003
partition p1 values less than maxvalue);
8004
create table t5 (colint int, col1 time) engine='INNODB'
8005
partition by list(colint)
8006
subpartition by hash(second(col1)) subpartitions 2
8007
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8008
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8009
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8010
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8011
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8012
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8014
create table t6 (colint int, col1 time) engine='INNODB'
8015
partition by range(colint)
8016
(partition p0 values less than (second('18:30:14')),
8017
partition p1 values less than maxvalue);
8018
-------------------------------------------------------------------------
8019
--- Access tables with second(col1)
8020
-------------------------------------------------------------------------
8021
insert into t1 values ('09:09:09');
8022
insert into t1 values ('14:30:20');
8023
insert into t2 values ('09:09:09');
8024
insert into t2 values ('14:30:20');
8025
insert into t2 values ('21:59:22');
8026
insert into t3 values ('09:09:09');
8027
insert into t3 values ('14:30:20');
8028
insert into t3 values ('21:59:22');
8029
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
8030
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
8031
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
8032
select second(col1) from t1 order by col1;
8036
select * from t1 order by col1;
8040
select * from t2 order by col1;
8045
select * from t3 order by col1;
8050
select * from t4 order by colint;
8056
select * from t5 order by colint;
8062
select * from t6 order by colint;
8068
update t1 set col1='10:22:33' where col1='09:09:09';
8069
update t2 set col1='10:22:33' where col1='09:09:09';
8070
update t3 set col1='10:22:33' where col1='09:09:09';
8071
update t4 set col1='10:22:33' where col1='09:09:09';
8072
update t5 set col1='10:22:33' where col1='09:09:09';
8073
update t6 set col1='10:22:33' where col1='09:09:09';
8074
select * from t1 order by col1;
8078
select * from t2 order by col1;
8083
select * from t3 order by col1;
8088
select * from t4 order by colint;
8094
select * from t5 order by colint;
8100
select * from t6 order by colint;
8106
-------------------------------------------------------------------------
8107
--- Alter tables with second(col1)
8108
-------------------------------------------------------------------------
8109
drop table if exists t11 ;
8110
drop table if exists t22 ;
8111
drop table if exists t33 ;
8112
drop table if exists t44 ;
8113
drop table if exists t55 ;
8114
drop table if exists t66 ;
8115
create table t11 engine='INNODB' as select * from t1;
8116
create table t22 engine='INNODB' as select * from t2;
8117
create table t33 engine='INNODB' as select * from t3;
8118
create table t44 engine='INNODB' as select * from t4;
8119
create table t55 engine='INNODB' as select * from t5;
8120
create table t66 engine='INNODB' as select * from t6;
8122
partition by range(second(col1))
8123
(partition p0 values less than (15),
8124
partition p1 values less than maxvalue);
8126
partition by list(second(col1))
8127
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8128
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8129
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8130
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8131
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8132
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8135
partition by hash(second(col1));
8137
partition by range(colint)
8138
subpartition by hash(second(col1)) subpartitions 2
8139
(partition p0 values less than (15),
8140
partition p1 values less than maxvalue);
8142
partition by list(colint)
8143
subpartition by hash(second(col1)) subpartitions 2
8144
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8145
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8146
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8147
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8148
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8149
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8152
partition by range(colint)
8153
(partition p0 values less than (second('18:30:14')),
8154
partition p1 values less than maxvalue);
8155
select * from t11 order by col1;
8159
select * from t22 order by col1;
8164
select * from t33 order by col1;
8169
select * from t44 order by colint;
8175
select * from t55 order by colint;
8181
select * from t66 order by colint;
8187
---------------------------
8188
---- some alter table begin
8189
---------------------------
8191
reorganize partition p0,p1 into
8192
(partition s1 values less than maxvalue);
8193
select * from t11 order by col1;
8198
reorganize partition s1 into
8199
(partition p0 values less than (15),
8200
partition p1 values less than maxvalue);
8201
select * from t11 order by col1;
8206
partition by list(colint)
8207
subpartition by hash(second(col1)) subpartitions 5
8208
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8209
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8210
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8211
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8212
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8213
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8215
show create table t55;
8217
t55 CREATE TABLE `t55` (
8218
`colint` int(11) DEFAULT NULL,
8219
`col1` time DEFAULT NULL
8220
) ENGINE=InnoDB DEFAULT CHARSET=latin1
8221
/*!50100 PARTITION BY LIST (colint)
8222
SUBPARTITION BY HASH (second(col1))
8224
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
8225
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
8226
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
8227
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
8228
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
8229
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
8230
select * from t55 order by colint;
8237
reorganize partition p0,p1 into
8238
(partition s1 values less than maxvalue);
8239
select * from t66 order by colint;
8246
reorganize partition s1 into
8247
(partition p0 values less than (second('18:30:14')),
8248
partition p1 values less than maxvalue);
8249
select * from t66 order by colint;
8256
reorganize partition p0,p1 into
8257
(partition s1 values less than maxvalue);
8258
select * from t66 order by colint;
8265
reorganize partition s1 into
8266
(partition p0 values less than (second('18:30:14')),
8267
partition p1 values less than maxvalue);
8268
select * from t66 order by colint;
8274
-------------------------------------------------------------------------
8275
--- Delete rows and partitions of tables with second(col1)
8276
-------------------------------------------------------------------------
8277
delete from t1 where col1='14:30:20';
8278
delete from t2 where col1='14:30:20';
8279
delete from t3 where col1='14:30:20';
8280
delete from t4 where col1='14:30:20';
8281
delete from t5 where col1='14:30:20';
8282
delete from t6 where col1='14:30:20';
8283
select * from t1 order by col1;
8286
select * from t2 order by col1;
8290
select * from t3 order by col1;
8294
select * from t4 order by colint;
8300
select * from t5 order by colint;
8306
insert into t1 values ('14:30:20');
8307
insert into t2 values ('14:30:20');
8308
insert into t3 values ('14:30:20');
8309
insert into t4 values (60,'14:30:20');
8310
insert into t5 values (60,'14:30:20');
8311
insert into t6 values (60,'14:30:20');
8312
select * from t1 order by col1;
8316
select * from t2 order by col1;
8321
select * from t3 order by col1;
8326
select * from t4 order by colint;
8333
select * from t5 order by colint;
8340
select * from t6 order by colint;
8347
alter table t1 drop partition p0;
8348
alter table t2 drop partition p0;
8349
alter table t4 drop partition p0;
8350
alter table t5 drop partition p0;
8351
alter table t6 drop partition p0;
8352
select * from t1 order by col1;
8356
select * from t2 order by col1;
8361
select * from t3 order by col1;
8366
select * from t4 order by colint;
8369
select * from t5 order by colint;
8372
select * from t6 order by colint;
8375
-------------------------------------------------------------------------
8376
--- Delete rows and partitions of tables with second(col1)
8377
-------------------------------------------------------------------------
8378
delete from t11 where col1='14:30:20';
8379
delete from t22 where col1='14:30:20';
8380
delete from t33 where col1='14:30:20';
8381
delete from t44 where col1='14:30:20';
8382
delete from t55 where col1='14:30:20';
8383
delete from t66 where col1='14:30:20';
8384
select * from t11 order by col1;
8387
select * from t22 order by col1;
8391
select * from t33 order by col1;
8395
select * from t44 order by colint;
8401
select * from t55 order by colint;
8407
insert into t11 values ('14:30:20');
8408
insert into t22 values ('14:30:20');
8409
insert into t33 values ('14:30:20');
8410
insert into t44 values (60,'14:30:20');
8411
insert into t55 values (60,'14:30:20');
8412
insert into t66 values (60,'14:30:20');
8413
select * from t11 order by col1;
8417
select * from t22 order by col1;
8422
select * from t33 order by col1;
8427
select * from t44 order by colint;
8434
select * from t55 order by colint;
8441
select * from t66 order by colint;
8448
alter table t11 drop partition p0;
8449
alter table t22 drop partition p0;
8450
alter table t44 drop partition p0;
8451
alter table t55 drop partition p0;
8452
alter table t66 drop partition p0;
8453
select * from t11 order by col1;
8457
select * from t22 order by col1;
8462
select * from t33 order by col1;
8467
select * from t44 order by colint;
8470
select * from t55 order by colint;
8473
select * from t66 order by colint;
8476
-------------------------
8477
---- some alter table end
8478
-------------------------
8479
drop table if exists t1 ;
8480
drop table if exists t2 ;
8481
drop table if exists t3 ;
8482
drop table if exists t4 ;
8483
drop table if exists t5 ;
8484
drop table if exists t6 ;
8485
drop table if exists t11 ;
8486
drop table if exists t22 ;
8487
drop table if exists t33 ;
8488
drop table if exists t44 ;
8489
drop table if exists t55 ;
8490
drop table if exists t66 ;
8491
-------------------------------------------------------------------------
8492
--- second(col1) in partition with coltype char(30)
8493
-------------------------------------------------------------------------
8494
drop table if exists t1 ;
8495
drop table if exists t2 ;
8496
drop table if exists t3 ;
8497
drop table if exists t4 ;
8498
drop table if exists t5 ;
8499
drop table if exists t6 ;
8500
-------------------------------------------------------------------------
8501
--- Create tables with second(col1)
8502
-------------------------------------------------------------------------
8503
create table t1 (col1 char(30)) engine='INNODB'
8504
partition by range(second(col1))
8505
(partition p0 values less than (15),
8506
partition p1 values less than maxvalue);
8507
create table t2 (col1 char(30)) engine='INNODB'
8508
partition by list(second(col1))
8509
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8510
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8511
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8512
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8513
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8514
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8516
create table t3 (col1 char(30)) engine='INNODB'
8517
partition by hash(second(col1));
8518
create table t4 (colint int, col1 char(30)) engine='INNODB'
8519
partition by range(colint)
8520
subpartition by hash(second(col1)) subpartitions 2
8521
(partition p0 values less than (15),
8522
partition p1 values less than maxvalue);
8523
create table t5 (colint int, col1 char(30)) engine='INNODB'
8524
partition by list(colint)
8525
subpartition by hash(second(col1)) subpartitions 2
8526
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8527
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8528
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8529
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8530
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8531
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8533
create table t6 (colint int, col1 char(30)) engine='INNODB'
8534
partition by range(colint)
8535
(partition p0 values less than (second('18:30:14')),
8536
partition p1 values less than maxvalue);
8537
-------------------------------------------------------------------------
8538
--- Access tables with second(col1)
8539
-------------------------------------------------------------------------
8540
insert into t1 values ('09:09:09');
8541
insert into t1 values ('14:30:20');
8542
insert into t2 values ('09:09:09');
8543
insert into t2 values ('14:30:20');
8544
insert into t2 values ('21:59:22');
8545
insert into t3 values ('09:09:09');
8546
insert into t3 values ('14:30:20');
8547
insert into t3 values ('21:59:22');
8548
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
8549
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
8550
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
8551
select second(col1) from t1 order by col1;
8555
select * from t1 order by col1;
8559
select * from t2 order by col1;
8564
select * from t3 order by col1;
8569
select * from t4 order by colint;
8575
select * from t5 order by colint;
8581
select * from t6 order by colint;
8587
update t1 set col1='10:22:33' where col1='09:09:09';
8588
update t2 set col1='10:22:33' where col1='09:09:09';
8589
update t3 set col1='10:22:33' where col1='09:09:09';
8590
update t4 set col1='10:22:33' where col1='09:09:09';
8591
update t5 set col1='10:22:33' where col1='09:09:09';
8592
update t6 set col1='10:22:33' where col1='09:09:09';
8593
select * from t1 order by col1;
8597
select * from t2 order by col1;
8602
select * from t3 order by col1;
8607
select * from t4 order by colint;
8613
select * from t5 order by colint;
8619
select * from t6 order by colint;
8625
-------------------------------------------------------------------------
8626
--- Alter tables with second(col1)
8627
-------------------------------------------------------------------------
8628
drop table if exists t11 ;
8629
drop table if exists t22 ;
8630
drop table if exists t33 ;
8631
drop table if exists t44 ;
8632
drop table if exists t55 ;
8633
drop table if exists t66 ;
8634
create table t11 engine='INNODB' as select * from t1;
8635
create table t22 engine='INNODB' as select * from t2;
8636
create table t33 engine='INNODB' as select * from t3;
8637
create table t44 engine='INNODB' as select * from t4;
8638
create table t55 engine='INNODB' as select * from t5;
8639
create table t66 engine='INNODB' as select * from t6;
8641
partition by range(second(col1))
8642
(partition p0 values less than (15),
8643
partition p1 values less than maxvalue);
8645
partition by list(second(col1))
8646
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8647
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8648
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8649
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8650
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8651
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8654
partition by hash(second(col1));
8656
partition by range(colint)
8657
subpartition by hash(second(col1)) subpartitions 2
8658
(partition p0 values less than (15),
8659
partition p1 values less than maxvalue);
8661
partition by list(colint)
8662
subpartition by hash(second(col1)) subpartitions 2
8663
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8664
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8665
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8666
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8667
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8668
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8671
partition by range(colint)
8672
(partition p0 values less than (second('18:30:14')),
8673
partition p1 values less than maxvalue);
8674
select * from t11 order by col1;
8678
select * from t22 order by col1;
8683
select * from t33 order by col1;
8688
select * from t44 order by colint;
8694
select * from t55 order by colint;
8700
select * from t66 order by colint;
8706
---------------------------
8707
---- some alter table begin
8708
---------------------------
8710
reorganize partition p0,p1 into
8711
(partition s1 values less than maxvalue);
8712
select * from t11 order by col1;
8717
reorganize partition s1 into
8718
(partition p0 values less than (15),
8719
partition p1 values less than maxvalue);
8720
select * from t11 order by col1;
8725
partition by list(colint)
8726
subpartition by hash(second(col1)) subpartitions 5
8727
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8728
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8729
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8730
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8731
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8732
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8734
show create table t55;
8736
t55 CREATE TABLE `t55` (
8737
`colint` int(11) DEFAULT NULL,
8738
`col1` char(30) DEFAULT NULL
8739
) ENGINE=InnoDB DEFAULT CHARSET=latin1
8740
/*!50100 PARTITION BY LIST (colint)
8741
SUBPARTITION BY HASH (second(col1))
8743
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
8744
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
8745
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
8746
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
8747
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
8748
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
8749
select * from t55 order by colint;
8756
reorganize partition p0,p1 into
8757
(partition s1 values less than maxvalue);
8758
select * from t66 order by colint;
8765
reorganize partition s1 into
8766
(partition p0 values less than (second('18:30:14')),
8767
partition p1 values less than maxvalue);
8768
select * from t66 order by colint;
8775
reorganize partition p0,p1 into
8776
(partition s1 values less than maxvalue);
8777
select * from t66 order by colint;
8784
reorganize partition s1 into
8785
(partition p0 values less than (second('18:30:14')),
8786
partition p1 values less than maxvalue);
8787
select * from t66 order by colint;
8793
-------------------------------------------------------------------------
8794
--- Delete rows and partitions of tables with second(col1)
8795
-------------------------------------------------------------------------
8796
delete from t1 where col1='14:30:20';
8797
delete from t2 where col1='14:30:20';
8798
delete from t3 where col1='14:30:20';
8799
delete from t4 where col1='14:30:20';
8800
delete from t5 where col1='14:30:20';
8801
delete from t6 where col1='14:30:20';
8802
select * from t1 order by col1;
8805
select * from t2 order by col1;
8809
select * from t3 order by col1;
8813
select * from t4 order by colint;
8819
select * from t5 order by colint;
8825
insert into t1 values ('14:30:20');
8826
insert into t2 values ('14:30:20');
8827
insert into t3 values ('14:30:20');
8828
insert into t4 values (60,'14:30:20');
8829
insert into t5 values (60,'14:30:20');
8830
insert into t6 values (60,'14:30:20');
8831
select * from t1 order by col1;
8835
select * from t2 order by col1;
8840
select * from t3 order by col1;
8845
select * from t4 order by colint;
8852
select * from t5 order by colint;
8859
select * from t6 order by colint;
8866
alter table t1 drop partition p0;
8867
alter table t2 drop partition p0;
8868
alter table t4 drop partition p0;
8869
alter table t5 drop partition p0;
8870
alter table t6 drop partition p0;
8871
select * from t1 order by col1;
8875
select * from t2 order by col1;
8880
select * from t3 order by col1;
8885
select * from t4 order by colint;
8888
select * from t5 order by colint;
8891
select * from t6 order by colint;
8894
-------------------------------------------------------------------------
8895
--- Delete rows and partitions of tables with second(col1)
8896
-------------------------------------------------------------------------
8897
delete from t11 where col1='14:30:20';
8898
delete from t22 where col1='14:30:20';
8899
delete from t33 where col1='14:30:20';
8900
delete from t44 where col1='14:30:20';
8901
delete from t55 where col1='14:30:20';
8902
delete from t66 where col1='14:30:20';
8903
select * from t11 order by col1;
8906
select * from t22 order by col1;
8910
select * from t33 order by col1;
8914
select * from t44 order by colint;
8920
select * from t55 order by colint;
8926
insert into t11 values ('14:30:20');
8927
insert into t22 values ('14:30:20');
8928
insert into t33 values ('14:30:20');
8929
insert into t44 values (60,'14:30:20');
8930
insert into t55 values (60,'14:30:20');
8931
insert into t66 values (60,'14:30:20');
8932
select * from t11 order by col1;
8936
select * from t22 order by col1;
8941
select * from t33 order by col1;
8946
select * from t44 order by colint;
8953
select * from t55 order by colint;
8960
select * from t66 order by colint;
8967
alter table t11 drop partition p0;
8968
alter table t22 drop partition p0;
8969
alter table t44 drop partition p0;
8970
alter table t55 drop partition p0;
8971
alter table t66 drop partition p0;
8972
select * from t11 order by col1;
8976
select * from t22 order by col1;
8981
select * from t33 order by col1;
8986
select * from t44 order by colint;
8989
select * from t55 order by colint;
8992
select * from t66 order by colint;
8995
-------------------------
8996
---- some alter table end
8997
-------------------------
8998
drop table if exists t1 ;
8999
drop table if exists t2 ;
9000
drop table if exists t3 ;
9001
drop table if exists t4 ;
9002
drop table if exists t5 ;
9003
drop table if exists t6 ;
9004
drop table if exists t11 ;
9005
drop table if exists t22 ;
9006
drop table if exists t33 ;
9007
drop table if exists t44 ;
9008
drop table if exists t55 ;
9009
drop table if exists t66 ;
9010
-------------------------------------------------------------------------
9011
--- month(col1) in partition with coltype date
9012
-------------------------------------------------------------------------
9013
drop table if exists t1 ;
9014
drop table if exists t2 ;
9015
drop table if exists t3 ;
9016
drop table if exists t4 ;
9017
drop table if exists t5 ;
9018
drop table if exists t6 ;
9019
-------------------------------------------------------------------------
9020
--- Create tables with month(col1)
9021
-------------------------------------------------------------------------
9022
create table t1 (col1 date) engine='INNODB'
9023
partition by range(month(col1))
9024
(partition p0 values less than (15),
9025
partition p1 values less than maxvalue);
9026
create table t2 (col1 date) engine='INNODB'
9027
partition by list(month(col1))
9028
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9029
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9030
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9031
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9032
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9033
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9035
create table t3 (col1 date) engine='INNODB'
9036
partition by hash(month(col1));
9037
create table t4 (colint int, col1 date) engine='INNODB'
9038
partition by range(colint)
9039
subpartition by hash(month(col1)) subpartitions 2
9040
(partition p0 values less than (15),
9041
partition p1 values less than maxvalue);
9042
create table t5 (colint int, col1 date) engine='INNODB'
9043
partition by list(colint)
9044
subpartition by hash(month(col1)) subpartitions 2
9045
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9046
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9047
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9048
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9049
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9050
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9052
create table t6 (colint int, col1 date) engine='INNODB'
9053
partition by range(colint)
9054
(partition p0 values less than (month('2006-10-14')),
9055
partition p1 values less than maxvalue);
9056
-------------------------------------------------------------------------
9057
--- Access tables with month(col1)
9058
-------------------------------------------------------------------------
9059
insert into t1 values ('2006-01-03');
9060
insert into t1 values ('2006-12-17');
9061
insert into t2 values ('2006-01-03');
9062
insert into t2 values ('2006-12-17');
9063
insert into t2 values ('2006-05-25');
9064
insert into t3 values ('2006-01-03');
9065
insert into t3 values ('2006-12-17');
9066
insert into t3 values ('2006-05-25');
9067
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
9068
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
9069
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
9070
select month(col1) from t1 order by col1;
9074
select * from t1 order by col1;
9078
select * from t2 order by col1;
9083
select * from t3 order by col1;
9088
select * from t4 order by colint;
9094
select * from t5 order by colint;
9100
select * from t6 order by colint;
9106
update t1 set col1='2006-11-06' where col1='2006-01-03';
9107
update t2 set col1='2006-11-06' where col1='2006-01-03';
9108
update t3 set col1='2006-11-06' where col1='2006-01-03';
9109
update t4 set col1='2006-11-06' where col1='2006-01-03';
9110
update t5 set col1='2006-11-06' where col1='2006-01-03';
9111
update t6 set col1='2006-11-06' where col1='2006-01-03';
9112
select * from t1 order by col1;
9116
select * from t2 order by col1;
9121
select * from t3 order by col1;
9126
select * from t4 order by colint;
9132
select * from t5 order by colint;
9138
select * from t6 order by colint;
9144
-------------------------------------------------------------------------
9145
--- Alter tables with month(col1)
9146
-------------------------------------------------------------------------
9147
drop table if exists t11 ;
9148
drop table if exists t22 ;
9149
drop table if exists t33 ;
9150
drop table if exists t44 ;
9151
drop table if exists t55 ;
9152
drop table if exists t66 ;
9153
create table t11 engine='INNODB' as select * from t1;
9154
create table t22 engine='INNODB' as select * from t2;
9155
create table t33 engine='INNODB' as select * from t3;
9156
create table t44 engine='INNODB' as select * from t4;
9157
create table t55 engine='INNODB' as select * from t5;
9158
create table t66 engine='INNODB' as select * from t6;
9160
partition by range(month(col1))
9161
(partition p0 values less than (15),
9162
partition p1 values less than maxvalue);
9164
partition by list(month(col1))
9165
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9166
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9167
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9168
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9169
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9170
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9173
partition by hash(month(col1));
9175
partition by range(colint)
9176
subpartition by hash(month(col1)) subpartitions 2
9177
(partition p0 values less than (15),
9178
partition p1 values less than maxvalue);
9180
partition by list(colint)
9181
subpartition by hash(month(col1)) subpartitions 2
9182
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9183
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9184
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9185
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9186
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9187
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9190
partition by range(colint)
9191
(partition p0 values less than (month('2006-10-14')),
9192
partition p1 values less than maxvalue);
9193
select * from t11 order by col1;
9197
select * from t22 order by col1;
9202
select * from t33 order by col1;
9207
select * from t44 order by colint;
9213
select * from t55 order by colint;
9219
select * from t66 order by colint;
9225
---------------------------
9226
---- some alter table begin
9227
---------------------------
9229
reorganize partition p0,p1 into
9230
(partition s1 values less than maxvalue);
9231
select * from t11 order by col1;
9236
reorganize partition s1 into
9237
(partition p0 values less than (15),
9238
partition p1 values less than maxvalue);
9239
select * from t11 order by col1;
9244
partition by list(colint)
9245
subpartition by hash(month(col1)) subpartitions 5
9246
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9247
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9248
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9249
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9250
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9251
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9253
show create table t55;
9255
t55 CREATE TABLE `t55` (
9256
`colint` int(11) DEFAULT NULL,
9257
`col1` date DEFAULT NULL
9258
) ENGINE=InnoDB DEFAULT CHARSET=latin1
9259
/*!50100 PARTITION BY LIST (colint)
9260
SUBPARTITION BY HASH (month(col1))
9262
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
9263
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
9264
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
9265
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
9266
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
9267
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
9268
select * from t55 order by colint;
9275
reorganize partition p0,p1 into
9276
(partition s1 values less than maxvalue);
9277
select * from t66 order by colint;
9284
reorganize partition s1 into
9285
(partition p0 values less than (month('2006-10-14')),
9286
partition p1 values less than maxvalue);
9287
select * from t66 order by colint;
9294
reorganize partition p0,p1 into
9295
(partition s1 values less than maxvalue);
9296
select * from t66 order by colint;
9303
reorganize partition s1 into
9304
(partition p0 values less than (month('2006-10-14')),
9305
partition p1 values less than maxvalue);
9306
select * from t66 order by colint;
9312
-------------------------------------------------------------------------
9313
--- Delete rows and partitions of tables with month(col1)
9314
-------------------------------------------------------------------------
9315
delete from t1 where col1='2006-12-17';
9316
delete from t2 where col1='2006-12-17';
9317
delete from t3 where col1='2006-12-17';
9318
delete from t4 where col1='2006-12-17';
9319
delete from t5 where col1='2006-12-17';
9320
delete from t6 where col1='2006-12-17';
9321
select * from t1 order by col1;
9324
select * from t2 order by col1;
9328
select * from t3 order by col1;
9332
select * from t4 order by colint;
9338
select * from t5 order by colint;
9344
insert into t1 values ('2006-12-17');
9345
insert into t2 values ('2006-12-17');
9346
insert into t3 values ('2006-12-17');
9347
insert into t4 values (60,'2006-12-17');
9348
insert into t5 values (60,'2006-12-17');
9349
insert into t6 values (60,'2006-12-17');
9350
select * from t1 order by col1;
9354
select * from t2 order by col1;
9359
select * from t3 order by col1;
9364
select * from t4 order by colint;
9371
select * from t5 order by colint;
9378
select * from t6 order by colint;
9385
alter table t1 drop partition p0;
9386
alter table t2 drop partition p0;
9387
alter table t4 drop partition p0;
9388
alter table t5 drop partition p0;
9389
alter table t6 drop partition p0;
9390
select * from t1 order by col1;
9392
select * from t2 order by col1;
9396
select * from t3 order by col1;
9401
select * from t4 order by colint;
9404
select * from t5 order by colint;
9407
select * from t6 order by colint;
9410
-------------------------------------------------------------------------
9411
--- Delete rows and partitions of tables with month(col1)
9412
-------------------------------------------------------------------------
9413
delete from t11 where col1='2006-12-17';
9414
delete from t22 where col1='2006-12-17';
9415
delete from t33 where col1='2006-12-17';
9416
delete from t44 where col1='2006-12-17';
9417
delete from t55 where col1='2006-12-17';
9418
delete from t66 where col1='2006-12-17';
9419
select * from t11 order by col1;
9422
select * from t22 order by col1;
9426
select * from t33 order by col1;
9430
select * from t44 order by colint;
9436
select * from t55 order by colint;
9442
insert into t11 values ('2006-12-17');
9443
insert into t22 values ('2006-12-17');
9444
insert into t33 values ('2006-12-17');
9445
insert into t44 values (60,'2006-12-17');
9446
insert into t55 values (60,'2006-12-17');
9447
insert into t66 values (60,'2006-12-17');
9448
select * from t11 order by col1;
9452
select * from t22 order by col1;
9457
select * from t33 order by col1;
9462
select * from t44 order by colint;
9469
select * from t55 order by colint;
9476
select * from t66 order by colint;
9483
alter table t11 drop partition p0;
9484
alter table t22 drop partition p0;
9485
alter table t44 drop partition p0;
9486
alter table t55 drop partition p0;
9487
alter table t66 drop partition p0;
9488
select * from t11 order by col1;
9490
select * from t22 order by col1;
9494
select * from t33 order by col1;
9499
select * from t44 order by colint;
9502
select * from t55 order by colint;
9505
select * from t66 order by colint;
9508
-------------------------
9509
---- some alter table end
9510
-------------------------
9511
drop table if exists t1 ;
9512
drop table if exists t2 ;
9513
drop table if exists t3 ;
9514
drop table if exists t4 ;
9515
drop table if exists t5 ;
9516
drop table if exists t6 ;
9517
drop table if exists t11 ;
9518
drop table if exists t22 ;
9519
drop table if exists t33 ;
9520
drop table if exists t44 ;
9521
drop table if exists t55 ;
9522
drop table if exists t66 ;
9523
-------------------------------------------------------------------------
9524
--- quarter(col1) in partition with coltype date
9525
-------------------------------------------------------------------------
9526
drop table if exists t1 ;
9527
drop table if exists t2 ;
9528
drop table if exists t3 ;
9529
drop table if exists t4 ;
9530
drop table if exists t5 ;
9531
drop table if exists t6 ;
9532
-------------------------------------------------------------------------
9533
--- Create tables with quarter(col1)
9534
-------------------------------------------------------------------------
9535
create table t1 (col1 date) engine='INNODB'
9536
partition by range(quarter(col1))
9537
(partition p0 values less than (15),
9538
partition p1 values less than maxvalue);
9539
create table t2 (col1 date) engine='INNODB'
9540
partition by list(quarter(col1))
9541
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9542
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9543
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9544
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9545
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9546
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9548
create table t3 (col1 date) engine='INNODB'
9549
partition by hash(quarter(col1));
9550
create table t4 (colint int, col1 date) engine='INNODB'
9551
partition by range(colint)
9552
subpartition by hash(quarter(col1)) subpartitions 2
9553
(partition p0 values less than (15),
9554
partition p1 values less than maxvalue);
9555
create table t5 (colint int, col1 date) engine='INNODB'
9556
partition by list(colint)
9557
subpartition by hash(quarter(col1)) subpartitions 2
9558
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9559
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9560
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9561
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9562
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9563
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9565
create table t6 (colint int, col1 date) engine='INNODB'
9566
partition by range(colint)
9567
(partition p0 values less than (quarter('2006-10-14')),
9568
partition p1 values less than maxvalue);
9569
-------------------------------------------------------------------------
9570
--- Access tables with quarter(col1)
9571
-------------------------------------------------------------------------
9572
insert into t1 values ('2006-01-03');
9573
insert into t1 values ('2006-12-17');
9574
insert into t2 values ('2006-01-03');
9575
insert into t2 values ('2006-12-17');
9576
insert into t2 values ('2006-09-25');
9577
insert into t3 values ('2006-01-03');
9578
insert into t3 values ('2006-12-17');
9579
insert into t3 values ('2006-09-25');
9580
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
9581
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
9582
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
9583
select quarter(col1) from t1 order by col1;
9587
select * from t1 order by col1;
9591
select * from t2 order by col1;
9596
select * from t3 order by col1;
9601
select * from t4 order by colint;
9607
select * from t5 order by colint;
9613
select * from t6 order by colint;
9619
update t1 set col1='2006-07-30' where col1='2006-01-03';
9620
update t2 set col1='2006-07-30' where col1='2006-01-03';
9621
update t3 set col1='2006-07-30' where col1='2006-01-03';
9622
update t4 set col1='2006-07-30' where col1='2006-01-03';
9623
update t5 set col1='2006-07-30' where col1='2006-01-03';
9624
update t6 set col1='2006-07-30' where col1='2006-01-03';
9625
select * from t1 order by col1;
9629
select * from t2 order by col1;
9634
select * from t3 order by col1;
9639
select * from t4 order by colint;
9645
select * from t5 order by colint;
9651
select * from t6 order by colint;
9657
-------------------------------------------------------------------------
9658
--- Alter tables with quarter(col1)
9659
-------------------------------------------------------------------------
9660
drop table if exists t11 ;
9661
drop table if exists t22 ;
9662
drop table if exists t33 ;
9663
drop table if exists t44 ;
9664
drop table if exists t55 ;
9665
drop table if exists t66 ;
9666
create table t11 engine='INNODB' as select * from t1;
9667
create table t22 engine='INNODB' as select * from t2;
9668
create table t33 engine='INNODB' as select * from t3;
9669
create table t44 engine='INNODB' as select * from t4;
9670
create table t55 engine='INNODB' as select * from t5;
9671
create table t66 engine='INNODB' as select * from t6;
9673
partition by range(quarter(col1))
9674
(partition p0 values less than (15),
9675
partition p1 values less than maxvalue);
9677
partition by list(quarter(col1))
9678
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9679
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9680
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9681
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9682
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9683
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9686
partition by hash(quarter(col1));
9688
partition by range(colint)
9689
subpartition by hash(quarter(col1)) subpartitions 2
9690
(partition p0 values less than (15),
9691
partition p1 values less than maxvalue);
9693
partition by list(colint)
9694
subpartition by hash(quarter(col1)) subpartitions 2
9695
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9696
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9697
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9698
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9699
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9700
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9703
partition by range(colint)
9704
(partition p0 values less than (quarter('2006-10-14')),
9705
partition p1 values less than maxvalue);
9706
select * from t11 order by col1;
9710
select * from t22 order by col1;
9715
select * from t33 order by col1;
9720
select * from t44 order by colint;
9726
select * from t55 order by colint;
9732
select * from t66 order by colint;
9738
---------------------------
9739
---- some alter table begin
9740
---------------------------
9742
reorganize partition p0,p1 into
9743
(partition s1 values less than maxvalue);
9744
select * from t11 order by col1;
9749
reorganize partition s1 into
9750
(partition p0 values less than (15),
9751
partition p1 values less than maxvalue);
9752
select * from t11 order by col1;
9757
partition by list(colint)
9758
subpartition by hash(quarter(col1)) subpartitions 5
9759
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
9760
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9761
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9762
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9763
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9764
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9766
show create table t55;
9768
t55 CREATE TABLE `t55` (
9769
`colint` int(11) DEFAULT NULL,
9770
`col1` date DEFAULT NULL
9771
) ENGINE=InnoDB DEFAULT CHARSET=latin1
9772
/*!50100 PARTITION BY LIST (colint)
9773
SUBPARTITION BY HASH (quarter(col1))
9775
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
9776
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
9777
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
9778
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
9779
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
9780
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
9781
select * from t55 order by colint;
9788
reorganize partition p0,p1 into
9789
(partition s1 values less than maxvalue);
9790
select * from t66 order by colint;
9797
reorganize partition s1 into
9798
(partition p0 values less than (quarter('2006-10-14')),
9799
partition p1 values less than maxvalue);
9800
select * from t66 order by colint;
9807
reorganize partition p0,p1 into
9808
(partition s1 values less than maxvalue);
9809
select * from t66 order by colint;
9816
reorganize partition s1 into
9817
(partition p0 values less than (quarter('2006-10-14')),
9818
partition p1 values less than maxvalue);
9819
select * from t66 order by colint;
9825
-------------------------------------------------------------------------
9826
--- Delete rows and partitions of tables with quarter(col1)
9827
-------------------------------------------------------------------------
9828
delete from t1 where col1='2006-12-17';
9829
delete from t2 where col1='2006-12-17';
9830
delete from t3 where col1='2006-12-17';
9831
delete from t4 where col1='2006-12-17';
9832
delete from t5 where col1='2006-12-17';
9833
delete from t6 where col1='2006-12-17';
9834
select * from t1 order by col1;
9837
select * from t2 order by col1;
9841
select * from t3 order by col1;
9845
select * from t4 order by colint;
9851
select * from t5 order by colint;
9857
insert into t1 values ('2006-12-17');
9858
insert into t2 values ('2006-12-17');
9859
insert into t3 values ('2006-12-17');
9860
insert into t4 values (60,'2006-12-17');
9861
insert into t5 values (60,'2006-12-17');
9862
insert into t6 values (60,'2006-12-17');
9863
select * from t1 order by col1;
9867
select * from t2 order by col1;
9872
select * from t3 order by col1;
9877
select * from t4 order by colint;
9884
select * from t5 order by colint;
9891
select * from t6 order by colint;
9898
alter table t1 drop partition p0;
9899
alter table t2 drop partition p0;
9900
alter table t4 drop partition p0;
9901
alter table t5 drop partition p0;
9902
alter table t6 drop partition p0;
9903
select * from t1 order by col1;
9905
select * from t2 order by col1;
9907
select * from t3 order by col1;
9912
select * from t4 order by colint;
9915
select * from t5 order by colint;
9918
select * from t6 order by colint;
9922
-------------------------------------------------------------------------
9923
--- Delete rows and partitions of tables with quarter(col1)
9924
-------------------------------------------------------------------------
9925
delete from t11 where col1='2006-12-17';
9926
delete from t22 where col1='2006-12-17';
9927
delete from t33 where col1='2006-12-17';
9928
delete from t44 where col1='2006-12-17';
9929
delete from t55 where col1='2006-12-17';
9930
delete from t66 where col1='2006-12-17';
9931
select * from t11 order by col1;
9934
select * from t22 order by col1;
9938
select * from t33 order by col1;
9942
select * from t44 order by colint;
9948
select * from t55 order by colint;
9954
insert into t11 values ('2006-12-17');
9955
insert into t22 values ('2006-12-17');
9956
insert into t33 values ('2006-12-17');
9957
insert into t44 values (60,'2006-12-17');
9958
insert into t55 values (60,'2006-12-17');
9959
insert into t66 values (60,'2006-12-17');
9960
select * from t11 order by col1;
9964
select * from t22 order by col1;
9969
select * from t33 order by col1;
9974
select * from t44 order by colint;
9981
select * from t55 order by colint;
9988
select * from t66 order by colint;
9995
alter table t11 drop partition p0;
9996
alter table t22 drop partition p0;
9997
alter table t44 drop partition p0;
9998
alter table t55 drop partition p0;
9999
alter table t66 drop partition p0;
10000
select * from t11 order by col1;
10002
select * from t22 order by col1;
10004
select * from t33 order by col1;
10009
select * from t44 order by colint;
10012
select * from t55 order by colint;
10015
select * from t66 order by colint;
10019
-------------------------
10020
---- some alter table end
10021
-------------------------
10022
drop table if exists t1 ;
10023
drop table if exists t2 ;
10024
drop table if exists t3 ;
10025
drop table if exists t4 ;
10026
drop table if exists t5 ;
10027
drop table if exists t6 ;
10028
drop table if exists t11 ;
10029
drop table if exists t22 ;
10030
drop table if exists t33 ;
10031
drop table if exists t44 ;
10032
drop table if exists t55 ;
10033
drop table if exists t66 ;
10034
-------------------------------------------------------------------------
10035
--- time_to_sec(col1)-(time_to_sec(col1)-20) in partition with coltype time
10036
-------------------------------------------------------------------------
10037
drop table if exists t1 ;
10038
drop table if exists t2 ;
10039
drop table if exists t3 ;
10040
drop table if exists t4 ;
10041
drop table if exists t5 ;
10042
drop table if exists t6 ;
10043
-------------------------------------------------------------------------
10044
--- Create tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10045
-------------------------------------------------------------------------
10046
create table t1 (col1 time) engine='INNODB'
10047
partition by range(time_to_sec(col1)-(time_to_sec(col1)-20))
10048
(partition p0 values less than (15),
10049
partition p1 values less than maxvalue);
10050
create table t2 (col1 time) engine='INNODB'
10051
partition by list(time_to_sec(col1)-(time_to_sec(col1)-20))
10052
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10053
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10054
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10055
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10056
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10057
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10059
create table t3 (col1 time) engine='INNODB'
10060
partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20));
10061
create table t4 (colint int, col1 time) engine='INNODB'
10062
partition by range(colint)
10063
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
10064
(partition p0 values less than (15),
10065
partition p1 values less than maxvalue);
10066
create table t5 (colint int, col1 time) engine='INNODB'
10067
partition by list(colint)
10068
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
10069
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10070
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10071
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10072
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10073
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10074
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10076
create table t6 (colint int, col1 time) engine='INNODB'
10077
partition by range(colint)
10078
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10079
partition p1 values less than maxvalue);
10080
-------------------------------------------------------------------------
10081
--- Access tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10082
-------------------------------------------------------------------------
10083
insert into t1 values ('09:09:15');
10084
insert into t1 values ('14:30:45');
10085
insert into t2 values ('09:09:15');
10086
insert into t2 values ('14:30:45');
10087
insert into t2 values ('21:59:22');
10088
insert into t3 values ('09:09:15');
10089
insert into t3 values ('14:30:45');
10090
insert into t3 values ('21:59:22');
10091
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
10092
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
10093
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
10094
select time_to_sec(col1)-(time_to_sec(col1)-20) from t1 order by col1;
10095
time_to_sec(col1)-(time_to_sec(col1)-20)
10098
select * from t1 order by col1;
10102
select * from t2 order by col1;
10107
select * from t3 order by col1;
10112
select * from t4 order by colint;
10118
select * from t5 order by colint;
10124
select * from t6 order by colint;
10130
update t1 set col1='10:33:11' where col1='09:09:15';
10131
update t2 set col1='10:33:11' where col1='09:09:15';
10132
update t3 set col1='10:33:11' where col1='09:09:15';
10133
update t4 set col1='10:33:11' where col1='09:09:15';
10134
update t5 set col1='10:33:11' where col1='09:09:15';
10135
update t6 set col1='10:33:11' where col1='09:09:15';
10136
select * from t1 order by col1;
10140
select * from t2 order by col1;
10145
select * from t3 order by col1;
10150
select * from t4 order by colint;
10156
select * from t5 order by colint;
10162
select * from t6 order by colint;
10168
-------------------------------------------------------------------------
10169
--- Alter tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10170
-------------------------------------------------------------------------
10171
drop table if exists t11 ;
10172
drop table if exists t22 ;
10173
drop table if exists t33 ;
10174
drop table if exists t44 ;
10175
drop table if exists t55 ;
10176
drop table if exists t66 ;
10177
create table t11 engine='INNODB' as select * from t1;
10178
create table t22 engine='INNODB' as select * from t2;
10179
create table t33 engine='INNODB' as select * from t3;
10180
create table t44 engine='INNODB' as select * from t4;
10181
create table t55 engine='INNODB' as select * from t5;
10182
create table t66 engine='INNODB' as select * from t6;
10184
partition by range(time_to_sec(col1)-(time_to_sec(col1)-20))
10185
(partition p0 values less than (15),
10186
partition p1 values less than maxvalue);
10188
partition by list(time_to_sec(col1)-(time_to_sec(col1)-20))
10189
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10190
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10191
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10192
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10193
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10194
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10197
partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20));
10199
partition by range(colint)
10200
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
10201
(partition p0 values less than (15),
10202
partition p1 values less than maxvalue);
10204
partition by list(colint)
10205
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
10206
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10207
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10208
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10209
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10210
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10211
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10214
partition by range(colint)
10215
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10216
partition p1 values less than maxvalue);
10217
select * from t11 order by col1;
10221
select * from t22 order by col1;
10226
select * from t33 order by col1;
10231
select * from t44 order by colint;
10237
select * from t55 order by colint;
10243
select * from t66 order by colint;
10249
---------------------------
10250
---- some alter table begin
10251
---------------------------
10253
reorganize partition p0,p1 into
10254
(partition s1 values less than maxvalue);
10255
select * from t11 order by col1;
10260
reorganize partition s1 into
10261
(partition p0 values less than (15),
10262
partition p1 values less than maxvalue);
10263
select * from t11 order by col1;
10268
partition by list(colint)
10269
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 5
10270
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10271
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10272
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10273
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10274
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10275
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10277
show create table t55;
10279
t55 CREATE TABLE `t55` (
10280
`colint` int(11) DEFAULT NULL,
10281
`col1` time DEFAULT NULL
10282
) ENGINE=InnoDB DEFAULT CHARSET=latin1
10283
/*!50100 PARTITION BY LIST (colint)
10284
SUBPARTITION BY HASH (time_to_sec(col1)-(time_to_sec(col1)-20))
10286
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
10287
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
10288
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
10289
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
10290
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
10291
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
10292
select * from t55 order by colint;
10299
reorganize partition p0,p1 into
10300
(partition s1 values less than maxvalue);
10301
select * from t66 order by colint;
10308
reorganize partition s1 into
10309
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10310
partition p1 values less than maxvalue);
10311
select * from t66 order by colint;
10318
reorganize partition p0,p1 into
10319
(partition s1 values less than maxvalue);
10320
select * from t66 order by colint;
10327
reorganize partition s1 into
10328
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
10329
partition p1 values less than maxvalue);
10330
select * from t66 order by colint;
10336
-------------------------------------------------------------------------
10337
--- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10338
-------------------------------------------------------------------------
10339
delete from t1 where col1='14:30:45';
10340
delete from t2 where col1='14:30:45';
10341
delete from t3 where col1='14:30:45';
10342
delete from t4 where col1='14:30:45';
10343
delete from t5 where col1='14:30:45';
10344
delete from t6 where col1='14:30:45';
10345
select * from t1 order by col1;
10348
select * from t2 order by col1;
10352
select * from t3 order by col1;
10356
select * from t4 order by colint;
10362
select * from t5 order by colint;
10368
insert into t1 values ('14:30:45');
10369
insert into t2 values ('14:30:45');
10370
insert into t3 values ('14:30:45');
10371
insert into t4 values (60,'14:30:45');
10372
insert into t5 values (60,'14:30:45');
10373
insert into t6 values (60,'14:30:45');
10374
select * from t1 order by col1;
10378
select * from t2 order by col1;
10383
select * from t3 order by col1;
10388
select * from t4 order by colint;
10395
select * from t5 order by colint;
10402
select * from t6 order by colint;
10409
alter table t1 drop partition p0;
10410
alter table t2 drop partition p0;
10411
alter table t4 drop partition p0;
10412
alter table t5 drop partition p0;
10413
alter table t6 drop partition p0;
10414
select * from t1 order by col1;
10418
select * from t2 order by col1;
10423
select * from t3 order by col1;
10428
select * from t4 order by colint;
10431
select * from t5 order by colint;
10434
select * from t6 order by colint;
10436
-------------------------------------------------------------------------
10437
--- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20)
10438
-------------------------------------------------------------------------
10439
delete from t11 where col1='14:30:45';
10440
delete from t22 where col1='14:30:45';
10441
delete from t33 where col1='14:30:45';
10442
delete from t44 where col1='14:30:45';
10443
delete from t55 where col1='14:30:45';
10444
delete from t66 where col1='14:30:45';
10445
select * from t11 order by col1;
10448
select * from t22 order by col1;
10452
select * from t33 order by col1;
10456
select * from t44 order by colint;
10462
select * from t55 order by colint;
10468
insert into t11 values ('14:30:45');
10469
insert into t22 values ('14:30:45');
10470
insert into t33 values ('14:30:45');
10471
insert into t44 values (60,'14:30:45');
10472
insert into t55 values (60,'14:30:45');
10473
insert into t66 values (60,'14:30:45');
10474
select * from t11 order by col1;
10478
select * from t22 order by col1;
10483
select * from t33 order by col1;
10488
select * from t44 order by colint;
10495
select * from t55 order by colint;
10502
select * from t66 order by colint;
10509
alter table t11 drop partition p0;
10510
alter table t22 drop partition p0;
10511
alter table t44 drop partition p0;
10512
alter table t55 drop partition p0;
10513
alter table t66 drop partition p0;
10514
select * from t11 order by col1;
10518
select * from t22 order by col1;
10523
select * from t33 order by col1;
10528
select * from t44 order by colint;
10531
select * from t55 order by colint;
10534
select * from t66 order by colint;
10536
-------------------------
10537
---- some alter table end
10538
-------------------------
10539
drop table if exists t1 ;
10540
drop table if exists t2 ;
10541
drop table if exists t3 ;
10542
drop table if exists t4 ;
10543
drop table if exists t5 ;
10544
drop table if exists t6 ;
10545
drop table if exists t11 ;
10546
drop table if exists t22 ;
10547
drop table if exists t33 ;
10548
drop table if exists t44 ;
10549
drop table if exists t55 ;
10550
drop table if exists t66 ;
10551
-------------------------------------------------------------------------
10552
--- to_days(col1)-to_days('2006-01-01') in partition with coltype date
10553
-------------------------------------------------------------------------
10554
drop table if exists t1 ;
10555
drop table if exists t2 ;
10556
drop table if exists t3 ;
10557
drop table if exists t4 ;
10558
drop table if exists t5 ;
10559
drop table if exists t6 ;
10560
-------------------------------------------------------------------------
10561
--- Create tables with to_days(col1)-to_days('2006-01-01')
10562
-------------------------------------------------------------------------
10563
create table t1 (col1 date) engine='INNODB'
10564
partition by range(to_days(col1)-to_days('2006-01-01'))
10565
(partition p0 values less than (15),
10566
partition p1 values less than maxvalue);
10567
create table t2 (col1 date) engine='INNODB'
10568
partition by list(to_days(col1)-to_days('2006-01-01'))
10569
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10570
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10571
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10572
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10573
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10574
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10576
create table t3 (col1 date) engine='INNODB'
10577
partition by hash(to_days(col1)-to_days('2006-01-01'));
10578
create table t4 (colint int, col1 date) engine='INNODB'
10579
partition by range(colint)
10580
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10581
(partition p0 values less than (15),
10582
partition p1 values less than maxvalue);
10583
create table t5 (colint int, col1 date) engine='INNODB'
10584
partition by list(colint)
10585
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10586
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10587
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10588
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10589
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10590
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10591
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10593
create table t6 (colint int, col1 date) engine='INNODB'
10594
partition by range(colint)
10595
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10596
partition p1 values less than maxvalue);
10597
-------------------------------------------------------------------------
10598
--- Access tables with to_days(col1)-to_days('2006-01-01')
10599
-------------------------------------------------------------------------
10600
insert into t1 values ('2006-02-03');
10601
insert into t1 values ('2006-01-17');
10602
insert into t2 values ('2006-02-03');
10603
insert into t2 values ('2006-01-17');
10604
insert into t2 values ('2006-01-25');
10605
insert into t3 values ('2006-02-03');
10606
insert into t3 values ('2006-01-17');
10607
insert into t3 values ('2006-01-25');
10608
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
10609
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
10610
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
10611
select to_days(col1)-to_days('2006-01-01') from t1 order by col1;
10612
to_days(col1)-to_days('2006-01-01')
10615
select * from t1 order by col1;
10619
select * from t2 order by col1;
10624
select * from t3 order by col1;
10629
select * from t4 order by colint;
10635
select * from t5 order by colint;
10641
select * from t6 order by colint;
10647
update t1 set col1='2006-02-06' where col1='2006-02-03';
10648
update t2 set col1='2006-02-06' where col1='2006-02-03';
10649
update t3 set col1='2006-02-06' where col1='2006-02-03';
10650
update t4 set col1='2006-02-06' where col1='2006-02-03';
10651
update t5 set col1='2006-02-06' where col1='2006-02-03';
10652
update t6 set col1='2006-02-06' where col1='2006-02-03';
10653
select * from t1 order by col1;
10657
select * from t2 order by col1;
10662
select * from t3 order by col1;
10667
select * from t4 order by colint;
10673
select * from t5 order by colint;
10679
select * from t6 order by colint;
10685
-------------------------------------------------------------------------
10686
--- Alter tables with to_days(col1)-to_days('2006-01-01')
10687
-------------------------------------------------------------------------
10688
drop table if exists t11 ;
10689
drop table if exists t22 ;
10690
drop table if exists t33 ;
10691
drop table if exists t44 ;
10692
drop table if exists t55 ;
10693
drop table if exists t66 ;
10694
create table t11 engine='INNODB' as select * from t1;
10695
create table t22 engine='INNODB' as select * from t2;
10696
create table t33 engine='INNODB' as select * from t3;
10697
create table t44 engine='INNODB' as select * from t4;
10698
create table t55 engine='INNODB' as select * from t5;
10699
create table t66 engine='INNODB' as select * from t6;
10701
partition by range(to_days(col1)-to_days('2006-01-01'))
10702
(partition p0 values less than (15),
10703
partition p1 values less than maxvalue);
10705
partition by list(to_days(col1)-to_days('2006-01-01'))
10706
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10707
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10708
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10709
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10710
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10711
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10714
partition by hash(to_days(col1)-to_days('2006-01-01'));
10716
partition by range(colint)
10717
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10718
(partition p0 values less than (15),
10719
partition p1 values less than maxvalue);
10721
partition by list(colint)
10722
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10723
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10724
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10725
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10726
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10727
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10728
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10731
partition by range(colint)
10732
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10733
partition p1 values less than maxvalue);
10734
select * from t11 order by col1;
10738
select * from t22 order by col1;
10743
select * from t33 order by col1;
10748
select * from t44 order by colint;
10754
select * from t55 order by colint;
10760
select * from t66 order by colint;
10766
---------------------------
10767
---- some alter table begin
10768
---------------------------
10770
reorganize partition p0,p1 into
10771
(partition s1 values less than maxvalue);
10772
select * from t11 order by col1;
10777
reorganize partition s1 into
10778
(partition p0 values less than (15),
10779
partition p1 values less than maxvalue);
10780
select * from t11 order by col1;
10785
partition by list(colint)
10786
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 5
10787
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10788
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10789
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10790
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10791
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10792
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10794
show create table t55;
10796
t55 CREATE TABLE `t55` (
10797
`colint` int(11) DEFAULT NULL,
10798
`col1` date DEFAULT NULL
10799
) ENGINE=InnoDB DEFAULT CHARSET=latin1
10800
/*!50100 PARTITION BY LIST (colint)
10801
SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01'))
10803
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
10804
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
10805
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
10806
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
10807
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
10808
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
10809
select * from t55 order by colint;
10816
reorganize partition p0,p1 into
10817
(partition s1 values less than maxvalue);
10818
select * from t66 order by colint;
10825
reorganize partition s1 into
10826
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10827
partition p1 values less than maxvalue);
10828
select * from t66 order by colint;
10835
reorganize partition p0,p1 into
10836
(partition s1 values less than maxvalue);
10837
select * from t66 order by colint;
10844
reorganize partition s1 into
10845
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10846
partition p1 values less than maxvalue);
10847
select * from t66 order by colint;
10853
-------------------------------------------------------------------------
10854
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
10855
-------------------------------------------------------------------------
10856
delete from t1 where col1='2006-01-17';
10857
delete from t2 where col1='2006-01-17';
10858
delete from t3 where col1='2006-01-17';
10859
delete from t4 where col1='2006-01-17';
10860
delete from t5 where col1='2006-01-17';
10861
delete from t6 where col1='2006-01-17';
10862
select * from t1 order by col1;
10865
select * from t2 order by col1;
10869
select * from t3 order by col1;
10873
select * from t4 order by colint;
10878
select * from t5 order by colint;
10883
insert into t1 values ('2006-01-17');
10884
insert into t2 values ('2006-01-17');
10885
insert into t3 values ('2006-01-17');
10886
insert into t4 values (60,'2006-01-17');
10887
insert into t5 values (60,'2006-01-17');
10888
insert into t6 values (60,'2006-01-17');
10889
select * from t1 order by col1;
10893
select * from t2 order by col1;
10898
select * from t3 order by col1;
10903
select * from t4 order by colint;
10909
select * from t5 order by colint;
10915
select * from t6 order by colint;
10921
alter table t1 drop partition p0;
10922
alter table t2 drop partition p0;
10923
alter table t4 drop partition p0;
10924
alter table t5 drop partition p0;
10925
alter table t6 drop partition p0;
10926
select * from t1 order by col1;
10930
select * from t2 order by col1;
10935
select * from t3 order by col1;
10940
select * from t4 order by colint;
10943
select * from t5 order by colint;
10946
select * from t6 order by colint;
10949
-------------------------------------------------------------------------
10950
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
10951
-------------------------------------------------------------------------
10952
delete from t11 where col1='2006-01-17';
10953
delete from t22 where col1='2006-01-17';
10954
delete from t33 where col1='2006-01-17';
10955
delete from t44 where col1='2006-01-17';
10956
delete from t55 where col1='2006-01-17';
10957
delete from t66 where col1='2006-01-17';
10958
select * from t11 order by col1;
10961
select * from t22 order by col1;
10965
select * from t33 order by col1;
10969
select * from t44 order by colint;
10974
select * from t55 order by colint;
10979
insert into t11 values ('2006-01-17');
10980
insert into t22 values ('2006-01-17');
10981
insert into t33 values ('2006-01-17');
10982
insert into t44 values (60,'2006-01-17');
10983
insert into t55 values (60,'2006-01-17');
10984
insert into t66 values (60,'2006-01-17');
10985
select * from t11 order by col1;
10989
select * from t22 order by col1;
10994
select * from t33 order by col1;
10999
select * from t44 order by colint;
11005
select * from t55 order by colint;
11011
select * from t66 order by colint;
11017
alter table t11 drop partition p0;
11018
alter table t22 drop partition p0;
11019
alter table t44 drop partition p0;
11020
alter table t55 drop partition p0;
11021
alter table t66 drop partition p0;
11022
select * from t11 order by col1;
11026
select * from t22 order by col1;
11031
select * from t33 order by col1;
11036
select * from t44 order by colint;
11039
select * from t55 order by colint;
11042
select * from t66 order by colint;
11045
-------------------------
11046
---- some alter table end
11047
-------------------------
11048
drop table if exists t1 ;
11049
drop table if exists t2 ;
11050
drop table if exists t3 ;
11051
drop table if exists t4 ;
11052
drop table if exists t5 ;
11053
drop table if exists t6 ;
11054
drop table if exists t11 ;
11055
drop table if exists t22 ;
11056
drop table if exists t33 ;
11057
drop table if exists t44 ;
11058
drop table if exists t55 ;
11059
drop table if exists t66 ;
11060
-------------------------------------------------------------------------
11061
--- datediff(col1, '2006-01-01') in partition with coltype date
11062
-------------------------------------------------------------------------
11063
drop table if exists t1 ;
11064
drop table if exists t2 ;
11065
drop table if exists t3 ;
11066
drop table if exists t4 ;
11067
drop table if exists t5 ;
11068
drop table if exists t6 ;
11069
-------------------------------------------------------------------------
11070
--- Create tables with datediff(col1, '2006-01-01')
11071
-------------------------------------------------------------------------
11072
create table t1 (col1 date) engine='INNODB'
11073
partition by range(datediff(col1, '2006-01-01'))
11074
(partition p0 values less than (15),
11075
partition p1 values less than maxvalue);
11076
create table t2 (col1 date) engine='INNODB'
11077
partition by list(datediff(col1, '2006-01-01'))
11078
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11079
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11080
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11081
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11082
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11083
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11085
create table t3 (col1 date) engine='INNODB'
11086
partition by hash(datediff(col1, '2006-01-01'));
11087
create table t4 (colint int, col1 date) engine='INNODB'
11088
partition by range(colint)
11089
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11090
(partition p0 values less than (15),
11091
partition p1 values less than maxvalue);
11092
create table t5 (colint int, col1 date) engine='INNODB'
11093
partition by list(colint)
11094
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11095
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11096
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11097
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11098
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11099
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11100
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11102
create table t6 (colint int, col1 date) engine='INNODB'
11103
partition by range(colint)
11104
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11105
partition p1 values less than maxvalue);
11106
-------------------------------------------------------------------------
11107
--- Access tables with datediff(col1, '2006-01-01')
11108
-------------------------------------------------------------------------
11109
insert into t1 values ('2006-02-03');
11110
insert into t1 values ('2006-01-17');
11111
insert into t2 values ('2006-02-03');
11112
insert into t2 values ('2006-01-17');
11113
insert into t2 values ('2006-01-25');
11114
insert into t3 values ('2006-02-03');
11115
insert into t3 values ('2006-01-17');
11116
insert into t3 values ('2006-01-25');
11117
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
11118
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
11119
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
11120
select datediff(col1, '2006-01-01') from t1 order by col1;
11121
datediff(col1, '2006-01-01')
11124
select * from t1 order by col1;
11128
select * from t2 order by col1;
11133
select * from t3 order by col1;
11138
select * from t4 order by colint;
11144
select * from t5 order by colint;
11150
select * from t6 order by colint;
11156
update t1 set col1='2006-02-06' where col1='2006-02-03';
11157
update t2 set col1='2006-02-06' where col1='2006-02-03';
11158
update t3 set col1='2006-02-06' where col1='2006-02-03';
11159
update t4 set col1='2006-02-06' where col1='2006-02-03';
11160
update t5 set col1='2006-02-06' where col1='2006-02-03';
11161
update t6 set col1='2006-02-06' where col1='2006-02-03';
11162
select * from t1 order by col1;
11166
select * from t2 order by col1;
11171
select * from t3 order by col1;
11176
select * from t4 order by colint;
11182
select * from t5 order by colint;
11188
select * from t6 order by colint;
11194
-------------------------------------------------------------------------
11195
--- Alter tables with datediff(col1, '2006-01-01')
11196
-------------------------------------------------------------------------
11197
drop table if exists t11 ;
11198
drop table if exists t22 ;
11199
drop table if exists t33 ;
11200
drop table if exists t44 ;
11201
drop table if exists t55 ;
11202
drop table if exists t66 ;
11203
create table t11 engine='INNODB' as select * from t1;
11204
create table t22 engine='INNODB' as select * from t2;
11205
create table t33 engine='INNODB' as select * from t3;
11206
create table t44 engine='INNODB' as select * from t4;
11207
create table t55 engine='INNODB' as select * from t5;
11208
create table t66 engine='INNODB' as select * from t6;
11210
partition by range(datediff(col1, '2006-01-01'))
11211
(partition p0 values less than (15),
11212
partition p1 values less than maxvalue);
11214
partition by list(datediff(col1, '2006-01-01'))
11215
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11216
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11217
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11218
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11219
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11220
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11223
partition by hash(datediff(col1, '2006-01-01'));
11225
partition by range(colint)
11226
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11227
(partition p0 values less than (15),
11228
partition p1 values less than maxvalue);
11230
partition by list(colint)
11231
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11232
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11233
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11234
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11235
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11236
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11237
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11240
partition by range(colint)
11241
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11242
partition p1 values less than maxvalue);
11243
select * from t11 order by col1;
11247
select * from t22 order by col1;
11252
select * from t33 order by col1;
11257
select * from t44 order by colint;
11263
select * from t55 order by colint;
11269
select * from t66 order by colint;
11275
---------------------------
11276
---- some alter table begin
11277
---------------------------
11279
reorganize partition p0,p1 into
11280
(partition s1 values less than maxvalue);
11281
select * from t11 order by col1;
11286
reorganize partition s1 into
11287
(partition p0 values less than (15),
11288
partition p1 values less than maxvalue);
11289
select * from t11 order by col1;
11294
partition by list(colint)
11295
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 5
11296
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11297
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11298
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11299
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11300
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11301
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11303
show create table t55;
11305
t55 CREATE TABLE `t55` (
11306
`colint` int(11) DEFAULT NULL,
11307
`col1` date DEFAULT NULL
11308
) ENGINE=InnoDB DEFAULT CHARSET=latin1
11309
/*!50100 PARTITION BY LIST (colint)
11310
SUBPARTITION BY HASH (datediff(col1, '2006-01-01'))
11312
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
11313
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
11314
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
11315
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
11316
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
11317
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
11318
select * from t55 order by colint;
11325
reorganize partition p0,p1 into
11326
(partition s1 values less than maxvalue);
11327
select * from t66 order by colint;
11334
reorganize partition s1 into
11335
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11336
partition p1 values less than maxvalue);
11337
select * from t66 order by colint;
11344
reorganize partition p0,p1 into
11345
(partition s1 values less than maxvalue);
11346
select * from t66 order by colint;
11353
reorganize partition s1 into
11354
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11355
partition p1 values less than maxvalue);
11356
select * from t66 order by colint;
11362
-------------------------------------------------------------------------
11363
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
11364
-------------------------------------------------------------------------
11365
delete from t1 where col1='2006-01-17';
11366
delete from t2 where col1='2006-01-17';
11367
delete from t3 where col1='2006-01-17';
11368
delete from t4 where col1='2006-01-17';
11369
delete from t5 where col1='2006-01-17';
11370
delete from t6 where col1='2006-01-17';
11371
select * from t1 order by col1;
11374
select * from t2 order by col1;
11378
select * from t3 order by col1;
11382
select * from t4 order by colint;
11387
select * from t5 order by colint;
11392
insert into t1 values ('2006-01-17');
11393
insert into t2 values ('2006-01-17');
11394
insert into t3 values ('2006-01-17');
11395
insert into t4 values (60,'2006-01-17');
11396
insert into t5 values (60,'2006-01-17');
11397
insert into t6 values (60,'2006-01-17');
11398
select * from t1 order by col1;
11402
select * from t2 order by col1;
11407
select * from t3 order by col1;
11412
select * from t4 order by colint;
11418
select * from t5 order by colint;
11424
select * from t6 order by colint;
11430
alter table t1 drop partition p0;
11431
alter table t2 drop partition p0;
11432
alter table t4 drop partition p0;
11433
alter table t5 drop partition p0;
11434
alter table t6 drop partition p0;
11435
select * from t1 order by col1;
11439
select * from t2 order by col1;
11444
select * from t3 order by col1;
11449
select * from t4 order by colint;
11452
select * from t5 order by colint;
11455
select * from t6 order by colint;
11458
-------------------------------------------------------------------------
11459
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
11460
-------------------------------------------------------------------------
11461
delete from t11 where col1='2006-01-17';
11462
delete from t22 where col1='2006-01-17';
11463
delete from t33 where col1='2006-01-17';
11464
delete from t44 where col1='2006-01-17';
11465
delete from t55 where col1='2006-01-17';
11466
delete from t66 where col1='2006-01-17';
11467
select * from t11 order by col1;
11470
select * from t22 order by col1;
11474
select * from t33 order by col1;
11478
select * from t44 order by colint;
11483
select * from t55 order by colint;
11488
insert into t11 values ('2006-01-17');
11489
insert into t22 values ('2006-01-17');
11490
insert into t33 values ('2006-01-17');
11491
insert into t44 values (60,'2006-01-17');
11492
insert into t55 values (60,'2006-01-17');
11493
insert into t66 values (60,'2006-01-17');
11494
select * from t11 order by col1;
11498
select * from t22 order by col1;
11503
select * from t33 order by col1;
11508
select * from t44 order by colint;
11514
select * from t55 order by colint;
11520
select * from t66 order by colint;
11526
alter table t11 drop partition p0;
11527
alter table t22 drop partition p0;
11528
alter table t44 drop partition p0;
11529
alter table t55 drop partition p0;
11530
alter table t66 drop partition p0;
11531
select * from t11 order by col1;
11535
select * from t22 order by col1;
11540
select * from t33 order by col1;
11545
select * from t44 order by colint;
11548
select * from t55 order by colint;
11551
select * from t66 order by colint;
11554
-------------------------
11555
---- some alter table end
11556
-------------------------
11557
drop table if exists t1 ;
11558
drop table if exists t2 ;
11559
drop table if exists t3 ;
11560
drop table if exists t4 ;
11561
drop table if exists t5 ;
11562
drop table if exists t6 ;
11563
drop table if exists t11 ;
11564
drop table if exists t22 ;
11565
drop table if exists t33 ;
11566
drop table if exists t44 ;
11567
drop table if exists t55 ;
11568
drop table if exists t66 ;
11569
-------------------------------------------------------------------------
11570
--- weekday(col1) in partition with coltype date
11571
-------------------------------------------------------------------------
11572
drop table if exists t1 ;
11573
drop table if exists t2 ;
11574
drop table if exists t3 ;
11575
drop table if exists t4 ;
11576
drop table if exists t5 ;
11577
drop table if exists t6 ;
11578
-------------------------------------------------------------------------
11579
--- Create tables with weekday(col1)
11580
-------------------------------------------------------------------------
11581
create table t1 (col1 date) engine='INNODB'
11582
partition by range(weekday(col1))
11583
(partition p0 values less than (15),
11584
partition p1 values less than maxvalue);
11585
create table t2 (col1 date) engine='INNODB'
11586
partition by list(weekday(col1))
11587
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11588
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11589
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11590
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11591
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11592
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11594
create table t3 (col1 date) engine='INNODB'
11595
partition by hash(weekday(col1));
11596
create table t4 (colint int, col1 date) engine='INNODB'
11597
partition by range(colint)
11598
subpartition by hash(weekday(col1)) subpartitions 2
11599
(partition p0 values less than (15),
11600
partition p1 values less than maxvalue);
11601
create table t5 (colint int, col1 date) engine='INNODB'
11602
partition by list(colint)
11603
subpartition by hash(weekday(col1)) subpartitions 2
11604
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11605
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11606
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11607
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11608
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11609
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11611
create table t6 (colint int, col1 date) engine='INNODB'
11612
partition by range(colint)
11613
(partition p0 values less than (weekday('2006-10-14')),
11614
partition p1 values less than maxvalue);
11615
-------------------------------------------------------------------------
11616
--- Access tables with weekday(col1)
11617
-------------------------------------------------------------------------
11618
insert into t1 values ('2006-12-03');
11619
insert into t1 values ('2006-11-17');
11620
insert into t2 values ('2006-12-03');
11621
insert into t2 values ('2006-11-17');
11622
insert into t2 values ('2006-05-25');
11623
insert into t3 values ('2006-12-03');
11624
insert into t3 values ('2006-11-17');
11625
insert into t3 values ('2006-05-25');
11626
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
11627
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
11628
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
11629
select weekday(col1) from t1 order by col1;
11633
select * from t1 order by col1;
11637
select * from t2 order by col1;
11642
select * from t3 order by col1;
11647
select * from t4 order by colint;
11653
select * from t5 order by colint;
11659
select * from t6 order by colint;
11665
update t1 set col1='2006-02-06' where col1='2006-12-03';
11666
update t2 set col1='2006-02-06' where col1='2006-12-03';
11667
update t3 set col1='2006-02-06' where col1='2006-12-03';
11668
update t4 set col1='2006-02-06' where col1='2006-12-03';
11669
update t5 set col1='2006-02-06' where col1='2006-12-03';
11670
update t6 set col1='2006-02-06' where col1='2006-12-03';
11671
select * from t1 order by col1;
11675
select * from t2 order by col1;
11680
select * from t3 order by col1;
11685
select * from t4 order by colint;
11691
select * from t5 order by colint;
11697
select * from t6 order by colint;
11703
-------------------------------------------------------------------------
11704
--- Alter tables with weekday(col1)
11705
-------------------------------------------------------------------------
11706
drop table if exists t11 ;
11707
drop table if exists t22 ;
11708
drop table if exists t33 ;
11709
drop table if exists t44 ;
11710
drop table if exists t55 ;
11711
drop table if exists t66 ;
11712
create table t11 engine='INNODB' as select * from t1;
11713
create table t22 engine='INNODB' as select * from t2;
11714
create table t33 engine='INNODB' as select * from t3;
11715
create table t44 engine='INNODB' as select * from t4;
11716
create table t55 engine='INNODB' as select * from t5;
11717
create table t66 engine='INNODB' as select * from t6;
11719
partition by range(weekday(col1))
11720
(partition p0 values less than (15),
11721
partition p1 values less than maxvalue);
11723
partition by list(weekday(col1))
11724
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11725
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11726
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11727
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11728
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11729
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11732
partition by hash(weekday(col1));
11734
partition by range(colint)
11735
subpartition by hash(weekday(col1)) subpartitions 2
11736
(partition p0 values less than (15),
11737
partition p1 values less than maxvalue);
11739
partition by list(colint)
11740
subpartition by hash(weekday(col1)) subpartitions 2
11741
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11742
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11743
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11744
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11745
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11746
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11749
partition by range(colint)
11750
(partition p0 values less than (weekday('2006-10-14')),
11751
partition p1 values less than maxvalue);
11752
select * from t11 order by col1;
11756
select * from t22 order by col1;
11761
select * from t33 order by col1;
11766
select * from t44 order by colint;
11772
select * from t55 order by colint;
11778
select * from t66 order by colint;
11784
---------------------------
11785
---- some alter table begin
11786
---------------------------
11788
reorganize partition p0,p1 into
11789
(partition s1 values less than maxvalue);
11790
select * from t11 order by col1;
11795
reorganize partition s1 into
11796
(partition p0 values less than (15),
11797
partition p1 values less than maxvalue);
11798
select * from t11 order by col1;
11803
partition by list(colint)
11804
subpartition by hash(weekday(col1)) subpartitions 5
11805
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11806
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11807
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11808
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11809
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11810
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11812
show create table t55;
11814
t55 CREATE TABLE `t55` (
11815
`colint` int(11) DEFAULT NULL,
11816
`col1` date DEFAULT NULL
11817
) ENGINE=InnoDB DEFAULT CHARSET=latin1
11818
/*!50100 PARTITION BY LIST (colint)
11819
SUBPARTITION BY HASH (weekday(col1))
11821
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
11822
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
11823
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
11824
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
11825
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
11826
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
11827
select * from t55 order by colint;
11834
reorganize partition p0,p1 into
11835
(partition s1 values less than maxvalue);
11836
select * from t66 order by colint;
11843
reorganize partition s1 into
11844
(partition p0 values less than (weekday('2006-10-14')),
11845
partition p1 values less than maxvalue);
11846
select * from t66 order by colint;
11853
reorganize partition p0,p1 into
11854
(partition s1 values less than maxvalue);
11855
select * from t66 order by colint;
11862
reorganize partition s1 into
11863
(partition p0 values less than (weekday('2006-10-14')),
11864
partition p1 values less than maxvalue);
11865
select * from t66 order by colint;
11871
-------------------------------------------------------------------------
11872
--- Delete rows and partitions of tables with weekday(col1)
11873
-------------------------------------------------------------------------
11874
delete from t1 where col1='2006-11-17';
11875
delete from t2 where col1='2006-11-17';
11876
delete from t3 where col1='2006-11-17';
11877
delete from t4 where col1='2006-11-17';
11878
delete from t5 where col1='2006-11-17';
11879
delete from t6 where col1='2006-11-17';
11880
select * from t1 order by col1;
11883
select * from t2 order by col1;
11887
select * from t3 order by col1;
11891
select * from t4 order by colint;
11897
select * from t5 order by colint;
11903
insert into t1 values ('2006-11-17');
11904
insert into t2 values ('2006-11-17');
11905
insert into t3 values ('2006-11-17');
11906
insert into t4 values (60,'2006-11-17');
11907
insert into t5 values (60,'2006-11-17');
11908
insert into t6 values (60,'2006-11-17');
11909
select * from t1 order by col1;
11913
select * from t2 order by col1;
11918
select * from t3 order by col1;
11923
select * from t4 order by colint;
11930
select * from t5 order by colint;
11937
select * from t6 order by colint;
11944
alter table t1 drop partition p0;
11945
alter table t2 drop partition p0;
11946
alter table t4 drop partition p0;
11947
alter table t5 drop partition p0;
11948
alter table t6 drop partition p0;
11949
select * from t1 order by col1;
11951
select * from t2 order by col1;
11953
select * from t3 order by col1;
11958
select * from t4 order by colint;
11961
select * from t5 order by colint;
11964
select * from t6 order by colint;
11967
-------------------------------------------------------------------------
11968
--- Delete rows and partitions of tables with weekday(col1)
11969
-------------------------------------------------------------------------
11970
delete from t11 where col1='2006-11-17';
11971
delete from t22 where col1='2006-11-17';
11972
delete from t33 where col1='2006-11-17';
11973
delete from t44 where col1='2006-11-17';
11974
delete from t55 where col1='2006-11-17';
11975
delete from t66 where col1='2006-11-17';
11976
select * from t11 order by col1;
11979
select * from t22 order by col1;
11983
select * from t33 order by col1;
11987
select * from t44 order by colint;
11993
select * from t55 order by colint;
11999
insert into t11 values ('2006-11-17');
12000
insert into t22 values ('2006-11-17');
12001
insert into t33 values ('2006-11-17');
12002
insert into t44 values (60,'2006-11-17');
12003
insert into t55 values (60,'2006-11-17');
12004
insert into t66 values (60,'2006-11-17');
12005
select * from t11 order by col1;
12009
select * from t22 order by col1;
12014
select * from t33 order by col1;
12019
select * from t44 order by colint;
12026
select * from t55 order by colint;
12033
select * from t66 order by colint;
12040
alter table t11 drop partition p0;
12041
alter table t22 drop partition p0;
12042
alter table t44 drop partition p0;
12043
alter table t55 drop partition p0;
12044
alter table t66 drop partition p0;
12045
select * from t11 order by col1;
12047
select * from t22 order by col1;
12049
select * from t33 order by col1;
12054
select * from t44 order by colint;
12057
select * from t55 order by colint;
12060
select * from t66 order by colint;
12063
-------------------------
12064
---- some alter table end
12065
-------------------------
12066
drop table if exists t1 ;
12067
drop table if exists t2 ;
12068
drop table if exists t3 ;
12069
drop table if exists t4 ;
12070
drop table if exists t5 ;
12071
drop table if exists t6 ;
12072
drop table if exists t11 ;
12073
drop table if exists t22 ;
12074
drop table if exists t33 ;
12075
drop table if exists t44 ;
12076
drop table if exists t55 ;
12077
drop table if exists t66 ;
12078
-------------------------------------------------------------------------
12079
--- year(col1)-1990 in partition with coltype date
12080
-------------------------------------------------------------------------
12081
drop table if exists t1 ;
12082
drop table if exists t2 ;
12083
drop table if exists t3 ;
12084
drop table if exists t4 ;
12085
drop table if exists t5 ;
12086
drop table if exists t6 ;
12087
-------------------------------------------------------------------------
12088
--- Create tables with year(col1)-1990
12089
-------------------------------------------------------------------------
12090
create table t1 (col1 date) engine='INNODB'
12091
partition by range(year(col1)-1990)
12092
(partition p0 values less than (15),
12093
partition p1 values less than maxvalue);
12094
create table t2 (col1 date) engine='INNODB'
12095
partition by list(year(col1)-1990)
12096
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12097
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12098
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12099
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12100
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12101
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12103
create table t3 (col1 date) engine='INNODB'
12104
partition by hash(year(col1)-1990);
12105
create table t4 (colint int, col1 date) engine='INNODB'
12106
partition by range(colint)
12107
subpartition by hash(year(col1)-1990) subpartitions 2
12108
(partition p0 values less than (15),
12109
partition p1 values less than maxvalue);
12110
create table t5 (colint int, col1 date) engine='INNODB'
12111
partition by list(colint)
12112
subpartition by hash(year(col1)-1990) subpartitions 2
12113
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12114
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12115
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12116
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12117
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12118
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12120
create table t6 (colint int, col1 date) engine='INNODB'
12121
partition by range(colint)
12122
(partition p0 values less than (year('2005-10-14')-1990),
12123
partition p1 values less than maxvalue);
12124
-------------------------------------------------------------------------
12125
--- Access tables with year(col1)-1990
12126
-------------------------------------------------------------------------
12127
insert into t1 values ('1996-01-03');
12128
insert into t1 values ('2000-02-17');
12129
insert into t2 values ('1996-01-03');
12130
insert into t2 values ('2000-02-17');
12131
insert into t2 values ('2004-05-25');
12132
insert into t3 values ('1996-01-03');
12133
insert into t3 values ('2000-02-17');
12134
insert into t3 values ('2004-05-25');
12135
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
12136
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
12137
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
12138
select year(col1)-1990 from t1 order by col1;
12142
select * from t1 order by col1;
12146
select * from t2 order by col1;
12151
select * from t3 order by col1;
12156
select * from t4 order by colint;
12162
select * from t5 order by colint;
12168
select * from t6 order by colint;
12174
update t1 set col1='2002-02-15' where col1='1996-01-03';
12175
update t2 set col1='2002-02-15' where col1='1996-01-03';
12176
update t3 set col1='2002-02-15' where col1='1996-01-03';
12177
update t4 set col1='2002-02-15' where col1='1996-01-03';
12178
update t5 set col1='2002-02-15' where col1='1996-01-03';
12179
update t6 set col1='2002-02-15' where col1='1996-01-03';
12180
select * from t1 order by col1;
12184
select * from t2 order by col1;
12189
select * from t3 order by col1;
12194
select * from t4 order by colint;
12200
select * from t5 order by colint;
12206
select * from t6 order by colint;
12212
-------------------------------------------------------------------------
12213
--- Alter tables with year(col1)-1990
12214
-------------------------------------------------------------------------
12215
drop table if exists t11 ;
12216
drop table if exists t22 ;
12217
drop table if exists t33 ;
12218
drop table if exists t44 ;
12219
drop table if exists t55 ;
12220
drop table if exists t66 ;
12221
create table t11 engine='INNODB' as select * from t1;
12222
create table t22 engine='INNODB' as select * from t2;
12223
create table t33 engine='INNODB' as select * from t3;
12224
create table t44 engine='INNODB' as select * from t4;
12225
create table t55 engine='INNODB' as select * from t5;
12226
create table t66 engine='INNODB' as select * from t6;
12228
partition by range(year(col1)-1990)
12229
(partition p0 values less than (15),
12230
partition p1 values less than maxvalue);
12232
partition by list(year(col1)-1990)
12233
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12234
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12235
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12236
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12237
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12238
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12241
partition by hash(year(col1)-1990);
12243
partition by range(colint)
12244
subpartition by hash(year(col1)-1990) subpartitions 2
12245
(partition p0 values less than (15),
12246
partition p1 values less than maxvalue);
12248
partition by list(colint)
12249
subpartition by hash(year(col1)-1990) subpartitions 2
12250
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12251
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12252
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12253
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12254
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12255
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12258
partition by range(colint)
12259
(partition p0 values less than (year('2005-10-14')-1990),
12260
partition p1 values less than maxvalue);
12261
select * from t11 order by col1;
12265
select * from t22 order by col1;
12270
select * from t33 order by col1;
12275
select * from t44 order by colint;
12281
select * from t55 order by colint;
12287
select * from t66 order by colint;
12293
---------------------------
12294
---- some alter table begin
12295
---------------------------
12297
reorganize partition p0,p1 into
12298
(partition s1 values less than maxvalue);
12299
select * from t11 order by col1;
12304
reorganize partition s1 into
12305
(partition p0 values less than (15),
12306
partition p1 values less than maxvalue);
12307
select * from t11 order by col1;
12312
partition by list(colint)
12313
subpartition by hash(year(col1)-1990) subpartitions 5
12314
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12315
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12316
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12317
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12318
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12319
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12321
show create table t55;
12323
t55 CREATE TABLE `t55` (
12324
`colint` int(11) DEFAULT NULL,
12325
`col1` date DEFAULT NULL
12326
) ENGINE=InnoDB DEFAULT CHARSET=latin1
12327
/*!50100 PARTITION BY LIST (colint)
12328
SUBPARTITION BY HASH (year(col1)-1990)
12330
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
12331
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
12332
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
12333
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
12334
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
12335
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
12336
select * from t55 order by colint;
12343
reorganize partition p0,p1 into
12344
(partition s1 values less than maxvalue);
12345
select * from t66 order by colint;
12352
reorganize partition s1 into
12353
(partition p0 values less than (year('2005-10-14')-1990),
12354
partition p1 values less than maxvalue);
12355
select * from t66 order by colint;
12362
reorganize partition p0,p1 into
12363
(partition s1 values less than maxvalue);
12364
select * from t66 order by colint;
12371
reorganize partition s1 into
12372
(partition p0 values less than (year('2005-10-14')-1990),
12373
partition p1 values less than maxvalue);
12374
select * from t66 order by colint;
12380
-------------------------------------------------------------------------
12381
--- Delete rows and partitions of tables with year(col1)-1990
12382
-------------------------------------------------------------------------
12383
delete from t1 where col1='2000-02-17';
12384
delete from t2 where col1='2000-02-17';
12385
delete from t3 where col1='2000-02-17';
12386
delete from t4 where col1='2000-02-17';
12387
delete from t5 where col1='2000-02-17';
12388
delete from t6 where col1='2000-02-17';
12389
select * from t1 order by col1;
12392
select * from t2 order by col1;
12396
select * from t3 order by col1;
12400
select * from t4 order by colint;
12406
select * from t5 order by colint;
12412
insert into t1 values ('2000-02-17');
12413
insert into t2 values ('2000-02-17');
12414
insert into t3 values ('2000-02-17');
12415
insert into t4 values (60,'2000-02-17');
12416
insert into t5 values (60,'2000-02-17');
12417
insert into t6 values (60,'2000-02-17');
12418
select * from t1 order by col1;
12422
select * from t2 order by col1;
12427
select * from t3 order by col1;
12432
select * from t4 order by colint;
12439
select * from t5 order by colint;
12446
select * from t6 order by colint;
12453
alter table t1 drop partition p0;
12454
alter table t2 drop partition p0;
12455
alter table t4 drop partition p0;
12456
alter table t5 drop partition p0;
12457
alter table t6 drop partition p0;
12458
select * from t1 order by col1;
12460
select * from t2 order by col1;
12464
select * from t3 order by col1;
12469
select * from t4 order by colint;
12472
select * from t5 order by colint;
12475
select * from t6 order by colint;
12478
-------------------------------------------------------------------------
12479
--- Delete rows and partitions of tables with year(col1)-1990
12480
-------------------------------------------------------------------------
12481
delete from t11 where col1='2000-02-17';
12482
delete from t22 where col1='2000-02-17';
12483
delete from t33 where col1='2000-02-17';
12484
delete from t44 where col1='2000-02-17';
12485
delete from t55 where col1='2000-02-17';
12486
delete from t66 where col1='2000-02-17';
12487
select * from t11 order by col1;
12490
select * from t22 order by col1;
12494
select * from t33 order by col1;
12498
select * from t44 order by colint;
12504
select * from t55 order by colint;
12510
insert into t11 values ('2000-02-17');
12511
insert into t22 values ('2000-02-17');
12512
insert into t33 values ('2000-02-17');
12513
insert into t44 values (60,'2000-02-17');
12514
insert into t55 values (60,'2000-02-17');
12515
insert into t66 values (60,'2000-02-17');
12516
select * from t11 order by col1;
12520
select * from t22 order by col1;
12525
select * from t33 order by col1;
12530
select * from t44 order by colint;
12537
select * from t55 order by colint;
12544
select * from t66 order by colint;
12551
alter table t11 drop partition p0;
12552
alter table t22 drop partition p0;
12553
alter table t44 drop partition p0;
12554
alter table t55 drop partition p0;
12555
alter table t66 drop partition p0;
12556
select * from t11 order by col1;
12558
select * from t22 order by col1;
12562
select * from t33 order by col1;
12567
select * from t44 order by colint;
12570
select * from t55 order by colint;
12573
select * from t66 order by colint;
12576
-------------------------
12577
---- some alter table end
12578
-------------------------
12579
drop table if exists t1 ;
12580
drop table if exists t2 ;
12581
drop table if exists t3 ;
12582
drop table if exists t4 ;
12583
drop table if exists t5 ;
12584
drop table if exists t6 ;
12585
drop table if exists t11 ;
12586
drop table if exists t22 ;
12587
drop table if exists t33 ;
12588
drop table if exists t44 ;
12589
drop table if exists t55 ;
12590
drop table if exists t66 ;
12591
-------------------------------------------------------------------------
12592
--- yearweek(col1)-200600 in partition with coltype date
12593
-------------------------------------------------------------------------
12594
drop table if exists t1 ;
12595
drop table if exists t2 ;
12596
drop table if exists t3 ;
12597
drop table if exists t4 ;
12598
drop table if exists t5 ;
12599
drop table if exists t6 ;
12600
-------------------------------------------------------------------------
12601
--- Create tables with yearweek(col1)-200600
12602
-------------------------------------------------------------------------
12603
create table t1 (col1 date) engine='INNODB'
12604
partition by range(yearweek(col1)-200600)
12605
(partition p0 values less than (15),
12606
partition p1 values less than maxvalue);
12607
create table t2 (col1 date) engine='INNODB'
12608
partition by list(yearweek(col1)-200600)
12609
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12610
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12611
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12612
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12613
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12614
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12616
create table t3 (col1 date) engine='INNODB'
12617
partition by hash(yearweek(col1)-200600);
12618
create table t4 (colint int, col1 date) engine='INNODB'
12619
partition by range(colint)
12620
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12621
(partition p0 values less than (15),
12622
partition p1 values less than maxvalue);
12623
create table t5 (colint int, col1 date) engine='INNODB'
12624
partition by list(colint)
12625
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12626
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12627
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12628
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12629
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12630
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12631
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12633
create table t6 (colint int, col1 date) engine='INNODB'
12634
partition by range(colint)
12635
(partition p0 values less than (yearweek('2006-10-14')-200600),
12636
partition p1 values less than maxvalue);
12637
-------------------------------------------------------------------------
12638
--- Access tables with yearweek(col1)-200600
12639
-------------------------------------------------------------------------
12640
insert into t1 values ('2006-01-03');
12641
insert into t1 values ('2006-08-17');
12642
insert into t2 values ('2006-01-03');
12643
insert into t2 values ('2006-08-17');
12644
insert into t2 values ('2006-03-25');
12645
insert into t3 values ('2006-01-03');
12646
insert into t3 values ('2006-08-17');
12647
insert into t3 values ('2006-03-25');
12648
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
12649
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
12650
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
12651
select yearweek(col1)-200600 from t1 order by col1;
12652
yearweek(col1)-200600
12655
select * from t1 order by col1;
12659
select * from t2 order by col1;
12664
select * from t3 order by col1;
12669
select * from t4 order by colint;
12675
select * from t5 order by colint;
12681
select * from t6 order by colint;
12687
update t1 set col1='2006-11-15' where col1='2006-01-03';
12688
update t2 set col1='2006-11-15' where col1='2006-01-03';
12689
update t3 set col1='2006-11-15' where col1='2006-01-03';
12690
update t4 set col1='2006-11-15' where col1='2006-01-03';
12691
update t5 set col1='2006-11-15' where col1='2006-01-03';
12692
update t6 set col1='2006-11-15' where col1='2006-01-03';
12693
select * from t1 order by col1;
12697
select * from t2 order by col1;
12702
select * from t3 order by col1;
12707
select * from t4 order by colint;
12713
select * from t5 order by colint;
12719
select * from t6 order by colint;
12725
-------------------------------------------------------------------------
12726
--- Alter tables with yearweek(col1)-200600
12727
-------------------------------------------------------------------------
12728
drop table if exists t11 ;
12729
drop table if exists t22 ;
12730
drop table if exists t33 ;
12731
drop table if exists t44 ;
12732
drop table if exists t55 ;
12733
drop table if exists t66 ;
12734
create table t11 engine='INNODB' as select * from t1;
12735
create table t22 engine='INNODB' as select * from t2;
12736
create table t33 engine='INNODB' as select * from t3;
12737
create table t44 engine='INNODB' as select * from t4;
12738
create table t55 engine='INNODB' as select * from t5;
12739
create table t66 engine='INNODB' as select * from t6;
12741
partition by range(yearweek(col1)-200600)
12742
(partition p0 values less than (15),
12743
partition p1 values less than maxvalue);
12745
partition by list(yearweek(col1)-200600)
12746
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12747
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12748
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12749
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12750
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12751
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12754
partition by hash(yearweek(col1)-200600);
12756
partition by range(colint)
12757
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12758
(partition p0 values less than (15),
12759
partition p1 values less than maxvalue);
12761
partition by list(colint)
12762
subpartition by hash(yearweek(col1)-200600) subpartitions 2
12763
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12764
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12765
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12766
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12767
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12768
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12771
partition by range(colint)
12772
(partition p0 values less than (yearweek('2006-10-14')-200600),
12773
partition p1 values less than maxvalue);
12774
select * from t11 order by col1;
12778
select * from t22 order by col1;
12783
select * from t33 order by col1;
12788
select * from t44 order by colint;
12794
select * from t55 order by colint;
12800
select * from t66 order by colint;
12806
---------------------------
12807
---- some alter table begin
12808
---------------------------
12810
reorganize partition p0,p1 into
12811
(partition s1 values less than maxvalue);
12812
select * from t11 order by col1;
12817
reorganize partition s1 into
12818
(partition p0 values less than (15),
12819
partition p1 values less than maxvalue);
12820
select * from t11 order by col1;
12825
partition by list(colint)
12826
subpartition by hash(yearweek(col1)-200600) subpartitions 5
12827
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12828
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12829
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12830
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12831
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12832
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12834
show create table t55;
12836
t55 CREATE TABLE `t55` (
12837
`colint` int(11) DEFAULT NULL,
12838
`col1` date DEFAULT NULL
12839
) ENGINE=InnoDB DEFAULT CHARSET=latin1
12840
/*!50100 PARTITION BY LIST (colint)
12841
SUBPARTITION BY HASH (yearweek(col1)-200600)
12843
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
12844
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
12845
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
12846
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
12847
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB,
12848
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */
12849
select * from t55 order by colint;
12856
reorganize partition p0,p1 into
12857
(partition s1 values less than maxvalue);
12858
select * from t66 order by colint;
12865
reorganize partition s1 into
12866
(partition p0 values less than (yearweek('2006-10-14')-200600),
12867
partition p1 values less than maxvalue);
12868
select * from t66 order by colint;
12875
reorganize partition p0,p1 into
12876
(partition s1 values less than maxvalue);
12877
select * from t66 order by colint;
12884
reorganize partition s1 into
12885
(partition p0 values less than (yearweek('2006-10-14')-200600),
12886
partition p1 values less than maxvalue);
12887
select * from t66 order by colint;
12893
-------------------------------------------------------------------------
12894
--- Delete rows and partitions of tables with yearweek(col1)-200600
12895
-------------------------------------------------------------------------
12896
delete from t1 where col1='2006-08-17';
12897
delete from t2 where col1='2006-08-17';
12898
delete from t3 where col1='2006-08-17';
12899
delete from t4 where col1='2006-08-17';
12900
delete from t5 where col1='2006-08-17';
12901
delete from t6 where col1='2006-08-17';
12902
select * from t1 order by col1;
12905
select * from t2 order by col1;
12909
select * from t3 order by col1;
12913
select * from t4 order by colint;
12919
select * from t5 order by colint;
12925
insert into t1 values ('2006-08-17');
12926
insert into t2 values ('2006-08-17');
12927
insert into t3 values ('2006-08-17');
12928
insert into t4 values (60,'2006-08-17');
12929
insert into t5 values (60,'2006-08-17');
12930
insert into t6 values (60,'2006-08-17');
12931
select * from t1 order by col1;
12935
select * from t2 order by col1;
12940
select * from t3 order by col1;
12945
select * from t4 order by colint;
12952
select * from t5 order by colint;
12959
select * from t6 order by colint;
12966
alter table t1 drop partition p0;
12967
alter table t2 drop partition p0;
12968
alter table t4 drop partition p0;
12969
alter table t5 drop partition p0;
12970
alter table t6 drop partition p0;
12971
select * from t1 order by col1;
12975
select * from t2 order by col1;
12980
select * from t3 order by col1;
12985
select * from t4 order by colint;
12988
select * from t5 order by colint;
12991
select * from t6 order by colint;
12994
-------------------------------------------------------------------------
12995
--- Delete rows and partitions of tables with yearweek(col1)-200600
12996
-------------------------------------------------------------------------
12997
delete from t11 where col1='2006-08-17';
12998
delete from t22 where col1='2006-08-17';
12999
delete from t33 where col1='2006-08-17';
13000
delete from t44 where col1='2006-08-17';
13001
delete from t55 where col1='2006-08-17';
13002
delete from t66 where col1='2006-08-17';
13003
select * from t11 order by col1;
13006
select * from t22 order by col1;
13010
select * from t33 order by col1;
13014
select * from t44 order by colint;
13020
select * from t55 order by colint;
13026
insert into t11 values ('2006-08-17');
13027
insert into t22 values ('2006-08-17');
13028
insert into t33 values ('2006-08-17');
13029
insert into t44 values (60,'2006-08-17');
13030
insert into t55 values (60,'2006-08-17');
13031
insert into t66 values (60,'2006-08-17');
13032
select * from t11 order by col1;
13036
select * from t22 order by col1;
13041
select * from t33 order by col1;
13046
select * from t44 order by colint;
13053
select * from t55 order by colint;
13060
select * from t66 order by colint;
13067
alter table t11 drop partition p0;
13068
alter table t22 drop partition p0;
13069
alter table t44 drop partition p0;
13070
alter table t55 drop partition p0;
13071
alter table t66 drop partition p0;
13072
select * from t11 order by col1;
13076
select * from t22 order by col1;
13081
select * from t33 order by col1;
13086
select * from t44 order by colint;
13089
select * from t55 order by colint;
13092
select * from t66 order by colint;
13095
-------------------------
13096
---- some alter table end
13097
-------------------------
13098
drop table if exists t1 ;
13099
drop table if exists t2 ;
13100
drop table if exists t3 ;
13101
drop table if exists t4 ;
13102
drop table if exists t5 ;
13103
drop table if exists t6 ;
13104
drop table if exists t11 ;
13105
drop table if exists t22 ;
13106
drop table if exists t33 ;
13107
drop table if exists t44 ;
13108
drop table if exists t55 ;
13109
drop table if exists t66 ;
added
removed
mysql-test/suite/parts/r/part_supported_sql_func_innodb.result
