1
-------------------------------------------------------------------------
2
--- abs(col1) in partition with coltype int
3
-------------------------------------------------------------------------
4
drop table if exists t1 ;
5
drop table if exists t2 ;
6
drop table if exists t3 ;
7
drop table if exists t4 ;
8
drop table if exists t5 ;
9
drop table if exists t6 ;
10
-------------------------------------------------------------------------
11
--- Create tables with abs(col1)
12
-------------------------------------------------------------------------
13
create table t1 (col1 int) engine='NDB'
14
partition by range(abs(col1))
15
(partition p0 values less than (15),
16
partition p1 values less than maxvalue);
17
create table t2 (col1 int) engine='NDB'
18
partition by list(abs(col1))
19
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
20
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
21
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
22
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
23
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
24
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
26
create table t3 (col1 int) engine='NDB'
27
partition by hash(abs(col1));
28
create table t4 (colint int, col1 int) engine='NDB'
29
partition by range(colint)
30
subpartition by hash(abs(col1)) subpartitions 2
31
(partition p0 values less than (15),
32
partition p1 values less than maxvalue);
33
create table t5 (colint int, col1 int) engine='NDB'
34
partition by list(colint)
35
subpartition by hash(abs(col1)) subpartitions 2
36
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
37
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
38
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
39
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
41
create table t6 (colint int, col1 int) engine='NDB'
42
partition by range(colint)
43
(partition p0 values less than (abs(15)),
44
partition p1 values less than maxvalue);
45
-------------------------------------------------------------------------
46
--- Access tables with abs(col1)
47
-------------------------------------------------------------------------
48
insert into t1 values (5 );
49
insert into t1 values (13 );
50
insert into t2 values (5 );
51
insert into t2 values (13 );
52
insert into t2 values (17 );
53
insert into t3 values (5 );
54
insert into t3 values (13 );
55
insert into t3 values (17 );
56
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t4;
57
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t5;
58
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t6;
59
select abs(col1) from t1 order by col1;
63
select * from t1 order by col1;
67
select * from t2 order by col1;
72
select * from t3 order by col1;
77
select * from t4 order by colint;
124
select * from t5 order by colint;
171
select * from t6 order by colint;
218
update t1 set col1=15 where col1=5 ;
219
update t2 set col1=15 where col1=5 ;
220
update t3 set col1=15 where col1=5 ;
221
update t4 set col1=15 where col1=5 ;
222
update t5 set col1=15 where col1=5 ;
223
update t6 set col1=15 where col1=5 ;
224
select * from t1 order by col1;
228
select * from t2 order by col1;
233
select * from t3 order by col1;
238
select * from t4 order by colint;
285
select * from t5 order by colint;
332
select * from t6 order by colint;
379
-------------------------------------------------------------------------
380
--- Alter tables with abs(col1)
381
-------------------------------------------------------------------------
382
drop table if exists t11 ;
383
drop table if exists t22 ;
384
drop table if exists t33 ;
385
drop table if exists t44 ;
386
drop table if exists t55 ;
387
drop table if exists t66 ;
388
create table t11 engine='NDB' as select * from t1;
389
create table t22 engine='NDB' as select * from t2;
390
create table t33 engine='NDB' as select * from t3;
391
create table t44 engine='NDB' as select * from t4;
392
create table t55 engine='NDB' as select * from t5;
393
create table t66 engine='NDB' as select * from t6;
395
partition by range(abs(col1))
396
(partition p0 values less than (15),
397
partition p1 values less than maxvalue);
399
partition by list(abs(col1))
400
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
401
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
402
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
403
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
404
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
405
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
408
partition by hash(abs(col1));
410
partition by range(colint)
411
subpartition by hash(abs(col1)) subpartitions 2
412
(partition p0 values less than (15),
413
partition p1 values less than maxvalue);
415
partition by list(colint)
416
subpartition by hash(abs(col1)) subpartitions 2
417
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
418
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
419
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
420
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
423
partition by range(colint)
424
(partition p0 values less than (abs(15)),
425
partition p1 values less than maxvalue);
426
select * from t11 order by col1;
430
select * from t22 order by col1;
435
select * from t33 order by col1;
440
select * from t44 order by colint;
487
select * from t55 order by colint;
534
select * from t66 order by colint;
581
---------------------------
582
---- some alter table begin
583
---------------------------
585
partition by list(colint)
586
subpartition by hash(abs(col1)) subpartitions 4
587
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
588
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
590
show create table t55;
592
t55 CREATE TABLE `t55` (
593
`colint` int(11) DEFAULT NULL,
594
`col1` int(11) DEFAULT NULL
595
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
596
/*!50100 PARTITION BY LIST (colint)
597
SUBPARTITION BY HASH (abs(col1))
599
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
600
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
601
select * from t55 order by colint;
648
-------------------------------------------------------------------------
649
--- Delete rows and partitions of tables with abs(col1)
650
-------------------------------------------------------------------------
651
delete from t1 where col1=13 ;
652
delete from t2 where col1=13 ;
653
delete from t3 where col1=13 ;
654
delete from t4 where col1=13 ;
655
delete from t5 where col1=13 ;
656
delete from t6 where col1=13 ;
657
select * from t1 order by col1;
660
select * from t2 order by col1;
664
select * from t3 order by col1;
668
select * from t4 order by colint;
714
select * from t5 order by colint;
760
insert into t1 values (13 );
761
insert into t2 values (13 );
762
insert into t3 values (13 );
763
insert into t4 values (60,13 );
764
insert into t5 values (60,13 );
765
insert into t6 values (60,13 );
766
select * from t1 order by col1;
770
select * from t2 order by col1;
775
select * from t3 order by col1;
780
select * from t4 order by colint;
827
select * from t5 order by colint;
874
select * from t6 order by colint;
921
-------------------------------------------------------------------------
922
--- Delete rows and partitions of tables with abs(col1)
923
-------------------------------------------------------------------------
924
delete from t11 where col1=13 ;
925
delete from t22 where col1=13 ;
926
delete from t33 where col1=13 ;
927
delete from t44 where col1=13 ;
928
delete from t55 where col1=13 ;
929
delete from t66 where col1=13 ;
930
select * from t11 order by col1;
933
select * from t22 order by col1;
937
select * from t33 order by col1;
941
select * from t44 order by colint;
987
select * from t55 order by colint;
1033
insert into t11 values (13 );
1034
insert into t22 values (13 );
1035
insert into t33 values (13 );
1036
insert into t44 values (60,13 );
1037
insert into t55 values (60,13 );
1038
insert into t66 values (60,13 );
1039
select * from t11 order by col1;
1043
select * from t22 order by col1;
1048
select * from t33 order by col1;
1053
select * from t44 order by colint;
1100
select * from t55 order by colint;
1147
select * from t66 order by colint;
1194
-------------------------
1195
---- some alter table end
1196
-------------------------
1197
drop table if exists t1 ;
1198
drop table if exists t2 ;
1199
drop table if exists t3 ;
1200
drop table if exists t4 ;
1201
drop table if exists t5 ;
1202
drop table if exists t6 ;
1203
drop table if exists t11 ;
1204
drop table if exists t22 ;
1205
drop table if exists t33 ;
1206
drop table if exists t44 ;
1207
drop table if exists t55 ;
1208
drop table if exists t66 ;
1209
-------------------------------------------------------------------------
1210
--- mod(col1,10) in partition with coltype int
1211
-------------------------------------------------------------------------
1212
drop table if exists t1 ;
1213
drop table if exists t2 ;
1214
drop table if exists t3 ;
1215
drop table if exists t4 ;
1216
drop table if exists t5 ;
1217
drop table if exists t6 ;
1218
-------------------------------------------------------------------------
1219
--- Create tables with mod(col1,10)
1220
-------------------------------------------------------------------------
1221
create table t1 (col1 int) engine='NDB'
1222
partition by range(mod(col1,10))
1223
(partition p0 values less than (15),
1224
partition p1 values less than maxvalue);
1225
create table t2 (col1 int) engine='NDB'
1226
partition by list(mod(col1,10))
1227
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1228
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1229
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1230
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1231
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1232
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1234
create table t3 (col1 int) engine='NDB'
1235
partition by hash(mod(col1,10));
1236
create table t4 (colint int, col1 int) engine='NDB'
1237
partition by range(colint)
1238
subpartition by hash(mod(col1,10)) subpartitions 2
1239
(partition p0 values less than (15),
1240
partition p1 values less than maxvalue);
1241
create table t5 (colint int, col1 int) engine='NDB'
1242
partition by list(colint)
1243
subpartition by hash(mod(col1,10)) subpartitions 2
1244
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
1245
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
1246
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
1247
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
1249
create table t6 (colint int, col1 int) engine='NDB'
1250
partition by range(colint)
1251
(partition p0 values less than (mod(15,10)),
1252
partition p1 values less than maxvalue);
1253
-------------------------------------------------------------------------
1254
--- Access tables with mod(col1,10)
1255
-------------------------------------------------------------------------
1256
insert into t1 values (5);
1257
insert into t1 values (19);
1258
insert into t2 values (5);
1259
insert into t2 values (19);
1260
insert into t2 values (17);
1261
insert into t3 values (5);
1262
insert into t3 values (19);
1263
insert into t3 values (17);
1264
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t4;
1265
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t5;
1266
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_int.inc' into table t6;
1267
select mod(col1,10) from t1 order by col1;
1271
select * from t1 order by col1;
1275
select * from t2 order by col1;
1280
select * from t3 order by col1;
1285
select * from t4 order by colint;
1332
select * from t5 order by colint;
1379
select * from t6 order by colint;
1426
update t1 set col1=15 where col1=5;
1427
update t2 set col1=15 where col1=5;
1428
update t3 set col1=15 where col1=5;
1429
update t4 set col1=15 where col1=5;
1430
update t5 set col1=15 where col1=5;
1431
update t6 set col1=15 where col1=5;
1432
select * from t1 order by col1;
1436
select * from t2 order by col1;
1441
select * from t3 order by col1;
1446
select * from t4 order by colint;
1493
select * from t5 order by colint;
1540
select * from t6 order by colint;
1587
-------------------------------------------------------------------------
1588
--- Alter tables with mod(col1,10)
1589
-------------------------------------------------------------------------
1590
drop table if exists t11 ;
1591
drop table if exists t22 ;
1592
drop table if exists t33 ;
1593
drop table if exists t44 ;
1594
drop table if exists t55 ;
1595
drop table if exists t66 ;
1596
create table t11 engine='NDB' as select * from t1;
1597
create table t22 engine='NDB' as select * from t2;
1598
create table t33 engine='NDB' as select * from t3;
1599
create table t44 engine='NDB' as select * from t4;
1600
create table t55 engine='NDB' as select * from t5;
1601
create table t66 engine='NDB' as select * from t6;
1603
partition by range(mod(col1,10))
1604
(partition p0 values less than (15),
1605
partition p1 values less than maxvalue);
1607
partition by list(mod(col1,10))
1608
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1609
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1610
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1611
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1612
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1613
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1616
partition by hash(mod(col1,10));
1618
partition by range(colint)
1619
subpartition by hash(mod(col1,10)) subpartitions 2
1620
(partition p0 values less than (15),
1621
partition p1 values less than maxvalue);
1623
partition by list(colint)
1624
subpartition by hash(mod(col1,10)) subpartitions 2
1625
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
1626
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
1627
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
1628
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
1631
partition by range(colint)
1632
(partition p0 values less than (mod(15,10)),
1633
partition p1 values less than maxvalue);
1634
select * from t11 order by col1;
1638
select * from t22 order by col1;
1643
select * from t33 order by col1;
1648
select * from t44 order by colint;
1695
select * from t55 order by colint;
1742
select * from t66 order by colint;
1789
---------------------------
1790
---- some alter table begin
1791
---------------------------
1793
partition by list(colint)
1794
subpartition by hash(mod(col1,10)) subpartitions 4
1795
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
1796
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
1798
show create table t55;
1800
t55 CREATE TABLE `t55` (
1801
`colint` int(11) DEFAULT NULL,
1802
`col1` int(11) DEFAULT NULL
1803
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1804
/*!50100 PARTITION BY LIST (colint)
1805
SUBPARTITION BY HASH (mod(col1,10))
1807
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
1808
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
1809
select * from t55 order by colint;
1856
-------------------------------------------------------------------------
1857
--- Delete rows and partitions of tables with mod(col1,10)
1858
-------------------------------------------------------------------------
1859
delete from t1 where col1=19;
1860
delete from t2 where col1=19;
1861
delete from t3 where col1=19;
1862
delete from t4 where col1=19;
1863
delete from t5 where col1=19;
1864
delete from t6 where col1=19;
1865
select * from t1 order by col1;
1868
select * from t2 order by col1;
1872
select * from t3 order by col1;
1876
select * from t4 order by colint;
1923
select * from t5 order by colint;
1970
insert into t1 values (19);
1971
insert into t2 values (19);
1972
insert into t3 values (19);
1973
insert into t4 values (60,19);
1974
insert into t5 values (60,19);
1975
insert into t6 values (60,19);
1976
select * from t1 order by col1;
1980
select * from t2 order by col1;
1985
select * from t3 order by col1;
1990
select * from t4 order by colint;
2038
select * from t5 order by colint;
2086
select * from t6 order by colint;
2134
-------------------------------------------------------------------------
2135
--- Delete rows and partitions of tables with mod(col1,10)
2136
-------------------------------------------------------------------------
2137
delete from t11 where col1=19;
2138
delete from t22 where col1=19;
2139
delete from t33 where col1=19;
2140
delete from t44 where col1=19;
2141
delete from t55 where col1=19;
2142
delete from t66 where col1=19;
2143
select * from t11 order by col1;
2146
select * from t22 order by col1;
2150
select * from t33 order by col1;
2154
select * from t44 order by colint;
2201
select * from t55 order by colint;
2248
insert into t11 values (19);
2249
insert into t22 values (19);
2250
insert into t33 values (19);
2251
insert into t44 values (60,19);
2252
insert into t55 values (60,19);
2253
insert into t66 values (60,19);
2254
select * from t11 order by col1;
2258
select * from t22 order by col1;
2263
select * from t33 order by col1;
2268
select * from t44 order by colint;
2316
select * from t55 order by colint;
2364
select * from t66 order by colint;
2412
-------------------------
2413
---- some alter table end
2414
-------------------------
2415
drop table if exists t1 ;
2416
drop table if exists t2 ;
2417
drop table if exists t3 ;
2418
drop table if exists t4 ;
2419
drop table if exists t5 ;
2420
drop table if exists t6 ;
2421
drop table if exists t11 ;
2422
drop table if exists t22 ;
2423
drop table if exists t33 ;
2424
drop table if exists t44 ;
2425
drop table if exists t55 ;
2426
drop table if exists t66 ;
2427
-------------------------------------------------------------------------
2428
--- day(col1) in partition with coltype date
2429
-------------------------------------------------------------------------
2430
drop table if exists t1 ;
2431
drop table if exists t2 ;
2432
drop table if exists t3 ;
2433
drop table if exists t4 ;
2434
drop table if exists t5 ;
2435
drop table if exists t6 ;
2436
-------------------------------------------------------------------------
2437
--- Create tables with day(col1)
2438
-------------------------------------------------------------------------
2439
create table t1 (col1 date) engine='NDB'
2440
partition by range(day(col1))
2441
(partition p0 values less than (15),
2442
partition p1 values less than maxvalue);
2443
create table t2 (col1 date) engine='NDB'
2444
partition by list(day(col1))
2445
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2446
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2447
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2448
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2449
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2450
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2452
create table t3 (col1 date) engine='NDB'
2453
partition by hash(day(col1));
2454
create table t4 (colint int, col1 date) engine='NDB'
2455
partition by range(colint)
2456
subpartition by hash(day(col1)) subpartitions 2
2457
(partition p0 values less than (15),
2458
partition p1 values less than maxvalue);
2459
create table t5 (colint int, col1 date) engine='NDB'
2460
partition by list(colint)
2461
subpartition by hash(day(col1)) subpartitions 2
2462
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
2463
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
2464
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
2465
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
2467
create table t6 (colint int, col1 date) engine='NDB'
2468
partition by range(colint)
2469
(partition p0 values less than (day('2006-12-21')),
2470
partition p1 values less than maxvalue);
2471
-------------------------------------------------------------------------
2472
--- Access tables with day(col1)
2473
-------------------------------------------------------------------------
2474
insert into t1 values ('2006-02-03');
2475
insert into t1 values ('2006-01-17');
2476
insert into t2 values ('2006-02-03');
2477
insert into t2 values ('2006-01-17');
2478
insert into t2 values ('2006-01-25');
2479
insert into t3 values ('2006-02-03');
2480
insert into t3 values ('2006-01-17');
2481
insert into t3 values ('2006-01-25');
2482
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
2483
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
2484
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
2485
select day(col1) from t1 order by col1;
2489
select * from t1 order by col1;
2493
select * from t2 order by col1;
2498
select * from t3 order by col1;
2503
select * from t4 order by colint;
2509
select * from t5 order by colint;
2515
select * from t6 order by colint;
2521
update t1 set col1='2006-02-05' where col1='2006-02-03';
2522
update t2 set col1='2006-02-05' where col1='2006-02-03';
2523
update t3 set col1='2006-02-05' where col1='2006-02-03';
2524
update t4 set col1='2006-02-05' where col1='2006-02-03';
2525
update t5 set col1='2006-02-05' where col1='2006-02-03';
2526
update t6 set col1='2006-02-05' where col1='2006-02-03';
2527
select * from t1 order by col1;
2531
select * from t2 order by col1;
2536
select * from t3 order by col1;
2541
select * from t4 order by colint;
2547
select * from t5 order by colint;
2553
select * from t6 order by colint;
2559
-------------------------------------------------------------------------
2560
--- Alter tables with day(col1)
2561
-------------------------------------------------------------------------
2562
drop table if exists t11 ;
2563
drop table if exists t22 ;
2564
drop table if exists t33 ;
2565
drop table if exists t44 ;
2566
drop table if exists t55 ;
2567
drop table if exists t66 ;
2568
create table t11 engine='NDB' as select * from t1;
2569
create table t22 engine='NDB' as select * from t2;
2570
create table t33 engine='NDB' as select * from t3;
2571
create table t44 engine='NDB' as select * from t4;
2572
create table t55 engine='NDB' as select * from t5;
2573
create table t66 engine='NDB' as select * from t6;
2575
partition by range(day(col1))
2576
(partition p0 values less than (15),
2577
partition p1 values less than maxvalue);
2579
partition by list(day(col1))
2580
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2581
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2582
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2583
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2584
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2585
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2588
partition by hash(day(col1));
2590
partition by range(colint)
2591
subpartition by hash(day(col1)) subpartitions 2
2592
(partition p0 values less than (15),
2593
partition p1 values less than maxvalue);
2595
partition by list(colint)
2596
subpartition by hash(day(col1)) subpartitions 2
2597
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
2598
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
2599
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
2600
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
2603
partition by range(colint)
2604
(partition p0 values less than (day('2006-12-21')),
2605
partition p1 values less than maxvalue);
2606
select * from t11 order by col1;
2610
select * from t22 order by col1;
2615
select * from t33 order by col1;
2620
select * from t44 order by colint;
2626
select * from t55 order by colint;
2632
select * from t66 order by colint;
2638
---------------------------
2639
---- some alter table begin
2640
---------------------------
2642
partition by list(colint)
2643
subpartition by hash(day(col1)) subpartitions 4
2644
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
2645
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
2647
show create table t55;
2649
t55 CREATE TABLE `t55` (
2650
`colint` int(11) DEFAULT NULL,
2651
`col1` date DEFAULT NULL
2652
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
2653
/*!50100 PARTITION BY LIST (colint)
2654
SUBPARTITION BY HASH (day(col1))
2656
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
2657
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
2658
select * from t55 order by colint;
2664
-------------------------------------------------------------------------
2665
--- Delete rows and partitions of tables with day(col1)
2666
-------------------------------------------------------------------------
2667
delete from t1 where col1='2006-01-17';
2668
delete from t2 where col1='2006-01-17';
2669
delete from t3 where col1='2006-01-17';
2670
delete from t4 where col1='2006-01-17';
2671
delete from t5 where col1='2006-01-17';
2672
delete from t6 where col1='2006-01-17';
2673
select * from t1 order by col1;
2676
select * from t2 order by col1;
2680
select * from t3 order by col1;
2684
select * from t4 order by colint;
2689
select * from t5 order by colint;
2694
insert into t1 values ('2006-01-17');
2695
insert into t2 values ('2006-01-17');
2696
insert into t3 values ('2006-01-17');
2697
insert into t4 values (60,'2006-01-17');
2698
insert into t5 values (60,'2006-01-17');
2699
insert into t6 values (60,'2006-01-17');
2700
select * from t1 order by col1;
2704
select * from t2 order by col1;
2709
select * from t3 order by col1;
2714
select * from t4 order by colint;
2720
select * from t5 order by colint;
2726
select * from t6 order by colint;
2732
-------------------------------------------------------------------------
2733
--- Delete rows and partitions of tables with day(col1)
2734
-------------------------------------------------------------------------
2735
delete from t11 where col1='2006-01-17';
2736
delete from t22 where col1='2006-01-17';
2737
delete from t33 where col1='2006-01-17';
2738
delete from t44 where col1='2006-01-17';
2739
delete from t55 where col1='2006-01-17';
2740
delete from t66 where col1='2006-01-17';
2741
select * from t11 order by col1;
2744
select * from t22 order by col1;
2748
select * from t33 order by col1;
2752
select * from t44 order by colint;
2757
select * from t55 order by colint;
2762
insert into t11 values ('2006-01-17');
2763
insert into t22 values ('2006-01-17');
2764
insert into t33 values ('2006-01-17');
2765
insert into t44 values (60,'2006-01-17');
2766
insert into t55 values (60,'2006-01-17');
2767
insert into t66 values (60,'2006-01-17');
2768
select * from t11 order by col1;
2772
select * from t22 order by col1;
2777
select * from t33 order by col1;
2782
select * from t44 order by colint;
2788
select * from t55 order by colint;
2794
select * from t66 order by colint;
2800
-------------------------
2801
---- some alter table end
2802
-------------------------
2803
drop table if exists t1 ;
2804
drop table if exists t2 ;
2805
drop table if exists t3 ;
2806
drop table if exists t4 ;
2807
drop table if exists t5 ;
2808
drop table if exists t6 ;
2809
drop table if exists t11 ;
2810
drop table if exists t22 ;
2811
drop table if exists t33 ;
2812
drop table if exists t44 ;
2813
drop table if exists t55 ;
2814
drop table if exists t66 ;
2815
-------------------------------------------------------------------------
2816
--- dayofmonth(col1) in partition with coltype date
2817
-------------------------------------------------------------------------
2818
drop table if exists t1 ;
2819
drop table if exists t2 ;
2820
drop table if exists t3 ;
2821
drop table if exists t4 ;
2822
drop table if exists t5 ;
2823
drop table if exists t6 ;
2824
-------------------------------------------------------------------------
2825
--- Create tables with dayofmonth(col1)
2826
-------------------------------------------------------------------------
2827
create table t1 (col1 date) engine='NDB'
2828
partition by range(dayofmonth(col1))
2829
(partition p0 values less than (15),
2830
partition p1 values less than maxvalue);
2831
create table t2 (col1 date) engine='NDB'
2832
partition by list(dayofmonth(col1))
2833
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2834
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2835
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2836
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2837
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2838
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2840
create table t3 (col1 date) engine='NDB'
2841
partition by hash(dayofmonth(col1));
2842
create table t4 (colint int, col1 date) engine='NDB'
2843
partition by range(colint)
2844
subpartition by hash(dayofmonth(col1)) subpartitions 2
2845
(partition p0 values less than (15),
2846
partition p1 values less than maxvalue);
2847
create table t5 (colint int, col1 date) engine='NDB'
2848
partition by list(colint)
2849
subpartition by hash(dayofmonth(col1)) subpartitions 2
2850
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
2851
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
2852
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
2853
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
2855
create table t6 (colint int, col1 date) engine='NDB'
2856
partition by range(colint)
2857
(partition p0 values less than (dayofmonth('2006-12-24')),
2858
partition p1 values less than maxvalue);
2859
-------------------------------------------------------------------------
2860
--- Access tables with dayofmonth(col1)
2861
-------------------------------------------------------------------------
2862
insert into t1 values ('2006-02-03');
2863
insert into t1 values ('2006-01-17');
2864
insert into t2 values ('2006-02-03');
2865
insert into t2 values ('2006-01-17');
2866
insert into t2 values ('2006-01-25');
2867
insert into t3 values ('2006-02-03');
2868
insert into t3 values ('2006-01-17');
2869
insert into t3 values ('2006-01-25');
2870
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
2871
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
2872
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
2873
select dayofmonth(col1) from t1 order by col1;
2877
select * from t1 order by col1;
2881
select * from t2 order by col1;
2886
select * from t3 order by col1;
2891
select * from t4 order by colint;
2897
select * from t5 order by colint;
2903
select * from t6 order by colint;
2909
update t1 set col1='2006-02-05' where col1='2006-02-03';
2910
update t2 set col1='2006-02-05' where col1='2006-02-03';
2911
update t3 set col1='2006-02-05' where col1='2006-02-03';
2912
update t4 set col1='2006-02-05' where col1='2006-02-03';
2913
update t5 set col1='2006-02-05' where col1='2006-02-03';
2914
update t6 set col1='2006-02-05' where col1='2006-02-03';
2915
select * from t1 order by col1;
2919
select * from t2 order by col1;
2924
select * from t3 order by col1;
2929
select * from t4 order by colint;
2935
select * from t5 order by colint;
2941
select * from t6 order by colint;
2947
-------------------------------------------------------------------------
2948
--- Alter tables with dayofmonth(col1)
2949
-------------------------------------------------------------------------
2950
drop table if exists t11 ;
2951
drop table if exists t22 ;
2952
drop table if exists t33 ;
2953
drop table if exists t44 ;
2954
drop table if exists t55 ;
2955
drop table if exists t66 ;
2956
create table t11 engine='NDB' as select * from t1;
2957
create table t22 engine='NDB' as select * from t2;
2958
create table t33 engine='NDB' as select * from t3;
2959
create table t44 engine='NDB' as select * from t4;
2960
create table t55 engine='NDB' as select * from t5;
2961
create table t66 engine='NDB' as select * from t6;
2963
partition by range(dayofmonth(col1))
2964
(partition p0 values less than (15),
2965
partition p1 values less than maxvalue);
2967
partition by list(dayofmonth(col1))
2968
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2969
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2970
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2971
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2972
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2973
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2976
partition by hash(dayofmonth(col1));
2978
partition by range(colint)
2979
subpartition by hash(dayofmonth(col1)) subpartitions 2
2980
(partition p0 values less than (15),
2981
partition p1 values less than maxvalue);
2983
partition by list(colint)
2984
subpartition by hash(dayofmonth(col1)) subpartitions 2
2985
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
2986
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
2987
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
2988
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
2991
partition by range(colint)
2992
(partition p0 values less than (dayofmonth('2006-12-24')),
2993
partition p1 values less than maxvalue);
2994
select * from t11 order by col1;
2998
select * from t22 order by col1;
3003
select * from t33 order by col1;
3008
select * from t44 order by colint;
3014
select * from t55 order by colint;
3020
select * from t66 order by colint;
3026
---------------------------
3027
---- some alter table begin
3028
---------------------------
3030
partition by list(colint)
3031
subpartition by hash(dayofmonth(col1)) subpartitions 4
3032
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3033
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3035
show create table t55;
3037
t55 CREATE TABLE `t55` (
3038
`colint` int(11) DEFAULT NULL,
3039
`col1` date DEFAULT NULL
3040
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
3041
/*!50100 PARTITION BY LIST (colint)
3042
SUBPARTITION BY HASH (dayofmonth(col1))
3044
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
3045
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
3046
select * from t55 order by colint;
3052
-------------------------------------------------------------------------
3053
--- Delete rows and partitions of tables with dayofmonth(col1)
3054
-------------------------------------------------------------------------
3055
delete from t1 where col1='2006-01-17';
3056
delete from t2 where col1='2006-01-17';
3057
delete from t3 where col1='2006-01-17';
3058
delete from t4 where col1='2006-01-17';
3059
delete from t5 where col1='2006-01-17';
3060
delete from t6 where col1='2006-01-17';
3061
select * from t1 order by col1;
3064
select * from t2 order by col1;
3068
select * from t3 order by col1;
3072
select * from t4 order by colint;
3077
select * from t5 order by colint;
3082
insert into t1 values ('2006-01-17');
3083
insert into t2 values ('2006-01-17');
3084
insert into t3 values ('2006-01-17');
3085
insert into t4 values (60,'2006-01-17');
3086
insert into t5 values (60,'2006-01-17');
3087
insert into t6 values (60,'2006-01-17');
3088
select * from t1 order by col1;
3092
select * from t2 order by col1;
3097
select * from t3 order by col1;
3102
select * from t4 order by colint;
3108
select * from t5 order by colint;
3114
select * from t6 order by colint;
3120
-------------------------------------------------------------------------
3121
--- Delete rows and partitions of tables with dayofmonth(col1)
3122
-------------------------------------------------------------------------
3123
delete from t11 where col1='2006-01-17';
3124
delete from t22 where col1='2006-01-17';
3125
delete from t33 where col1='2006-01-17';
3126
delete from t44 where col1='2006-01-17';
3127
delete from t55 where col1='2006-01-17';
3128
delete from t66 where col1='2006-01-17';
3129
select * from t11 order by col1;
3132
select * from t22 order by col1;
3136
select * from t33 order by col1;
3140
select * from t44 order by colint;
3145
select * from t55 order by colint;
3150
insert into t11 values ('2006-01-17');
3151
insert into t22 values ('2006-01-17');
3152
insert into t33 values ('2006-01-17');
3153
insert into t44 values (60,'2006-01-17');
3154
insert into t55 values (60,'2006-01-17');
3155
insert into t66 values (60,'2006-01-17');
3156
select * from t11 order by col1;
3160
select * from t22 order by col1;
3165
select * from t33 order by col1;
3170
select * from t44 order by colint;
3176
select * from t55 order by colint;
3182
select * from t66 order by colint;
3188
-------------------------
3189
---- some alter table end
3190
-------------------------
3191
drop table if exists t1 ;
3192
drop table if exists t2 ;
3193
drop table if exists t3 ;
3194
drop table if exists t4 ;
3195
drop table if exists t5 ;
3196
drop table if exists t6 ;
3197
drop table if exists t11 ;
3198
drop table if exists t22 ;
3199
drop table if exists t33 ;
3200
drop table if exists t44 ;
3201
drop table if exists t55 ;
3202
drop table if exists t66 ;
3203
-------------------------------------------------------------------------
3204
--- dayofweek(col1) in partition with coltype date
3205
-------------------------------------------------------------------------
3206
drop table if exists t1 ;
3207
drop table if exists t2 ;
3208
drop table if exists t3 ;
3209
drop table if exists t4 ;
3210
drop table if exists t5 ;
3211
drop table if exists t6 ;
3212
-------------------------------------------------------------------------
3213
--- Create tables with dayofweek(col1)
3214
-------------------------------------------------------------------------
3215
create table t1 (col1 date) engine='NDB'
3216
partition by range(dayofweek(col1))
3217
(partition p0 values less than (15),
3218
partition p1 values less than maxvalue);
3219
create table t2 (col1 date) engine='NDB'
3220
partition by list(dayofweek(col1))
3221
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3222
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3223
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3224
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3225
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3226
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3228
create table t3 (col1 date) engine='NDB'
3229
partition by hash(dayofweek(col1));
3230
create table t4 (colint int, col1 date) engine='NDB'
3231
partition by range(colint)
3232
subpartition by hash(dayofweek(col1)) subpartitions 2
3233
(partition p0 values less than (15),
3234
partition p1 values less than maxvalue);
3235
create table t5 (colint int, col1 date) engine='NDB'
3236
partition by list(colint)
3237
subpartition by hash(dayofweek(col1)) subpartitions 2
3238
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
3239
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3240
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
3241
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3243
create table t6 (colint int, col1 date) engine='NDB'
3244
partition by range(colint)
3245
(partition p0 values less than (dayofweek('2006-12-24')),
3246
partition p1 values less than maxvalue);
3247
-------------------------------------------------------------------------
3248
--- Access tables with dayofweek(col1)
3249
-------------------------------------------------------------------------
3250
insert into t1 values ('2006-01-03');
3251
insert into t1 values ('2006-02-17');
3252
insert into t2 values ('2006-01-03');
3253
insert into t2 values ('2006-02-17');
3254
insert into t2 values ('2006-01-25');
3255
insert into t3 values ('2006-01-03');
3256
insert into t3 values ('2006-02-17');
3257
insert into t3 values ('2006-01-25');
3258
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
3259
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
3260
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
3261
select dayofweek(col1) from t1 order by col1;
3265
select * from t1 order by col1;
3269
select * from t2 order by col1;
3274
select * from t3 order by col1;
3279
select * from t4 order by colint;
3285
select * from t5 order by colint;
3291
select * from t6 order by colint;
3297
update t1 set col1='2006-02-05' where col1='2006-01-03';
3298
update t2 set col1='2006-02-05' where col1='2006-01-03';
3299
update t3 set col1='2006-02-05' where col1='2006-01-03';
3300
update t4 set col1='2006-02-05' where col1='2006-01-03';
3301
update t5 set col1='2006-02-05' where col1='2006-01-03';
3302
update t6 set col1='2006-02-05' where col1='2006-01-03';
3303
select * from t1 order by col1;
3307
select * from t2 order by col1;
3312
select * from t3 order by col1;
3317
select * from t4 order by colint;
3323
select * from t5 order by colint;
3329
select * from t6 order by colint;
3335
-------------------------------------------------------------------------
3336
--- Alter tables with dayofweek(col1)
3337
-------------------------------------------------------------------------
3338
drop table if exists t11 ;
3339
drop table if exists t22 ;
3340
drop table if exists t33 ;
3341
drop table if exists t44 ;
3342
drop table if exists t55 ;
3343
drop table if exists t66 ;
3344
create table t11 engine='NDB' as select * from t1;
3345
create table t22 engine='NDB' as select * from t2;
3346
create table t33 engine='NDB' as select * from t3;
3347
create table t44 engine='NDB' as select * from t4;
3348
create table t55 engine='NDB' as select * from t5;
3349
create table t66 engine='NDB' as select * from t6;
3351
partition by range(dayofweek(col1))
3352
(partition p0 values less than (15),
3353
partition p1 values less than maxvalue);
3355
partition by list(dayofweek(col1))
3356
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3357
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3358
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3359
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3360
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3361
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3364
partition by hash(dayofweek(col1));
3366
partition by range(colint)
3367
subpartition by hash(dayofweek(col1)) subpartitions 2
3368
(partition p0 values less than (15),
3369
partition p1 values less than maxvalue);
3371
partition by list(colint)
3372
subpartition by hash(dayofweek(col1)) subpartitions 2
3373
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
3374
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3375
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
3376
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3379
partition by range(colint)
3380
(partition p0 values less than (dayofweek('2006-12-24')),
3381
partition p1 values less than maxvalue);
3382
select * from t11 order by col1;
3386
select * from t22 order by col1;
3391
select * from t33 order by col1;
3396
select * from t44 order by colint;
3402
select * from t55 order by colint;
3408
select * from t66 order by colint;
3414
---------------------------
3415
---- some alter table begin
3416
---------------------------
3418
partition by list(colint)
3419
subpartition by hash(dayofweek(col1)) subpartitions 4
3420
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3421
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3423
show create table t55;
3425
t55 CREATE TABLE `t55` (
3426
`colint` int(11) DEFAULT NULL,
3427
`col1` date DEFAULT NULL
3428
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
3429
/*!50100 PARTITION BY LIST (colint)
3430
SUBPARTITION BY HASH (dayofweek(col1))
3432
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
3433
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
3434
select * from t55 order by colint;
3440
-------------------------------------------------------------------------
3441
--- Delete rows and partitions of tables with dayofweek(col1)
3442
-------------------------------------------------------------------------
3443
delete from t1 where col1='2006-02-17';
3444
delete from t2 where col1='2006-02-17';
3445
delete from t3 where col1='2006-02-17';
3446
delete from t4 where col1='2006-02-17';
3447
delete from t5 where col1='2006-02-17';
3448
delete from t6 where col1='2006-02-17';
3449
select * from t1 order by col1;
3452
select * from t2 order by col1;
3456
select * from t3 order by col1;
3460
select * from t4 order by colint;
3466
select * from t5 order by colint;
3472
insert into t1 values ('2006-02-17');
3473
insert into t2 values ('2006-02-17');
3474
insert into t3 values ('2006-02-17');
3475
insert into t4 values (60,'2006-02-17');
3476
insert into t5 values (60,'2006-02-17');
3477
insert into t6 values (60,'2006-02-17');
3478
select * from t1 order by col1;
3482
select * from t2 order by col1;
3487
select * from t3 order by col1;
3492
select * from t4 order by colint;
3499
select * from t5 order by colint;
3506
select * from t6 order by colint;
3513
-------------------------------------------------------------------------
3514
--- Delete rows and partitions of tables with dayofweek(col1)
3515
-------------------------------------------------------------------------
3516
delete from t11 where col1='2006-02-17';
3517
delete from t22 where col1='2006-02-17';
3518
delete from t33 where col1='2006-02-17';
3519
delete from t44 where col1='2006-02-17';
3520
delete from t55 where col1='2006-02-17';
3521
delete from t66 where col1='2006-02-17';
3522
select * from t11 order by col1;
3525
select * from t22 order by col1;
3529
select * from t33 order by col1;
3533
select * from t44 order by colint;
3539
select * from t55 order by colint;
3545
insert into t11 values ('2006-02-17');
3546
insert into t22 values ('2006-02-17');
3547
insert into t33 values ('2006-02-17');
3548
insert into t44 values (60,'2006-02-17');
3549
insert into t55 values (60,'2006-02-17');
3550
insert into t66 values (60,'2006-02-17');
3551
select * from t11 order by col1;
3555
select * from t22 order by col1;
3560
select * from t33 order by col1;
3565
select * from t44 order by colint;
3572
select * from t55 order by colint;
3579
select * from t66 order by colint;
3586
-------------------------
3587
---- some alter table end
3588
-------------------------
3589
drop table if exists t1 ;
3590
drop table if exists t2 ;
3591
drop table if exists t3 ;
3592
drop table if exists t4 ;
3593
drop table if exists t5 ;
3594
drop table if exists t6 ;
3595
drop table if exists t11 ;
3596
drop table if exists t22 ;
3597
drop table if exists t33 ;
3598
drop table if exists t44 ;
3599
drop table if exists t55 ;
3600
drop table if exists t66 ;
3601
-------------------------------------------------------------------------
3602
--- dayofyear(col1) in partition with coltype date
3603
-------------------------------------------------------------------------
3604
drop table if exists t1 ;
3605
drop table if exists t2 ;
3606
drop table if exists t3 ;
3607
drop table if exists t4 ;
3608
drop table if exists t5 ;
3609
drop table if exists t6 ;
3610
-------------------------------------------------------------------------
3611
--- Create tables with dayofyear(col1)
3612
-------------------------------------------------------------------------
3613
create table t1 (col1 date) engine='NDB'
3614
partition by range(dayofyear(col1))
3615
(partition p0 values less than (15),
3616
partition p1 values less than maxvalue);
3617
create table t2 (col1 date) engine='NDB'
3618
partition by list(dayofyear(col1))
3619
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3620
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3621
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3622
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3623
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3624
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3626
create table t3 (col1 date) engine='NDB'
3627
partition by hash(dayofyear(col1));
3628
create table t4 (colint int, col1 date) engine='NDB'
3629
partition by range(colint)
3630
subpartition by hash(dayofyear(col1)) subpartitions 2
3631
(partition p0 values less than (15),
3632
partition p1 values less than maxvalue);
3633
create table t5 (colint int, col1 date) engine='NDB'
3634
partition by list(colint)
3635
subpartition by hash(dayofyear(col1)) subpartitions 2
3636
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
3637
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3638
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
3639
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3641
create table t6 (colint int, col1 date) engine='NDB'
3642
partition by range(colint)
3643
(partition p0 values less than (dayofyear('2006-12-25')),
3644
partition p1 values less than maxvalue);
3645
-------------------------------------------------------------------------
3646
--- Access tables with dayofyear(col1)
3647
-------------------------------------------------------------------------
3648
insert into t1 values ('2006-01-03');
3649
insert into t1 values ('2006-01-17');
3650
insert into t2 values ('2006-01-03');
3651
insert into t2 values ('2006-01-17');
3652
insert into t2 values ('2006-02-25');
3653
insert into t3 values ('2006-01-03');
3654
insert into t3 values ('2006-01-17');
3655
insert into t3 values ('2006-02-25');
3656
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
3657
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
3658
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
3659
select dayofyear(col1) from t1 order by col1;
3663
select * from t1 order by col1;
3667
select * from t2 order by col1;
3672
select * from t3 order by col1;
3677
select * from t4 order by colint;
3683
select * from t5 order by colint;
3689
select * from t6 order by colint;
3695
update t1 set col1='2006-02-05' where col1='2006-01-03';
3696
update t2 set col1='2006-02-05' where col1='2006-01-03';
3697
update t3 set col1='2006-02-05' where col1='2006-01-03';
3698
update t4 set col1='2006-02-05' where col1='2006-01-03';
3699
update t5 set col1='2006-02-05' where col1='2006-01-03';
3700
update t6 set col1='2006-02-05' where col1='2006-01-03';
3701
select * from t1 order by col1;
3705
select * from t2 order by col1;
3710
select * from t3 order by col1;
3715
select * from t4 order by colint;
3721
select * from t5 order by colint;
3727
select * from t6 order by colint;
3733
-------------------------------------------------------------------------
3734
--- Alter tables with dayofyear(col1)
3735
-------------------------------------------------------------------------
3736
drop table if exists t11 ;
3737
drop table if exists t22 ;
3738
drop table if exists t33 ;
3739
drop table if exists t44 ;
3740
drop table if exists t55 ;
3741
drop table if exists t66 ;
3742
create table t11 engine='NDB' as select * from t1;
3743
create table t22 engine='NDB' as select * from t2;
3744
create table t33 engine='NDB' as select * from t3;
3745
create table t44 engine='NDB' as select * from t4;
3746
create table t55 engine='NDB' as select * from t5;
3747
create table t66 engine='NDB' as select * from t6;
3749
partition by range(dayofyear(col1))
3750
(partition p0 values less than (15),
3751
partition p1 values less than maxvalue);
3753
partition by list(dayofyear(col1))
3754
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3755
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3756
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3757
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3758
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3759
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3762
partition by hash(dayofyear(col1));
3764
partition by range(colint)
3765
subpartition by hash(dayofyear(col1)) subpartitions 2
3766
(partition p0 values less than (15),
3767
partition p1 values less than maxvalue);
3769
partition by list(colint)
3770
subpartition by hash(dayofyear(col1)) subpartitions 2
3771
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
3772
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3773
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
3774
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3777
partition by range(colint)
3778
(partition p0 values less than (dayofyear('2006-12-25')),
3779
partition p1 values less than maxvalue);
3780
select * from t11 order by col1;
3784
select * from t22 order by col1;
3789
select * from t33 order by col1;
3794
select * from t44 order by colint;
3800
select * from t55 order by colint;
3806
select * from t66 order by colint;
3812
---------------------------
3813
---- some alter table begin
3814
---------------------------
3816
partition by list(colint)
3817
subpartition by hash(dayofyear(col1)) subpartitions 4
3818
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
3819
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
3821
show create table t55;
3823
t55 CREATE TABLE `t55` (
3824
`colint` int(11) DEFAULT NULL,
3825
`col1` date DEFAULT NULL
3826
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
3827
/*!50100 PARTITION BY LIST (colint)
3828
SUBPARTITION BY HASH (dayofyear(col1))
3830
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
3831
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
3832
select * from t55 order by colint;
3838
-------------------------------------------------------------------------
3839
--- Delete rows and partitions of tables with dayofyear(col1)
3840
-------------------------------------------------------------------------
3841
delete from t1 where col1='2006-01-17';
3842
delete from t2 where col1='2006-01-17';
3843
delete from t3 where col1='2006-01-17';
3844
delete from t4 where col1='2006-01-17';
3845
delete from t5 where col1='2006-01-17';
3846
delete from t6 where col1='2006-01-17';
3847
select * from t1 order by col1;
3850
select * from t2 order by col1;
3854
select * from t3 order by col1;
3858
select * from t4 order by colint;
3863
select * from t5 order by colint;
3868
insert into t1 values ('2006-01-17');
3869
insert into t2 values ('2006-01-17');
3870
insert into t3 values ('2006-01-17');
3871
insert into t4 values (60,'2006-01-17');
3872
insert into t5 values (60,'2006-01-17');
3873
insert into t6 values (60,'2006-01-17');
3874
select * from t1 order by col1;
3878
select * from t2 order by col1;
3883
select * from t3 order by col1;
3888
select * from t4 order by colint;
3894
select * from t5 order by colint;
3900
select * from t6 order by colint;
3906
-------------------------------------------------------------------------
3907
--- Delete rows and partitions of tables with dayofyear(col1)
3908
-------------------------------------------------------------------------
3909
delete from t11 where col1='2006-01-17';
3910
delete from t22 where col1='2006-01-17';
3911
delete from t33 where col1='2006-01-17';
3912
delete from t44 where col1='2006-01-17';
3913
delete from t55 where col1='2006-01-17';
3914
delete from t66 where col1='2006-01-17';
3915
select * from t11 order by col1;
3918
select * from t22 order by col1;
3922
select * from t33 order by col1;
3926
select * from t44 order by colint;
3931
select * from t55 order by colint;
3936
insert into t11 values ('2006-01-17');
3937
insert into t22 values ('2006-01-17');
3938
insert into t33 values ('2006-01-17');
3939
insert into t44 values (60,'2006-01-17');
3940
insert into t55 values (60,'2006-01-17');
3941
insert into t66 values (60,'2006-01-17');
3942
select * from t11 order by col1;
3946
select * from t22 order by col1;
3951
select * from t33 order by col1;
3956
select * from t44 order by colint;
3962
select * from t55 order by colint;
3968
select * from t66 order by colint;
3974
-------------------------
3975
---- some alter table end
3976
-------------------------
3977
drop table if exists t1 ;
3978
drop table if exists t2 ;
3979
drop table if exists t3 ;
3980
drop table if exists t4 ;
3981
drop table if exists t5 ;
3982
drop table if exists t6 ;
3983
drop table if exists t11 ;
3984
drop table if exists t22 ;
3985
drop table if exists t33 ;
3986
drop table if exists t44 ;
3987
drop table if exists t55 ;
3988
drop table if exists t66 ;
3989
-------------------------------------------------------------------------
3990
--- dayofyear(col1) in partition with coltype char(30)
3991
-------------------------------------------------------------------------
3992
drop table if exists t1 ;
3993
drop table if exists t2 ;
3994
drop table if exists t3 ;
3995
drop table if exists t4 ;
3996
drop table if exists t5 ;
3997
drop table if exists t6 ;
3998
-------------------------------------------------------------------------
3999
--- Create tables with dayofyear(col1)
4000
-------------------------------------------------------------------------
4001
create table t1 (col1 char(30)) engine='NDB'
4002
partition by range(dayofyear(col1))
4003
(partition p0 values less than (15),
4004
partition p1 values less than maxvalue);
4005
create table t2 (col1 char(30)) engine='NDB'
4006
partition by list(dayofyear(col1))
4007
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4008
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4009
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4010
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4011
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4012
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4014
create table t3 (col1 char(30)) engine='NDB'
4015
partition by hash(dayofyear(col1));
4016
create table t4 (colint int, col1 char(30)) engine='NDB'
4017
partition by range(colint)
4018
subpartition by hash(dayofyear(col1)) subpartitions 2
4019
(partition p0 values less than (15),
4020
partition p1 values less than maxvalue);
4021
create table t5 (colint int, col1 char(30)) engine='NDB'
4022
partition by list(colint)
4023
subpartition by hash(dayofyear(col1)) subpartitions 2
4024
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
4025
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4026
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
4027
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4029
create table t6 (colint int, col1 char(30)) engine='NDB'
4030
partition by range(colint)
4031
(partition p0 values less than (dayofyear('2006-12-25')),
4032
partition p1 values less than maxvalue);
4033
-------------------------------------------------------------------------
4034
--- Access tables with dayofyear(col1)
4035
-------------------------------------------------------------------------
4036
insert into t1 values ('2006-01-03');
4037
insert into t1 values ('2006-01-17');
4038
insert into t2 values ('2006-01-03');
4039
insert into t2 values ('2006-01-17');
4040
insert into t2 values ('2006-02-25');
4041
insert into t3 values ('2006-01-03');
4042
insert into t3 values ('2006-01-17');
4043
insert into t3 values ('2006-02-25');
4044
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
4045
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
4046
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
4047
select dayofyear(col1) from t1 order by col1;
4051
select * from t1 order by col1;
4055
select * from t2 order by col1;
4060
select * from t3 order by col1;
4065
select * from t4 order by colint;
4071
select * from t5 order by colint;
4077
select * from t6 order by colint;
4083
update t1 set col1='2006-02-05' where col1='2006-01-03';
4084
update t2 set col1='2006-02-05' where col1='2006-01-03';
4085
update t3 set col1='2006-02-05' where col1='2006-01-03';
4086
update t4 set col1='2006-02-05' where col1='2006-01-03';
4087
update t5 set col1='2006-02-05' where col1='2006-01-03';
4088
update t6 set col1='2006-02-05' where col1='2006-01-03';
4089
select * from t1 order by col1;
4093
select * from t2 order by col1;
4098
select * from t3 order by col1;
4103
select * from t4 order by colint;
4109
select * from t5 order by colint;
4115
select * from t6 order by colint;
4121
-------------------------------------------------------------------------
4122
--- Alter tables with dayofyear(col1)
4123
-------------------------------------------------------------------------
4124
drop table if exists t11 ;
4125
drop table if exists t22 ;
4126
drop table if exists t33 ;
4127
drop table if exists t44 ;
4128
drop table if exists t55 ;
4129
drop table if exists t66 ;
4130
create table t11 engine='NDB' as select * from t1;
4131
create table t22 engine='NDB' as select * from t2;
4132
create table t33 engine='NDB' as select * from t3;
4133
create table t44 engine='NDB' as select * from t4;
4134
create table t55 engine='NDB' as select * from t5;
4135
create table t66 engine='NDB' as select * from t6;
4137
partition by range(dayofyear(col1))
4138
(partition p0 values less than (15),
4139
partition p1 values less than maxvalue);
4141
partition by list(dayofyear(col1))
4142
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4143
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4144
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4145
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4146
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4147
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4150
partition by hash(dayofyear(col1));
4152
partition by range(colint)
4153
subpartition by hash(dayofyear(col1)) subpartitions 2
4154
(partition p0 values less than (15),
4155
partition p1 values less than maxvalue);
4157
partition by list(colint)
4158
subpartition by hash(dayofyear(col1)) subpartitions 2
4159
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
4160
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4161
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
4162
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4165
partition by range(colint)
4166
(partition p0 values less than (dayofyear('2006-12-25')),
4167
partition p1 values less than maxvalue);
4168
select * from t11 order by col1;
4172
select * from t22 order by col1;
4177
select * from t33 order by col1;
4182
select * from t44 order by colint;
4188
select * from t55 order by colint;
4194
select * from t66 order by colint;
4200
---------------------------
4201
---- some alter table begin
4202
---------------------------
4204
partition by list(colint)
4205
subpartition by hash(dayofyear(col1)) subpartitions 4
4206
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4207
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4209
show create table t55;
4211
t55 CREATE TABLE `t55` (
4212
`colint` int(11) DEFAULT NULL,
4213
`col1` char(30) DEFAULT NULL
4214
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4215
/*!50100 PARTITION BY LIST (colint)
4216
SUBPARTITION BY HASH (dayofyear(col1))
4218
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
4219
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
4220
select * from t55 order by colint;
4226
-------------------------------------------------------------------------
4227
--- Delete rows and partitions of tables with dayofyear(col1)
4228
-------------------------------------------------------------------------
4229
delete from t1 where col1='2006-01-17';
4230
delete from t2 where col1='2006-01-17';
4231
delete from t3 where col1='2006-01-17';
4232
delete from t4 where col1='2006-01-17';
4233
delete from t5 where col1='2006-01-17';
4234
delete from t6 where col1='2006-01-17';
4235
select * from t1 order by col1;
4238
select * from t2 order by col1;
4242
select * from t3 order by col1;
4246
select * from t4 order by colint;
4251
select * from t5 order by colint;
4256
insert into t1 values ('2006-01-17');
4257
insert into t2 values ('2006-01-17');
4258
insert into t3 values ('2006-01-17');
4259
insert into t4 values (60,'2006-01-17');
4260
insert into t5 values (60,'2006-01-17');
4261
insert into t6 values (60,'2006-01-17');
4262
select * from t1 order by col1;
4266
select * from t2 order by col1;
4271
select * from t3 order by col1;
4276
select * from t4 order by colint;
4282
select * from t5 order by colint;
4288
select * from t6 order by colint;
4294
-------------------------------------------------------------------------
4295
--- Delete rows and partitions of tables with dayofyear(col1)
4296
-------------------------------------------------------------------------
4297
delete from t11 where col1='2006-01-17';
4298
delete from t22 where col1='2006-01-17';
4299
delete from t33 where col1='2006-01-17';
4300
delete from t44 where col1='2006-01-17';
4301
delete from t55 where col1='2006-01-17';
4302
delete from t66 where col1='2006-01-17';
4303
select * from t11 order by col1;
4306
select * from t22 order by col1;
4310
select * from t33 order by col1;
4314
select * from t44 order by colint;
4319
select * from t55 order by colint;
4324
insert into t11 values ('2006-01-17');
4325
insert into t22 values ('2006-01-17');
4326
insert into t33 values ('2006-01-17');
4327
insert into t44 values (60,'2006-01-17');
4328
insert into t55 values (60,'2006-01-17');
4329
insert into t66 values (60,'2006-01-17');
4330
select * from t11 order by col1;
4334
select * from t22 order by col1;
4339
select * from t33 order by col1;
4344
select * from t44 order by colint;
4350
select * from t55 order by colint;
4356
select * from t66 order by colint;
4362
-------------------------
4363
---- some alter table end
4364
-------------------------
4365
drop table if exists t1 ;
4366
drop table if exists t2 ;
4367
drop table if exists t3 ;
4368
drop table if exists t4 ;
4369
drop table if exists t5 ;
4370
drop table if exists t6 ;
4371
drop table if exists t11 ;
4372
drop table if exists t22 ;
4373
drop table if exists t33 ;
4374
drop table if exists t44 ;
4375
drop table if exists t55 ;
4376
drop table if exists t66 ;
4377
-------------------------------------------------------------------------
4378
--- extract(month from col1) in partition with coltype date
4379
-------------------------------------------------------------------------
4380
drop table if exists t1 ;
4381
drop table if exists t2 ;
4382
drop table if exists t3 ;
4383
drop table if exists t4 ;
4384
drop table if exists t5 ;
4385
drop table if exists t6 ;
4386
-------------------------------------------------------------------------
4387
--- Create tables with extract(month from col1)
4388
-------------------------------------------------------------------------
4389
create table t1 (col1 date) engine='NDB'
4390
partition by range(extract(month from col1))
4391
(partition p0 values less than (15),
4392
partition p1 values less than maxvalue);
4393
create table t2 (col1 date) engine='NDB'
4394
partition by list(extract(month from col1))
4395
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4396
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4397
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4398
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4399
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4400
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4402
create table t3 (col1 date) engine='NDB'
4403
partition by hash(extract(month from col1));
4404
create table t4 (colint int, col1 date) engine='NDB'
4405
partition by range(colint)
4406
subpartition by hash(extract(month from col1)) subpartitions 2
4407
(partition p0 values less than (15),
4408
partition p1 values less than maxvalue);
4409
create table t5 (colint int, col1 date) engine='NDB'
4410
partition by list(colint)
4411
subpartition by hash(extract(month from col1)) subpartitions 2
4412
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
4413
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4414
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
4415
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4417
create table t6 (colint int, col1 date) engine='NDB'
4418
partition by range(colint)
4419
(partition p0 values less than (extract(year from '1998-11-23')),
4420
partition p1 values less than maxvalue);
4421
-------------------------------------------------------------------------
4422
--- Access tables with extract(month from col1)
4423
-------------------------------------------------------------------------
4424
insert into t1 values ('2006-01-03');
4425
insert into t1 values ('2006-02-17');
4426
insert into t2 values ('2006-01-03');
4427
insert into t2 values ('2006-02-17');
4428
insert into t2 values ('2006-01-25');
4429
insert into t3 values ('2006-01-03');
4430
insert into t3 values ('2006-02-17');
4431
insert into t3 values ('2006-01-25');
4432
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
4433
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
4434
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
4435
select extract(month from col1) from t1 order by col1;
4436
extract(month from col1)
4439
select * from t1 order by col1;
4443
select * from t2 order by col1;
4448
select * from t3 order by col1;
4453
select * from t4 order by colint;
4459
select * from t5 order by colint;
4465
select * from t6 order by colint;
4471
update t1 set col1='2006-02-05' where col1='2006-01-03';
4472
update t2 set col1='2006-02-05' where col1='2006-01-03';
4473
update t3 set col1='2006-02-05' where col1='2006-01-03';
4474
update t4 set col1='2006-02-05' where col1='2006-01-03';
4475
update t5 set col1='2006-02-05' where col1='2006-01-03';
4476
update t6 set col1='2006-02-05' where col1='2006-01-03';
4477
select * from t1 order by col1;
4481
select * from t2 order by col1;
4486
select * from t3 order by col1;
4491
select * from t4 order by colint;
4497
select * from t5 order by colint;
4503
select * from t6 order by colint;
4509
-------------------------------------------------------------------------
4510
--- Alter tables with extract(month from col1)
4511
-------------------------------------------------------------------------
4512
drop table if exists t11 ;
4513
drop table if exists t22 ;
4514
drop table if exists t33 ;
4515
drop table if exists t44 ;
4516
drop table if exists t55 ;
4517
drop table if exists t66 ;
4518
create table t11 engine='NDB' as select * from t1;
4519
create table t22 engine='NDB' as select * from t2;
4520
create table t33 engine='NDB' as select * from t3;
4521
create table t44 engine='NDB' as select * from t4;
4522
create table t55 engine='NDB' as select * from t5;
4523
create table t66 engine='NDB' as select * from t6;
4525
partition by range(extract(month from col1))
4526
(partition p0 values less than (15),
4527
partition p1 values less than maxvalue);
4529
partition by list(extract(month from col1))
4530
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4531
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4532
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4533
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4534
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4535
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4538
partition by hash(extract(month from col1));
4540
partition by range(colint)
4541
subpartition by hash(extract(month from col1)) subpartitions 2
4542
(partition p0 values less than (15),
4543
partition p1 values less than maxvalue);
4545
partition by list(colint)
4546
subpartition by hash(extract(month from col1)) subpartitions 2
4547
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
4548
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4549
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
4550
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4553
partition by range(colint)
4554
(partition p0 values less than (extract(year from '1998-11-23')),
4555
partition p1 values less than maxvalue);
4556
select * from t11 order by col1;
4560
select * from t22 order by col1;
4565
select * from t33 order by col1;
4570
select * from t44 order by colint;
4576
select * from t55 order by colint;
4582
select * from t66 order by colint;
4588
---------------------------
4589
---- some alter table begin
4590
---------------------------
4592
partition by list(colint)
4593
subpartition by hash(extract(month from col1)) subpartitions 4
4594
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4595
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4597
show create table t55;
4599
t55 CREATE TABLE `t55` (
4600
`colint` int(11) DEFAULT NULL,
4601
`col1` date DEFAULT NULL
4602
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
4603
/*!50100 PARTITION BY LIST (colint)
4604
SUBPARTITION BY HASH (extract(month from col1))
4606
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
4607
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
4608
select * from t55 order by colint;
4614
-------------------------------------------------------------------------
4615
--- Delete rows and partitions of tables with extract(month from col1)
4616
-------------------------------------------------------------------------
4617
delete from t1 where col1='2006-02-17';
4618
delete from t2 where col1='2006-02-17';
4619
delete from t3 where col1='2006-02-17';
4620
delete from t4 where col1='2006-02-17';
4621
delete from t5 where col1='2006-02-17';
4622
delete from t6 where col1='2006-02-17';
4623
select * from t1 order by col1;
4626
select * from t2 order by col1;
4630
select * from t3 order by col1;
4634
select * from t4 order by colint;
4640
select * from t5 order by colint;
4646
insert into t1 values ('2006-02-17');
4647
insert into t2 values ('2006-02-17');
4648
insert into t3 values ('2006-02-17');
4649
insert into t4 values (60,'2006-02-17');
4650
insert into t5 values (60,'2006-02-17');
4651
insert into t6 values (60,'2006-02-17');
4652
select * from t1 order by col1;
4656
select * from t2 order by col1;
4661
select * from t3 order by col1;
4666
select * from t4 order by colint;
4673
select * from t5 order by colint;
4680
select * from t6 order by colint;
4687
-------------------------------------------------------------------------
4688
--- Delete rows and partitions of tables with extract(month from col1)
4689
-------------------------------------------------------------------------
4690
delete from t11 where col1='2006-02-17';
4691
delete from t22 where col1='2006-02-17';
4692
delete from t33 where col1='2006-02-17';
4693
delete from t44 where col1='2006-02-17';
4694
delete from t55 where col1='2006-02-17';
4695
delete from t66 where col1='2006-02-17';
4696
select * from t11 order by col1;
4699
select * from t22 order by col1;
4703
select * from t33 order by col1;
4707
select * from t44 order by colint;
4713
select * from t55 order by colint;
4719
insert into t11 values ('2006-02-17');
4720
insert into t22 values ('2006-02-17');
4721
insert into t33 values ('2006-02-17');
4722
insert into t44 values (60,'2006-02-17');
4723
insert into t55 values (60,'2006-02-17');
4724
insert into t66 values (60,'2006-02-17');
4725
select * from t11 order by col1;
4729
select * from t22 order by col1;
4734
select * from t33 order by col1;
4739
select * from t44 order by colint;
4746
select * from t55 order by colint;
4753
select * from t66 order by colint;
4760
-------------------------
4761
---- some alter table end
4762
-------------------------
4763
drop table if exists t1 ;
4764
drop table if exists t2 ;
4765
drop table if exists t3 ;
4766
drop table if exists t4 ;
4767
drop table if exists t5 ;
4768
drop table if exists t6 ;
4769
drop table if exists t11 ;
4770
drop table if exists t22 ;
4771
drop table if exists t33 ;
4772
drop table if exists t44 ;
4773
drop table if exists t55 ;
4774
drop table if exists t66 ;
4775
-------------------------------------------------------------------------
4776
--- hour(col1) in partition with coltype time
4777
-------------------------------------------------------------------------
4778
drop table if exists t1 ;
4779
drop table if exists t2 ;
4780
drop table if exists t3 ;
4781
drop table if exists t4 ;
4782
drop table if exists t5 ;
4783
drop table if exists t6 ;
4784
-------------------------------------------------------------------------
4785
--- Create tables with hour(col1)
4786
-------------------------------------------------------------------------
4787
create table t1 (col1 time) engine='NDB'
4788
partition by range(hour(col1))
4789
(partition p0 values less than (15),
4790
partition p1 values less than maxvalue);
4791
create table t2 (col1 time) engine='NDB'
4792
partition by list(hour(col1))
4793
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4794
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4795
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4796
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4797
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4798
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4800
create table t3 (col1 time) engine='NDB'
4801
partition by hash(hour(col1));
4802
create table t4 (colint int, col1 time) engine='NDB'
4803
partition by range(colint)
4804
subpartition by hash(hour(col1)) subpartitions 2
4805
(partition p0 values less than (15),
4806
partition p1 values less than maxvalue);
4807
create table t5 (colint int, col1 time) engine='NDB'
4808
partition by list(colint)
4809
subpartition by hash(hour(col1)) subpartitions 2
4810
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
4811
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4812
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
4813
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4815
create table t6 (colint int, col1 time) engine='NDB'
4816
partition by range(colint)
4817
(partition p0 values less than (hour('18:30')),
4818
partition p1 values less than maxvalue);
4819
-------------------------------------------------------------------------
4820
--- Access tables with hour(col1)
4821
-------------------------------------------------------------------------
4822
insert into t1 values ('09:09');
4823
insert into t1 values ('14:30');
4824
insert into t2 values ('09:09');
4825
insert into t2 values ('14:30');
4826
insert into t2 values ('21:59');
4827
insert into t3 values ('09:09');
4828
insert into t3 values ('14:30');
4829
insert into t3 values ('21:59');
4830
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
4831
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
4832
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
4833
select hour(col1) from t1 order by col1;
4837
select * from t1 order by col1;
4841
select * from t2 order by col1;
4846
select * from t3 order by col1;
4851
select * from t4 order by colint;
4857
select * from t5 order by colint;
4863
select * from t6 order by colint;
4869
update t1 set col1='10:30' where col1='09:09';
4870
update t2 set col1='10:30' where col1='09:09';
4871
update t3 set col1='10:30' where col1='09:09';
4872
update t4 set col1='10:30' where col1='09:09';
4873
update t5 set col1='10:30' where col1='09:09';
4874
update t6 set col1='10:30' where col1='09:09';
4875
select * from t1 order by col1;
4879
select * from t2 order by col1;
4884
select * from t3 order by col1;
4889
select * from t4 order by colint;
4895
select * from t5 order by colint;
4901
select * from t6 order by colint;
4907
-------------------------------------------------------------------------
4908
--- Alter tables with hour(col1)
4909
-------------------------------------------------------------------------
4910
drop table if exists t11 ;
4911
drop table if exists t22 ;
4912
drop table if exists t33 ;
4913
drop table if exists t44 ;
4914
drop table if exists t55 ;
4915
drop table if exists t66 ;
4916
create table t11 engine='NDB' as select * from t1;
4917
create table t22 engine='NDB' as select * from t2;
4918
create table t33 engine='NDB' as select * from t3;
4919
create table t44 engine='NDB' as select * from t4;
4920
create table t55 engine='NDB' as select * from t5;
4921
create table t66 engine='NDB' as select * from t6;
4923
partition by range(hour(col1))
4924
(partition p0 values less than (15),
4925
partition p1 values less than maxvalue);
4927
partition by list(hour(col1))
4928
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
4929
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
4930
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
4931
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
4932
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
4933
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
4936
partition by hash(hour(col1));
4938
partition by range(colint)
4939
subpartition by hash(hour(col1)) subpartitions 2
4940
(partition p0 values less than (15),
4941
partition p1 values less than maxvalue);
4943
partition by list(colint)
4944
subpartition by hash(hour(col1)) subpartitions 2
4945
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
4946
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4947
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
4948
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4951
partition by range(colint)
4952
(partition p0 values less than (hour('18:30')),
4953
partition p1 values less than maxvalue);
4954
select * from t11 order by col1;
4958
select * from t22 order by col1;
4963
select * from t33 order by col1;
4968
select * from t44 order by colint;
4974
select * from t55 order by colint;
4980
select * from t66 order by colint;
4986
---------------------------
4987
---- some alter table begin
4988
---------------------------
4990
partition by list(colint)
4991
subpartition by hash(hour(col1)) subpartitions 4
4992
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
4993
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
4995
show create table t55;
4997
t55 CREATE TABLE `t55` (
4998
`colint` int(11) DEFAULT NULL,
4999
`col1` time DEFAULT NULL
5000
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
5001
/*!50100 PARTITION BY LIST (colint)
5002
SUBPARTITION BY HASH (hour(col1))
5004
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
5005
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
5006
select * from t55 order by colint;
5012
-------------------------------------------------------------------------
5013
--- Delete rows and partitions of tables with hour(col1)
5014
-------------------------------------------------------------------------
5015
delete from t1 where col1='14:30';
5016
delete from t2 where col1='14:30';
5017
delete from t3 where col1='14:30';
5018
delete from t4 where col1='14:30';
5019
delete from t5 where col1='14:30';
5020
delete from t6 where col1='14:30';
5021
select * from t1 order by col1;
5024
select * from t2 order by col1;
5028
select * from t3 order by col1;
5032
select * from t4 order by colint;
5038
select * from t5 order by colint;
5044
insert into t1 values ('14:30');
5045
insert into t2 values ('14:30');
5046
insert into t3 values ('14:30');
5047
insert into t4 values (60,'14:30');
5048
insert into t5 values (60,'14:30');
5049
insert into t6 values (60,'14:30');
5050
select * from t1 order by col1;
5054
select * from t2 order by col1;
5059
select * from t3 order by col1;
5064
select * from t4 order by colint;
5071
select * from t5 order by colint;
5078
select * from t6 order by colint;
5085
-------------------------------------------------------------------------
5086
--- Delete rows and partitions of tables with hour(col1)
5087
-------------------------------------------------------------------------
5088
delete from t11 where col1='14:30';
5089
delete from t22 where col1='14:30';
5090
delete from t33 where col1='14:30';
5091
delete from t44 where col1='14:30';
5092
delete from t55 where col1='14:30';
5093
delete from t66 where col1='14:30';
5094
select * from t11 order by col1;
5097
select * from t22 order by col1;
5101
select * from t33 order by col1;
5105
select * from t44 order by colint;
5111
select * from t55 order by colint;
5117
insert into t11 values ('14:30');
5118
insert into t22 values ('14:30');
5119
insert into t33 values ('14:30');
5120
insert into t44 values (60,'14:30');
5121
insert into t55 values (60,'14:30');
5122
insert into t66 values (60,'14:30');
5123
select * from t11 order by col1;
5127
select * from t22 order by col1;
5132
select * from t33 order by col1;
5137
select * from t44 order by colint;
5144
select * from t55 order by colint;
5151
select * from t66 order by colint;
5158
-------------------------
5159
---- some alter table end
5160
-------------------------
5161
drop table if exists t1 ;
5162
drop table if exists t2 ;
5163
drop table if exists t3 ;
5164
drop table if exists t4 ;
5165
drop table if exists t5 ;
5166
drop table if exists t6 ;
5167
drop table if exists t11 ;
5168
drop table if exists t22 ;
5169
drop table if exists t33 ;
5170
drop table if exists t44 ;
5171
drop table if exists t55 ;
5172
drop table if exists t66 ;
5173
-------------------------------------------------------------------------
5174
--- microsecond(col1) in partition with coltype time
5175
-------------------------------------------------------------------------
5176
drop table if exists t1 ;
5177
drop table if exists t2 ;
5178
drop table if exists t3 ;
5179
drop table if exists t4 ;
5180
drop table if exists t5 ;
5181
drop table if exists t6 ;
5182
-------------------------------------------------------------------------
5183
--- Create tables with microsecond(col1)
5184
-------------------------------------------------------------------------
5185
create table t1 (col1 time) engine='NDB'
5186
partition by range(microsecond(col1))
5187
(partition p0 values less than (15),
5188
partition p1 values less than maxvalue);
5189
create table t2 (col1 time) engine='NDB'
5190
partition by list(microsecond(col1))
5191
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5192
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5193
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5194
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5195
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5196
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5198
create table t3 (col1 time) engine='NDB'
5199
partition by hash(microsecond(col1));
5200
create table t4 (colint int, col1 time) engine='NDB'
5201
partition by range(colint)
5202
subpartition by hash(microsecond(col1)) subpartitions 2
5203
(partition p0 values less than (15),
5204
partition p1 values less than maxvalue);
5205
create table t5 (colint int, col1 time) engine='NDB'
5206
partition by list(colint)
5207
subpartition by hash(microsecond(col1)) subpartitions 2
5208
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
5209
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5210
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
5211
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5213
create table t6 (colint int, col1 time) engine='NDB'
5214
partition by range(colint)
5215
(partition p0 values less than (microsecond('10:30:10.000010')),
5216
partition p1 values less than maxvalue);
5217
-------------------------------------------------------------------------
5218
--- Access tables with microsecond(col1)
5219
-------------------------------------------------------------------------
5220
insert into t1 values ('09:09:15.000002');
5221
insert into t1 values ('04:30:01.000018');
5222
insert into t2 values ('09:09:15.000002');
5223
insert into t2 values ('04:30:01.000018');
5224
insert into t2 values ('00:59:22.000024');
5225
insert into t3 values ('09:09:15.000002');
5226
insert into t3 values ('04:30:01.000018');
5227
insert into t3 values ('00:59:22.000024');
5228
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
5229
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
5230
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
5231
select microsecond(col1) from t1 order by col1;
5235
select * from t1 order by col1;
5239
select * from t2 order by col1;
5244
select * from t3 order by col1;
5249
select * from t4 order by colint;
5255
select * from t5 order by colint;
5261
select * from t6 order by colint;
5267
update t1 set col1='05:30:34.000037' where col1='09:09:15.000002';
5268
update t2 set col1='05:30:34.000037' where col1='09:09:15.000002';
5269
update t3 set col1='05:30:34.000037' where col1='09:09:15.000002';
5270
update t4 set col1='05:30:34.000037' where col1='09:09:15.000002';
5271
update t5 set col1='05:30:34.000037' where col1='09:09:15.000002';
5272
update t6 set col1='05:30:34.000037' where col1='09:09:15.000002';
5273
select * from t1 order by col1;
5277
select * from t2 order by col1;
5282
select * from t3 order by col1;
5287
select * from t4 order by colint;
5293
select * from t5 order by colint;
5299
select * from t6 order by colint;
5305
-------------------------------------------------------------------------
5306
--- Alter tables with microsecond(col1)
5307
-------------------------------------------------------------------------
5308
drop table if exists t11 ;
5309
drop table if exists t22 ;
5310
drop table if exists t33 ;
5311
drop table if exists t44 ;
5312
drop table if exists t55 ;
5313
drop table if exists t66 ;
5314
create table t11 engine='NDB' as select * from t1;
5315
create table t22 engine='NDB' as select * from t2;
5316
create table t33 engine='NDB' as select * from t3;
5317
create table t44 engine='NDB' as select * from t4;
5318
create table t55 engine='NDB' as select * from t5;
5319
create table t66 engine='NDB' as select * from t6;
5321
partition by range(microsecond(col1))
5322
(partition p0 values less than (15),
5323
partition p1 values less than maxvalue);
5325
partition by list(microsecond(col1))
5326
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5327
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5328
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5329
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5330
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5331
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5334
partition by hash(microsecond(col1));
5336
partition by range(colint)
5337
subpartition by hash(microsecond(col1)) subpartitions 2
5338
(partition p0 values less than (15),
5339
partition p1 values less than maxvalue);
5341
partition by list(colint)
5342
subpartition by hash(microsecond(col1)) subpartitions 2
5343
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
5344
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5345
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
5346
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5349
partition by range(colint)
5350
(partition p0 values less than (microsecond('10:30:10.000010')),
5351
partition p1 values less than maxvalue);
5352
select * from t11 order by col1;
5356
select * from t22 order by col1;
5361
select * from t33 order by col1;
5366
select * from t44 order by colint;
5372
select * from t55 order by colint;
5378
select * from t66 order by colint;
5384
---------------------------
5385
---- some alter table begin
5386
---------------------------
5388
partition by list(colint)
5389
subpartition by hash(microsecond(col1)) subpartitions 4
5390
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5391
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5393
show create table t55;
5395
t55 CREATE TABLE `t55` (
5396
`colint` int(11) DEFAULT NULL,
5397
`col1` time DEFAULT NULL
5398
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
5399
/*!50100 PARTITION BY LIST (colint)
5400
SUBPARTITION BY HASH (microsecond(col1))
5402
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
5403
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
5404
select * from t55 order by colint;
5410
-------------------------------------------------------------------------
5411
--- Delete rows and partitions of tables with microsecond(col1)
5412
-------------------------------------------------------------------------
5413
delete from t1 where col1='04:30:01.000018';
5414
delete from t2 where col1='04:30:01.000018';
5415
delete from t3 where col1='04:30:01.000018';
5416
delete from t4 where col1='04:30:01.000018';
5417
delete from t5 where col1='04:30:01.000018';
5418
delete from t6 where col1='04:30:01.000018';
5419
select * from t1 order by col1;
5422
select * from t2 order by col1;
5426
select * from t3 order by col1;
5430
select * from t4 order by colint;
5435
select * from t5 order by colint;
5440
insert into t1 values ('04:30:01.000018');
5441
insert into t2 values ('04:30:01.000018');
5442
insert into t3 values ('04:30:01.000018');
5443
insert into t4 values (60,'04:30:01.000018');
5444
insert into t5 values (60,'04:30:01.000018');
5445
insert into t6 values (60,'04:30:01.000018');
5446
select * from t1 order by col1;
5450
select * from t2 order by col1;
5455
select * from t3 order by col1;
5460
select * from t4 order by colint;
5466
select * from t5 order by colint;
5472
select * from t6 order by colint;
5478
-------------------------------------------------------------------------
5479
--- Delete rows and partitions of tables with microsecond(col1)
5480
-------------------------------------------------------------------------
5481
delete from t11 where col1='04:30:01.000018';
5482
delete from t22 where col1='04:30:01.000018';
5483
delete from t33 where col1='04:30:01.000018';
5484
delete from t44 where col1='04:30:01.000018';
5485
delete from t55 where col1='04:30:01.000018';
5486
delete from t66 where col1='04:30:01.000018';
5487
select * from t11 order by col1;
5490
select * from t22 order by col1;
5494
select * from t33 order by col1;
5498
select * from t44 order by colint;
5503
select * from t55 order by colint;
5508
insert into t11 values ('04:30:01.000018');
5509
insert into t22 values ('04:30:01.000018');
5510
insert into t33 values ('04:30:01.000018');
5511
insert into t44 values (60,'04:30:01.000018');
5512
insert into t55 values (60,'04:30:01.000018');
5513
insert into t66 values (60,'04:30:01.000018');
5514
select * from t11 order by col1;
5518
select * from t22 order by col1;
5523
select * from t33 order by col1;
5528
select * from t44 order by colint;
5534
select * from t55 order by colint;
5540
select * from t66 order by colint;
5546
-------------------------
5547
---- some alter table end
5548
-------------------------
5549
drop table if exists t1 ;
5550
drop table if exists t2 ;
5551
drop table if exists t3 ;
5552
drop table if exists t4 ;
5553
drop table if exists t5 ;
5554
drop table if exists t6 ;
5555
drop table if exists t11 ;
5556
drop table if exists t22 ;
5557
drop table if exists t33 ;
5558
drop table if exists t44 ;
5559
drop table if exists t55 ;
5560
drop table if exists t66 ;
5561
-------------------------------------------------------------------------
5562
--- minute(col1) in partition with coltype time
5563
-------------------------------------------------------------------------
5564
drop table if exists t1 ;
5565
drop table if exists t2 ;
5566
drop table if exists t3 ;
5567
drop table if exists t4 ;
5568
drop table if exists t5 ;
5569
drop table if exists t6 ;
5570
-------------------------------------------------------------------------
5571
--- Create tables with minute(col1)
5572
-------------------------------------------------------------------------
5573
create table t1 (col1 time) engine='NDB'
5574
partition by range(minute(col1))
5575
(partition p0 values less than (15),
5576
partition p1 values less than maxvalue);
5577
create table t2 (col1 time) engine='NDB'
5578
partition by list(minute(col1))
5579
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5580
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5581
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5582
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5583
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5584
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5586
create table t3 (col1 time) engine='NDB'
5587
partition by hash(minute(col1));
5588
create table t4 (colint int, col1 time) engine='NDB'
5589
partition by range(colint)
5590
subpartition by hash(minute(col1)) subpartitions 2
5591
(partition p0 values less than (15),
5592
partition p1 values less than maxvalue);
5593
create table t5 (colint int, col1 time) engine='NDB'
5594
partition by list(colint)
5595
subpartition by hash(minute(col1)) subpartitions 2
5596
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
5597
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5598
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
5599
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5601
create table t6 (colint int, col1 time) engine='NDB'
5602
partition by range(colint)
5603
(partition p0 values less than (minute('18:30')),
5604
partition p1 values less than maxvalue);
5605
-------------------------------------------------------------------------
5606
--- Access tables with minute(col1)
5607
-------------------------------------------------------------------------
5608
insert into t1 values ('09:09:15');
5609
insert into t1 values ('14:30:45');
5610
insert into t2 values ('09:09:15');
5611
insert into t2 values ('14:30:45');
5612
insert into t2 values ('21:59:22');
5613
insert into t3 values ('09:09:15');
5614
insert into t3 values ('14:30:45');
5615
insert into t3 values ('21:59:22');
5616
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
5617
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
5618
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
5619
select minute(col1) from t1 order by col1;
5623
select * from t1 order by col1;
5627
select * from t2 order by col1;
5632
select * from t3 order by col1;
5637
select * from t4 order by colint;
5643
select * from t5 order by colint;
5649
select * from t6 order by colint;
5655
update t1 set col1='10:24:23' where col1='09:09:15';
5656
update t2 set col1='10:24:23' where col1='09:09:15';
5657
update t3 set col1='10:24:23' where col1='09:09:15';
5658
update t4 set col1='10:24:23' where col1='09:09:15';
5659
update t5 set col1='10:24:23' where col1='09:09:15';
5660
update t6 set col1='10:24:23' where col1='09:09:15';
5661
select * from t1 order by col1;
5665
select * from t2 order by col1;
5670
select * from t3 order by col1;
5675
select * from t4 order by colint;
5681
select * from t5 order by colint;
5687
select * from t6 order by colint;
5693
-------------------------------------------------------------------------
5694
--- Alter tables with minute(col1)
5695
-------------------------------------------------------------------------
5696
drop table if exists t11 ;
5697
drop table if exists t22 ;
5698
drop table if exists t33 ;
5699
drop table if exists t44 ;
5700
drop table if exists t55 ;
5701
drop table if exists t66 ;
5702
create table t11 engine='NDB' as select * from t1;
5703
create table t22 engine='NDB' as select * from t2;
5704
create table t33 engine='NDB' as select * from t3;
5705
create table t44 engine='NDB' as select * from t4;
5706
create table t55 engine='NDB' as select * from t5;
5707
create table t66 engine='NDB' as select * from t6;
5709
partition by range(minute(col1))
5710
(partition p0 values less than (15),
5711
partition p1 values less than maxvalue);
5713
partition by list(minute(col1))
5714
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5715
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5716
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5717
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5718
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5719
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5722
partition by hash(minute(col1));
5724
partition by range(colint)
5725
subpartition by hash(minute(col1)) subpartitions 2
5726
(partition p0 values less than (15),
5727
partition p1 values less than maxvalue);
5729
partition by list(colint)
5730
subpartition by hash(minute(col1)) subpartitions 2
5731
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
5732
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5733
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
5734
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5737
partition by range(colint)
5738
(partition p0 values less than (minute('18:30')),
5739
partition p1 values less than maxvalue);
5740
select * from t11 order by col1;
5744
select * from t22 order by col1;
5749
select * from t33 order by col1;
5754
select * from t44 order by colint;
5760
select * from t55 order by colint;
5766
select * from t66 order by colint;
5772
---------------------------
5773
---- some alter table begin
5774
---------------------------
5776
partition by list(colint)
5777
subpartition by hash(minute(col1)) subpartitions 4
5778
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5779
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5781
show create table t55;
5783
t55 CREATE TABLE `t55` (
5784
`colint` int(11) DEFAULT NULL,
5785
`col1` time DEFAULT NULL
5786
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
5787
/*!50100 PARTITION BY LIST (colint)
5788
SUBPARTITION BY HASH (minute(col1))
5790
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
5791
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
5792
select * from t55 order by colint;
5798
-------------------------------------------------------------------------
5799
--- Delete rows and partitions of tables with minute(col1)
5800
-------------------------------------------------------------------------
5801
delete from t1 where col1='14:30:45';
5802
delete from t2 where col1='14:30:45';
5803
delete from t3 where col1='14:30:45';
5804
delete from t4 where col1='14:30:45';
5805
delete from t5 where col1='14:30:45';
5806
delete from t6 where col1='14:30:45';
5807
select * from t1 order by col1;
5810
select * from t2 order by col1;
5814
select * from t3 order by col1;
5818
select * from t4 order by colint;
5824
select * from t5 order by colint;
5830
insert into t1 values ('14:30:45');
5831
insert into t2 values ('14:30:45');
5832
insert into t3 values ('14:30:45');
5833
insert into t4 values (60,'14:30:45');
5834
insert into t5 values (60,'14:30:45');
5835
insert into t6 values (60,'14:30:45');
5836
select * from t1 order by col1;
5840
select * from t2 order by col1;
5845
select * from t3 order by col1;
5850
select * from t4 order by colint;
5857
select * from t5 order by colint;
5864
select * from t6 order by colint;
5871
-------------------------------------------------------------------------
5872
--- Delete rows and partitions of tables with minute(col1)
5873
-------------------------------------------------------------------------
5874
delete from t11 where col1='14:30:45';
5875
delete from t22 where col1='14:30:45';
5876
delete from t33 where col1='14:30:45';
5877
delete from t44 where col1='14:30:45';
5878
delete from t55 where col1='14:30:45';
5879
delete from t66 where col1='14:30:45';
5880
select * from t11 order by col1;
5883
select * from t22 order by col1;
5887
select * from t33 order by col1;
5891
select * from t44 order by colint;
5897
select * from t55 order by colint;
5903
insert into t11 values ('14:30:45');
5904
insert into t22 values ('14:30:45');
5905
insert into t33 values ('14:30:45');
5906
insert into t44 values (60,'14:30:45');
5907
insert into t55 values (60,'14:30:45');
5908
insert into t66 values (60,'14:30:45');
5909
select * from t11 order by col1;
5913
select * from t22 order by col1;
5918
select * from t33 order by col1;
5923
select * from t44 order by colint;
5930
select * from t55 order by colint;
5937
select * from t66 order by colint;
5944
-------------------------
5945
---- some alter table end
5946
-------------------------
5947
drop table if exists t1 ;
5948
drop table if exists t2 ;
5949
drop table if exists t3 ;
5950
drop table if exists t4 ;
5951
drop table if exists t5 ;
5952
drop table if exists t6 ;
5953
drop table if exists t11 ;
5954
drop table if exists t22 ;
5955
drop table if exists t33 ;
5956
drop table if exists t44 ;
5957
drop table if exists t55 ;
5958
drop table if exists t66 ;
5959
-------------------------------------------------------------------------
5960
--- second(col1) in partition with coltype time
5961
-------------------------------------------------------------------------
5962
drop table if exists t1 ;
5963
drop table if exists t2 ;
5964
drop table if exists t3 ;
5965
drop table if exists t4 ;
5966
drop table if exists t5 ;
5967
drop table if exists t6 ;
5968
-------------------------------------------------------------------------
5969
--- Create tables with second(col1)
5970
-------------------------------------------------------------------------
5971
create table t1 (col1 time) engine='NDB'
5972
partition by range(second(col1))
5973
(partition p0 values less than (15),
5974
partition p1 values less than maxvalue);
5975
create table t2 (col1 time) engine='NDB'
5976
partition by list(second(col1))
5977
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
5978
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
5979
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
5980
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
5981
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
5982
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
5984
create table t3 (col1 time) engine='NDB'
5985
partition by hash(second(col1));
5986
create table t4 (colint int, col1 time) engine='NDB'
5987
partition by range(colint)
5988
subpartition by hash(second(col1)) subpartitions 2
5989
(partition p0 values less than (15),
5990
partition p1 values less than maxvalue);
5991
create table t5 (colint int, col1 time) engine='NDB'
5992
partition by list(colint)
5993
subpartition by hash(second(col1)) subpartitions 2
5994
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
5995
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
5996
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
5997
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
5999
create table t6 (colint int, col1 time) engine='NDB'
6000
partition by range(colint)
6001
(partition p0 values less than (second('18:30:14')),
6002
partition p1 values less than maxvalue);
6003
-------------------------------------------------------------------------
6004
--- Access tables with second(col1)
6005
-------------------------------------------------------------------------
6006
insert into t1 values ('09:09:09');
6007
insert into t1 values ('14:30:20');
6008
insert into t2 values ('09:09:09');
6009
insert into t2 values ('14:30:20');
6010
insert into t2 values ('21:59:22');
6011
insert into t3 values ('09:09:09');
6012
insert into t3 values ('14:30:20');
6013
insert into t3 values ('21:59:22');
6014
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
6015
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
6016
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
6017
select second(col1) from t1 order by col1;
6021
select * from t1 order by col1;
6025
select * from t2 order by col1;
6030
select * from t3 order by col1;
6035
select * from t4 order by colint;
6041
select * from t5 order by colint;
6047
select * from t6 order by colint;
6053
update t1 set col1='10:22:33' where col1='09:09:09';
6054
update t2 set col1='10:22:33' where col1='09:09:09';
6055
update t3 set col1='10:22:33' where col1='09:09:09';
6056
update t4 set col1='10:22:33' where col1='09:09:09';
6057
update t5 set col1='10:22:33' where col1='09:09:09';
6058
update t6 set col1='10:22:33' where col1='09:09:09';
6059
select * from t1 order by col1;
6063
select * from t2 order by col1;
6068
select * from t3 order by col1;
6073
select * from t4 order by colint;
6079
select * from t5 order by colint;
6085
select * from t6 order by colint;
6091
-------------------------------------------------------------------------
6092
--- Alter tables with second(col1)
6093
-------------------------------------------------------------------------
6094
drop table if exists t11 ;
6095
drop table if exists t22 ;
6096
drop table if exists t33 ;
6097
drop table if exists t44 ;
6098
drop table if exists t55 ;
6099
drop table if exists t66 ;
6100
create table t11 engine='NDB' as select * from t1;
6101
create table t22 engine='NDB' as select * from t2;
6102
create table t33 engine='NDB' as select * from t3;
6103
create table t44 engine='NDB' as select * from t4;
6104
create table t55 engine='NDB' as select * from t5;
6105
create table t66 engine='NDB' as select * from t6;
6107
partition by range(second(col1))
6108
(partition p0 values less than (15),
6109
partition p1 values less than maxvalue);
6111
partition by list(second(col1))
6112
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6113
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6114
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6115
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6116
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6117
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6120
partition by hash(second(col1));
6122
partition by range(colint)
6123
subpartition by hash(second(col1)) subpartitions 2
6124
(partition p0 values less than (15),
6125
partition p1 values less than maxvalue);
6127
partition by list(colint)
6128
subpartition by hash(second(col1)) subpartitions 2
6129
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
6130
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6131
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
6132
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6135
partition by range(colint)
6136
(partition p0 values less than (second('18:30:14')),
6137
partition p1 values less than maxvalue);
6138
select * from t11 order by col1;
6142
select * from t22 order by col1;
6147
select * from t33 order by col1;
6152
select * from t44 order by colint;
6158
select * from t55 order by colint;
6164
select * from t66 order by colint;
6170
---------------------------
6171
---- some alter table begin
6172
---------------------------
6174
partition by list(colint)
6175
subpartition by hash(second(col1)) subpartitions 4
6176
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6177
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6179
show create table t55;
6181
t55 CREATE TABLE `t55` (
6182
`colint` int(11) DEFAULT NULL,
6183
`col1` time DEFAULT NULL
6184
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
6185
/*!50100 PARTITION BY LIST (colint)
6186
SUBPARTITION BY HASH (second(col1))
6188
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
6189
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
6190
select * from t55 order by colint;
6196
-------------------------------------------------------------------------
6197
--- Delete rows and partitions of tables with second(col1)
6198
-------------------------------------------------------------------------
6199
delete from t1 where col1='14:30:20';
6200
delete from t2 where col1='14:30:20';
6201
delete from t3 where col1='14:30:20';
6202
delete from t4 where col1='14:30:20';
6203
delete from t5 where col1='14:30:20';
6204
delete from t6 where col1='14:30:20';
6205
select * from t1 order by col1;
6208
select * from t2 order by col1;
6212
select * from t3 order by col1;
6216
select * from t4 order by colint;
6222
select * from t5 order by colint;
6228
insert into t1 values ('14:30:20');
6229
insert into t2 values ('14:30:20');
6230
insert into t3 values ('14:30:20');
6231
insert into t4 values (60,'14:30:20');
6232
insert into t5 values (60,'14:30:20');
6233
insert into t6 values (60,'14:30:20');
6234
select * from t1 order by col1;
6238
select * from t2 order by col1;
6243
select * from t3 order by col1;
6248
select * from t4 order by colint;
6255
select * from t5 order by colint;
6262
select * from t6 order by colint;
6269
-------------------------------------------------------------------------
6270
--- Delete rows and partitions of tables with second(col1)
6271
-------------------------------------------------------------------------
6272
delete from t11 where col1='14:30:20';
6273
delete from t22 where col1='14:30:20';
6274
delete from t33 where col1='14:30:20';
6275
delete from t44 where col1='14:30:20';
6276
delete from t55 where col1='14:30:20';
6277
delete from t66 where col1='14:30:20';
6278
select * from t11 order by col1;
6281
select * from t22 order by col1;
6285
select * from t33 order by col1;
6289
select * from t44 order by colint;
6295
select * from t55 order by colint;
6301
insert into t11 values ('14:30:20');
6302
insert into t22 values ('14:30:20');
6303
insert into t33 values ('14:30:20');
6304
insert into t44 values (60,'14:30:20');
6305
insert into t55 values (60,'14:30:20');
6306
insert into t66 values (60,'14:30:20');
6307
select * from t11 order by col1;
6311
select * from t22 order by col1;
6316
select * from t33 order by col1;
6321
select * from t44 order by colint;
6328
select * from t55 order by colint;
6335
select * from t66 order by colint;
6342
-------------------------
6343
---- some alter table end
6344
-------------------------
6345
drop table if exists t1 ;
6346
drop table if exists t2 ;
6347
drop table if exists t3 ;
6348
drop table if exists t4 ;
6349
drop table if exists t5 ;
6350
drop table if exists t6 ;
6351
drop table if exists t11 ;
6352
drop table if exists t22 ;
6353
drop table if exists t33 ;
6354
drop table if exists t44 ;
6355
drop table if exists t55 ;
6356
drop table if exists t66 ;
6357
-------------------------------------------------------------------------
6358
--- second(col1) in partition with coltype char(30)
6359
-------------------------------------------------------------------------
6360
drop table if exists t1 ;
6361
drop table if exists t2 ;
6362
drop table if exists t3 ;
6363
drop table if exists t4 ;
6364
drop table if exists t5 ;
6365
drop table if exists t6 ;
6366
-------------------------------------------------------------------------
6367
--- Create tables with second(col1)
6368
-------------------------------------------------------------------------
6369
create table t1 (col1 char(30)) engine='NDB'
6370
partition by range(second(col1))
6371
(partition p0 values less than (15),
6372
partition p1 values less than maxvalue);
6373
create table t2 (col1 char(30)) engine='NDB'
6374
partition by list(second(col1))
6375
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6376
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6377
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6378
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6379
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6380
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6382
create table t3 (col1 char(30)) engine='NDB'
6383
partition by hash(second(col1));
6384
create table t4 (colint int, col1 char(30)) engine='NDB'
6385
partition by range(colint)
6386
subpartition by hash(second(col1)) subpartitions 2
6387
(partition p0 values less than (15),
6388
partition p1 values less than maxvalue);
6389
create table t5 (colint int, col1 char(30)) engine='NDB'
6390
partition by list(colint)
6391
subpartition by hash(second(col1)) subpartitions 2
6392
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
6393
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6394
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
6395
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6397
create table t6 (colint int, col1 char(30)) engine='NDB'
6398
partition by range(colint)
6399
(partition p0 values less than (second('18:30:14')),
6400
partition p1 values less than maxvalue);
6401
-------------------------------------------------------------------------
6402
--- Access tables with second(col1)
6403
-------------------------------------------------------------------------
6404
insert into t1 values ('09:09:09');
6405
insert into t1 values ('14:30:20');
6406
insert into t2 values ('09:09:09');
6407
insert into t2 values ('14:30:20');
6408
insert into t2 values ('21:59:22');
6409
insert into t3 values ('09:09:09');
6410
insert into t3 values ('14:30:20');
6411
insert into t3 values ('21:59:22');
6412
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
6413
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
6414
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
6415
select second(col1) from t1 order by col1;
6419
select * from t1 order by col1;
6423
select * from t2 order by col1;
6428
select * from t3 order by col1;
6433
select * from t4 order by colint;
6439
select * from t5 order by colint;
6445
select * from t6 order by colint;
6451
update t1 set col1='10:22:33' where col1='09:09:09';
6452
update t2 set col1='10:22:33' where col1='09:09:09';
6453
update t3 set col1='10:22:33' where col1='09:09:09';
6454
update t4 set col1='10:22:33' where col1='09:09:09';
6455
update t5 set col1='10:22:33' where col1='09:09:09';
6456
update t6 set col1='10:22:33' where col1='09:09:09';
6457
select * from t1 order by col1;
6461
select * from t2 order by col1;
6466
select * from t3 order by col1;
6471
select * from t4 order by colint;
6477
select * from t5 order by colint;
6483
select * from t6 order by colint;
6489
-------------------------------------------------------------------------
6490
--- Alter tables with second(col1)
6491
-------------------------------------------------------------------------
6492
drop table if exists t11 ;
6493
drop table if exists t22 ;
6494
drop table if exists t33 ;
6495
drop table if exists t44 ;
6496
drop table if exists t55 ;
6497
drop table if exists t66 ;
6498
create table t11 engine='NDB' as select * from t1;
6499
create table t22 engine='NDB' as select * from t2;
6500
create table t33 engine='NDB' as select * from t3;
6501
create table t44 engine='NDB' as select * from t4;
6502
create table t55 engine='NDB' as select * from t5;
6503
create table t66 engine='NDB' as select * from t6;
6505
partition by range(second(col1))
6506
(partition p0 values less than (15),
6507
partition p1 values less than maxvalue);
6509
partition by list(second(col1))
6510
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6511
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6512
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6513
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6514
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6515
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6518
partition by hash(second(col1));
6520
partition by range(colint)
6521
subpartition by hash(second(col1)) subpartitions 2
6522
(partition p0 values less than (15),
6523
partition p1 values less than maxvalue);
6525
partition by list(colint)
6526
subpartition by hash(second(col1)) subpartitions 2
6527
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
6528
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6529
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
6530
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6533
partition by range(colint)
6534
(partition p0 values less than (second('18:30:14')),
6535
partition p1 values less than maxvalue);
6536
select * from t11 order by col1;
6540
select * from t22 order by col1;
6545
select * from t33 order by col1;
6550
select * from t44 order by colint;
6556
select * from t55 order by colint;
6562
select * from t66 order by colint;
6568
---------------------------
6569
---- some alter table begin
6570
---------------------------
6572
partition by list(colint)
6573
subpartition by hash(second(col1)) subpartitions 4
6574
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6575
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6577
show create table t55;
6579
t55 CREATE TABLE `t55` (
6580
`colint` int(11) DEFAULT NULL,
6581
`col1` char(30) DEFAULT NULL
6582
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
6583
/*!50100 PARTITION BY LIST (colint)
6584
SUBPARTITION BY HASH (second(col1))
6586
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
6587
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
6588
select * from t55 order by colint;
6594
-------------------------------------------------------------------------
6595
--- Delete rows and partitions of tables with second(col1)
6596
-------------------------------------------------------------------------
6597
delete from t1 where col1='14:30:20';
6598
delete from t2 where col1='14:30:20';
6599
delete from t3 where col1='14:30:20';
6600
delete from t4 where col1='14:30:20';
6601
delete from t5 where col1='14:30:20';
6602
delete from t6 where col1='14:30:20';
6603
select * from t1 order by col1;
6606
select * from t2 order by col1;
6610
select * from t3 order by col1;
6614
select * from t4 order by colint;
6620
select * from t5 order by colint;
6626
insert into t1 values ('14:30:20');
6627
insert into t2 values ('14:30:20');
6628
insert into t3 values ('14:30:20');
6629
insert into t4 values (60,'14:30:20');
6630
insert into t5 values (60,'14:30:20');
6631
insert into t6 values (60,'14:30:20');
6632
select * from t1 order by col1;
6636
select * from t2 order by col1;
6641
select * from t3 order by col1;
6646
select * from t4 order by colint;
6653
select * from t5 order by colint;
6660
select * from t6 order by colint;
6667
-------------------------------------------------------------------------
6668
--- Delete rows and partitions of tables with second(col1)
6669
-------------------------------------------------------------------------
6670
delete from t11 where col1='14:30:20';
6671
delete from t22 where col1='14:30:20';
6672
delete from t33 where col1='14:30:20';
6673
delete from t44 where col1='14:30:20';
6674
delete from t55 where col1='14:30:20';
6675
delete from t66 where col1='14:30:20';
6676
select * from t11 order by col1;
6679
select * from t22 order by col1;
6683
select * from t33 order by col1;
6687
select * from t44 order by colint;
6693
select * from t55 order by colint;
6699
insert into t11 values ('14:30:20');
6700
insert into t22 values ('14:30:20');
6701
insert into t33 values ('14:30:20');
6702
insert into t44 values (60,'14:30:20');
6703
insert into t55 values (60,'14:30:20');
6704
insert into t66 values (60,'14:30:20');
6705
select * from t11 order by col1;
6709
select * from t22 order by col1;
6714
select * from t33 order by col1;
6719
select * from t44 order by colint;
6726
select * from t55 order by colint;
6733
select * from t66 order by colint;
6740
-------------------------
6741
---- some alter table end
6742
-------------------------
6743
drop table if exists t1 ;
6744
drop table if exists t2 ;
6745
drop table if exists t3 ;
6746
drop table if exists t4 ;
6747
drop table if exists t5 ;
6748
drop table if exists t6 ;
6749
drop table if exists t11 ;
6750
drop table if exists t22 ;
6751
drop table if exists t33 ;
6752
drop table if exists t44 ;
6753
drop table if exists t55 ;
6754
drop table if exists t66 ;
6755
-------------------------------------------------------------------------
6756
--- month(col1) in partition with coltype date
6757
-------------------------------------------------------------------------
6758
drop table if exists t1 ;
6759
drop table if exists t2 ;
6760
drop table if exists t3 ;
6761
drop table if exists t4 ;
6762
drop table if exists t5 ;
6763
drop table if exists t6 ;
6764
-------------------------------------------------------------------------
6765
--- Create tables with month(col1)
6766
-------------------------------------------------------------------------
6767
create table t1 (col1 date) engine='NDB'
6768
partition by range(month(col1))
6769
(partition p0 values less than (15),
6770
partition p1 values less than maxvalue);
6771
create table t2 (col1 date) engine='NDB'
6772
partition by list(month(col1))
6773
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6774
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6775
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6776
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6777
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6778
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6780
create table t3 (col1 date) engine='NDB'
6781
partition by hash(month(col1));
6782
create table t4 (colint int, col1 date) engine='NDB'
6783
partition by range(colint)
6784
subpartition by hash(month(col1)) subpartitions 2
6785
(partition p0 values less than (15),
6786
partition p1 values less than maxvalue);
6787
create table t5 (colint int, col1 date) engine='NDB'
6788
partition by list(colint)
6789
subpartition by hash(month(col1)) subpartitions 2
6790
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
6791
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6792
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
6793
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6795
create table t6 (colint int, col1 date) engine='NDB'
6796
partition by range(colint)
6797
(partition p0 values less than (month('2006-10-14')),
6798
partition p1 values less than maxvalue);
6799
-------------------------------------------------------------------------
6800
--- Access tables with month(col1)
6801
-------------------------------------------------------------------------
6802
insert into t1 values ('2006-01-03');
6803
insert into t1 values ('2006-12-17');
6804
insert into t2 values ('2006-01-03');
6805
insert into t2 values ('2006-12-17');
6806
insert into t2 values ('2006-05-25');
6807
insert into t3 values ('2006-01-03');
6808
insert into t3 values ('2006-12-17');
6809
insert into t3 values ('2006-05-25');
6810
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
6811
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
6812
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
6813
select month(col1) from t1 order by col1;
6817
select * from t1 order by col1;
6821
select * from t2 order by col1;
6826
select * from t3 order by col1;
6831
select * from t4 order by colint;
6837
select * from t5 order by colint;
6843
select * from t6 order by colint;
6849
update t1 set col1='2006-11-06' where col1='2006-01-03';
6850
update t2 set col1='2006-11-06' where col1='2006-01-03';
6851
update t3 set col1='2006-11-06' where col1='2006-01-03';
6852
update t4 set col1='2006-11-06' where col1='2006-01-03';
6853
update t5 set col1='2006-11-06' where col1='2006-01-03';
6854
update t6 set col1='2006-11-06' where col1='2006-01-03';
6855
select * from t1 order by col1;
6859
select * from t2 order by col1;
6864
select * from t3 order by col1;
6869
select * from t4 order by colint;
6875
select * from t5 order by colint;
6881
select * from t6 order by colint;
6887
-------------------------------------------------------------------------
6888
--- Alter tables with month(col1)
6889
-------------------------------------------------------------------------
6890
drop table if exists t11 ;
6891
drop table if exists t22 ;
6892
drop table if exists t33 ;
6893
drop table if exists t44 ;
6894
drop table if exists t55 ;
6895
drop table if exists t66 ;
6896
create table t11 engine='NDB' as select * from t1;
6897
create table t22 engine='NDB' as select * from t2;
6898
create table t33 engine='NDB' as select * from t3;
6899
create table t44 engine='NDB' as select * from t4;
6900
create table t55 engine='NDB' as select * from t5;
6901
create table t66 engine='NDB' as select * from t6;
6903
partition by range(month(col1))
6904
(partition p0 values less than (15),
6905
partition p1 values less than maxvalue);
6907
partition by list(month(col1))
6908
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
6909
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
6910
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
6911
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
6912
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
6913
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
6916
partition by hash(month(col1));
6918
partition by range(colint)
6919
subpartition by hash(month(col1)) subpartitions 2
6920
(partition p0 values less than (15),
6921
partition p1 values less than maxvalue);
6923
partition by list(colint)
6924
subpartition by hash(month(col1)) subpartitions 2
6925
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
6926
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6927
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
6928
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6931
partition by range(colint)
6932
(partition p0 values less than (month('2006-10-14')),
6933
partition p1 values less than maxvalue);
6934
select * from t11 order by col1;
6938
select * from t22 order by col1;
6943
select * from t33 order by col1;
6948
select * from t44 order by colint;
6954
select * from t55 order by colint;
6960
select * from t66 order by colint;
6966
---------------------------
6967
---- some alter table begin
6968
---------------------------
6970
partition by list(colint)
6971
subpartition by hash(month(col1)) subpartitions 4
6972
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
6973
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
6975
show create table t55;
6977
t55 CREATE TABLE `t55` (
6978
`colint` int(11) DEFAULT NULL,
6979
`col1` date DEFAULT NULL
6980
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
6981
/*!50100 PARTITION BY LIST (colint)
6982
SUBPARTITION BY HASH (month(col1))
6984
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
6985
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
6986
select * from t55 order by colint;
6992
-------------------------------------------------------------------------
6993
--- Delete rows and partitions of tables with month(col1)
6994
-------------------------------------------------------------------------
6995
delete from t1 where col1='2006-12-17';
6996
delete from t2 where col1='2006-12-17';
6997
delete from t3 where col1='2006-12-17';
6998
delete from t4 where col1='2006-12-17';
6999
delete from t5 where col1='2006-12-17';
7000
delete from t6 where col1='2006-12-17';
7001
select * from t1 order by col1;
7004
select * from t2 order by col1;
7008
select * from t3 order by col1;
7012
select * from t4 order by colint;
7018
select * from t5 order by colint;
7024
insert into t1 values ('2006-12-17');
7025
insert into t2 values ('2006-12-17');
7026
insert into t3 values ('2006-12-17');
7027
insert into t4 values (60,'2006-12-17');
7028
insert into t5 values (60,'2006-12-17');
7029
insert into t6 values (60,'2006-12-17');
7030
select * from t1 order by col1;
7034
select * from t2 order by col1;
7039
select * from t3 order by col1;
7044
select * from t4 order by colint;
7051
select * from t5 order by colint;
7058
select * from t6 order by colint;
7065
-------------------------------------------------------------------------
7066
--- Delete rows and partitions of tables with month(col1)
7067
-------------------------------------------------------------------------
7068
delete from t11 where col1='2006-12-17';
7069
delete from t22 where col1='2006-12-17';
7070
delete from t33 where col1='2006-12-17';
7071
delete from t44 where col1='2006-12-17';
7072
delete from t55 where col1='2006-12-17';
7073
delete from t66 where col1='2006-12-17';
7074
select * from t11 order by col1;
7077
select * from t22 order by col1;
7081
select * from t33 order by col1;
7085
select * from t44 order by colint;
7091
select * from t55 order by colint;
7097
insert into t11 values ('2006-12-17');
7098
insert into t22 values ('2006-12-17');
7099
insert into t33 values ('2006-12-17');
7100
insert into t44 values (60,'2006-12-17');
7101
insert into t55 values (60,'2006-12-17');
7102
insert into t66 values (60,'2006-12-17');
7103
select * from t11 order by col1;
7107
select * from t22 order by col1;
7112
select * from t33 order by col1;
7117
select * from t44 order by colint;
7124
select * from t55 order by colint;
7131
select * from t66 order by colint;
7138
-------------------------
7139
---- some alter table end
7140
-------------------------
7141
drop table if exists t1 ;
7142
drop table if exists t2 ;
7143
drop table if exists t3 ;
7144
drop table if exists t4 ;
7145
drop table if exists t5 ;
7146
drop table if exists t6 ;
7147
drop table if exists t11 ;
7148
drop table if exists t22 ;
7149
drop table if exists t33 ;
7150
drop table if exists t44 ;
7151
drop table if exists t55 ;
7152
drop table if exists t66 ;
7153
-------------------------------------------------------------------------
7154
--- quarter(col1) in partition with coltype date
7155
-------------------------------------------------------------------------
7156
drop table if exists t1 ;
7157
drop table if exists t2 ;
7158
drop table if exists t3 ;
7159
drop table if exists t4 ;
7160
drop table if exists t5 ;
7161
drop table if exists t6 ;
7162
-------------------------------------------------------------------------
7163
--- Create tables with quarter(col1)
7164
-------------------------------------------------------------------------
7165
create table t1 (col1 date) engine='NDB'
7166
partition by range(quarter(col1))
7167
(partition p0 values less than (15),
7168
partition p1 values less than maxvalue);
7169
create table t2 (col1 date) engine='NDB'
7170
partition by list(quarter(col1))
7171
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7172
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7173
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7174
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7175
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7176
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7178
create table t3 (col1 date) engine='NDB'
7179
partition by hash(quarter(col1));
7180
create table t4 (colint int, col1 date) engine='NDB'
7181
partition by range(colint)
7182
subpartition by hash(quarter(col1)) subpartitions 2
7183
(partition p0 values less than (15),
7184
partition p1 values less than maxvalue);
7185
create table t5 (colint int, col1 date) engine='NDB'
7186
partition by list(colint)
7187
subpartition by hash(quarter(col1)) subpartitions 2
7188
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
7189
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7190
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
7191
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7193
create table t6 (colint int, col1 date) engine='NDB'
7194
partition by range(colint)
7195
(partition p0 values less than (quarter('2006-10-14')),
7196
partition p1 values less than maxvalue);
7197
-------------------------------------------------------------------------
7198
--- Access tables with quarter(col1)
7199
-------------------------------------------------------------------------
7200
insert into t1 values ('2006-01-03');
7201
insert into t1 values ('2006-12-17');
7202
insert into t2 values ('2006-01-03');
7203
insert into t2 values ('2006-12-17');
7204
insert into t2 values ('2006-09-25');
7205
insert into t3 values ('2006-01-03');
7206
insert into t3 values ('2006-12-17');
7207
insert into t3 values ('2006-09-25');
7208
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
7209
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
7210
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
7211
select quarter(col1) from t1 order by col1;
7215
select * from t1 order by col1;
7219
select * from t2 order by col1;
7224
select * from t3 order by col1;
7229
select * from t4 order by colint;
7235
select * from t5 order by colint;
7241
select * from t6 order by colint;
7247
update t1 set col1='2006-07-30' where col1='2006-01-03';
7248
update t2 set col1='2006-07-30' where col1='2006-01-03';
7249
update t3 set col1='2006-07-30' where col1='2006-01-03';
7250
update t4 set col1='2006-07-30' where col1='2006-01-03';
7251
update t5 set col1='2006-07-30' where col1='2006-01-03';
7252
update t6 set col1='2006-07-30' where col1='2006-01-03';
7253
select * from t1 order by col1;
7257
select * from t2 order by col1;
7262
select * from t3 order by col1;
7267
select * from t4 order by colint;
7273
select * from t5 order by colint;
7279
select * from t6 order by colint;
7285
-------------------------------------------------------------------------
7286
--- Alter tables with quarter(col1)
7287
-------------------------------------------------------------------------
7288
drop table if exists t11 ;
7289
drop table if exists t22 ;
7290
drop table if exists t33 ;
7291
drop table if exists t44 ;
7292
drop table if exists t55 ;
7293
drop table if exists t66 ;
7294
create table t11 engine='NDB' as select * from t1;
7295
create table t22 engine='NDB' as select * from t2;
7296
create table t33 engine='NDB' as select * from t3;
7297
create table t44 engine='NDB' as select * from t4;
7298
create table t55 engine='NDB' as select * from t5;
7299
create table t66 engine='NDB' as select * from t6;
7301
partition by range(quarter(col1))
7302
(partition p0 values less than (15),
7303
partition p1 values less than maxvalue);
7305
partition by list(quarter(col1))
7306
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7307
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7308
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7309
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7310
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7311
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7314
partition by hash(quarter(col1));
7316
partition by range(colint)
7317
subpartition by hash(quarter(col1)) subpartitions 2
7318
(partition p0 values less than (15),
7319
partition p1 values less than maxvalue);
7321
partition by list(colint)
7322
subpartition by hash(quarter(col1)) subpartitions 2
7323
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
7324
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7325
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
7326
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7329
partition by range(colint)
7330
(partition p0 values less than (quarter('2006-10-14')),
7331
partition p1 values less than maxvalue);
7332
select * from t11 order by col1;
7336
select * from t22 order by col1;
7341
select * from t33 order by col1;
7346
select * from t44 order by colint;
7352
select * from t55 order by colint;
7358
select * from t66 order by colint;
7364
---------------------------
7365
---- some alter table begin
7366
---------------------------
7368
partition by list(colint)
7369
subpartition by hash(quarter(col1)) subpartitions 4
7370
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7371
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7373
show create table t55;
7375
t55 CREATE TABLE `t55` (
7376
`colint` int(11) DEFAULT NULL,
7377
`col1` date DEFAULT NULL
7378
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
7379
/*!50100 PARTITION BY LIST (colint)
7380
SUBPARTITION BY HASH (quarter(col1))
7382
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
7383
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
7384
select * from t55 order by colint;
7390
-------------------------------------------------------------------------
7391
--- Delete rows and partitions of tables with quarter(col1)
7392
-------------------------------------------------------------------------
7393
delete from t1 where col1='2006-12-17';
7394
delete from t2 where col1='2006-12-17';
7395
delete from t3 where col1='2006-12-17';
7396
delete from t4 where col1='2006-12-17';
7397
delete from t5 where col1='2006-12-17';
7398
delete from t6 where col1='2006-12-17';
7399
select * from t1 order by col1;
7402
select * from t2 order by col1;
7406
select * from t3 order by col1;
7410
select * from t4 order by colint;
7416
select * from t5 order by colint;
7422
insert into t1 values ('2006-12-17');
7423
insert into t2 values ('2006-12-17');
7424
insert into t3 values ('2006-12-17');
7425
insert into t4 values (60,'2006-12-17');
7426
insert into t5 values (60,'2006-12-17');
7427
insert into t6 values (60,'2006-12-17');
7428
select * from t1 order by col1;
7432
select * from t2 order by col1;
7437
select * from t3 order by col1;
7442
select * from t4 order by colint;
7449
select * from t5 order by colint;
7456
select * from t6 order by colint;
7463
-------------------------------------------------------------------------
7464
--- Delete rows and partitions of tables with quarter(col1)
7465
-------------------------------------------------------------------------
7466
delete from t11 where col1='2006-12-17';
7467
delete from t22 where col1='2006-12-17';
7468
delete from t33 where col1='2006-12-17';
7469
delete from t44 where col1='2006-12-17';
7470
delete from t55 where col1='2006-12-17';
7471
delete from t66 where col1='2006-12-17';
7472
select * from t11 order by col1;
7475
select * from t22 order by col1;
7479
select * from t33 order by col1;
7483
select * from t44 order by colint;
7489
select * from t55 order by colint;
7495
insert into t11 values ('2006-12-17');
7496
insert into t22 values ('2006-12-17');
7497
insert into t33 values ('2006-12-17');
7498
insert into t44 values (60,'2006-12-17');
7499
insert into t55 values (60,'2006-12-17');
7500
insert into t66 values (60,'2006-12-17');
7501
select * from t11 order by col1;
7505
select * from t22 order by col1;
7510
select * from t33 order by col1;
7515
select * from t44 order by colint;
7522
select * from t55 order by colint;
7529
select * from t66 order by colint;
7536
-------------------------
7537
---- some alter table end
7538
-------------------------
7539
drop table if exists t1 ;
7540
drop table if exists t2 ;
7541
drop table if exists t3 ;
7542
drop table if exists t4 ;
7543
drop table if exists t5 ;
7544
drop table if exists t6 ;
7545
drop table if exists t11 ;
7546
drop table if exists t22 ;
7547
drop table if exists t33 ;
7548
drop table if exists t44 ;
7549
drop table if exists t55 ;
7550
drop table if exists t66 ;
7551
-------------------------------------------------------------------------
7552
--- time_to_sec(col1)-(time_to_sec(col1)-20) in partition with coltype time
7553
-------------------------------------------------------------------------
7554
drop table if exists t1 ;
7555
drop table if exists t2 ;
7556
drop table if exists t3 ;
7557
drop table if exists t4 ;
7558
drop table if exists t5 ;
7559
drop table if exists t6 ;
7560
-------------------------------------------------------------------------
7561
--- Create tables with time_to_sec(col1)-(time_to_sec(col1)-20)
7562
-------------------------------------------------------------------------
7563
create table t1 (col1 time) engine='NDB'
7564
partition by range(time_to_sec(col1)-(time_to_sec(col1)-20))
7565
(partition p0 values less than (15),
7566
partition p1 values less than maxvalue);
7567
create table t2 (col1 time) engine='NDB'
7568
partition by list(time_to_sec(col1)-(time_to_sec(col1)-20))
7569
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7570
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7571
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7572
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7573
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7574
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7576
create table t3 (col1 time) engine='NDB'
7577
partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20));
7578
create table t4 (colint int, col1 time) engine='NDB'
7579
partition by range(colint)
7580
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
7581
(partition p0 values less than (15),
7582
partition p1 values less than maxvalue);
7583
create table t5 (colint int, col1 time) engine='NDB'
7584
partition by list(colint)
7585
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
7586
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
7587
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7588
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
7589
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7591
create table t6 (colint int, col1 time) engine='NDB'
7592
partition by range(colint)
7593
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
7594
partition p1 values less than maxvalue);
7595
-------------------------------------------------------------------------
7596
--- Access tables with time_to_sec(col1)-(time_to_sec(col1)-20)
7597
-------------------------------------------------------------------------
7598
insert into t1 values ('09:09:15');
7599
insert into t1 values ('14:30:45');
7600
insert into t2 values ('09:09:15');
7601
insert into t2 values ('14:30:45');
7602
insert into t2 values ('21:59:22');
7603
insert into t3 values ('09:09:15');
7604
insert into t3 values ('14:30:45');
7605
insert into t3 values ('21:59:22');
7606
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t4;
7607
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t5;
7608
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_time.inc' into table t6;
7609
select time_to_sec(col1)-(time_to_sec(col1)-20) from t1 order by col1;
7610
time_to_sec(col1)-(time_to_sec(col1)-20)
7613
select * from t1 order by col1;
7617
select * from t2 order by col1;
7622
select * from t3 order by col1;
7627
select * from t4 order by colint;
7633
select * from t5 order by colint;
7639
select * from t6 order by colint;
7645
update t1 set col1='10:33:11' where col1='09:09:15';
7646
update t2 set col1='10:33:11' where col1='09:09:15';
7647
update t3 set col1='10:33:11' where col1='09:09:15';
7648
update t4 set col1='10:33:11' where col1='09:09:15';
7649
update t5 set col1='10:33:11' where col1='09:09:15';
7650
update t6 set col1='10:33:11' where col1='09:09:15';
7651
select * from t1 order by col1;
7655
select * from t2 order by col1;
7660
select * from t3 order by col1;
7665
select * from t4 order by colint;
7671
select * from t5 order by colint;
7677
select * from t6 order by colint;
7683
-------------------------------------------------------------------------
7684
--- Alter tables with time_to_sec(col1)-(time_to_sec(col1)-20)
7685
-------------------------------------------------------------------------
7686
drop table if exists t11 ;
7687
drop table if exists t22 ;
7688
drop table if exists t33 ;
7689
drop table if exists t44 ;
7690
drop table if exists t55 ;
7691
drop table if exists t66 ;
7692
create table t11 engine='NDB' as select * from t1;
7693
create table t22 engine='NDB' as select * from t2;
7694
create table t33 engine='NDB' as select * from t3;
7695
create table t44 engine='NDB' as select * from t4;
7696
create table t55 engine='NDB' as select * from t5;
7697
create table t66 engine='NDB' as select * from t6;
7699
partition by range(time_to_sec(col1)-(time_to_sec(col1)-20))
7700
(partition p0 values less than (15),
7701
partition p1 values less than maxvalue);
7703
partition by list(time_to_sec(col1)-(time_to_sec(col1)-20))
7704
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7705
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7706
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7707
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7708
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7709
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7712
partition by hash(time_to_sec(col1)-(time_to_sec(col1)-20));
7714
partition by range(colint)
7715
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
7716
(partition p0 values less than (15),
7717
partition p1 values less than maxvalue);
7719
partition by list(colint)
7720
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 2
7721
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
7722
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7723
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
7724
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7727
partition by range(colint)
7728
(partition p0 values less than (time_to_sec('18:30:14')-(time_to_sec('17:59:59'))),
7729
partition p1 values less than maxvalue);
7730
select * from t11 order by col1;
7734
select * from t22 order by col1;
7739
select * from t33 order by col1;
7744
select * from t44 order by colint;
7750
select * from t55 order by colint;
7756
select * from t66 order by colint;
7762
---------------------------
7763
---- some alter table begin
7764
---------------------------
7766
partition by list(colint)
7767
subpartition by hash(time_to_sec(col1)-(time_to_sec(col1)-20)) subpartitions 4
7768
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7769
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7771
show create table t55;
7773
t55 CREATE TABLE `t55` (
7774
`colint` int(11) DEFAULT NULL,
7775
`col1` time DEFAULT NULL
7776
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
7777
/*!50100 PARTITION BY LIST (colint)
7778
SUBPARTITION BY HASH (time_to_sec(col1)-(time_to_sec(col1)-20))
7780
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
7781
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
7782
select * from t55 order by colint;
7788
-------------------------------------------------------------------------
7789
--- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20)
7790
-------------------------------------------------------------------------
7791
delete from t1 where col1='14:30:45';
7792
delete from t2 where col1='14:30:45';
7793
delete from t3 where col1='14:30:45';
7794
delete from t4 where col1='14:30:45';
7795
delete from t5 where col1='14:30:45';
7796
delete from t6 where col1='14:30:45';
7797
select * from t1 order by col1;
7800
select * from t2 order by col1;
7804
select * from t3 order by col1;
7808
select * from t4 order by colint;
7814
select * from t5 order by colint;
7820
insert into t1 values ('14:30:45');
7821
insert into t2 values ('14:30:45');
7822
insert into t3 values ('14:30:45');
7823
insert into t4 values (60,'14:30:45');
7824
insert into t5 values (60,'14:30:45');
7825
insert into t6 values (60,'14:30:45');
7826
select * from t1 order by col1;
7830
select * from t2 order by col1;
7835
select * from t3 order by col1;
7840
select * from t4 order by colint;
7847
select * from t5 order by colint;
7854
select * from t6 order by colint;
7861
-------------------------------------------------------------------------
7862
--- Delete rows and partitions of tables with time_to_sec(col1)-(time_to_sec(col1)-20)
7863
-------------------------------------------------------------------------
7864
delete from t11 where col1='14:30:45';
7865
delete from t22 where col1='14:30:45';
7866
delete from t33 where col1='14:30:45';
7867
delete from t44 where col1='14:30:45';
7868
delete from t55 where col1='14:30:45';
7869
delete from t66 where col1='14:30:45';
7870
select * from t11 order by col1;
7873
select * from t22 order by col1;
7877
select * from t33 order by col1;
7881
select * from t44 order by colint;
7887
select * from t55 order by colint;
7893
insert into t11 values ('14:30:45');
7894
insert into t22 values ('14:30:45');
7895
insert into t33 values ('14:30:45');
7896
insert into t44 values (60,'14:30:45');
7897
insert into t55 values (60,'14:30:45');
7898
insert into t66 values (60,'14:30:45');
7899
select * from t11 order by col1;
7903
select * from t22 order by col1;
7908
select * from t33 order by col1;
7913
select * from t44 order by colint;
7920
select * from t55 order by colint;
7927
select * from t66 order by colint;
7934
-------------------------
7935
---- some alter table end
7936
-------------------------
7937
drop table if exists t1 ;
7938
drop table if exists t2 ;
7939
drop table if exists t3 ;
7940
drop table if exists t4 ;
7941
drop table if exists t5 ;
7942
drop table if exists t6 ;
7943
drop table if exists t11 ;
7944
drop table if exists t22 ;
7945
drop table if exists t33 ;
7946
drop table if exists t44 ;
7947
drop table if exists t55 ;
7948
drop table if exists t66 ;
7949
-------------------------------------------------------------------------
7950
--- to_days(col1)-to_days('2006-01-01') in partition with coltype date
7951
-------------------------------------------------------------------------
7952
drop table if exists t1 ;
7953
drop table if exists t2 ;
7954
drop table if exists t3 ;
7955
drop table if exists t4 ;
7956
drop table if exists t5 ;
7957
drop table if exists t6 ;
7958
-------------------------------------------------------------------------
7959
--- Create tables with to_days(col1)-to_days('2006-01-01')
7960
-------------------------------------------------------------------------
7961
create table t1 (col1 date) engine='NDB'
7962
partition by range(to_days(col1)-to_days('2006-01-01'))
7963
(partition p0 values less than (15),
7964
partition p1 values less than maxvalue);
7965
create table t2 (col1 date) engine='NDB'
7966
partition by list(to_days(col1)-to_days('2006-01-01'))
7967
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
7968
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
7969
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
7970
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
7971
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
7972
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
7974
create table t3 (col1 date) engine='NDB'
7975
partition by hash(to_days(col1)-to_days('2006-01-01'));
7976
create table t4 (colint int, col1 date) engine='NDB'
7977
partition by range(colint)
7978
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
7979
(partition p0 values less than (15),
7980
partition p1 values less than maxvalue);
7981
create table t5 (colint int, col1 date) engine='NDB'
7982
partition by list(colint)
7983
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
7984
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
7985
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
7986
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
7987
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
7989
create table t6 (colint int, col1 date) engine='NDB'
7990
partition by range(colint)
7991
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
7992
partition p1 values less than maxvalue);
7993
-------------------------------------------------------------------------
7994
--- Access tables with to_days(col1)-to_days('2006-01-01')
7995
-------------------------------------------------------------------------
7996
insert into t1 values ('2006-02-03');
7997
insert into t1 values ('2006-01-17');
7998
insert into t2 values ('2006-02-03');
7999
insert into t2 values ('2006-01-17');
8000
insert into t2 values ('2006-01-25');
8001
insert into t3 values ('2006-02-03');
8002
insert into t3 values ('2006-01-17');
8003
insert into t3 values ('2006-01-25');
8004
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
8005
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
8006
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
8007
select to_days(col1)-to_days('2006-01-01') from t1 order by col1;
8008
to_days(col1)-to_days('2006-01-01')
8011
select * from t1 order by col1;
8015
select * from t2 order by col1;
8020
select * from t3 order by col1;
8025
select * from t4 order by colint;
8031
select * from t5 order by colint;
8037
select * from t6 order by colint;
8043
update t1 set col1='2006-02-06' where col1='2006-02-03';
8044
update t2 set col1='2006-02-06' where col1='2006-02-03';
8045
update t3 set col1='2006-02-06' where col1='2006-02-03';
8046
update t4 set col1='2006-02-06' where col1='2006-02-03';
8047
update t5 set col1='2006-02-06' where col1='2006-02-03';
8048
update t6 set col1='2006-02-06' where col1='2006-02-03';
8049
select * from t1 order by col1;
8053
select * from t2 order by col1;
8058
select * from t3 order by col1;
8063
select * from t4 order by colint;
8069
select * from t5 order by colint;
8075
select * from t6 order by colint;
8081
-------------------------------------------------------------------------
8082
--- Alter tables with to_days(col1)-to_days('2006-01-01')
8083
-------------------------------------------------------------------------
8084
drop table if exists t11 ;
8085
drop table if exists t22 ;
8086
drop table if exists t33 ;
8087
drop table if exists t44 ;
8088
drop table if exists t55 ;
8089
drop table if exists t66 ;
8090
create table t11 engine='NDB' as select * from t1;
8091
create table t22 engine='NDB' as select * from t2;
8092
create table t33 engine='NDB' as select * from t3;
8093
create table t44 engine='NDB' as select * from t4;
8094
create table t55 engine='NDB' as select * from t5;
8095
create table t66 engine='NDB' as select * from t6;
8097
partition by range(to_days(col1)-to_days('2006-01-01'))
8098
(partition p0 values less than (15),
8099
partition p1 values less than maxvalue);
8101
partition by list(to_days(col1)-to_days('2006-01-01'))
8102
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8103
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8104
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8105
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8106
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8107
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8110
partition by hash(to_days(col1)-to_days('2006-01-01'));
8112
partition by range(colint)
8113
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
8114
(partition p0 values less than (15),
8115
partition p1 values less than maxvalue);
8117
partition by list(colint)
8118
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
8119
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8120
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8121
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8122
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8125
partition by range(colint)
8126
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
8127
partition p1 values less than maxvalue);
8128
select * from t11 order by col1;
8132
select * from t22 order by col1;
8137
select * from t33 order by col1;
8142
select * from t44 order by colint;
8148
select * from t55 order by colint;
8154
select * from t66 order by colint;
8160
---------------------------
8161
---- some alter table begin
8162
---------------------------
8164
partition by list(colint)
8165
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 4
8166
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8167
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8169
show create table t55;
8171
t55 CREATE TABLE `t55` (
8172
`colint` int(11) DEFAULT NULL,
8173
`col1` date DEFAULT NULL
8174
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
8175
/*!50100 PARTITION BY LIST (colint)
8176
SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01'))
8178
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
8179
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
8180
select * from t55 order by colint;
8186
-------------------------------------------------------------------------
8187
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
8188
-------------------------------------------------------------------------
8189
delete from t1 where col1='2006-01-17';
8190
delete from t2 where col1='2006-01-17';
8191
delete from t3 where col1='2006-01-17';
8192
delete from t4 where col1='2006-01-17';
8193
delete from t5 where col1='2006-01-17';
8194
delete from t6 where col1='2006-01-17';
8195
select * from t1 order by col1;
8198
select * from t2 order by col1;
8202
select * from t3 order by col1;
8206
select * from t4 order by colint;
8211
select * from t5 order by colint;
8216
insert into t1 values ('2006-01-17');
8217
insert into t2 values ('2006-01-17');
8218
insert into t3 values ('2006-01-17');
8219
insert into t4 values (60,'2006-01-17');
8220
insert into t5 values (60,'2006-01-17');
8221
insert into t6 values (60,'2006-01-17');
8222
select * from t1 order by col1;
8226
select * from t2 order by col1;
8231
select * from t3 order by col1;
8236
select * from t4 order by colint;
8242
select * from t5 order by colint;
8248
select * from t6 order by colint;
8254
-------------------------------------------------------------------------
8255
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
8256
-------------------------------------------------------------------------
8257
delete from t11 where col1='2006-01-17';
8258
delete from t22 where col1='2006-01-17';
8259
delete from t33 where col1='2006-01-17';
8260
delete from t44 where col1='2006-01-17';
8261
delete from t55 where col1='2006-01-17';
8262
delete from t66 where col1='2006-01-17';
8263
select * from t11 order by col1;
8266
select * from t22 order by col1;
8270
select * from t33 order by col1;
8274
select * from t44 order by colint;
8279
select * from t55 order by colint;
8284
insert into t11 values ('2006-01-17');
8285
insert into t22 values ('2006-01-17');
8286
insert into t33 values ('2006-01-17');
8287
insert into t44 values (60,'2006-01-17');
8288
insert into t55 values (60,'2006-01-17');
8289
insert into t66 values (60,'2006-01-17');
8290
select * from t11 order by col1;
8294
select * from t22 order by col1;
8299
select * from t33 order by col1;
8304
select * from t44 order by colint;
8310
select * from t55 order by colint;
8316
select * from t66 order by colint;
8322
-------------------------
8323
---- some alter table end
8324
-------------------------
8325
drop table if exists t1 ;
8326
drop table if exists t2 ;
8327
drop table if exists t3 ;
8328
drop table if exists t4 ;
8329
drop table if exists t5 ;
8330
drop table if exists t6 ;
8331
drop table if exists t11 ;
8332
drop table if exists t22 ;
8333
drop table if exists t33 ;
8334
drop table if exists t44 ;
8335
drop table if exists t55 ;
8336
drop table if exists t66 ;
8337
-------------------------------------------------------------------------
8338
--- datediff(col1, '2006-01-01') in partition with coltype date
8339
-------------------------------------------------------------------------
8340
drop table if exists t1 ;
8341
drop table if exists t2 ;
8342
drop table if exists t3 ;
8343
drop table if exists t4 ;
8344
drop table if exists t5 ;
8345
drop table if exists t6 ;
8346
-------------------------------------------------------------------------
8347
--- Create tables with datediff(col1, '2006-01-01')
8348
-------------------------------------------------------------------------
8349
create table t1 (col1 date) engine='NDB'
8350
partition by range(datediff(col1, '2006-01-01'))
8351
(partition p0 values less than (15),
8352
partition p1 values less than maxvalue);
8353
create table t2 (col1 date) engine='NDB'
8354
partition by list(datediff(col1, '2006-01-01'))
8355
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8356
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8357
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8358
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8359
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8360
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8362
create table t3 (col1 date) engine='NDB'
8363
partition by hash(datediff(col1, '2006-01-01'));
8364
create table t4 (colint int, col1 date) engine='NDB'
8365
partition by range(colint)
8366
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8367
(partition p0 values less than (15),
8368
partition p1 values less than maxvalue);
8369
create table t5 (colint int, col1 date) engine='NDB'
8370
partition by list(colint)
8371
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8372
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8373
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8374
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8375
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8377
create table t6 (colint int, col1 date) engine='NDB'
8378
partition by range(colint)
8379
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
8380
partition p1 values less than maxvalue);
8381
-------------------------------------------------------------------------
8382
--- Access tables with datediff(col1, '2006-01-01')
8383
-------------------------------------------------------------------------
8384
insert into t1 values ('2006-02-03');
8385
insert into t1 values ('2006-01-17');
8386
insert into t2 values ('2006-02-03');
8387
insert into t2 values ('2006-01-17');
8388
insert into t2 values ('2006-01-25');
8389
insert into t3 values ('2006-02-03');
8390
insert into t3 values ('2006-01-17');
8391
insert into t3 values ('2006-01-25');
8392
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
8393
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
8394
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
8395
select datediff(col1, '2006-01-01') from t1 order by col1;
8396
datediff(col1, '2006-01-01')
8399
select * from t1 order by col1;
8403
select * from t2 order by col1;
8408
select * from t3 order by col1;
8413
select * from t4 order by colint;
8419
select * from t5 order by colint;
8425
select * from t6 order by colint;
8431
update t1 set col1='2006-02-06' where col1='2006-02-03';
8432
update t2 set col1='2006-02-06' where col1='2006-02-03';
8433
update t3 set col1='2006-02-06' where col1='2006-02-03';
8434
update t4 set col1='2006-02-06' where col1='2006-02-03';
8435
update t5 set col1='2006-02-06' where col1='2006-02-03';
8436
update t6 set col1='2006-02-06' where col1='2006-02-03';
8437
select * from t1 order by col1;
8441
select * from t2 order by col1;
8446
select * from t3 order by col1;
8451
select * from t4 order by colint;
8457
select * from t5 order by colint;
8463
select * from t6 order by colint;
8469
-------------------------------------------------------------------------
8470
--- Alter tables with datediff(col1, '2006-01-01')
8471
-------------------------------------------------------------------------
8472
drop table if exists t11 ;
8473
drop table if exists t22 ;
8474
drop table if exists t33 ;
8475
drop table if exists t44 ;
8476
drop table if exists t55 ;
8477
drop table if exists t66 ;
8478
create table t11 engine='NDB' as select * from t1;
8479
create table t22 engine='NDB' as select * from t2;
8480
create table t33 engine='NDB' as select * from t3;
8481
create table t44 engine='NDB' as select * from t4;
8482
create table t55 engine='NDB' as select * from t5;
8483
create table t66 engine='NDB' as select * from t6;
8485
partition by range(datediff(col1, '2006-01-01'))
8486
(partition p0 values less than (15),
8487
partition p1 values less than maxvalue);
8489
partition by list(datediff(col1, '2006-01-01'))
8490
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8491
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8492
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8493
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8494
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8495
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8498
partition by hash(datediff(col1, '2006-01-01'));
8500
partition by range(colint)
8501
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8502
(partition p0 values less than (15),
8503
partition p1 values less than maxvalue);
8505
partition by list(colint)
8506
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8507
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8508
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8509
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8510
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8513
partition by range(colint)
8514
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
8515
partition p1 values less than maxvalue);
8516
select * from t11 order by col1;
8520
select * from t22 order by col1;
8525
select * from t33 order by col1;
8530
select * from t44 order by colint;
8536
select * from t55 order by colint;
8542
select * from t66 order by colint;
8548
---------------------------
8549
---- some alter table begin
8550
---------------------------
8552
partition by list(colint)
8553
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 4
8554
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8555
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8557
show create table t55;
8559
t55 CREATE TABLE `t55` (
8560
`colint` int(11) DEFAULT NULL,
8561
`col1` date DEFAULT NULL
8562
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
8563
/*!50100 PARTITION BY LIST (colint)
8564
SUBPARTITION BY HASH (datediff(col1, '2006-01-01'))
8566
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
8567
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
8568
select * from t55 order by colint;
8574
-------------------------------------------------------------------------
8575
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
8576
-------------------------------------------------------------------------
8577
delete from t1 where col1='2006-01-17';
8578
delete from t2 where col1='2006-01-17';
8579
delete from t3 where col1='2006-01-17';
8580
delete from t4 where col1='2006-01-17';
8581
delete from t5 where col1='2006-01-17';
8582
delete from t6 where col1='2006-01-17';
8583
select * from t1 order by col1;
8586
select * from t2 order by col1;
8590
select * from t3 order by col1;
8594
select * from t4 order by colint;
8599
select * from t5 order by colint;
8604
insert into t1 values ('2006-01-17');
8605
insert into t2 values ('2006-01-17');
8606
insert into t3 values ('2006-01-17');
8607
insert into t4 values (60,'2006-01-17');
8608
insert into t5 values (60,'2006-01-17');
8609
insert into t6 values (60,'2006-01-17');
8610
select * from t1 order by col1;
8614
select * from t2 order by col1;
8619
select * from t3 order by col1;
8624
select * from t4 order by colint;
8630
select * from t5 order by colint;
8636
select * from t6 order by colint;
8642
-------------------------------------------------------------------------
8643
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
8644
-------------------------------------------------------------------------
8645
delete from t11 where col1='2006-01-17';
8646
delete from t22 where col1='2006-01-17';
8647
delete from t33 where col1='2006-01-17';
8648
delete from t44 where col1='2006-01-17';
8649
delete from t55 where col1='2006-01-17';
8650
delete from t66 where col1='2006-01-17';
8651
select * from t11 order by col1;
8654
select * from t22 order by col1;
8658
select * from t33 order by col1;
8662
select * from t44 order by colint;
8667
select * from t55 order by colint;
8672
insert into t11 values ('2006-01-17');
8673
insert into t22 values ('2006-01-17');
8674
insert into t33 values ('2006-01-17');
8675
insert into t44 values (60,'2006-01-17');
8676
insert into t55 values (60,'2006-01-17');
8677
insert into t66 values (60,'2006-01-17');
8678
select * from t11 order by col1;
8682
select * from t22 order by col1;
8687
select * from t33 order by col1;
8692
select * from t44 order by colint;
8698
select * from t55 order by colint;
8704
select * from t66 order by colint;
8710
-------------------------
8711
---- some alter table end
8712
-------------------------
8713
drop table if exists t1 ;
8714
drop table if exists t2 ;
8715
drop table if exists t3 ;
8716
drop table if exists t4 ;
8717
drop table if exists t5 ;
8718
drop table if exists t6 ;
8719
drop table if exists t11 ;
8720
drop table if exists t22 ;
8721
drop table if exists t33 ;
8722
drop table if exists t44 ;
8723
drop table if exists t55 ;
8724
drop table if exists t66 ;
8725
-------------------------------------------------------------------------
8726
--- weekday(col1) in partition with coltype date
8727
-------------------------------------------------------------------------
8728
drop table if exists t1 ;
8729
drop table if exists t2 ;
8730
drop table if exists t3 ;
8731
drop table if exists t4 ;
8732
drop table if exists t5 ;
8733
drop table if exists t6 ;
8734
-------------------------------------------------------------------------
8735
--- Create tables with weekday(col1)
8736
-------------------------------------------------------------------------
8737
create table t1 (col1 date) engine='NDB'
8738
partition by range(weekday(col1))
8739
(partition p0 values less than (15),
8740
partition p1 values less than maxvalue);
8741
create table t2 (col1 date) engine='NDB'
8742
partition by list(weekday(col1))
8743
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8744
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8745
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8746
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8747
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8748
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8750
create table t3 (col1 date) engine='NDB'
8751
partition by hash(weekday(col1));
8752
create table t4 (colint int, col1 date) engine='NDB'
8753
partition by range(colint)
8754
subpartition by hash(weekday(col1)) subpartitions 2
8755
(partition p0 values less than (15),
8756
partition p1 values less than maxvalue);
8757
create table t5 (colint int, col1 date) engine='NDB'
8758
partition by list(colint)
8759
subpartition by hash(weekday(col1)) subpartitions 2
8760
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8761
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8762
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8763
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8765
create table t6 (colint int, col1 date) engine='NDB'
8766
partition by range(colint)
8767
(partition p0 values less than (weekday('2006-10-14')),
8768
partition p1 values less than maxvalue);
8769
-------------------------------------------------------------------------
8770
--- Access tables with weekday(col1)
8771
-------------------------------------------------------------------------
8772
insert into t1 values ('2006-12-03');
8773
insert into t1 values ('2006-11-17');
8774
insert into t2 values ('2006-12-03');
8775
insert into t2 values ('2006-11-17');
8776
insert into t2 values ('2006-05-25');
8777
insert into t3 values ('2006-12-03');
8778
insert into t3 values ('2006-11-17');
8779
insert into t3 values ('2006-05-25');
8780
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
8781
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
8782
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
8783
select weekday(col1) from t1 order by col1;
8787
select * from t1 order by col1;
8791
select * from t2 order by col1;
8796
select * from t3 order by col1;
8801
select * from t4 order by colint;
8807
select * from t5 order by colint;
8813
select * from t6 order by colint;
8819
update t1 set col1='2006-02-06' where col1='2006-12-03';
8820
update t2 set col1='2006-02-06' where col1='2006-12-03';
8821
update t3 set col1='2006-02-06' where col1='2006-12-03';
8822
update t4 set col1='2006-02-06' where col1='2006-12-03';
8823
update t5 set col1='2006-02-06' where col1='2006-12-03';
8824
update t6 set col1='2006-02-06' where col1='2006-12-03';
8825
select * from t1 order by col1;
8829
select * from t2 order by col1;
8834
select * from t3 order by col1;
8839
select * from t4 order by colint;
8845
select * from t5 order by colint;
8851
select * from t6 order by colint;
8857
-------------------------------------------------------------------------
8858
--- Alter tables with weekday(col1)
8859
-------------------------------------------------------------------------
8860
drop table if exists t11 ;
8861
drop table if exists t22 ;
8862
drop table if exists t33 ;
8863
drop table if exists t44 ;
8864
drop table if exists t55 ;
8865
drop table if exists t66 ;
8866
create table t11 engine='NDB' as select * from t1;
8867
create table t22 engine='NDB' as select * from t2;
8868
create table t33 engine='NDB' as select * from t3;
8869
create table t44 engine='NDB' as select * from t4;
8870
create table t55 engine='NDB' as select * from t5;
8871
create table t66 engine='NDB' as select * from t6;
8873
partition by range(weekday(col1))
8874
(partition p0 values less than (15),
8875
partition p1 values less than maxvalue);
8877
partition by list(weekday(col1))
8878
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8879
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8880
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8881
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8882
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8883
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8886
partition by hash(weekday(col1));
8888
partition by range(colint)
8889
subpartition by hash(weekday(col1)) subpartitions 2
8890
(partition p0 values less than (15),
8891
partition p1 values less than maxvalue);
8893
partition by list(colint)
8894
subpartition by hash(weekday(col1)) subpartitions 2
8895
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8896
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8897
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8898
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8901
partition by range(colint)
8902
(partition p0 values less than (weekday('2006-10-14')),
8903
partition p1 values less than maxvalue);
8904
select * from t11 order by col1;
8908
select * from t22 order by col1;
8913
select * from t33 order by col1;
8918
select * from t44 order by colint;
8924
select * from t55 order by colint;
8930
select * from t66 order by colint;
8936
---------------------------
8937
---- some alter table begin
8938
---------------------------
8940
partition by list(colint)
8941
subpartition by hash(weekday(col1)) subpartitions 4
8942
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8943
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8945
show create table t55;
8947
t55 CREATE TABLE `t55` (
8948
`colint` int(11) DEFAULT NULL,
8949
`col1` date DEFAULT NULL
8950
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
8951
/*!50100 PARTITION BY LIST (colint)
8952
SUBPARTITION BY HASH (weekday(col1))
8954
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
8955
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
8956
select * from t55 order by colint;
8962
-------------------------------------------------------------------------
8963
--- Delete rows and partitions of tables with weekday(col1)
8964
-------------------------------------------------------------------------
8965
delete from t1 where col1='2006-11-17';
8966
delete from t2 where col1='2006-11-17';
8967
delete from t3 where col1='2006-11-17';
8968
delete from t4 where col1='2006-11-17';
8969
delete from t5 where col1='2006-11-17';
8970
delete from t6 where col1='2006-11-17';
8971
select * from t1 order by col1;
8974
select * from t2 order by col1;
8978
select * from t3 order by col1;
8982
select * from t4 order by colint;
8988
select * from t5 order by colint;
8994
insert into t1 values ('2006-11-17');
8995
insert into t2 values ('2006-11-17');
8996
insert into t3 values ('2006-11-17');
8997
insert into t4 values (60,'2006-11-17');
8998
insert into t5 values (60,'2006-11-17');
8999
insert into t6 values (60,'2006-11-17');
9000
select * from t1 order by col1;
9004
select * from t2 order by col1;
9009
select * from t3 order by col1;
9014
select * from t4 order by colint;
9021
select * from t5 order by colint;
9028
select * from t6 order by colint;
9035
-------------------------------------------------------------------------
9036
--- Delete rows and partitions of tables with weekday(col1)
9037
-------------------------------------------------------------------------
9038
delete from t11 where col1='2006-11-17';
9039
delete from t22 where col1='2006-11-17';
9040
delete from t33 where col1='2006-11-17';
9041
delete from t44 where col1='2006-11-17';
9042
delete from t55 where col1='2006-11-17';
9043
delete from t66 where col1='2006-11-17';
9044
select * from t11 order by col1;
9047
select * from t22 order by col1;
9051
select * from t33 order by col1;
9055
select * from t44 order by colint;
9061
select * from t55 order by colint;
9067
insert into t11 values ('2006-11-17');
9068
insert into t22 values ('2006-11-17');
9069
insert into t33 values ('2006-11-17');
9070
insert into t44 values (60,'2006-11-17');
9071
insert into t55 values (60,'2006-11-17');
9072
insert into t66 values (60,'2006-11-17');
9073
select * from t11 order by col1;
9077
select * from t22 order by col1;
9082
select * from t33 order by col1;
9087
select * from t44 order by colint;
9094
select * from t55 order by colint;
9101
select * from t66 order by colint;
9108
-------------------------
9109
---- some alter table end
9110
-------------------------
9111
drop table if exists t1 ;
9112
drop table if exists t2 ;
9113
drop table if exists t3 ;
9114
drop table if exists t4 ;
9115
drop table if exists t5 ;
9116
drop table if exists t6 ;
9117
drop table if exists t11 ;
9118
drop table if exists t22 ;
9119
drop table if exists t33 ;
9120
drop table if exists t44 ;
9121
drop table if exists t55 ;
9122
drop table if exists t66 ;
9123
-------------------------------------------------------------------------
9124
--- year(col1)-1990 in partition with coltype date
9125
-------------------------------------------------------------------------
9126
drop table if exists t1 ;
9127
drop table if exists t2 ;
9128
drop table if exists t3 ;
9129
drop table if exists t4 ;
9130
drop table if exists t5 ;
9131
drop table if exists t6 ;
9132
-------------------------------------------------------------------------
9133
--- Create tables with year(col1)-1990
9134
-------------------------------------------------------------------------
9135
create table t1 (col1 date) engine='NDB'
9136
partition by range(year(col1)-1990)
9137
(partition p0 values less than (15),
9138
partition p1 values less than maxvalue);
9139
create table t2 (col1 date) engine='NDB'
9140
partition by list(year(col1)-1990)
9141
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9142
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9143
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9144
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9145
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9146
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9148
create table t3 (col1 date) engine='NDB'
9149
partition by hash(year(col1)-1990);
9150
create table t4 (colint int, col1 date) engine='NDB'
9151
partition by range(colint)
9152
subpartition by hash(year(col1)-1990) subpartitions 2
9153
(partition p0 values less than (15),
9154
partition p1 values less than maxvalue);
9155
create table t5 (colint int, col1 date) engine='NDB'
9156
partition by list(colint)
9157
subpartition by hash(year(col1)-1990) subpartitions 2
9158
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
9159
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
9160
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
9161
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
9163
create table t6 (colint int, col1 date) engine='NDB'
9164
partition by range(colint)
9165
(partition p0 values less than (year('2005-10-14')-1990),
9166
partition p1 values less than maxvalue);
9167
-------------------------------------------------------------------------
9168
--- Access tables with year(col1)-1990
9169
-------------------------------------------------------------------------
9170
insert into t1 values ('1996-01-03');
9171
insert into t1 values ('2000-02-17');
9172
insert into t2 values ('1996-01-03');
9173
insert into t2 values ('2000-02-17');
9174
insert into t2 values ('2004-05-25');
9175
insert into t3 values ('1996-01-03');
9176
insert into t3 values ('2000-02-17');
9177
insert into t3 values ('2004-05-25');
9178
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
9179
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
9180
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
9181
select year(col1)-1990 from t1 order by col1;
9185
select * from t1 order by col1;
9189
select * from t2 order by col1;
9194
select * from t3 order by col1;
9199
select * from t4 order by colint;
9205
select * from t5 order by colint;
9211
select * from t6 order by colint;
9217
update t1 set col1='2002-02-15' where col1='1996-01-03';
9218
update t2 set col1='2002-02-15' where col1='1996-01-03';
9219
update t3 set col1='2002-02-15' where col1='1996-01-03';
9220
update t4 set col1='2002-02-15' where col1='1996-01-03';
9221
update t5 set col1='2002-02-15' where col1='1996-01-03';
9222
update t6 set col1='2002-02-15' where col1='1996-01-03';
9223
select * from t1 order by col1;
9227
select * from t2 order by col1;
9232
select * from t3 order by col1;
9237
select * from t4 order by colint;
9243
select * from t5 order by colint;
9249
select * from t6 order by colint;
9255
-------------------------------------------------------------------------
9256
--- Alter tables with year(col1)-1990
9257
-------------------------------------------------------------------------
9258
drop table if exists t11 ;
9259
drop table if exists t22 ;
9260
drop table if exists t33 ;
9261
drop table if exists t44 ;
9262
drop table if exists t55 ;
9263
drop table if exists t66 ;
9264
create table t11 engine='NDB' as select * from t1;
9265
create table t22 engine='NDB' as select * from t2;
9266
create table t33 engine='NDB' as select * from t3;
9267
create table t44 engine='NDB' as select * from t4;
9268
create table t55 engine='NDB' as select * from t5;
9269
create table t66 engine='NDB' as select * from t6;
9271
partition by range(year(col1)-1990)
9272
(partition p0 values less than (15),
9273
partition p1 values less than maxvalue);
9275
partition by list(year(col1)-1990)
9276
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9277
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9278
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9279
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9280
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9281
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9284
partition by hash(year(col1)-1990);
9286
partition by range(colint)
9287
subpartition by hash(year(col1)-1990) subpartitions 2
9288
(partition p0 values less than (15),
9289
partition p1 values less than maxvalue);
9291
partition by list(colint)
9292
subpartition by hash(year(col1)-1990) subpartitions 2
9293
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
9294
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
9295
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
9296
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
9299
partition by range(colint)
9300
(partition p0 values less than (year('2005-10-14')-1990),
9301
partition p1 values less than maxvalue);
9302
select * from t11 order by col1;
9306
select * from t22 order by col1;
9311
select * from t33 order by col1;
9316
select * from t44 order by colint;
9322
select * from t55 order by colint;
9328
select * from t66 order by colint;
9334
---------------------------
9335
---- some alter table begin
9336
---------------------------
9338
partition by list(colint)
9339
subpartition by hash(year(col1)-1990) subpartitions 4
9340
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
9341
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
9343
show create table t55;
9345
t55 CREATE TABLE `t55` (
9346
`colint` int(11) DEFAULT NULL,
9347
`col1` date DEFAULT NULL
9348
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
9349
/*!50100 PARTITION BY LIST (colint)
9350
SUBPARTITION BY HASH (year(col1)-1990)
9352
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
9353
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
9354
select * from t55 order by colint;
9360
-------------------------------------------------------------------------
9361
--- Delete rows and partitions of tables with year(col1)-1990
9362
-------------------------------------------------------------------------
9363
delete from t1 where col1='2000-02-17';
9364
delete from t2 where col1='2000-02-17';
9365
delete from t3 where col1='2000-02-17';
9366
delete from t4 where col1='2000-02-17';
9367
delete from t5 where col1='2000-02-17';
9368
delete from t6 where col1='2000-02-17';
9369
select * from t1 order by col1;
9372
select * from t2 order by col1;
9376
select * from t3 order by col1;
9380
select * from t4 order by colint;
9386
select * from t5 order by colint;
9392
insert into t1 values ('2000-02-17');
9393
insert into t2 values ('2000-02-17');
9394
insert into t3 values ('2000-02-17');
9395
insert into t4 values (60,'2000-02-17');
9396
insert into t5 values (60,'2000-02-17');
9397
insert into t6 values (60,'2000-02-17');
9398
select * from t1 order by col1;
9402
select * from t2 order by col1;
9407
select * from t3 order by col1;
9412
select * from t4 order by colint;
9419
select * from t5 order by colint;
9426
select * from t6 order by colint;
9433
-------------------------------------------------------------------------
9434
--- Delete rows and partitions of tables with year(col1)-1990
9435
-------------------------------------------------------------------------
9436
delete from t11 where col1='2000-02-17';
9437
delete from t22 where col1='2000-02-17';
9438
delete from t33 where col1='2000-02-17';
9439
delete from t44 where col1='2000-02-17';
9440
delete from t55 where col1='2000-02-17';
9441
delete from t66 where col1='2000-02-17';
9442
select * from t11 order by col1;
9445
select * from t22 order by col1;
9449
select * from t33 order by col1;
9453
select * from t44 order by colint;
9459
select * from t55 order by colint;
9465
insert into t11 values ('2000-02-17');
9466
insert into t22 values ('2000-02-17');
9467
insert into t33 values ('2000-02-17');
9468
insert into t44 values (60,'2000-02-17');
9469
insert into t55 values (60,'2000-02-17');
9470
insert into t66 values (60,'2000-02-17');
9471
select * from t11 order by col1;
9475
select * from t22 order by col1;
9480
select * from t33 order by col1;
9485
select * from t44 order by colint;
9492
select * from t55 order by colint;
9499
select * from t66 order by colint;
9506
-------------------------
9507
---- some alter table end
9508
-------------------------
9509
drop table if exists t1 ;
9510
drop table if exists t2 ;
9511
drop table if exists t3 ;
9512
drop table if exists t4 ;
9513
drop table if exists t5 ;
9514
drop table if exists t6 ;
9515
drop table if exists t11 ;
9516
drop table if exists t22 ;
9517
drop table if exists t33 ;
9518
drop table if exists t44 ;
9519
drop table if exists t55 ;
9520
drop table if exists t66 ;
9521
-------------------------------------------------------------------------
9522
--- yearweek(col1)-200600 in partition with coltype date
9523
-------------------------------------------------------------------------
9524
drop table if exists t1 ;
9525
drop table if exists t2 ;
9526
drop table if exists t3 ;
9527
drop table if exists t4 ;
9528
drop table if exists t5 ;
9529
drop table if exists t6 ;
9530
-------------------------------------------------------------------------
9531
--- Create tables with yearweek(col1)-200600
9532
-------------------------------------------------------------------------
9533
create table t1 (col1 date) engine='NDB'
9534
partition by range(yearweek(col1)-200600)
9535
(partition p0 values less than (15),
9536
partition p1 values less than maxvalue);
9537
create table t2 (col1 date) engine='NDB'
9538
partition by list(yearweek(col1)-200600)
9539
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9540
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9541
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9542
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9543
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9544
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9546
create table t3 (col1 date) engine='NDB'
9547
partition by hash(yearweek(col1)-200600);
9548
create table t4 (colint int, col1 date) engine='NDB'
9549
partition by range(colint)
9550
subpartition by hash(yearweek(col1)-200600) subpartitions 2
9551
(partition p0 values less than (15),
9552
partition p1 values less than maxvalue);
9553
create table t5 (colint int, col1 date) engine='NDB'
9554
partition by list(colint)
9555
subpartition by hash(yearweek(col1)-200600) subpartitions 2
9556
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
9557
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
9558
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
9559
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
9561
create table t6 (colint int, col1 date) engine='NDB'
9562
partition by range(colint)
9563
(partition p0 values less than (yearweek('2006-10-14')-200600),
9564
partition p1 values less than maxvalue);
9565
-------------------------------------------------------------------------
9566
--- Access tables with yearweek(col1)-200600
9567
-------------------------------------------------------------------------
9568
insert into t1 values ('2006-01-03');
9569
insert into t1 values ('2006-08-17');
9570
insert into t2 values ('2006-01-03');
9571
insert into t2 values ('2006-08-17');
9572
insert into t2 values ('2006-03-25');
9573
insert into t3 values ('2006-01-03');
9574
insert into t3 values ('2006-08-17');
9575
insert into t3 values ('2006-03-25');
9576
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t4;
9577
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t5;
9578
load data infile 'MYSQLTEST_VARDIR/std_data/parts/part_supported_sql_funcs_int_date.inc' into table t6;
9579
select yearweek(col1)-200600 from t1 order by col1;
9580
yearweek(col1)-200600
9583
select * from t1 order by col1;
9587
select * from t2 order by col1;
9592
select * from t3 order by col1;
9597
select * from t4 order by colint;
9603
select * from t5 order by colint;
9609
select * from t6 order by colint;
9615
update t1 set col1='2006-11-15' where col1='2006-01-03';
9616
update t2 set col1='2006-11-15' where col1='2006-01-03';
9617
update t3 set col1='2006-11-15' where col1='2006-01-03';
9618
update t4 set col1='2006-11-15' where col1='2006-01-03';
9619
update t5 set col1='2006-11-15' where col1='2006-01-03';
9620
update t6 set col1='2006-11-15' where col1='2006-01-03';
9621
select * from t1 order by col1;
9625
select * from t2 order by col1;
9630
select * from t3 order by col1;
9635
select * from t4 order by colint;
9641
select * from t5 order by colint;
9647
select * from t6 order by colint;
9653
-------------------------------------------------------------------------
9654
--- Alter tables with yearweek(col1)-200600
9655
-------------------------------------------------------------------------
9656
drop table if exists t11 ;
9657
drop table if exists t22 ;
9658
drop table if exists t33 ;
9659
drop table if exists t44 ;
9660
drop table if exists t55 ;
9661
drop table if exists t66 ;
9662
create table t11 engine='NDB' as select * from t1;
9663
create table t22 engine='NDB' as select * from t2;
9664
create table t33 engine='NDB' as select * from t3;
9665
create table t44 engine='NDB' as select * from t4;
9666
create table t55 engine='NDB' as select * from t5;
9667
create table t66 engine='NDB' as select * from t6;
9669
partition by range(yearweek(col1)-200600)
9670
(partition p0 values less than (15),
9671
partition p1 values less than maxvalue);
9673
partition by list(yearweek(col1)-200600)
9674
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
9675
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
9676
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
9677
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
9678
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
9679
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
9682
partition by hash(yearweek(col1)-200600);
9684
partition by range(colint)
9685
subpartition by hash(yearweek(col1)-200600) subpartitions 2
9686
(partition p0 values less than (15),
9687
partition p1 values less than maxvalue);
9689
partition by list(colint)
9690
subpartition by hash(yearweek(col1)-200600) subpartitions 2
9691
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
9692
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
9693
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
9694
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
9697
partition by range(colint)
9698
(partition p0 values less than (yearweek('2006-10-14')-200600),
9699
partition p1 values less than maxvalue);
9700
select * from t11 order by col1;
9704
select * from t22 order by col1;
9709
select * from t33 order by col1;
9714
select * from t44 order by colint;
9720
select * from t55 order by colint;
9726
select * from t66 order by colint;
9732
---------------------------
9733
---- some alter table begin
9734
---------------------------
9736
partition by list(colint)
9737
subpartition by hash(yearweek(col1)-200600) subpartitions 4
9738
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
9739
partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
9741
show create table t55;
9743
t55 CREATE TABLE `t55` (
9744
`colint` int(11) DEFAULT NULL,
9745
`col1` date DEFAULT NULL
9746
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
9747
/*!50100 PARTITION BY LIST (colint)
9748
SUBPARTITION BY HASH (yearweek(col1)-200600)
9750
(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = ndbcluster,
9751
PARTITION p1 VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = ndbcluster) */
9752
select * from t55 order by colint;
9758
-------------------------------------------------------------------------
9759
--- Delete rows and partitions of tables with yearweek(col1)-200600
9760
-------------------------------------------------------------------------
9761
delete from t1 where col1='2006-08-17';
9762
delete from t2 where col1='2006-08-17';
9763
delete from t3 where col1='2006-08-17';
9764
delete from t4 where col1='2006-08-17';
9765
delete from t5 where col1='2006-08-17';
9766
delete from t6 where col1='2006-08-17';
9767
select * from t1 order by col1;
9770
select * from t2 order by col1;
9774
select * from t3 order by col1;
9778
select * from t4 order by colint;
9784
select * from t5 order by colint;
9790
insert into t1 values ('2006-08-17');
9791
insert into t2 values ('2006-08-17');
9792
insert into t3 values ('2006-08-17');
9793
insert into t4 values (60,'2006-08-17');
9794
insert into t5 values (60,'2006-08-17');
9795
insert into t6 values (60,'2006-08-17');
9796
select * from t1 order by col1;
9800
select * from t2 order by col1;
9805
select * from t3 order by col1;
9810
select * from t4 order by colint;
9817
select * from t5 order by colint;
9824
select * from t6 order by colint;
9831
-------------------------------------------------------------------------
9832
--- Delete rows and partitions of tables with yearweek(col1)-200600
9833
-------------------------------------------------------------------------
9834
delete from t11 where col1='2006-08-17';
9835
delete from t22 where col1='2006-08-17';
9836
delete from t33 where col1='2006-08-17';
9837
delete from t44 where col1='2006-08-17';
9838
delete from t55 where col1='2006-08-17';
9839
delete from t66 where col1='2006-08-17';
9840
select * from t11 order by col1;
9843
select * from t22 order by col1;
9847
select * from t33 order by col1;
9851
select * from t44 order by colint;
9857
select * from t55 order by colint;
9863
insert into t11 values ('2006-08-17');
9864
insert into t22 values ('2006-08-17');
9865
insert into t33 values ('2006-08-17');
9866
insert into t44 values (60,'2006-08-17');
9867
insert into t55 values (60,'2006-08-17');
9868
insert into t66 values (60,'2006-08-17');
9869
select * from t11 order by col1;
9873
select * from t22 order by col1;
9878
select * from t33 order by col1;
9883
select * from t44 order by colint;
9890
select * from t55 order by colint;
9897
select * from t66 order by colint;
9904
-------------------------
9905
---- some alter table end
9906
-------------------------
9907
drop table if exists t1 ;
9908
drop table if exists t2 ;
9909
drop table if exists t3 ;
9910
drop table if exists t4 ;
9911
drop table if exists t5 ;
9912
drop table if exists t6 ;
9913
drop table if exists t11 ;
9914
drop table if exists t22 ;
9915
drop table if exists t33 ;
9916
drop table if exists t44 ;
9917
drop table if exists t55 ;
9918
drop table if exists t66 ;