5425
5425
drop table if exists t55 ;
5426
5426
drop table if exists t66 ;
5427
5427
-------------------------------------------------------------------------
5428
--- dayofyear(col1) in partition with coltype char(30)
5429
-------------------------------------------------------------------------
5430
drop table if exists t1 ;
5431
drop table if exists t2 ;
5432
drop table if exists t3 ;
5433
drop table if exists t4 ;
5434
drop table if exists t5 ;
5435
drop table if exists t6 ;
5436
-------------------------------------------------------------------------
5437
--- Create tables with dayofyear(col1)
5438
-------------------------------------------------------------------------
5439
create table t1 (col1 char(30)) engine='MYISAM'
5440
partition by range(dayofyear(col1))
5441
(partition p0 values less than (15),
5442
partition p1 values less than maxvalue);
5443
create table t2 (col1 char(30)) engine='MYISAM'
5444
partition by list(dayofyear(col1))
5445
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5446
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5447
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5448
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5449
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5450
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5452
create table t3 (col1 char(30)) engine='MYISAM'
5453
partition by hash(dayofyear(col1));
5454
create table t4 (colint int, col1 char(30)) engine='MYISAM'
5455
partition by range(colint)
5456
subpartition by hash(dayofyear(col1)) subpartitions 2
5457
(partition p0 values less than (15),
5458
partition p1 values less than maxvalue);
5459
create table t5 (colint int, col1 char(30)) engine='MYISAM'
5460
partition by list(colint)
5461
subpartition by hash(dayofyear(col1)) subpartitions 2
5462
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5463
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5464
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5465
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5466
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5467
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5469
create table t6 (colint int, col1 char(30)) engine='MYISAM'
5470
partition by range(colint)
5471
(partition p0 values less than (dayofyear('2006-12-25')),
5472
partition p1 values less than maxvalue);
5473
-------------------------------------------------------------------------
5474
--- Access tables with dayofyear(col1)
5475
-------------------------------------------------------------------------
5476
insert into t1 values ('2006-01-03');
5477
insert into t1 values ('2006-01-17');
5478
insert into t2 values ('2006-01-03');
5479
insert into t2 values ('2006-01-17');
5480
insert into t2 values ('2006-02-25');
5481
insert into t3 values ('2006-01-03');
5482
insert into t3 values ('2006-01-17');
5483
insert into t3 values ('2006-02-25');
5484
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
5485
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
5486
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
5487
select dayofyear(col1) from t1 order by col1;
5491
select * from t1 order by col1;
5495
select * from t2 order by col1;
5500
select * from t3 order by col1;
5505
select * from t4 order by colint;
5511
select * from t5 order by colint;
5517
select * from t6 order by colint;
5523
update t1 set col1='2006-02-05' where col1='2006-01-03';
5524
update t2 set col1='2006-02-05' where col1='2006-01-03';
5525
update t3 set col1='2006-02-05' where col1='2006-01-03';
5526
update t4 set col1='2006-02-05' where col1='2006-01-03';
5527
update t5 set col1='2006-02-05' where col1='2006-01-03';
5528
update t6 set col1='2006-02-05' where col1='2006-01-03';
5529
select * from t1 order by col1;
5533
select * from t2 order by col1;
5538
select * from t3 order by col1;
5543
select * from t4 order by colint;
5549
select * from t5 order by colint;
5555
select * from t6 order by colint;
5561
-------------------------------------------------------------------------
5562
--- Alter tables with dayofyear(col1)
5563
-------------------------------------------------------------------------
5564
drop table if exists t11 ;
5565
drop table if exists t22 ;
5566
drop table if exists t33 ;
5567
drop table if exists t44 ;
5568
drop table if exists t55 ;
5569
drop table if exists t66 ;
5570
create table t11 engine='MYISAM' as select * from t1;
5571
create table t22 engine='MYISAM' as select * from t2;
5572
create table t33 engine='MYISAM' as select * from t3;
5573
create table t44 engine='MYISAM' as select * from t4;
5574
create table t55 engine='MYISAM' as select * from t5;
5575
create table t66 engine='MYISAM' as select * from t6;
5577
partition by range(dayofyear(col1))
5578
(partition p0 values less than (15),
5579
partition p1 values less than maxvalue);
5581
partition by list(dayofyear(col1))
5582
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5583
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5584
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5585
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5586
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5587
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5590
partition by hash(dayofyear(col1));
5592
partition by range(colint)
5593
subpartition by hash(dayofyear(col1)) subpartitions 2
5594
(partition p0 values less than (15),
5595
partition p1 values less than maxvalue);
5597
partition by list(colint)
5598
subpartition by hash(dayofyear(col1)) subpartitions 2
5599
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5600
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5601
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5602
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5603
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5604
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5607
partition by range(colint)
5608
(partition p0 values less than (dayofyear('2006-12-25')),
5609
partition p1 values less than maxvalue);
5610
select * from t11 order by col1;
5614
select * from t22 order by col1;
5619
select * from t33 order by col1;
5624
select * from t44 order by colint;
5630
select * from t55 order by colint;
5636
select * from t66 order by colint;
5642
---------------------------
5643
---- some alter table begin
5644
---------------------------
5646
reorganize partition p0,p1 into
5647
(partition s1 values less than maxvalue);
5648
select * from t11 order by col1;
5653
reorganize partition s1 into
5654
(partition p0 values less than (15),
5655
partition p1 values less than maxvalue);
5656
select * from t11 order by col1;
5661
partition by list(colint)
5662
subpartition by hash(dayofyear(col1)) subpartitions 5
5663
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
5664
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5665
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5666
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5667
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5668
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5670
show create table t55;
5672
t55 CREATE TABLE `t55` (
5673
`colint` int(11) DEFAULT NULL,
5674
`col1` char(30) DEFAULT NULL
5675
) ENGINE=MyISAM DEFAULT CHARSET=latin1
5676
/*!50100 PARTITION BY LIST (colint)
5677
SUBPARTITION BY HASH (dayofyear(col1))
5679
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
5680
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
5681
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
5682
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
5683
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
5684
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
5685
select * from t55 order by colint;
5692
reorganize partition p0,p1 into
5693
(partition s1 values less than maxvalue);
5694
select * from t66 order by colint;
5701
reorganize partition s1 into
5702
(partition p0 values less than (dayofyear('2006-12-25')),
5703
partition p1 values less than maxvalue);
5704
select * from t66 order by colint;
5711
reorganize partition p0,p1 into
5712
(partition s1 values less than maxvalue);
5713
select * from t66 order by colint;
5720
reorganize partition s1 into
5721
(partition p0 values less than (dayofyear('2006-12-25')),
5722
partition p1 values less than maxvalue);
5723
select * from t66 order by colint;
5729
-------------------------------------------------------------------------
5730
--- Delete rows and partitions of tables with dayofyear(col1)
5731
-------------------------------------------------------------------------
5732
delete from t1 where col1='2006-01-17';
5733
delete from t2 where col1='2006-01-17';
5734
delete from t3 where col1='2006-01-17';
5735
delete from t4 where col1='2006-01-17';
5736
delete from t5 where col1='2006-01-17';
5737
delete from t6 where col1='2006-01-17';
5738
select * from t1 order by col1;
5741
select * from t2 order by col1;
5745
select * from t3 order by col1;
5749
select * from t4 order by colint;
5754
select * from t5 order by colint;
5759
insert into t1 values ('2006-01-17');
5760
insert into t2 values ('2006-01-17');
5761
insert into t3 values ('2006-01-17');
5762
insert into t4 values (60,'2006-01-17');
5763
insert into t5 values (60,'2006-01-17');
5764
insert into t6 values (60,'2006-01-17');
5765
select * from t1 order by col1;
5769
select * from t2 order by col1;
5774
select * from t3 order by col1;
5779
select * from t4 order by colint;
5785
select * from t5 order by colint;
5791
select * from t6 order by colint;
5797
alter table t1 drop partition p0;
5798
alter table t2 drop partition p0;
5799
alter table t4 drop partition p0;
5800
alter table t5 drop partition p0;
5801
alter table t6 drop partition p0;
5802
select * from t1 order by col1;
5806
select * from t2 order by col1;
5811
select * from t3 order by col1;
5816
select * from t4 order by colint;
5819
select * from t5 order by colint;
5822
select * from t6 order by colint;
5824
-------------------------------------------------------------------------
5825
--- Delete rows and partitions of tables with dayofyear(col1)
5826
-------------------------------------------------------------------------
5827
delete from t11 where col1='2006-01-17';
5828
delete from t22 where col1='2006-01-17';
5829
delete from t33 where col1='2006-01-17';
5830
delete from t44 where col1='2006-01-17';
5831
delete from t55 where col1='2006-01-17';
5832
delete from t66 where col1='2006-01-17';
5833
select * from t11 order by col1;
5836
select * from t22 order by col1;
5840
select * from t33 order by col1;
5844
select * from t44 order by colint;
5849
select * from t55 order by colint;
5854
insert into t11 values ('2006-01-17');
5855
insert into t22 values ('2006-01-17');
5856
insert into t33 values ('2006-01-17');
5857
insert into t44 values (60,'2006-01-17');
5858
insert into t55 values (60,'2006-01-17');
5859
insert into t66 values (60,'2006-01-17');
5860
select * from t11 order by col1;
5864
select * from t22 order by col1;
5869
select * from t33 order by col1;
5874
select * from t44 order by colint;
5880
select * from t55 order by colint;
5886
select * from t66 order by colint;
5892
alter table t11 drop partition p0;
5893
alter table t22 drop partition p0;
5894
alter table t44 drop partition p0;
5895
alter table t55 drop partition p0;
5896
alter table t66 drop partition p0;
5897
select * from t11 order by col1;
5901
select * from t22 order by col1;
5906
select * from t33 order by col1;
5911
select * from t44 order by colint;
5914
select * from t55 order by colint;
5917
select * from t66 order by colint;
5919
-------------------------
5920
---- some alter table end
5921
-------------------------
5922
drop table if exists t1 ;
5923
drop table if exists t2 ;
5924
drop table if exists t3 ;
5925
drop table if exists t4 ;
5926
drop table if exists t5 ;
5927
drop table if exists t6 ;
5928
drop table if exists t11 ;
5929
drop table if exists t22 ;
5930
drop table if exists t33 ;
5931
drop table if exists t44 ;
5932
drop table if exists t55 ;
5933
drop table if exists t66 ;
5934
-------------------------------------------------------------------------
5935
5428
--- extract(month from col1) in partition with coltype date
5936
5429
-------------------------------------------------------------------------
5937
5430
drop table if exists t1 ;
8489
7982
drop table if exists t55 ;
8490
7983
drop table if exists t66 ;
8491
7984
-------------------------------------------------------------------------
8492
--- second(col1) in partition with coltype char(30)
8493
-------------------------------------------------------------------------
8494
drop table if exists t1 ;
8495
drop table if exists t2 ;
8496
drop table if exists t3 ;
8497
drop table if exists t4 ;
8498
drop table if exists t5 ;
8499
drop table if exists t6 ;
8500
-------------------------------------------------------------------------
8501
--- Create tables with second(col1)
8502
-------------------------------------------------------------------------
8503
create table t1 (col1 char(30)) engine='MYISAM'
8504
partition by range(second(col1))
8505
(partition p0 values less than (15),
8506
partition p1 values less than maxvalue);
8507
create table t2 (col1 char(30)) engine='MYISAM'
8508
partition by list(second(col1))
8509
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8510
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8511
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8512
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8513
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8514
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8516
create table t3 (col1 char(30)) engine='MYISAM'
8517
partition by hash(second(col1));
8518
create table t4 (colint int, col1 char(30)) engine='MYISAM'
8519
partition by range(colint)
8520
subpartition by hash(second(col1)) subpartitions 2
8521
(partition p0 values less than (15),
8522
partition p1 values less than maxvalue);
8523
create table t5 (colint int, col1 char(30)) engine='MYISAM'
8524
partition by list(colint)
8525
subpartition by hash(second(col1)) subpartitions 2
8526
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8527
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8528
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8529
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8530
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8531
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8533
create table t6 (colint int, col1 char(30)) engine='MYISAM'
8534
partition by range(colint)
8535
(partition p0 values less than (second('18:30:14')),
8536
partition p1 values less than maxvalue);
8537
-------------------------------------------------------------------------
8538
--- Access tables with second(col1)
8539
-------------------------------------------------------------------------
8540
insert into t1 values ('09:09:09');
8541
insert into t1 values ('14:30:20');
8542
insert into t2 values ('09:09:09');
8543
insert into t2 values ('14:30:20');
8544
insert into t2 values ('21:59:22');
8545
insert into t3 values ('09:09:09');
8546
insert into t3 values ('14:30:20');
8547
insert into t3 values ('21:59:22');
8548
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
8549
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
8550
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
8551
select second(col1) from t1 order by col1;
8555
select * from t1 order by col1;
8559
select * from t2 order by col1;
8564
select * from t3 order by col1;
8569
select * from t4 order by colint;
8575
select * from t5 order by colint;
8581
select * from t6 order by colint;
8587
update t1 set col1='10:22:33' where col1='09:09:09';
8588
update t2 set col1='10:22:33' where col1='09:09:09';
8589
update t3 set col1='10:22:33' where col1='09:09:09';
8590
update t4 set col1='10:22:33' where col1='09:09:09';
8591
update t5 set col1='10:22:33' where col1='09:09:09';
8592
update t6 set col1='10:22:33' where col1='09:09:09';
8593
select * from t1 order by col1;
8597
select * from t2 order by col1;
8602
select * from t3 order by col1;
8607
select * from t4 order by colint;
8613
select * from t5 order by colint;
8619
select * from t6 order by colint;
8625
-------------------------------------------------------------------------
8626
--- Alter tables with second(col1)
8627
-------------------------------------------------------------------------
8628
drop table if exists t11 ;
8629
drop table if exists t22 ;
8630
drop table if exists t33 ;
8631
drop table if exists t44 ;
8632
drop table if exists t55 ;
8633
drop table if exists t66 ;
8634
create table t11 engine='MYISAM' as select * from t1;
8635
create table t22 engine='MYISAM' as select * from t2;
8636
create table t33 engine='MYISAM' as select * from t3;
8637
create table t44 engine='MYISAM' as select * from t4;
8638
create table t55 engine='MYISAM' as select * from t5;
8639
create table t66 engine='MYISAM' as select * from t6;
8641
partition by range(second(col1))
8642
(partition p0 values less than (15),
8643
partition p1 values less than maxvalue);
8645
partition by list(second(col1))
8646
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8647
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8648
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8649
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8650
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8651
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8654
partition by hash(second(col1));
8656
partition by range(colint)
8657
subpartition by hash(second(col1)) subpartitions 2
8658
(partition p0 values less than (15),
8659
partition p1 values less than maxvalue);
8661
partition by list(colint)
8662
subpartition by hash(second(col1)) subpartitions 2
8663
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8664
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8665
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8666
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8667
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8668
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8671
partition by range(colint)
8672
(partition p0 values less than (second('18:30:14')),
8673
partition p1 values less than maxvalue);
8674
select * from t11 order by col1;
8678
select * from t22 order by col1;
8683
select * from t33 order by col1;
8688
select * from t44 order by colint;
8694
select * from t55 order by colint;
8700
select * from t66 order by colint;
8706
---------------------------
8707
---- some alter table begin
8708
---------------------------
8710
reorganize partition p0,p1 into
8711
(partition s1 values less than maxvalue);
8712
select * from t11 order by col1;
8717
reorganize partition s1 into
8718
(partition p0 values less than (15),
8719
partition p1 values less than maxvalue);
8720
select * from t11 order by col1;
8725
partition by list(colint)
8726
subpartition by hash(second(col1)) subpartitions 5
8727
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
8728
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8729
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8730
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8731
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8732
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8734
show create table t55;
8736
t55 CREATE TABLE `t55` (
8737
`colint` int(11) DEFAULT NULL,
8738
`col1` char(30) DEFAULT NULL
8739
) ENGINE=MyISAM DEFAULT CHARSET=latin1
8740
/*!50100 PARTITION BY LIST (colint)
8741
SUBPARTITION BY HASH (second(col1))
8743
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
8744
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
8745
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
8746
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
8747
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
8748
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
8749
select * from t55 order by colint;
8756
reorganize partition p0,p1 into
8757
(partition s1 values less than maxvalue);
8758
select * from t66 order by colint;
8765
reorganize partition s1 into
8766
(partition p0 values less than (second('18:30:14')),
8767
partition p1 values less than maxvalue);
8768
select * from t66 order by colint;
8775
reorganize partition p0,p1 into
8776
(partition s1 values less than maxvalue);
8777
select * from t66 order by colint;
8784
reorganize partition s1 into
8785
(partition p0 values less than (second('18:30:14')),
8786
partition p1 values less than maxvalue);
8787
select * from t66 order by colint;
8793
-------------------------------------------------------------------------
8794
--- Delete rows and partitions of tables with second(col1)
8795
-------------------------------------------------------------------------
8796
delete from t1 where col1='14:30:20';
8797
delete from t2 where col1='14:30:20';
8798
delete from t3 where col1='14:30:20';
8799
delete from t4 where col1='14:30:20';
8800
delete from t5 where col1='14:30:20';
8801
delete from t6 where col1='14:30:20';
8802
select * from t1 order by col1;
8805
select * from t2 order by col1;
8809
select * from t3 order by col1;
8813
select * from t4 order by colint;
8819
select * from t5 order by colint;
8825
insert into t1 values ('14:30:20');
8826
insert into t2 values ('14:30:20');
8827
insert into t3 values ('14:30:20');
8828
insert into t4 values (60,'14:30:20');
8829
insert into t5 values (60,'14:30:20');
8830
insert into t6 values (60,'14:30:20');
8831
select * from t1 order by col1;
8835
select * from t2 order by col1;
8840
select * from t3 order by col1;
8845
select * from t4 order by colint;
8852
select * from t5 order by colint;
8859
select * from t6 order by colint;
8866
alter table t1 drop partition p0;
8867
alter table t2 drop partition p0;
8868
alter table t4 drop partition p0;
8869
alter table t5 drop partition p0;
8870
alter table t6 drop partition p0;
8871
select * from t1 order by col1;
8875
select * from t2 order by col1;
8880
select * from t3 order by col1;
8885
select * from t4 order by colint;
8888
select * from t5 order by colint;
8891
select * from t6 order by colint;
8894
-------------------------------------------------------------------------
8895
--- Delete rows and partitions of tables with second(col1)
8896
-------------------------------------------------------------------------
8897
delete from t11 where col1='14:30:20';
8898
delete from t22 where col1='14:30:20';
8899
delete from t33 where col1='14:30:20';
8900
delete from t44 where col1='14:30:20';
8901
delete from t55 where col1='14:30:20';
8902
delete from t66 where col1='14:30:20';
8903
select * from t11 order by col1;
8906
select * from t22 order by col1;
8910
select * from t33 order by col1;
8914
select * from t44 order by colint;
8920
select * from t55 order by colint;
8926
insert into t11 values ('14:30:20');
8927
insert into t22 values ('14:30:20');
8928
insert into t33 values ('14:30:20');
8929
insert into t44 values (60,'14:30:20');
8930
insert into t55 values (60,'14:30:20');
8931
insert into t66 values (60,'14:30:20');
8932
select * from t11 order by col1;
8936
select * from t22 order by col1;
8941
select * from t33 order by col1;
8946
select * from t44 order by colint;
8953
select * from t55 order by colint;
8960
select * from t66 order by colint;
8967
alter table t11 drop partition p0;
8968
alter table t22 drop partition p0;
8969
alter table t44 drop partition p0;
8970
alter table t55 drop partition p0;
8971
alter table t66 drop partition p0;
8972
select * from t11 order by col1;
8976
select * from t22 order by col1;
8981
select * from t33 order by col1;
8986
select * from t44 order by colint;
8989
select * from t55 order by colint;
8992
select * from t66 order by colint;
8995
-------------------------
8996
---- some alter table end
8997
-------------------------
8998
drop table if exists t1 ;
8999
drop table if exists t2 ;
9000
drop table if exists t3 ;
9001
drop table if exists t4 ;
9002
drop table if exists t5 ;
9003
drop table if exists t6 ;
9004
drop table if exists t11 ;
9005
drop table if exists t22 ;
9006
drop table if exists t33 ;
9007
drop table if exists t44 ;
9008
drop table if exists t55 ;
9009
drop table if exists t66 ;
9010
-------------------------------------------------------------------------
9011
7985
--- month(col1) in partition with coltype date
9012
7986
-------------------------------------------------------------------------
9013
7987
drop table if exists t1 ;
10549
9523
drop table if exists t55 ;
10550
9524
drop table if exists t66 ;
10551
9525
-------------------------------------------------------------------------
10552
--- to_days(col1)-to_days('2006-01-01') in partition with coltype date
10553
-------------------------------------------------------------------------
10554
drop table if exists t1 ;
10555
drop table if exists t2 ;
10556
drop table if exists t3 ;
10557
drop table if exists t4 ;
10558
drop table if exists t5 ;
10559
drop table if exists t6 ;
10560
-------------------------------------------------------------------------
10561
--- Create tables with to_days(col1)-to_days('2006-01-01')
10562
-------------------------------------------------------------------------
10563
create table t1 (col1 date) engine='MYISAM'
10564
partition by range(to_days(col1)-to_days('2006-01-01'))
10565
(partition p0 values less than (15),
10566
partition p1 values less than maxvalue);
10567
create table t2 (col1 date) engine='MYISAM'
10568
partition by list(to_days(col1)-to_days('2006-01-01'))
10569
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10570
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10571
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10572
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10573
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10574
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10576
create table t3 (col1 date) engine='MYISAM'
10577
partition by hash(to_days(col1)-to_days('2006-01-01'));
10578
create table t4 (colint int, col1 date) engine='MYISAM'
10579
partition by range(colint)
10580
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10581
(partition p0 values less than (15),
10582
partition p1 values less than maxvalue);
10583
create table t5 (colint int, col1 date) engine='MYISAM'
10584
partition by list(colint)
10585
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10586
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10587
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10588
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10589
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10590
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10591
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10593
create table t6 (colint int, col1 date) engine='MYISAM'
10594
partition by range(colint)
10595
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10596
partition p1 values less than maxvalue);
10597
-------------------------------------------------------------------------
10598
--- Access tables with to_days(col1)-to_days('2006-01-01')
10599
-------------------------------------------------------------------------
10600
insert into t1 values ('2006-02-03');
10601
insert into t1 values ('2006-01-17');
10602
insert into t2 values ('2006-02-03');
10603
insert into t2 values ('2006-01-17');
10604
insert into t2 values ('2006-01-25');
10605
insert into t3 values ('2006-02-03');
10606
insert into t3 values ('2006-01-17');
10607
insert into t3 values ('2006-01-25');
10608
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
10609
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
10610
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
10611
select to_days(col1)-to_days('2006-01-01') from t1 order by col1;
10612
to_days(col1)-to_days('2006-01-01')
10615
select * from t1 order by col1;
10619
select * from t2 order by col1;
10624
select * from t3 order by col1;
10629
select * from t4 order by colint;
10635
select * from t5 order by colint;
10641
select * from t6 order by colint;
10647
update t1 set col1='2006-02-06' where col1='2006-02-03';
10648
update t2 set col1='2006-02-06' where col1='2006-02-03';
10649
update t3 set col1='2006-02-06' where col1='2006-02-03';
10650
update t4 set col1='2006-02-06' where col1='2006-02-03';
10651
update t5 set col1='2006-02-06' where col1='2006-02-03';
10652
update t6 set col1='2006-02-06' where col1='2006-02-03';
10653
select * from t1 order by col1;
10657
select * from t2 order by col1;
10662
select * from t3 order by col1;
10667
select * from t4 order by colint;
10673
select * from t5 order by colint;
10679
select * from t6 order by colint;
10685
-------------------------------------------------------------------------
10686
--- Alter tables with to_days(col1)-to_days('2006-01-01')
10687
-------------------------------------------------------------------------
10688
drop table if exists t11 ;
10689
drop table if exists t22 ;
10690
drop table if exists t33 ;
10691
drop table if exists t44 ;
10692
drop table if exists t55 ;
10693
drop table if exists t66 ;
10694
create table t11 engine='MYISAM' as select * from t1;
10695
create table t22 engine='MYISAM' as select * from t2;
10696
create table t33 engine='MYISAM' as select * from t3;
10697
create table t44 engine='MYISAM' as select * from t4;
10698
create table t55 engine='MYISAM' as select * from t5;
10699
create table t66 engine='MYISAM' as select * from t6;
10701
partition by range(to_days(col1)-to_days('2006-01-01'))
10702
(partition p0 values less than (15),
10703
partition p1 values less than maxvalue);
10705
partition by list(to_days(col1)-to_days('2006-01-01'))
10706
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
10707
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10708
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10709
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10710
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10711
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10714
partition by hash(to_days(col1)-to_days('2006-01-01'));
10716
partition by range(colint)
10717
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10718
(partition p0 values less than (15),
10719
partition p1 values less than maxvalue);
10721
partition by list(colint)
10722
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
10723
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10724
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10725
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10726
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10727
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10728
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10731
partition by range(colint)
10732
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10733
partition p1 values less than maxvalue);
10734
select * from t11 order by col1;
10738
select * from t22 order by col1;
10743
select * from t33 order by col1;
10748
select * from t44 order by colint;
10754
select * from t55 order by colint;
10760
select * from t66 order by colint;
10766
---------------------------
10767
---- some alter table begin
10768
---------------------------
10770
reorganize partition p0,p1 into
10771
(partition s1 values less than maxvalue);
10772
select * from t11 order by col1;
10777
reorganize partition s1 into
10778
(partition p0 values less than (15),
10779
partition p1 values less than maxvalue);
10780
select * from t11 order by col1;
10785
partition by list(colint)
10786
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 5
10787
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
10788
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
10789
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
10790
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
10791
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
10792
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
10794
show create table t55;
10796
t55 CREATE TABLE `t55` (
10797
`colint` int(11) DEFAULT NULL,
10798
`col1` date DEFAULT NULL
10799
) ENGINE=MyISAM DEFAULT CHARSET=latin1
10800
/*!50100 PARTITION BY LIST (colint)
10801
SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01'))
10803
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
10804
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
10805
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
10806
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
10807
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
10808
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
10809
select * from t55 order by colint;
10816
reorganize partition p0,p1 into
10817
(partition s1 values less than maxvalue);
10818
select * from t66 order by colint;
10825
reorganize partition s1 into
10826
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10827
partition p1 values less than maxvalue);
10828
select * from t66 order by colint;
10835
reorganize partition p0,p1 into
10836
(partition s1 values less than maxvalue);
10837
select * from t66 order by colint;
10844
reorganize partition s1 into
10845
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
10846
partition p1 values less than maxvalue);
10847
select * from t66 order by colint;
10853
-------------------------------------------------------------------------
10854
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
10855
-------------------------------------------------------------------------
10856
delete from t1 where col1='2006-01-17';
10857
delete from t2 where col1='2006-01-17';
10858
delete from t3 where col1='2006-01-17';
10859
delete from t4 where col1='2006-01-17';
10860
delete from t5 where col1='2006-01-17';
10861
delete from t6 where col1='2006-01-17';
10862
select * from t1 order by col1;
10865
select * from t2 order by col1;
10869
select * from t3 order by col1;
10873
select * from t4 order by colint;
10878
select * from t5 order by colint;
10883
insert into t1 values ('2006-01-17');
10884
insert into t2 values ('2006-01-17');
10885
insert into t3 values ('2006-01-17');
10886
insert into t4 values (60,'2006-01-17');
10887
insert into t5 values (60,'2006-01-17');
10888
insert into t6 values (60,'2006-01-17');
10889
select * from t1 order by col1;
10893
select * from t2 order by col1;
10898
select * from t3 order by col1;
10903
select * from t4 order by colint;
10909
select * from t5 order by colint;
10915
select * from t6 order by colint;
10921
alter table t1 drop partition p0;
10922
alter table t2 drop partition p0;
10923
alter table t4 drop partition p0;
10924
alter table t5 drop partition p0;
10925
alter table t6 drop partition p0;
10926
select * from t1 order by col1;
10930
select * from t2 order by col1;
10935
select * from t3 order by col1;
10940
select * from t4 order by colint;
10943
select * from t5 order by colint;
10946
select * from t6 order by colint;
10949
-------------------------------------------------------------------------
10950
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
10951
-------------------------------------------------------------------------
10952
delete from t11 where col1='2006-01-17';
10953
delete from t22 where col1='2006-01-17';
10954
delete from t33 where col1='2006-01-17';
10955
delete from t44 where col1='2006-01-17';
10956
delete from t55 where col1='2006-01-17';
10957
delete from t66 where col1='2006-01-17';
10958
select * from t11 order by col1;
10961
select * from t22 order by col1;
10965
select * from t33 order by col1;
10969
select * from t44 order by colint;
10974
select * from t55 order by colint;
10979
insert into t11 values ('2006-01-17');
10980
insert into t22 values ('2006-01-17');
10981
insert into t33 values ('2006-01-17');
10982
insert into t44 values (60,'2006-01-17');
10983
insert into t55 values (60,'2006-01-17');
10984
insert into t66 values (60,'2006-01-17');
10985
select * from t11 order by col1;
10989
select * from t22 order by col1;
10994
select * from t33 order by col1;
10999
select * from t44 order by colint;
11005
select * from t55 order by colint;
11011
select * from t66 order by colint;
11017
alter table t11 drop partition p0;
11018
alter table t22 drop partition p0;
11019
alter table t44 drop partition p0;
11020
alter table t55 drop partition p0;
11021
alter table t66 drop partition p0;
11022
select * from t11 order by col1;
11026
select * from t22 order by col1;
11031
select * from t33 order by col1;
11036
select * from t44 order by colint;
11039
select * from t55 order by colint;
11042
select * from t66 order by colint;
11045
-------------------------
11046
---- some alter table end
11047
-------------------------
11048
drop table if exists t1 ;
11049
drop table if exists t2 ;
11050
drop table if exists t3 ;
11051
drop table if exists t4 ;
11052
drop table if exists t5 ;
11053
drop table if exists t6 ;
11054
drop table if exists t11 ;
11055
drop table if exists t22 ;
11056
drop table if exists t33 ;
11057
drop table if exists t44 ;
11058
drop table if exists t55 ;
11059
drop table if exists t66 ;
11060
-------------------------------------------------------------------------
11061
--- datediff(col1, '2006-01-01') in partition with coltype date
11062
-------------------------------------------------------------------------
11063
drop table if exists t1 ;
11064
drop table if exists t2 ;
11065
drop table if exists t3 ;
11066
drop table if exists t4 ;
11067
drop table if exists t5 ;
11068
drop table if exists t6 ;
11069
-------------------------------------------------------------------------
11070
--- Create tables with datediff(col1, '2006-01-01')
11071
-------------------------------------------------------------------------
11072
create table t1 (col1 date) engine='MYISAM'
11073
partition by range(datediff(col1, '2006-01-01'))
11074
(partition p0 values less than (15),
11075
partition p1 values less than maxvalue);
11076
create table t2 (col1 date) engine='MYISAM'
11077
partition by list(datediff(col1, '2006-01-01'))
11078
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11079
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11080
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11081
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11082
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11083
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11085
create table t3 (col1 date) engine='MYISAM'
11086
partition by hash(datediff(col1, '2006-01-01'));
11087
create table t4 (colint int, col1 date) engine='MYISAM'
11088
partition by range(colint)
11089
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11090
(partition p0 values less than (15),
11091
partition p1 values less than maxvalue);
11092
create table t5 (colint int, col1 date) engine='MYISAM'
11093
partition by list(colint)
11094
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11095
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11096
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11097
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11098
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11099
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11100
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11102
create table t6 (colint int, col1 date) engine='MYISAM'
11103
partition by range(colint)
11104
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11105
partition p1 values less than maxvalue);
11106
-------------------------------------------------------------------------
11107
--- Access tables with datediff(col1, '2006-01-01')
11108
-------------------------------------------------------------------------
11109
insert into t1 values ('2006-02-03');
11110
insert into t1 values ('2006-01-17');
11111
insert into t2 values ('2006-02-03');
11112
insert into t2 values ('2006-01-17');
11113
insert into t2 values ('2006-01-25');
11114
insert into t3 values ('2006-02-03');
11115
insert into t3 values ('2006-01-17');
11116
insert into t3 values ('2006-01-25');
11117
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
11118
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
11119
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
11120
select datediff(col1, '2006-01-01') from t1 order by col1;
11121
datediff(col1, '2006-01-01')
11124
select * from t1 order by col1;
11128
select * from t2 order by col1;
11133
select * from t3 order by col1;
11138
select * from t4 order by colint;
11144
select * from t5 order by colint;
11150
select * from t6 order by colint;
11156
update t1 set col1='2006-02-06' where col1='2006-02-03';
11157
update t2 set col1='2006-02-06' where col1='2006-02-03';
11158
update t3 set col1='2006-02-06' where col1='2006-02-03';
11159
update t4 set col1='2006-02-06' where col1='2006-02-03';
11160
update t5 set col1='2006-02-06' where col1='2006-02-03';
11161
update t6 set col1='2006-02-06' where col1='2006-02-03';
11162
select * from t1 order by col1;
11166
select * from t2 order by col1;
11171
select * from t3 order by col1;
11176
select * from t4 order by colint;
11182
select * from t5 order by colint;
11188
select * from t6 order by colint;
11194
-------------------------------------------------------------------------
11195
--- Alter tables with datediff(col1, '2006-01-01')
11196
-------------------------------------------------------------------------
11197
drop table if exists t11 ;
11198
drop table if exists t22 ;
11199
drop table if exists t33 ;
11200
drop table if exists t44 ;
11201
drop table if exists t55 ;
11202
drop table if exists t66 ;
11203
create table t11 engine='MYISAM' as select * from t1;
11204
create table t22 engine='MYISAM' as select * from t2;
11205
create table t33 engine='MYISAM' as select * from t3;
11206
create table t44 engine='MYISAM' as select * from t4;
11207
create table t55 engine='MYISAM' as select * from t5;
11208
create table t66 engine='MYISAM' as select * from t6;
11210
partition by range(datediff(col1, '2006-01-01'))
11211
(partition p0 values less than (15),
11212
partition p1 values less than maxvalue);
11214
partition by list(datediff(col1, '2006-01-01'))
11215
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
11216
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11217
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11218
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11219
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11220
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11223
partition by hash(datediff(col1, '2006-01-01'));
11225
partition by range(colint)
11226
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11227
(partition p0 values less than (15),
11228
partition p1 values less than maxvalue);
11230
partition by list(colint)
11231
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
11232
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11233
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11234
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11235
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11236
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11237
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11240
partition by range(colint)
11241
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11242
partition p1 values less than maxvalue);
11243
select * from t11 order by col1;
11247
select * from t22 order by col1;
11252
select * from t33 order by col1;
11257
select * from t44 order by colint;
11263
select * from t55 order by colint;
11269
select * from t66 order by colint;
11275
---------------------------
11276
---- some alter table begin
11277
---------------------------
11279
reorganize partition p0,p1 into
11280
(partition s1 values less than maxvalue);
11281
select * from t11 order by col1;
11286
reorganize partition s1 into
11287
(partition p0 values less than (15),
11288
partition p1 values less than maxvalue);
11289
select * from t11 order by col1;
11294
partition by list(colint)
11295
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 5
11296
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11297
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11298
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11299
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11300
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11301
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11303
show create table t55;
11305
t55 CREATE TABLE `t55` (
11306
`colint` int(11) DEFAULT NULL,
11307
`col1` date DEFAULT NULL
11308
) ENGINE=MyISAM DEFAULT CHARSET=latin1
11309
/*!50100 PARTITION BY LIST (colint)
11310
SUBPARTITION BY HASH (datediff(col1, '2006-01-01'))
11312
(PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = MyISAM,
11313
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = MyISAM,
11314
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = MyISAM,
11315
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = MyISAM,
11316
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = MyISAM,
11317
PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = MyISAM) */
11318
select * from t55 order by colint;
11325
reorganize partition p0,p1 into
11326
(partition s1 values less than maxvalue);
11327
select * from t66 order by colint;
11334
reorganize partition s1 into
11335
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11336
partition p1 values less than maxvalue);
11337
select * from t66 order by colint;
11344
reorganize partition p0,p1 into
11345
(partition s1 values less than maxvalue);
11346
select * from t66 order by colint;
11353
reorganize partition s1 into
11354
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
11355
partition p1 values less than maxvalue);
11356
select * from t66 order by colint;
11362
-------------------------------------------------------------------------
11363
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
11364
-------------------------------------------------------------------------
11365
delete from t1 where col1='2006-01-17';
11366
delete from t2 where col1='2006-01-17';
11367
delete from t3 where col1='2006-01-17';
11368
delete from t4 where col1='2006-01-17';
11369
delete from t5 where col1='2006-01-17';
11370
delete from t6 where col1='2006-01-17';
11371
select * from t1 order by col1;
11374
select * from t2 order by col1;
11378
select * from t3 order by col1;
11382
select * from t4 order by colint;
11387
select * from t5 order by colint;
11392
insert into t1 values ('2006-01-17');
11393
insert into t2 values ('2006-01-17');
11394
insert into t3 values ('2006-01-17');
11395
insert into t4 values (60,'2006-01-17');
11396
insert into t5 values (60,'2006-01-17');
11397
insert into t6 values (60,'2006-01-17');
11398
select * from t1 order by col1;
11402
select * from t2 order by col1;
11407
select * from t3 order by col1;
11412
select * from t4 order by colint;
11418
select * from t5 order by colint;
11424
select * from t6 order by colint;
11430
alter table t1 drop partition p0;
11431
alter table t2 drop partition p0;
11432
alter table t4 drop partition p0;
11433
alter table t5 drop partition p0;
11434
alter table t6 drop partition p0;
11435
select * from t1 order by col1;
11439
select * from t2 order by col1;
11444
select * from t3 order by col1;
11449
select * from t4 order by colint;
11452
select * from t5 order by colint;
11455
select * from t6 order by colint;
11458
-------------------------------------------------------------------------
11459
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
11460
-------------------------------------------------------------------------
11461
delete from t11 where col1='2006-01-17';
11462
delete from t22 where col1='2006-01-17';
11463
delete from t33 where col1='2006-01-17';
11464
delete from t44 where col1='2006-01-17';
11465
delete from t55 where col1='2006-01-17';
11466
delete from t66 where col1='2006-01-17';
11467
select * from t11 order by col1;
11470
select * from t22 order by col1;
11474
select * from t33 order by col1;
11478
select * from t44 order by colint;
11483
select * from t55 order by colint;
11488
insert into t11 values ('2006-01-17');
11489
insert into t22 values ('2006-01-17');
11490
insert into t33 values ('2006-01-17');
11491
insert into t44 values (60,'2006-01-17');
11492
insert into t55 values (60,'2006-01-17');
11493
insert into t66 values (60,'2006-01-17');
11494
select * from t11 order by col1;
11498
select * from t22 order by col1;
11503
select * from t33 order by col1;
11508
select * from t44 order by colint;
11514
select * from t55 order by colint;
11520
select * from t66 order by colint;
11526
alter table t11 drop partition p0;
11527
alter table t22 drop partition p0;
11528
alter table t44 drop partition p0;
11529
alter table t55 drop partition p0;
11530
alter table t66 drop partition p0;
11531
select * from t11 order by col1;
11535
select * from t22 order by col1;
11540
select * from t33 order by col1;
11545
select * from t44 order by colint;
11548
select * from t55 order by colint;
11551
select * from t66 order by colint;
11554
-------------------------
11555
---- some alter table end
11556
-------------------------
11557
drop table if exists t1 ;
11558
drop table if exists t2 ;
11559
drop table if exists t3 ;
11560
drop table if exists t4 ;
11561
drop table if exists t5 ;
11562
drop table if exists t6 ;
11563
drop table if exists t11 ;
11564
drop table if exists t22 ;
11565
drop table if exists t33 ;
11566
drop table if exists t44 ;
11567
drop table if exists t55 ;
11568
drop table if exists t66 ;
11569
-------------------------------------------------------------------------
11570
9526
--- weekday(col1) in partition with coltype date
11571
9527
-------------------------------------------------------------------------
11572
9528
drop table if exists t1 ;