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 ;