Edit File: part_supported_sql_func_innodb.result
------------------------------------------------------------------------- --- abs(col1) in partition with coltype int ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with abs(col1) ------------------------------------------------------------------------- create table t1 (col1 int) engine='INNODB' partition by range(abs(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 int) engine='INNODB' partition by list(abs(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 int) engine='INNODB' partition by hash(abs(col1)); create table t4 (colint int, col1 int) engine='INNODB' partition by range(colint) subpartition by hash(abs(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 int) engine='INNODB' partition by list(colint) subpartition by hash(abs(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 int) engine='INNODB' partition by range(colint) (partition p0 values less than (abs(15)), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with abs(col1) ------------------------------------------------------------------------- insert into t1 values (5 ); insert into t1 values (13 ); insert into t2 values (5 ); insert into t2 values (13 ); insert into t2 values (17 ); insert into t3 values (5 ); insert into t3 values (13 ); insert into t3 values (17 ); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t6; select abs(col1) from t1 order by col1; abs(col1) 5 13 select * from t1 order by col1; col1 5 13 select * from t2 order by col1; col1 5 13 17 select * from t3 order by col1; col1 5 13 17 select * from t4 order by colint; colint col1 1 5 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 5 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t5 order by colint; colint col1 1 5 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 5 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t6 order by colint; colint col1 1 5 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 5 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 update t1 set col1=15 where col1=5 ; update t2 set col1=15 where col1=5 ; update t3 set col1=15 where col1=5 ; update t4 set col1=15 where col1=5 ; update t5 set col1=15 where col1=5 ; update t6 set col1=15 where col1=5 ; select * from t1 order by col1; col1 13 15 select * from t2 order by col1; col1 13 15 17 select * from t3 order by col1; col1 13 15 17 select * from t4 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t5 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t6 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 ------------------------------------------------------------------------- --- Alter tables with abs(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(abs(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(abs(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(abs(col1)); alter table t44 partition by range(colint) subpartition by hash(abs(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(abs(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (abs(15)), partition p1 values less than maxvalue); select * from t11 order by col1; col1 13 15 select * from t22 order by col1; col1 13 15 17 select * from t33 order by col1; col1 13 15 17 select * from t44 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t55 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 13 15 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 13 15 alter table t55 partition by list(colint) subpartition by hash(abs(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (abs(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition s1 into (partition p0 values less than (abs(15)), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition s1 into (partition p0 values less than (abs(15)), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with abs(col1) ------------------------------------------------------------------------- delete from t1 where col1=13 ; delete from t2 where col1=13 ; delete from t3 where col1=13 ; delete from t4 where col1=13 ; delete from t5 where col1=13 ; delete from t6 where col1=13 ; select * from t1 order by col1; col1 15 select * from t2 order by col1; col1 15 17 select * from t3 order by col1; col1 15 17 select * from t4 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t5 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 insert into t1 values (13 ); insert into t2 values (13 ); insert into t3 values (13 ); insert into t4 values (60,13 ); insert into t5 values (60,13 ); insert into t6 values (60,13 ); select * from t1 order by col1; col1 13 15 select * from t2 order by col1; col1 13 15 17 select * from t3 order by col1; col1 13 15 17 select * from t4 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t5 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t6 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 15 select * from t2 order by col1; col1 13 15 17 select * from t3 order by col1; col1 13 15 17 select * from t4 order by colint; colint col1 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t5 order by colint; colint col1 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t6 order by colint; colint col1 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with abs(col1) ------------------------------------------------------------------------- delete from t11 where col1=13 ; delete from t22 where col1=13 ; delete from t33 where col1=13 ; delete from t44 where col1=13 ; delete from t55 where col1=13 ; delete from t66 where col1=13 ; select * from t11 order by col1; col1 15 select * from t22 order by col1; col1 15 17 select * from t33 order by col1; col1 15 17 select * from t44 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t55 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 insert into t11 values (13 ); insert into t22 values (13 ); insert into t33 values (13 ); insert into t44 values (60,13 ); insert into t55 values (60,13 ); insert into t66 values (60,13 ); select * from t11 order by col1; col1 13 15 select * from t22 order by col1; col1 13 15 17 select * from t33 order by col1; col1 13 15 17 select * from t44 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t55 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t66 order by colint; colint col1 1 15 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 15 select * from t22 order by col1; col1 13 15 17 select * from t33 order by col1; col1 13 15 17 select * from t44 order by colint; colint col1 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t55 order by colint; colint col1 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 select * from t66 order by colint; colint col1 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 13 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- mod(col1,10) in partition with coltype int ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with mod(col1,10) ------------------------------------------------------------------------- create table t1 (col1 int) engine='INNODB' partition by range(mod(col1,10)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 int) engine='INNODB' partition by list(mod(col1,10)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 int) engine='INNODB' partition by hash(mod(col1,10)); create table t4 (colint int, col1 int) engine='INNODB' partition by range(colint) subpartition by hash(mod(col1,10)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 int) engine='INNODB' partition by list(colint) subpartition by hash(mod(col1,10)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 int) engine='INNODB' partition by range(colint) (partition p0 values less than (mod(15,10)), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with mod(col1,10) ------------------------------------------------------------------------- insert into t1 values (5); insert into t1 values (19); insert into t2 values (5); insert into t2 values (19); insert into t2 values (17); insert into t3 values (5); insert into t3 values (19); insert into t3 values (17); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t6; select mod(col1,10) from t1 order by col1; mod(col1,10) 5 9 select * from t1 order by col1; col1 5 19 select * from t2 order by col1; col1 5 17 19 select * from t3 order by col1; col1 5 17 19 select * from t4 order by colint; colint col1 1 5 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 5 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t5 order by colint; colint col1 1 5 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 5 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t6 order by colint; colint col1 1 5 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 5 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 update t1 set col1=15 where col1=5; update t2 set col1=15 where col1=5; update t3 set col1=15 where col1=5; update t4 set col1=15 where col1=5; update t5 set col1=15 where col1=5; update t6 set col1=15 where col1=5; select * from t1 order by col1; col1 15 19 select * from t2 order by col1; col1 15 17 19 select * from t3 order by col1; col1 15 17 19 select * from t4 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t5 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t6 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 ------------------------------------------------------------------------- --- Alter tables with mod(col1,10) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(mod(col1,10)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(mod(col1,10)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(mod(col1,10)); alter table t44 partition by range(colint) subpartition by hash(mod(col1,10)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(mod(col1,10)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (mod(15,10)), partition p1 values less than maxvalue); select * from t11 order by col1; col1 15 19 select * from t22 order by col1; col1 15 17 19 select * from t33 order by col1; col1 15 17 19 select * from t44 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t55 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 15 19 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 15 19 alter table t55 partition by list(colint) subpartition by hash(mod(col1,10)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (mod(col1,10)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition s1 into (partition p0 values less than (mod(15,10)), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 alter table t66 reorganize partition s1 into (partition p0 values less than (mod(15,10)), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with mod(col1,10) ------------------------------------------------------------------------- delete from t1 where col1=19; delete from t2 where col1=19; delete from t3 where col1=19; delete from t4 where col1=19; delete from t5 where col1=19; delete from t6 where col1=19; select * from t1 order by col1; col1 15 select * from t2 order by col1; col1 15 17 select * from t3 order by col1; col1 15 17 select * from t4 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t5 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 insert into t1 values (19); insert into t2 values (19); insert into t3 values (19); insert into t4 values (60,19); insert into t5 values (60,19); insert into t6 values (60,19); select * from t1 order by col1; col1 15 19 select * from t2 order by col1; col1 15 17 19 select * from t3 order by col1; col1 15 17 19 select * from t4 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t5 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t6 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 select * from t3 order by col1; col1 15 17 19 select * from t4 order by colint; colint col1 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t5 order by colint; colint col1 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t6 order by colint; colint col1 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with mod(col1,10) ------------------------------------------------------------------------- delete from t11 where col1=19; delete from t22 where col1=19; delete from t33 where col1=19; delete from t44 where col1=19; delete from t55 where col1=19; delete from t66 where col1=19; select * from t11 order by col1; col1 15 select * from t22 order by col1; col1 15 17 select * from t33 order by col1; col1 15 17 select * from t44 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 select * from t55 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 insert into t11 values (19); insert into t22 values (19); insert into t33 values (19); insert into t44 values (60,19); insert into t55 values (60,19); insert into t66 values (60,19); select * from t11 order by col1; col1 15 19 select * from t22 order by col1; col1 15 17 19 select * from t33 order by col1; col1 15 17 19 select * from t44 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t55 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t66 order by colint; colint col1 1 15 2 13 3 15 4 17 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 select * from t33 order by col1; col1 15 17 19 select * from t44 order by colint; colint col1 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t55 order by colint; colint col1 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 select * from t66 order by colint; colint col1 5 23 6 34 7 56 8 56 9 45 10 34 11 78 12 89 13 67 14 46 15 34 16 324 17 345 18 34 19 78 20 567 21 4 22 435 23 34 24 45 25 4565 26 4 27 3 28 2 29 3 30 15 31 6 32 8 33 9 34 745 35 34 36 34 37 324 38 67 39 78 40 89 41 90 42 78967 50 56 51 34 55 123 60 19 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- day(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with day(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(day(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(day(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(day(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(day(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(day(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (day('2006-12-21')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with day(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-02-03'); insert into t1 values ('2006-01-17'); insert into t2 values ('2006-02-03'); insert into t2 values ('2006-01-17'); insert into t2 values ('2006-01-25'); insert into t3 values ('2006-02-03'); insert into t3 values ('2006-01-17'); insert into t3 values ('2006-01-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select day(col1) from t1 order by col1; day(col1) 17 3 select * from t1 order by col1; col1 2006-01-17 2006-02-03 select * from t2 order by col1; col1 2006-01-17 2006-01-25 2006-02-03 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-03 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-02-05' where col1='2006-02-03'; update t2 set col1='2006-02-05' where col1='2006-02-03'; update t3 set col1='2006-02-05' where col1='2006-02-03'; update t4 set col1='2006-02-05' where col1='2006-02-03'; update t5 set col1='2006-02-05' where col1='2006-02-03'; update t6 set col1='2006-02-05' where col1='2006-02-03'; select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with day(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(day(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(day(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(day(col1)); alter table t44 partition by range(colint) subpartition by hash(day(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(day(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (day('2006-12-21')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 alter table t55 partition by list(colint) subpartition by hash(day(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (day(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (day('2006-12-21')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (day('2006-12-21')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with day(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-01-17'; delete from t2 where col1='2006-01-17'; delete from t3 where col1='2006-01-17'; delete from t4 where col1='2006-01-17'; delete from t5 where col1='2006-01-17'; delete from t6 where col1='2006-01-17'; select * from t1 order by col1; col1 2006-02-05 select * from t2 order by col1; col1 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-01-17'); insert into t2 values ('2006-01-17'); insert into t3 values ('2006-01-17'); insert into t4 values (60,'2006-01-17'); insert into t5 values (60,'2006-01-17'); insert into t6 values (60,'2006-01-17'); select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t5 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t6 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 2006-01-17 select * from t2 order by col1; col1 2006-01-17 2006-01-25 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 60 2006-01-17 select * from t5 order by colint; colint col1 60 2006-01-17 select * from t6 order by colint; colint col1 60 2006-01-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with day(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-01-17'; delete from t22 where col1='2006-01-17'; delete from t33 where col1='2006-01-17'; delete from t44 where col1='2006-01-17'; delete from t55 where col1='2006-01-17'; delete from t66 where col1='2006-01-17'; select * from t11 order by col1; col1 2006-02-05 select * from t22 order by col1; col1 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-01-17'); insert into t22 values ('2006-01-17'); insert into t33 values ('2006-01-17'); insert into t44 values (60,'2006-01-17'); insert into t55 values (60,'2006-01-17'); insert into t66 values (60,'2006-01-17'); select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t55 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t66 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 2006-01-17 select * from t22 order by col1; col1 2006-01-17 2006-01-25 select * from t33 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 60 2006-01-17 select * from t55 order by colint; colint col1 60 2006-01-17 select * from t66 order by colint; colint col1 60 2006-01-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- dayofmonth(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with dayofmonth(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(dayofmonth(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(dayofmonth(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(dayofmonth(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(dayofmonth(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(dayofmonth(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (dayofmonth('2006-12-24')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with dayofmonth(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-02-03'); insert into t1 values ('2006-01-17'); insert into t2 values ('2006-02-03'); insert into t2 values ('2006-01-17'); insert into t2 values ('2006-01-25'); insert into t3 values ('2006-02-03'); insert into t3 values ('2006-01-17'); insert into t3 values ('2006-01-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select dayofmonth(col1) from t1 order by col1; dayofmonth(col1) 17 3 select * from t1 order by col1; col1 2006-01-17 2006-02-03 select * from t2 order by col1; col1 2006-01-17 2006-01-25 2006-02-03 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-03 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-02-05' where col1='2006-02-03'; update t2 set col1='2006-02-05' where col1='2006-02-03'; update t3 set col1='2006-02-05' where col1='2006-02-03'; update t4 set col1='2006-02-05' where col1='2006-02-03'; update t5 set col1='2006-02-05' where col1='2006-02-03'; update t6 set col1='2006-02-05' where col1='2006-02-03'; select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with dayofmonth(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(dayofmonth(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(dayofmonth(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(dayofmonth(col1)); alter table t44 partition by range(colint) subpartition by hash(dayofmonth(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(dayofmonth(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (dayofmonth('2006-12-24')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 alter table t55 partition by list(colint) subpartition by hash(dayofmonth(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofmonth(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (dayofmonth('2006-12-24')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (dayofmonth('2006-12-24')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-05 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with dayofmonth(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-01-17'; delete from t2 where col1='2006-01-17'; delete from t3 where col1='2006-01-17'; delete from t4 where col1='2006-01-17'; delete from t5 where col1='2006-01-17'; delete from t6 where col1='2006-01-17'; select * from t1 order by col1; col1 2006-02-05 select * from t2 order by col1; col1 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-01-17'); insert into t2 values ('2006-01-17'); insert into t3 values ('2006-01-17'); insert into t4 values (60,'2006-01-17'); insert into t5 values (60,'2006-01-17'); insert into t6 values (60,'2006-01-17'); select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t5 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t6 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 2006-01-17 select * from t2 order by col1; col1 2006-01-17 2006-01-25 select * from t3 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 60 2006-01-17 select * from t5 order by colint; colint col1 60 2006-01-17 select * from t6 order by colint; colint col1 60 2006-01-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with dayofmonth(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-01-17'; delete from t22 where col1='2006-01-17'; delete from t33 where col1='2006-01-17'; delete from t44 where col1='2006-01-17'; delete from t55 where col1='2006-01-17'; delete from t66 where col1='2006-01-17'; select * from t11 order by col1; col1 2006-02-05 select * from t22 order by col1; col1 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-01-17'); insert into t22 values ('2006-01-17'); insert into t33 values ('2006-01-17'); insert into t44 values (60,'2006-01-17'); insert into t55 values (60,'2006-01-17'); insert into t66 values (60,'2006-01-17'); select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t55 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t66 order by colint; colint col1 1 2006-02-05 3 2006-01-25 4 2006-02-05 60 2006-01-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 2006-01-17 select * from t22 order by col1; col1 2006-01-17 2006-01-25 select * from t33 order by col1; col1 2006-01-17 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 60 2006-01-17 select * from t55 order by colint; colint col1 60 2006-01-17 select * from t66 order by colint; colint col1 60 2006-01-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- dayofweek(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with dayofweek(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(dayofweek(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(dayofweek(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(dayofweek(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(dayofweek(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(dayofweek(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (dayofweek('2006-12-24')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with dayofweek(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-01-03'); insert into t1 values ('2006-02-17'); insert into t2 values ('2006-01-03'); insert into t2 values ('2006-02-17'); insert into t2 values ('2006-01-25'); insert into t3 values ('2006-01-03'); insert into t3 values ('2006-02-17'); insert into t3 values ('2006-01-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select dayofweek(col1) from t1 order by col1; dayofweek(col1) 3 6 select * from t1 order by col1; col1 2006-01-03 2006-02-17 select * from t2 order by col1; col1 2006-01-03 2006-01-25 2006-02-17 select * from t3 order by col1; col1 2006-01-03 2006-01-25 2006-02-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-02-05' where col1='2006-01-03'; update t2 set col1='2006-02-05' where col1='2006-01-03'; update t3 set col1='2006-02-05' where col1='2006-01-03'; update t4 set col1='2006-02-05' where col1='2006-01-03'; update t5 set col1='2006-02-05' where col1='2006-01-03'; update t6 set col1='2006-02-05' where col1='2006-01-03'; select * from t1 order by col1; col1 2006-02-05 2006-02-17 select * from t2 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t3 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with dayofweek(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(dayofweek(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(dayofweek(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(dayofweek(col1)); alter table t44 partition by range(colint) subpartition by hash(dayofweek(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(dayofweek(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (dayofweek('2006-12-24')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-05 2006-02-17 select * from t22 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t33 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-05 2006-02-17 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-05 2006-02-17 alter table t55 partition by list(colint) subpartition by hash(dayofweek(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofweek(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (dayofweek('2006-12-24')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (dayofweek('2006-12-24')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with dayofweek(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-02-17'; delete from t2 where col1='2006-02-17'; delete from t3 where col1='2006-02-17'; delete from t4 where col1='2006-02-17'; delete from t5 where col1='2006-02-17'; delete from t6 where col1='2006-02-17'; select * from t1 order by col1; col1 2006-02-05 select * from t2 order by col1; col1 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-02-17'); insert into t2 values ('2006-02-17'); insert into t3 values ('2006-02-17'); insert into t4 values (60,'2006-02-17'); insert into t5 values (60,'2006-02-17'); insert into t6 values (60,'2006-02-17'); select * from t1 order by col1; col1 2006-02-05 2006-02-17 select * from t2 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t3 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 select * from t3 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t4 order by colint; colint col1 60 2006-02-17 select * from t5 order by colint; colint col1 60 2006-02-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with dayofweek(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-02-17'; delete from t22 where col1='2006-02-17'; delete from t33 where col1='2006-02-17'; delete from t44 where col1='2006-02-17'; delete from t55 where col1='2006-02-17'; delete from t66 where col1='2006-02-17'; select * from t11 order by col1; col1 2006-02-05 select * from t22 order by col1; col1 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-02-17'); insert into t22 values ('2006-02-17'); insert into t33 values ('2006-02-17'); insert into t44 values (60,'2006-02-17'); insert into t55 values (60,'2006-02-17'); insert into t66 values (60,'2006-02-17'); select * from t11 order by col1; col1 2006-02-05 2006-02-17 select * from t22 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t33 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 select * from t33 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t44 order by colint; colint col1 60 2006-02-17 select * from t55 order by colint; colint col1 60 2006-02-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- dayofyear(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with dayofyear(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(dayofyear(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(dayofyear(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(dayofyear(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(dayofyear(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(dayofyear(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (dayofyear('2006-12-25')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with dayofyear(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-01-03'); insert into t1 values ('2006-01-17'); insert into t2 values ('2006-01-03'); insert into t2 values ('2006-01-17'); insert into t2 values ('2006-02-25'); insert into t3 values ('2006-01-03'); insert into t3 values ('2006-01-17'); insert into t3 values ('2006-02-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select dayofyear(col1) from t1 order by col1; dayofyear(col1) 3 17 select * from t1 order by col1; col1 2006-01-03 2006-01-17 select * from t2 order by col1; col1 2006-01-03 2006-01-17 2006-02-25 select * from t3 order by col1; col1 2006-01-03 2006-01-17 2006-02-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-02-05' where col1='2006-01-03'; update t2 set col1='2006-02-05' where col1='2006-01-03'; update t3 set col1='2006-02-05' where col1='2006-01-03'; update t4 set col1='2006-02-05' where col1='2006-01-03'; update t5 set col1='2006-02-05' where col1='2006-01-03'; update t6 set col1='2006-02-05' where col1='2006-01-03'; select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t3 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with dayofyear(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(dayofyear(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(dayofyear(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(dayofyear(col1)); alter table t44 partition by range(colint) subpartition by hash(dayofyear(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(dayofyear(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (dayofyear('2006-12-25')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t33 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-01-17 2006-02-05 alter table t55 partition by list(colint) subpartition by hash(dayofyear(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (dayofyear(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (dayofyear('2006-12-25')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (dayofyear('2006-12-25')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with dayofyear(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-01-17'; delete from t2 where col1='2006-01-17'; delete from t3 where col1='2006-01-17'; delete from t4 where col1='2006-01-17'; delete from t5 where col1='2006-01-17'; delete from t6 where col1='2006-01-17'; select * from t1 order by col1; col1 2006-02-05 select * from t2 order by col1; col1 2006-02-05 2006-02-25 select * from t3 order by col1; col1 2006-02-05 2006-02-25 select * from t4 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-01-17'); insert into t2 values ('2006-01-17'); insert into t3 values ('2006-01-17'); insert into t4 values (60,'2006-01-17'); insert into t5 values (60,'2006-01-17'); insert into t6 values (60,'2006-01-17'); select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t3 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t4 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t5 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t6 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 60 2006-01-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 2006-01-17 2006-02-05 select * from t2 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t3 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t4 order by colint; colint col1 60 2006-01-17 select * from t5 order by colint; colint col1 60 2006-01-17 select * from t6 order by colint; colint col1 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with dayofyear(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-01-17'; delete from t22 where col1='2006-01-17'; delete from t33 where col1='2006-01-17'; delete from t44 where col1='2006-01-17'; delete from t55 where col1='2006-01-17'; delete from t66 where col1='2006-01-17'; select * from t11 order by col1; col1 2006-02-05 select * from t22 order by col1; col1 2006-02-05 2006-02-25 select * from t33 order by col1; col1 2006-02-05 2006-02-25 select * from t44 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-01-17'); insert into t22 values ('2006-01-17'); insert into t33 values ('2006-01-17'); insert into t44 values (60,'2006-01-17'); insert into t55 values (60,'2006-01-17'); insert into t66 values (60,'2006-01-17'); select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t33 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t44 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t55 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 60 2006-01-17 select * from t66 order by colint; colint col1 1 2006-02-03 3 2006-01-25 4 2006-02-05 60 2006-01-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 2006-01-17 2006-02-05 select * from t22 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t33 order by col1; col1 2006-01-17 2006-02-05 2006-02-25 select * from t44 order by colint; colint col1 60 2006-01-17 select * from t55 order by colint; colint col1 60 2006-01-17 select * from t66 order by colint; colint col1 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- extract(month from col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with extract(month from col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(extract(month from col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(extract(month from col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(extract(month from col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(extract(month from col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(extract(month from col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (extract(year from '1998-11-23')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with extract(month from col1) ------------------------------------------------------------------------- insert into t1 values ('2006-01-03'); insert into t1 values ('2006-02-17'); insert into t2 values ('2006-01-03'); insert into t2 values ('2006-02-17'); insert into t2 values ('2006-01-25'); insert into t3 values ('2006-01-03'); insert into t3 values ('2006-02-17'); insert into t3 values ('2006-01-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select extract(month from col1) from t1 order by col1; extract(month from col1) 1 2 select * from t1 order by col1; col1 2006-01-03 2006-02-17 select * from t2 order by col1; col1 2006-01-03 2006-01-25 2006-02-17 select * from t3 order by col1; col1 2006-01-03 2006-01-25 2006-02-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-02-05' where col1='2006-01-03'; update t2 set col1='2006-02-05' where col1='2006-01-03'; update t3 set col1='2006-02-05' where col1='2006-01-03'; update t4 set col1='2006-02-05' where col1='2006-01-03'; update t5 set col1='2006-02-05' where col1='2006-01-03'; update t6 set col1='2006-02-05' where col1='2006-01-03'; select * from t1 order by col1; col1 2006-02-05 2006-02-17 select * from t2 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t3 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with extract(month from col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(extract(month from col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(extract(month from col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(extract(month from col1)); alter table t44 partition by range(colint) subpartition by hash(extract(month from col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(extract(month from col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (extract(year from '1998-11-23')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-05 2006-02-17 select * from t22 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t33 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-05 2006-02-17 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-05 2006-02-17 alter table t55 partition by list(colint) subpartition by hash(extract(month from col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (extract(month from col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (extract(year from '1998-11-23')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (extract(year from '1998-11-23')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with extract(month from col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-02-17'; delete from t2 where col1='2006-02-17'; delete from t3 where col1='2006-02-17'; delete from t4 where col1='2006-02-17'; delete from t5 where col1='2006-02-17'; delete from t6 where col1='2006-02-17'; select * from t1 order by col1; col1 2006-02-05 select * from t2 order by col1; col1 2006-01-25 2006-02-05 select * from t3 order by col1; col1 2006-01-25 2006-02-05 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-02-17'); insert into t2 values ('2006-02-17'); insert into t3 values ('2006-02-17'); insert into t4 values (60,'2006-02-17'); insert into t5 values (60,'2006-02-17'); insert into t6 values (60,'2006-02-17'); select * from t1 order by col1; col1 2006-02-05 2006-02-17 select * from t2 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t3 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 select * from t3 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t4 order by colint; colint col1 60 2006-02-17 select * from t5 order by colint; colint col1 60 2006-02-17 select * from t6 order by colint; colint col1 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with extract(month from col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-02-17'; delete from t22 where col1='2006-02-17'; delete from t33 where col1='2006-02-17'; delete from t44 where col1='2006-02-17'; delete from t55 where col1='2006-02-17'; delete from t66 where col1='2006-02-17'; select * from t11 order by col1; col1 2006-02-05 select * from t22 order by col1; col1 2006-01-25 2006-02-05 select * from t33 order by col1; col1 2006-01-25 2006-02-05 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-02-17'); insert into t22 values ('2006-02-17'); insert into t33 values ('2006-02-17'); insert into t44 values (60,'2006-02-17'); insert into t55 values (60,'2006-02-17'); insert into t66 values (60,'2006-02-17'); select * from t11 order by col1; col1 2006-02-05 2006-02-17 select * from t22 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t33 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-02-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 select * from t33 order by col1; col1 2006-01-25 2006-02-05 2006-02-17 select * from t44 order by colint; colint col1 60 2006-02-17 select * from t55 order by colint; colint col1 60 2006-02-17 select * from t66 order by colint; colint col1 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- hour(col1) in partition with coltype time ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with hour(col1) ------------------------------------------------------------------------- create table t1 (col1 time) engine='INNODB' partition by range(hour(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 time) engine='INNODB' partition by list(hour(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 time) engine='INNODB' partition by hash(hour(col1)); create table t4 (colint int, col1 time) engine='INNODB' partition by range(colint) subpartition by hash(hour(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 time) engine='INNODB' partition by list(colint) subpartition by hash(hour(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 time) engine='INNODB' partition by range(colint) (partition p0 values less than (hour('18:30')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with hour(col1) ------------------------------------------------------------------------- insert into t1 values ('09:09'); insert into t1 values ('14:30'); insert into t2 values ('09:09'); insert into t2 values ('14:30'); insert into t2 values ('21:59'); insert into t3 values ('09:09'); insert into t3 values ('14:30'); insert into t3 values ('21:59'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6; select hour(col1) from t1 order by col1; hour(col1) 9 14 select * from t1 order by col1; col1 09:09:00 14:30:00 select * from t2 order by col1; col1 09:09:00 14:30:00 21:59:00 select * from t3 order by col1; col1 09:09:00 14:30:00 21:59:00 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 update t1 set col1='10:30' where col1='09:09'; update t2 set col1='10:30' where col1='09:09'; update t3 set col1='10:30' where col1='09:09'; update t4 set col1='10:30' where col1='09:09'; update t5 set col1='10:30' where col1='09:09'; update t6 set col1='10:30' where col1='09:09'; select * from t1 order by col1; col1 10:30:00 14:30:00 select * from t2 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t3 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Alter tables with hour(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(hour(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(hour(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(hour(col1)); alter table t44 partition by range(colint) subpartition by hash(hour(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(hour(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (hour('18:30')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:30:00 14:30:00 select * from t22 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t33 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t44 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 10:30:00 14:30:00 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:30:00 14:30:00 alter table t55 partition by list(colint) subpartition by hash(hour(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` time DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (hour(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (hour('18:30')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (hour('18:30')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with hour(col1) ------------------------------------------------------------------------- delete from t1 where col1='14:30'; delete from t2 where col1='14:30'; delete from t3 where col1='14:30'; delete from t4 where col1='14:30'; delete from t5 where col1='14:30'; delete from t6 where col1='14:30'; select * from t1 order by col1; col1 10:30:00 select * from t2 order by col1; col1 10:30:00 21:59:00 select * from t3 order by col1; col1 10:30:00 21:59:00 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 insert into t1 values ('14:30'); insert into t2 values ('14:30'); insert into t3 values ('14:30'); insert into t4 values (60,'14:30'); insert into t5 values (60,'14:30'); insert into t6 values (60,'14:30'); select * from t1 order by col1; col1 10:30:00 14:30:00 select * from t2 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t3 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:00 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:00 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:00 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 14:30:00 21:59:00 select * from t3 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t4 order by colint; colint col1 60 14:30:00 select * from t5 order by colint; colint col1 60 14:30:00 select * from t6 order by colint; colint col1 60 14:30:00 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with hour(col1) ------------------------------------------------------------------------- delete from t11 where col1='14:30'; delete from t22 where col1='14:30'; delete from t33 where col1='14:30'; delete from t44 where col1='14:30'; delete from t55 where col1='14:30'; delete from t66 where col1='14:30'; select * from t11 order by col1; col1 10:30:00 select * from t22 order by col1; col1 10:30:00 21:59:00 select * from t33 order by col1; col1 10:30:00 21:59:00 select * from t44 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 insert into t11 values ('14:30'); insert into t22 values ('14:30'); insert into t33 values ('14:30'); insert into t44 values (60,'14:30'); insert into t55 values (60,'14:30'); insert into t66 values (60,'14:30'); select * from t11 order by col1; col1 10:30:00 14:30:00 select * from t22 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t33 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t44 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:00 select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:00 select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:00 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 14:30:00 21:59:00 select * from t33 order by col1; col1 10:30:00 14:30:00 21:59:00 select * from t44 order by colint; colint col1 60 14:30:00 select * from t55 order by colint; colint col1 60 14:30:00 select * from t66 order by colint; colint col1 60 14:30:00 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- microsecond(col1) in partition with coltype time ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with microsecond(col1) ------------------------------------------------------------------------- create table t1 (col1 time) engine='INNODB' partition by range(microsecond(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 time) engine='INNODB' partition by list(microsecond(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 time) engine='INNODB' partition by hash(microsecond(col1)); create table t4 (colint int, col1 time) engine='INNODB' partition by range(colint) subpartition by hash(microsecond(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 time) engine='INNODB' partition by list(colint) subpartition by hash(microsecond(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 time) engine='INNODB' partition by range(colint) (partition p0 values less than (microsecond('10:30:10.000010')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with microsecond(col1) ------------------------------------------------------------------------- insert into t1 values ('09:09:15.000002'); insert into t1 values ('04:30:01.000018'); insert into t2 values ('09:09:15.000002'); insert into t2 values ('04:30:01.000018'); insert into t2 values ('00:59:22.000024'); insert into t3 values ('09:09:15.000002'); insert into t3 values ('04:30:01.000018'); insert into t3 values ('00:59:22.000024'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6; select microsecond(col1) from t1 order by col1; microsecond(col1) 0 0 select * from t1 order by col1; col1 04:30:01 09:09:15 select * from t2 order by col1; col1 00:59:22 04:30:01 09:09:15 select * from t3 order by col1; col1 00:59:22 04:30:01 09:09:15 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 update t1 set col1='05:30:34.000037' where col1='09:09:15.000002'; update t2 set col1='05:30:34.000037' where col1='09:09:15.000002'; update t3 set col1='05:30:34.000037' where col1='09:09:15.000002'; update t4 set col1='05:30:34.000037' where col1='09:09:15.000002'; update t5 set col1='05:30:34.000037' where col1='09:09:15.000002'; update t6 set col1='05:30:34.000037' where col1='09:09:15.000002'; select * from t1 order by col1; col1 04:30:01 05:30:34 select * from t2 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t3 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t4 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Alter tables with microsecond(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(microsecond(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(microsecond(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(microsecond(col1)); alter table t44 partition by range(colint) subpartition by hash(microsecond(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(microsecond(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (microsecond('10:30:10.000010')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 04:30:01 05:30:34 select * from t22 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t33 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t44 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 select * from t66 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 04:30:01 05:30:34 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 04:30:01 05:30:34 alter table t55 partition by list(colint) subpartition by hash(microsecond(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` time DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (microsecond(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (microsecond('10:30:10.000010')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (microsecond('10:30:10.000010')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 05:30:34 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with microsecond(col1) ------------------------------------------------------------------------- delete from t1 where col1='04:30:01.000018'; delete from t2 where col1='04:30:01.000018'; delete from t3 where col1='04:30:01.000018'; delete from t4 where col1='04:30:01.000018'; delete from t5 where col1='04:30:01.000018'; delete from t6 where col1='04:30:01.000018'; select * from t1 order by col1; col1 05:30:34 select * from t2 order by col1; col1 00:59:22 05:30:34 select * from t3 order by col1; col1 00:59:22 05:30:34 select * from t4 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 insert into t1 values ('04:30:01.000018'); insert into t2 values ('04:30:01.000018'); insert into t3 values ('04:30:01.000018'); insert into t4 values (60,'04:30:01.000018'); insert into t5 values (60,'04:30:01.000018'); insert into t6 values (60,'04:30:01.000018'); select * from t1 order by col1; col1 04:30:01 05:30:34 select * from t2 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t3 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t4 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 60 04:30:01 select * from t5 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 60 04:30:01 select * from t6 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 60 04:30:01 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 select * from t3 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t4 order by colint; colint col1 60 04:30:01 select * from t5 order by colint; colint col1 60 04:30:01 select * from t6 order by colint; colint col1 60 04:30:01 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with microsecond(col1) ------------------------------------------------------------------------- delete from t11 where col1='04:30:01.000018'; delete from t22 where col1='04:30:01.000018'; delete from t33 where col1='04:30:01.000018'; delete from t44 where col1='04:30:01.000018'; delete from t55 where col1='04:30:01.000018'; delete from t66 where col1='04:30:01.000018'; select * from t11 order by col1; col1 05:30:34 select * from t22 order by col1; col1 00:59:22 05:30:34 select * from t33 order by col1; col1 00:59:22 05:30:34 select * from t44 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 insert into t11 values ('04:30:01.000018'); insert into t22 values ('04:30:01.000018'); insert into t33 values ('04:30:01.000018'); insert into t44 values (60,'04:30:01.000018'); insert into t55 values (60,'04:30:01.000018'); insert into t66 values (60,'04:30:01.000018'); select * from t11 order by col1; col1 04:30:01 05:30:34 select * from t22 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t33 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t44 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 60 04:30:01 select * from t55 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 60 04:30:01 select * from t66 order by colint; colint col1 1 05:30:34 3 00:59:22 4 05:30:34 60 04:30:01 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 select * from t33 order by col1; col1 00:59:22 04:30:01 05:30:34 select * from t44 order by colint; colint col1 60 04:30:01 select * from t55 order by colint; colint col1 60 04:30:01 select * from t66 order by colint; colint col1 60 04:30:01 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- minute(col1) in partition with coltype time ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with minute(col1) ------------------------------------------------------------------------- create table t1 (col1 time) engine='INNODB' partition by range(minute(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 time) engine='INNODB' partition by list(minute(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 time) engine='INNODB' partition by hash(minute(col1)); create table t4 (colint int, col1 time) engine='INNODB' partition by range(colint) subpartition by hash(minute(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 time) engine='INNODB' partition by list(colint) subpartition by hash(minute(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 time) engine='INNODB' partition by range(colint) (partition p0 values less than (minute('18:30')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with minute(col1) ------------------------------------------------------------------------- insert into t1 values ('09:09:15'); insert into t1 values ('14:30:45'); insert into t2 values ('09:09:15'); insert into t2 values ('14:30:45'); insert into t2 values ('21:59:22'); insert into t3 values ('09:09:15'); insert into t3 values ('14:30:45'); insert into t3 values ('21:59:22'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6; select minute(col1) from t1 order by col1; minute(col1) 9 30 select * from t1 order by col1; col1 09:09:15 14:30:45 select * from t2 order by col1; col1 09:09:15 14:30:45 21:59:22 select * from t3 order by col1; col1 09:09:15 14:30:45 21:59:22 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 update t1 set col1='10:24:23' where col1='09:09:15'; update t2 set col1='10:24:23' where col1='09:09:15'; update t3 set col1='10:24:23' where col1='09:09:15'; update t4 set col1='10:24:23' where col1='09:09:15'; update t5 set col1='10:24:23' where col1='09:09:15'; update t6 set col1='10:24:23' where col1='09:09:15'; select * from t1 order by col1; col1 10:24:23 14:30:45 select * from t2 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t3 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t4 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Alter tables with minute(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(minute(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(minute(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(minute(col1)); alter table t44 partition by range(colint) subpartition by hash(minute(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(minute(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (minute('18:30')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:24:23 14:30:45 select * from t22 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t33 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t44 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 select * from t66 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 10:24:23 14:30:45 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:24:23 14:30:45 alter table t55 partition by list(colint) subpartition by hash(minute(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` time DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (minute(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (minute('18:30')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (minute('18:30')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with minute(col1) ------------------------------------------------------------------------- delete from t1 where col1='14:30:45'; delete from t2 where col1='14:30:45'; delete from t3 where col1='14:30:45'; delete from t4 where col1='14:30:45'; delete from t5 where col1='14:30:45'; delete from t6 where col1='14:30:45'; select * from t1 order by col1; col1 10:24:23 select * from t2 order by col1; col1 10:24:23 21:59:22 select * from t3 order by col1; col1 10:24:23 21:59:22 select * from t4 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 insert into t1 values ('14:30:45'); insert into t2 values ('14:30:45'); insert into t3 values ('14:30:45'); insert into t4 values (60,'14:30:45'); insert into t5 values (60,'14:30:45'); insert into t6 values (60,'14:30:45'); select * from t1 order by col1; col1 10:24:23 14:30:45 select * from t2 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t3 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t4 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t5 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t6 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 10:24:23 14:30:45 select * from t2 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t3 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t4 order by colint; colint col1 60 14:30:45 select * from t5 order by colint; colint col1 60 14:30:45 select * from t6 order by colint; colint col1 60 14:30:45 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with minute(col1) ------------------------------------------------------------------------- delete from t11 where col1='14:30:45'; delete from t22 where col1='14:30:45'; delete from t33 where col1='14:30:45'; delete from t44 where col1='14:30:45'; delete from t55 where col1='14:30:45'; delete from t66 where col1='14:30:45'; select * from t11 order by col1; col1 10:24:23 select * from t22 order by col1; col1 10:24:23 21:59:22 select * from t33 order by col1; col1 10:24:23 21:59:22 select * from t44 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 insert into t11 values ('14:30:45'); insert into t22 values ('14:30:45'); insert into t33 values ('14:30:45'); insert into t44 values (60,'14:30:45'); insert into t55 values (60,'14:30:45'); insert into t66 values (60,'14:30:45'); select * from t11 order by col1; col1 10:24:23 14:30:45 select * from t22 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t33 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t44 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t55 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t66 order by colint; colint col1 1 10:24:23 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 10:24:23 14:30:45 select * from t22 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t33 order by col1; col1 10:24:23 14:30:45 21:59:22 select * from t44 order by colint; colint col1 60 14:30:45 select * from t55 order by colint; colint col1 60 14:30:45 select * from t66 order by colint; colint col1 60 14:30:45 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- second(col1) in partition with coltype time ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with second(col1) ------------------------------------------------------------------------- create table t1 (col1 time) engine='INNODB' partition by range(second(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 time) engine='INNODB' partition by list(second(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 time) engine='INNODB' partition by hash(second(col1)); create table t4 (colint int, col1 time) engine='INNODB' partition by range(colint) subpartition by hash(second(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 time) engine='INNODB' partition by list(colint) subpartition by hash(second(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 time) engine='INNODB' partition by range(colint) (partition p0 values less than (second('18:30:14')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with second(col1) ------------------------------------------------------------------------- insert into t1 values ('09:09:09'); insert into t1 values ('14:30:20'); insert into t2 values ('09:09:09'); insert into t2 values ('14:30:20'); insert into t2 values ('21:59:22'); insert into t3 values ('09:09:09'); insert into t3 values ('14:30:20'); insert into t3 values ('21:59:22'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6; select second(col1) from t1 order by col1; second(col1) 9 20 select * from t1 order by col1; col1 09:09:09 14:30:20 select * from t2 order by col1; col1 09:09:09 14:30:20 21:59:22 select * from t3 order by col1; col1 09:09:09 14:30:20 21:59:22 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 update t1 set col1='10:22:33' where col1='09:09:09'; update t2 set col1='10:22:33' where col1='09:09:09'; update t3 set col1='10:22:33' where col1='09:09:09'; update t4 set col1='10:22:33' where col1='09:09:09'; update t5 set col1='10:22:33' where col1='09:09:09'; update t6 set col1='10:22:33' where col1='09:09:09'; select * from t1 order by col1; col1 10:22:33 14:30:20 select * from t2 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t3 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Alter tables with second(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(second(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(second(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(second(col1)); alter table t44 partition by range(colint) subpartition by hash(second(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(second(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (second('18:30:14')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:22:33 14:30:20 select * from t22 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t33 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t44 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 10:22:33 14:30:20 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:22:33 14:30:20 alter table t55 partition by list(colint) subpartition by hash(second(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` time DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (second(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (second('18:30:14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (second('18:30:14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with second(col1) ------------------------------------------------------------------------- delete from t1 where col1='14:30:20'; delete from t2 where col1='14:30:20'; delete from t3 where col1='14:30:20'; delete from t4 where col1='14:30:20'; delete from t5 where col1='14:30:20'; delete from t6 where col1='14:30:20'; select * from t1 order by col1; col1 10:22:33 select * from t2 order by col1; col1 10:22:33 21:59:22 select * from t3 order by col1; col1 10:22:33 21:59:22 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 insert into t1 values ('14:30:20'); insert into t2 values ('14:30:20'); insert into t3 values ('14:30:20'); insert into t4 values (60,'14:30:20'); insert into t5 values (60,'14:30:20'); insert into t6 values (60,'14:30:20'); select * from t1 order by col1; col1 10:22:33 14:30:20 select * from t2 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t3 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:20 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:20 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:20 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 10:22:33 14:30:20 select * from t2 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t3 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t4 order by colint; colint col1 60 14:30:20 select * from t5 order by colint; colint col1 60 14:30:20 select * from t6 order by colint; colint col1 60 14:30:20 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with second(col1) ------------------------------------------------------------------------- delete from t11 where col1='14:30:20'; delete from t22 where col1='14:30:20'; delete from t33 where col1='14:30:20'; delete from t44 where col1='14:30:20'; delete from t55 where col1='14:30:20'; delete from t66 where col1='14:30:20'; select * from t11 order by col1; col1 10:22:33 select * from t22 order by col1; col1 10:22:33 21:59:22 select * from t33 order by col1; col1 10:22:33 21:59:22 select * from t44 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 insert into t11 values ('14:30:20'); insert into t22 values ('14:30:20'); insert into t33 values ('14:30:20'); insert into t44 values (60,'14:30:20'); insert into t55 values (60,'14:30:20'); insert into t66 values (60,'14:30:20'); select * from t11 order by col1; col1 10:22:33 14:30:20 select * from t22 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t33 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t44 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:20 select * from t55 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:20 select * from t66 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:20 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 10:22:33 14:30:20 select * from t22 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t33 order by col1; col1 10:22:33 14:30:20 21:59:22 select * from t44 order by colint; colint col1 60 14:30:20 select * from t55 order by colint; colint col1 60 14:30:20 select * from t66 order by colint; colint col1 60 14:30:20 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- month(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with month(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(month(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(month(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(month(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(month(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(month(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (month('2006-10-14')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with month(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-01-03'); insert into t1 values ('2006-12-17'); insert into t2 values ('2006-01-03'); insert into t2 values ('2006-12-17'); insert into t2 values ('2006-05-25'); insert into t3 values ('2006-01-03'); insert into t3 values ('2006-12-17'); insert into t3 values ('2006-05-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select month(col1) from t1 order by col1; month(col1) 1 12 select * from t1 order by col1; col1 2006-01-03 2006-12-17 select * from t2 order by col1; col1 2006-01-03 2006-05-25 2006-12-17 select * from t3 order by col1; col1 2006-01-03 2006-05-25 2006-12-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-11-06' where col1='2006-01-03'; update t2 set col1='2006-11-06' where col1='2006-01-03'; update t3 set col1='2006-11-06' where col1='2006-01-03'; update t4 set col1='2006-11-06' where col1='2006-01-03'; update t5 set col1='2006-11-06' where col1='2006-01-03'; update t6 set col1='2006-11-06' where col1='2006-01-03'; select * from t1 order by col1; col1 2006-11-06 2006-12-17 select * from t2 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t3 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with month(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(month(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(month(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(month(col1)); alter table t44 partition by range(colint) subpartition by hash(month(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(month(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (month('2006-10-14')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-11-06 2006-12-17 select * from t22 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t33 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-11-06 2006-12-17 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-11-06 2006-12-17 alter table t55 partition by list(colint) subpartition by hash(month(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (month(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (month('2006-10-14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (month('2006-10-14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with month(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-12-17'; delete from t2 where col1='2006-12-17'; delete from t3 where col1='2006-12-17'; delete from t4 where col1='2006-12-17'; delete from t5 where col1='2006-12-17'; delete from t6 where col1='2006-12-17'; select * from t1 order by col1; col1 2006-11-06 select * from t2 order by col1; col1 2006-05-25 2006-11-06 select * from t3 order by col1; col1 2006-05-25 2006-11-06 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-12-17'); insert into t2 values ('2006-12-17'); insert into t3 values ('2006-12-17'); insert into t4 values (60,'2006-12-17'); insert into t5 values (60,'2006-12-17'); insert into t6 values (60,'2006-12-17'); select * from t1 order by col1; col1 2006-11-06 2006-12-17 select * from t2 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t3 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 2006-11-06 2006-12-17 select * from t3 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t4 order by colint; colint col1 60 2006-12-17 select * from t5 order by colint; colint col1 60 2006-12-17 select * from t6 order by colint; colint col1 60 2006-12-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with month(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-12-17'; delete from t22 where col1='2006-12-17'; delete from t33 where col1='2006-12-17'; delete from t44 where col1='2006-12-17'; delete from t55 where col1='2006-12-17'; delete from t66 where col1='2006-12-17'; select * from t11 order by col1; col1 2006-11-06 select * from t22 order by col1; col1 2006-05-25 2006-11-06 select * from t33 order by col1; col1 2006-05-25 2006-11-06 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-12-17'); insert into t22 values ('2006-12-17'); insert into t33 values ('2006-12-17'); insert into t44 values (60,'2006-12-17'); insert into t55 values (60,'2006-12-17'); insert into t66 values (60,'2006-12-17'); select * from t11 order by col1; col1 2006-11-06 2006-12-17 select * from t22 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t33 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 2006-11-06 2006-12-17 select * from t33 order by col1; col1 2006-05-25 2006-11-06 2006-12-17 select * from t44 order by colint; colint col1 60 2006-12-17 select * from t55 order by colint; colint col1 60 2006-12-17 select * from t66 order by colint; colint col1 60 2006-12-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- quarter(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with quarter(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(quarter(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(quarter(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(quarter(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(quarter(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(quarter(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (quarter('2006-10-14')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with quarter(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-01-03'); insert into t1 values ('2006-12-17'); insert into t2 values ('2006-01-03'); insert into t2 values ('2006-12-17'); insert into t2 values ('2006-09-25'); insert into t3 values ('2006-01-03'); insert into t3 values ('2006-12-17'); insert into t3 values ('2006-09-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select quarter(col1) from t1 order by col1; quarter(col1) 1 4 select * from t1 order by col1; col1 2006-01-03 2006-12-17 select * from t2 order by col1; col1 2006-01-03 2006-09-25 2006-12-17 select * from t3 order by col1; col1 2006-01-03 2006-09-25 2006-12-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-07-30' where col1='2006-01-03'; update t2 set col1='2006-07-30' where col1='2006-01-03'; update t3 set col1='2006-07-30' where col1='2006-01-03'; update t4 set col1='2006-07-30' where col1='2006-01-03'; update t5 set col1='2006-07-30' where col1='2006-01-03'; update t6 set col1='2006-07-30' where col1='2006-01-03'; select * from t1 order by col1; col1 2006-07-30 2006-12-17 select * from t2 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t3 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with quarter(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(quarter(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(quarter(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(quarter(col1)); alter table t44 partition by range(colint) subpartition by hash(quarter(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(quarter(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (quarter('2006-10-14')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-07-30 2006-12-17 select * from t22 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t33 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-07-30 2006-12-17 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-07-30 2006-12-17 alter table t55 partition by list(colint) subpartition by hash(quarter(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (quarter(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (quarter('2006-10-14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (quarter('2006-10-14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with quarter(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-12-17'; delete from t2 where col1='2006-12-17'; delete from t3 where col1='2006-12-17'; delete from t4 where col1='2006-12-17'; delete from t5 where col1='2006-12-17'; delete from t6 where col1='2006-12-17'; select * from t1 order by col1; col1 2006-07-30 select * from t2 order by col1; col1 2006-07-30 2006-09-25 select * from t3 order by col1; col1 2006-07-30 2006-09-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-12-17'); insert into t2 values ('2006-12-17'); insert into t3 values ('2006-12-17'); insert into t4 values (60,'2006-12-17'); insert into t5 values (60,'2006-12-17'); insert into t6 values (60,'2006-12-17'); select * from t1 order by col1; col1 2006-07-30 2006-12-17 select * from t2 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t3 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 select * from t3 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t4 order by colint; colint col1 60 2006-12-17 select * from t5 order by colint; colint col1 60 2006-12-17 select * from t6 order by colint; colint col1 4 2006-02-05 60 2006-12-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with quarter(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-12-17'; delete from t22 where col1='2006-12-17'; delete from t33 where col1='2006-12-17'; delete from t44 where col1='2006-12-17'; delete from t55 where col1='2006-12-17'; delete from t66 where col1='2006-12-17'; select * from t11 order by col1; col1 2006-07-30 select * from t22 order by col1; col1 2006-07-30 2006-09-25 select * from t33 order by col1; col1 2006-07-30 2006-09-25 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-12-17'); insert into t22 values ('2006-12-17'); insert into t33 values ('2006-12-17'); insert into t44 values (60,'2006-12-17'); insert into t55 values (60,'2006-12-17'); insert into t66 values (60,'2006-12-17'); select * from t11 order by col1; col1 2006-07-30 2006-12-17 select * from t22 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t33 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-12-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 select * from t33 order by col1; col1 2006-07-30 2006-09-25 2006-12-17 select * from t44 order by colint; colint col1 60 2006-12-17 select * from t55 order by colint; colint col1 60 2006-12-17 select * from t66 order by colint; colint col1 4 2006-02-05 60 2006-12-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- time_to_sec(col1)-(time_to_sec(col1)-20) in partition with coltype time ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with time_to_sec(col1)-(time_to_sec(col1)-20) ------------------------------------------------------------------------- create table t1 (col1 time) engine='INNODB' partition by range(time_to_sec(col1)-(time_to_sec(col1)-20)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 time) engine='INNODB' partition by list(time_to_sec(col1)-(time_to_sec(col1)-20)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 time) engine='INNODB' partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)); create table t4 (colint int, col1 time) engine='INNODB' partition by range(colint) subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 time) engine='INNODB' partition by list(colint) subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 time) engine='INNODB' partition by range(colint) (partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with time_to_sec(col1)-(time_to_sec(col1)-20) ------------------------------------------------------------------------- insert into t1 values ('09:09:15'); insert into t1 values ('14:30:45'); insert into t2 values ('09:09:15'); insert into t2 values ('14:30:45'); insert into t2 values ('21:59:22'); insert into t3 values ('09:09:15'); insert into t3 values ('14:30:45'); insert into t3 values ('21:59:22'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6; select time_to_sec(col1)-(time_to_sec(col1)-20) from t1 order by col1; time_to_sec(col1)-(time_to_sec(col1)-20) 20 20 select * from t1 order by col1; col1 09:09:15 14:30:45 select * from t2 order by col1; col1 09:09:15 14:30:45 21:59:22 select * from t3 order by col1; col1 09:09:15 14:30:45 21:59:22 select * from t4 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 09:09:15 2 04:30:01 3 00:59:22 4 05:30:34 update t1 set col1='10:33:11' where col1='09:09:15'; update t2 set col1='10:33:11' where col1='09:09:15'; update t3 set col1='10:33:11' where col1='09:09:15'; update t4 set col1='10:33:11' where col1='09:09:15'; update t5 set col1='10:33:11' where col1='09:09:15'; update t6 set col1='10:33:11' where col1='09:09:15'; select * from t1 order by col1; col1 10:33:11 14:30:45 select * from t2 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t3 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t4 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 select * from t6 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Alter tables with time_to_sec(col1)-(time_to_sec(col1)-20) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(time_to_sec(col1)-(time_to_sec(col1)-20)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(time_to_sec(col1)-(time_to_sec(col1)-20)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)); alter table t44 partition by range(colint) subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:33:11 14:30:45 select * from t22 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t33 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t44 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 select * from t66 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 10:33:11 14:30:45 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 10:33:11 14:30:45 alter table t55 partition by list(colint) subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` time DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (time_to_sec(col1)-(time_to_sec(col1)-20)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 alter table t66 reorganize partition s1 into (partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20) ------------------------------------------------------------------------- delete from t1 where col1='14:30:45'; delete from t2 where col1='14:30:45'; delete from t3 where col1='14:30:45'; delete from t4 where col1='14:30:45'; delete from t5 where col1='14:30:45'; delete from t6 where col1='14:30:45'; select * from t1 order by col1; col1 10:33:11 select * from t2 order by col1; col1 10:33:11 21:59:22 select * from t3 order by col1; col1 10:33:11 21:59:22 select * from t4 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 select * from t5 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 insert into t1 values ('14:30:45'); insert into t2 values ('14:30:45'); insert into t3 values ('14:30:45'); insert into t4 values (60,'14:30:45'); insert into t5 values (60,'14:30:45'); insert into t6 values (60,'14:30:45'); select * from t1 order by col1; col1 10:33:11 14:30:45 select * from t2 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t3 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t4 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t5 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t6 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 10:33:11 14:30:45 select * from t2 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t3 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t4 order by colint; colint col1 60 14:30:45 select * from t5 order by colint; colint col1 60 14:30:45 select * from t6 order by colint; colint col1 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20) ------------------------------------------------------------------------- delete from t11 where col1='14:30:45'; delete from t22 where col1='14:30:45'; delete from t33 where col1='14:30:45'; delete from t44 where col1='14:30:45'; delete from t55 where col1='14:30:45'; delete from t66 where col1='14:30:45'; select * from t11 order by col1; col1 10:33:11 select * from t22 order by col1; col1 10:33:11 21:59:22 select * from t33 order by col1; col1 10:33:11 21:59:22 select * from t44 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 select * from t55 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 insert into t11 values ('14:30:45'); insert into t22 values ('14:30:45'); insert into t33 values ('14:30:45'); insert into t44 values (60,'14:30:45'); insert into t55 values (60,'14:30:45'); insert into t66 values (60,'14:30:45'); select * from t11 order by col1; col1 10:33:11 14:30:45 select * from t22 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t33 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t44 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t55 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 select * from t66 order by colint; colint col1 1 10:33:11 2 04:30:01 3 00:59:22 4 05:30:34 60 14:30:45 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 10:33:11 14:30:45 select * from t22 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t33 order by col1; col1 10:33:11 14:30:45 21:59:22 select * from t44 order by colint; colint col1 60 14:30:45 select * from t55 order by colint; colint col1 60 14:30:45 select * from t66 order by colint; colint col1 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- weekday(col1) in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with weekday(col1) ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(weekday(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(weekday(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(weekday(col1)); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(weekday(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(weekday(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (weekday('2006-10-14')), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with weekday(col1) ------------------------------------------------------------------------- insert into t1 values ('2006-12-03'); insert into t1 values ('2006-11-17'); insert into t2 values ('2006-12-03'); insert into t2 values ('2006-11-17'); insert into t2 values ('2006-05-25'); insert into t3 values ('2006-12-03'); insert into t3 values ('2006-11-17'); insert into t3 values ('2006-05-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select weekday(col1) from t1 order by col1; weekday(col1) 4 6 select * from t1 order by col1; col1 2006-11-17 2006-12-03 select * from t2 order by col1; col1 2006-05-25 2006-11-17 2006-12-03 select * from t3 order by col1; col1 2006-05-25 2006-11-17 2006-12-03 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-02-06' where col1='2006-12-03'; update t2 set col1='2006-02-06' where col1='2006-12-03'; update t3 set col1='2006-02-06' where col1='2006-12-03'; update t4 set col1='2006-02-06' where col1='2006-12-03'; update t5 set col1='2006-02-06' where col1='2006-12-03'; update t6 set col1='2006-02-06' where col1='2006-12-03'; select * from t1 order by col1; col1 2006-02-06 2006-11-17 select * from t2 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t3 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with weekday(col1) ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(weekday(col1)) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(weekday(col1)) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(weekday(col1)); alter table t44 partition by range(colint) subpartition by hash(weekday(col1)) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(weekday(col1)) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (weekday('2006-10-14')), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-06 2006-11-17 select * from t22 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t33 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-06 2006-11-17 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-02-06 2006-11-17 alter table t55 partition by list(colint) subpartition by hash(weekday(col1)) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (weekday(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (weekday('2006-10-14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (weekday('2006-10-14')), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with weekday(col1) ------------------------------------------------------------------------- delete from t1 where col1='2006-11-17'; delete from t2 where col1='2006-11-17'; delete from t3 where col1='2006-11-17'; delete from t4 where col1='2006-11-17'; delete from t5 where col1='2006-11-17'; delete from t6 where col1='2006-11-17'; select * from t1 order by col1; col1 2006-02-06 select * from t2 order by col1; col1 2006-02-06 2006-05-25 select * from t3 order by col1; col1 2006-02-06 2006-05-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-11-17'); insert into t2 values ('2006-11-17'); insert into t3 values ('2006-11-17'); insert into t4 values (60,'2006-11-17'); insert into t5 values (60,'2006-11-17'); insert into t6 values (60,'2006-11-17'); select * from t1 order by col1; col1 2006-02-06 2006-11-17 select * from t2 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t3 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-11-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-11-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-11-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 select * from t3 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t4 order by colint; colint col1 60 2006-11-17 select * from t5 order by colint; colint col1 60 2006-11-17 select * from t6 order by colint; colint col1 60 2006-11-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with weekday(col1) ------------------------------------------------------------------------- delete from t11 where col1='2006-11-17'; delete from t22 where col1='2006-11-17'; delete from t33 where col1='2006-11-17'; delete from t44 where col1='2006-11-17'; delete from t55 where col1='2006-11-17'; delete from t66 where col1='2006-11-17'; select * from t11 order by col1; col1 2006-02-06 select * from t22 order by col1; col1 2006-02-06 2006-05-25 select * from t33 order by col1; col1 2006-02-06 2006-05-25 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-11-17'); insert into t22 values ('2006-11-17'); insert into t33 values ('2006-11-17'); insert into t44 values (60,'2006-11-17'); insert into t55 values (60,'2006-11-17'); insert into t66 values (60,'2006-11-17'); select * from t11 order by col1; col1 2006-02-06 2006-11-17 select * from t22 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t33 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-11-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-11-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-11-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 select * from t33 order by col1; col1 2006-02-06 2006-05-25 2006-11-17 select * from t44 order by colint; colint col1 60 2006-11-17 select * from t55 order by colint; colint col1 60 2006-11-17 select * from t66 order by colint; colint col1 60 2006-11-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- year(col1)-1990 in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with year(col1)-1990 ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(year(col1)-1990) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(year(col1)-1990) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(year(col1)-1990); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(year(col1)-1990) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(year(col1)-1990) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (year('2005-10-14')-1990), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with year(col1)-1990 ------------------------------------------------------------------------- insert into t1 values ('1996-01-03'); insert into t1 values ('2000-02-17'); insert into t2 values ('1996-01-03'); insert into t2 values ('2000-02-17'); insert into t2 values ('2004-05-25'); insert into t3 values ('1996-01-03'); insert into t3 values ('2000-02-17'); insert into t3 values ('2004-05-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select year(col1)-1990 from t1 order by col1; year(col1)-1990 6 10 select * from t1 order by col1; col1 1996-01-03 2000-02-17 select * from t2 order by col1; col1 1996-01-03 2000-02-17 2004-05-25 select * from t3 order by col1; col1 1996-01-03 2000-02-17 2004-05-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2002-02-15' where col1='1996-01-03'; update t2 set col1='2002-02-15' where col1='1996-01-03'; update t3 set col1='2002-02-15' where col1='1996-01-03'; update t4 set col1='2002-02-15' where col1='1996-01-03'; update t5 set col1='2002-02-15' where col1='1996-01-03'; update t6 set col1='2002-02-15' where col1='1996-01-03'; select * from t1 order by col1; col1 2000-02-17 2002-02-15 select * from t2 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t3 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with year(col1)-1990 ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(year(col1)-1990) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(year(col1)-1990) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(year(col1)-1990); alter table t44 partition by range(colint) subpartition by hash(year(col1)-1990) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(year(col1)-1990) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (year('2005-10-14')-1990), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2000-02-17 2002-02-15 select * from t22 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t33 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2000-02-17 2002-02-15 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2000-02-17 2002-02-15 alter table t55 partition by list(colint) subpartition by hash(year(col1)-1990) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (year(col1)-1990) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (year('2005-10-14')-1990), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (year('2005-10-14')-1990), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with year(col1)-1990 ------------------------------------------------------------------------- delete from t1 where col1='2000-02-17'; delete from t2 where col1='2000-02-17'; delete from t3 where col1='2000-02-17'; delete from t4 where col1='2000-02-17'; delete from t5 where col1='2000-02-17'; delete from t6 where col1='2000-02-17'; select * from t1 order by col1; col1 2002-02-15 select * from t2 order by col1; col1 2002-02-15 2004-05-25 select * from t3 order by col1; col1 2002-02-15 2004-05-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2000-02-17'); insert into t2 values ('2000-02-17'); insert into t3 values ('2000-02-17'); insert into t4 values (60,'2000-02-17'); insert into t5 values (60,'2000-02-17'); insert into t6 values (60,'2000-02-17'); select * from t1 order by col1; col1 2000-02-17 2002-02-15 select * from t2 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t3 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2000-02-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2000-02-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2000-02-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 select * from t2 order by col1; col1 2002-02-15 2004-05-25 select * from t3 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t4 order by colint; colint col1 60 2000-02-17 select * from t5 order by colint; colint col1 60 2000-02-17 select * from t6 order by colint; colint col1 60 2000-02-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with year(col1)-1990 ------------------------------------------------------------------------- delete from t11 where col1='2000-02-17'; delete from t22 where col1='2000-02-17'; delete from t33 where col1='2000-02-17'; delete from t44 where col1='2000-02-17'; delete from t55 where col1='2000-02-17'; delete from t66 where col1='2000-02-17'; select * from t11 order by col1; col1 2002-02-15 select * from t22 order by col1; col1 2002-02-15 2004-05-25 select * from t33 order by col1; col1 2002-02-15 2004-05-25 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2000-02-17'); insert into t22 values ('2000-02-17'); insert into t33 values ('2000-02-17'); insert into t44 values (60,'2000-02-17'); insert into t55 values (60,'2000-02-17'); insert into t66 values (60,'2000-02-17'); select * from t11 order by col1; col1 2000-02-17 2002-02-15 select * from t22 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t33 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2000-02-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2000-02-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2000-02-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 select * from t22 order by col1; col1 2002-02-15 2004-05-25 select * from t33 order by col1; col1 2000-02-17 2002-02-15 2004-05-25 select * from t44 order by colint; colint col1 60 2000-02-17 select * from t55 order by colint; colint col1 60 2000-02-17 select * from t66 order by colint; colint col1 60 2000-02-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; ------------------------------------------------------------------------- --- yearweek(col1)-200600 in partition with coltype date ------------------------------------------------------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; ------------------------------------------------------------------------- --- Create tables with yearweek(col1)-200600 ------------------------------------------------------------------------- create table t1 (col1 date) engine='INNODB' partition by range(yearweek(col1)-200600) (partition p0 values less than (15), partition p1 values less than maxvalue); create table t2 (col1 date) engine='INNODB' partition by list(yearweek(col1)-200600) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t3 (col1 date) engine='INNODB' partition by hash(yearweek(col1)-200600); create table t4 (colint int, col1 date) engine='INNODB' partition by range(colint) subpartition by hash(yearweek(col1)-200600) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); create table t5 (colint int, col1 date) engine='INNODB' partition by list(colint) subpartition by hash(yearweek(col1)-200600) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); create table t6 (colint int, col1 date) engine='INNODB' partition by range(colint) (partition p0 values less than (yearweek('2006-10-14')-200600), partition p1 values less than maxvalue); ------------------------------------------------------------------------- --- Access tables with yearweek(col1)-200600 ------------------------------------------------------------------------- insert into t1 values ('2006-01-03'); insert into t1 values ('2006-08-17'); insert into t2 values ('2006-01-03'); insert into t2 values ('2006-08-17'); insert into t2 values ('2006-03-25'); insert into t3 values ('2006-01-03'); insert into t3 values ('2006-08-17'); insert into t3 values ('2006-03-25'); load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5; load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6; select yearweek(col1)-200600 from t1 order by col1; yearweek(col1)-200600 1 33 select * from t1 order by col1; col1 2006-01-03 2006-08-17 select * from t2 order by col1; col1 2006-01-03 2006-03-25 2006-08-17 select * from t3 order by col1; col1 2006-01-03 2006-03-25 2006-08-17 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 update t1 set col1='2006-11-15' where col1='2006-01-03'; update t2 set col1='2006-11-15' where col1='2006-01-03'; update t3 set col1='2006-11-15' where col1='2006-01-03'; update t4 set col1='2006-11-15' where col1='2006-01-03'; update t5 set col1='2006-11-15' where col1='2006-01-03'; update t6 set col1='2006-11-15' where col1='2006-01-03'; select * from t1 order by col1; col1 2006-08-17 2006-11-15 select * from t2 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t3 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Alter tables with yearweek(col1)-200600 ------------------------------------------------------------------------- drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ; create table t11 engine='INNODB' as select * from t1; create table t22 engine='INNODB' as select * from t2; create table t33 engine='INNODB' as select * from t3; create table t44 engine='INNODB' as select * from t4; create table t55 engine='INNODB' as select * from t5; create table t66 engine='INNODB' as select * from t6; alter table t11 partition by range(yearweek(col1)-200600) (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t22 partition by list(yearweek(col1)-200600) (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t33 partition by hash(yearweek(col1)-200600); alter table t44 partition by range(colint) subpartition by hash(yearweek(col1)-200600) subpartitions 2 (partition p0 values less than (15), partition p1 values less than maxvalue); alter table t55 partition by list(colint) subpartition by hash(yearweek(col1)-200600) subpartitions 2 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); alter table t66 partition by range(colint) (partition p0 values less than (yearweek('2006-10-14')-200600), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-08-17 2006-11-15 select * from t22 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t33 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 --------------------------- ---- some alter table begin --------------------------- alter table t11 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t11 order by col1; col1 2006-08-17 2006-11-15 alter table t11 reorganize partition s1 into (partition p0 values less than (15), partition p1 values less than maxvalue); select * from t11 order by col1; col1 2006-08-17 2006-11-15 alter table t55 partition by list(colint) subpartition by hash(yearweek(col1)-200600) subpartitions 5 (partition p0 values in (1,2,3,4,5,6,7,8,9,10), partition p1 values in (11,12,13,14,15,16,17,18,19,20), partition p2 values in (21,22,23,24,25,26,27,28,29,30), partition p3 values in (31,32,33,34,35,36,37,38,39,40), partition p4 values in (41,42,43,44,45,46,47,48,49,50), partition p5 values in (51,52,53,54,55,56,57,58,59,60) ); show create table t55; Table Create Table t55 CREATE TABLE `t55` ( `colint` int(11) DEFAULT NULL, `col1` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (yearweek(col1)-200600) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB) */ select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (yearweek('2006-10-14')-200600), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition p0,p1 into (partition s1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 alter table t66 reorganize partition s1 into (partition p0 values less than (yearweek('2006-10-14')-200600), partition p1 values less than maxvalue); select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with yearweek(col1)-200600 ------------------------------------------------------------------------- delete from t1 where col1='2006-08-17'; delete from t2 where col1='2006-08-17'; delete from t3 where col1='2006-08-17'; delete from t4 where col1='2006-08-17'; delete from t5 where col1='2006-08-17'; delete from t6 where col1='2006-08-17'; select * from t1 order by col1; col1 2006-11-15 select * from t2 order by col1; col1 2006-03-25 2006-11-15 select * from t3 order by col1; col1 2006-03-25 2006-11-15 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t1 values ('2006-08-17'); insert into t2 values ('2006-08-17'); insert into t3 values ('2006-08-17'); insert into t4 values (60,'2006-08-17'); insert into t5 values (60,'2006-08-17'); insert into t6 values (60,'2006-08-17'); select * from t1 order by col1; col1 2006-08-17 2006-11-15 select * from t2 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t3 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t4 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-08-17 select * from t5 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-08-17 select * from t6 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-08-17 alter table t1 drop partition p0; alter table t2 drop partition p0; alter table t4 drop partition p0; alter table t5 drop partition p0; alter table t6 drop partition p0; select * from t1 order by col1; col1 2006-08-17 2006-11-15 select * from t2 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t3 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t4 order by colint; colint col1 60 2006-08-17 select * from t5 order by colint; colint col1 60 2006-08-17 select * from t6 order by colint; colint col1 60 2006-08-17 ------------------------------------------------------------------------- --- Delete rows and partitions of tables with yearweek(col1)-200600 ------------------------------------------------------------------------- delete from t11 where col1='2006-08-17'; delete from t22 where col1='2006-08-17'; delete from t33 where col1='2006-08-17'; delete from t44 where col1='2006-08-17'; delete from t55 where col1='2006-08-17'; delete from t66 where col1='2006-08-17'; select * from t11 order by col1; col1 2006-11-15 select * from t22 order by col1; col1 2006-03-25 2006-11-15 select * from t33 order by col1; col1 2006-03-25 2006-11-15 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 insert into t11 values ('2006-08-17'); insert into t22 values ('2006-08-17'); insert into t33 values ('2006-08-17'); insert into t44 values (60,'2006-08-17'); insert into t55 values (60,'2006-08-17'); insert into t66 values (60,'2006-08-17'); select * from t11 order by col1; col1 2006-08-17 2006-11-15 select * from t22 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t33 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t44 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-08-17 select * from t55 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-08-17 select * from t66 order by colint; colint col1 1 2006-02-03 2 2006-01-17 3 2006-01-25 4 2006-02-05 60 2006-08-17 alter table t11 drop partition p0; alter table t22 drop partition p0; alter table t44 drop partition p0; alter table t55 drop partition p0; alter table t66 drop partition p0; select * from t11 order by col1; col1 2006-08-17 2006-11-15 select * from t22 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t33 order by col1; col1 2006-03-25 2006-08-17 2006-11-15 select * from t44 order by colint; colint col1 60 2006-08-17 select * from t55 order by colint; colint col1 60 2006-08-17 select * from t66 order by colint; colint col1 60 2006-08-17 ------------------------- ---- some alter table end ------------------------- drop table if exists t1 ; drop table if exists t2 ; drop table if exists t3 ; drop table if exists t4 ; drop table if exists t5 ; drop table if exists t6 ; drop table if exists t11 ; drop table if exists t22 ; drop table if exists t33 ; drop table if exists t44 ; drop table if exists t55 ; drop table if exists t66 ;