1538
alter table t11 drop partition p0;
1539
alter table t22 drop partition p0;
1540
alter table t44 drop partition p0;
1541
alter table t55 drop partition p0;
1542
alter table t66 drop partition p0;
1543
select * from t11 order by col1;
1546
select * from t22 order by col1;
1551
select * from t33 order by col1;
1556
select * from t44 order by colint;
1590
select * from t55 order by colint;
1628
select * from t66 order by colint;
1662
-------------------------
1663
---- some alter table end
1664
-------------------------
1665
drop table if exists t1 ;
1666
drop table if exists t2 ;
1667
drop table if exists t3 ;
1668
drop table if exists t4 ;
1669
drop table if exists t5 ;
1670
drop table if exists t6 ;
1671
drop table if exists t11 ;
1672
drop table if exists t22 ;
1673
drop table if exists t33 ;
1674
drop table if exists t44 ;
1675
drop table if exists t55 ;
1676
drop table if exists t66 ;
1677
-------------------------------------------------------------------------
1678
--- ascii(col1) in partition with coltype char(1)
1679
-------------------------------------------------------------------------
1680
drop table if exists t1 ;
1681
drop table if exists t2 ;
1682
drop table if exists t3 ;
1683
drop table if exists t4 ;
1684
drop table if exists t5 ;
1685
drop table if exists t6 ;
1686
-------------------------------------------------------------------------
1687
--- Create tables with ascii(col1)
1688
-------------------------------------------------------------------------
1689
create table t1 (col1 char(1)) engine='NDB'
1690
partition by range(ascii(col1))
1691
(partition p0 values less than (15),
1692
partition p1 values less than maxvalue);
1693
create table t2 (col1 char(1)) engine='NDB'
1694
partition by list(ascii(col1))
1695
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1696
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1697
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1698
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1699
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1700
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1702
create table t3 (col1 char(1)) engine='NDB'
1703
partition by hash(ascii(col1));
1704
create table t4 (colint int, col1 char(1)) engine='NDB'
1705
partition by range(colint)
1706
subpartition by hash(ascii(col1)) subpartitions 2
1707
(partition p0 values less than (15),
1708
partition p1 values less than maxvalue);
1709
create table t5 (colint int, col1 char(1)) engine='NDB'
1710
partition by list(colint)
1711
subpartition by hash(ascii(col1)) subpartitions 2
1712
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
1713
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1714
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1715
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1716
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1717
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1719
create table t6 (colint int, col1 char(1)) engine='NDB'
1720
partition by range(colint)
1721
(partition p0 values less than (ascii('5')),
1722
partition p1 values less than maxvalue);
1723
-------------------------------------------------------------------------
1724
--- Access tables with ascii(col1)
1725
-------------------------------------------------------------------------
1726
insert into t1 values ('1');
1727
insert into t1 values ('9');
1728
insert into t2 values ('1');
1729
insert into t2 values ('9');
1730
insert into t2 values ('3');
1731
insert into t3 values ('1');
1732
insert into t3 values ('9');
1733
insert into t3 values ('3');
1734
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_ch1.in' into table t4;
1735
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_ch1.in' into table t5;
1736
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_ch1.in' into table t6;
1737
select ascii(col1) from t1 order by col1;
1741
select * from t1 order by col1;
1745
select * from t2 order by col1;
1750
select * from t3 order by col1;
1755
select * from t4 order by colint;
1761
select * from t5 order by colint;
1767
select * from t6 order by colint;
1773
update t1 set col1='8' where col1='1';
1774
update t2 set col1='8' where col1='1';
1775
update t3 set col1='8' where col1='1';
1776
update t4 set col1='8' where col1='1';
1777
update t5 set col1='8' where col1='1';
1778
update t6 set col1='8' where col1='1';
1779
select * from t1 order by col1;
1783
select * from t2 order by col1;
1788
select * from t3 order by col1;
1793
select * from t4 order by colint;
1799
select * from t5 order by colint;
1805
select * from t6 order by colint;
1811
-------------------------------------------------------------------------
1812
--- Alter tables with ascii(col1)
1813
-------------------------------------------------------------------------
1814
drop table if exists t11 ;
1815
drop table if exists t22 ;
1816
drop table if exists t33 ;
1817
drop table if exists t44 ;
1818
drop table if exists t55 ;
1819
drop table if exists t66 ;
1820
create table t11 engine='NDB' as select * from t1;
1821
create table t22 engine='NDB' as select * from t2;
1822
create table t33 engine='NDB' as select * from t3;
1823
create table t44 engine='NDB' as select * from t4;
1824
create table t55 engine='NDB' as select * from t5;
1825
create table t66 engine='NDB' as select * from t6;
1827
partition by range(ascii(col1))
1828
(partition p0 values less than (15),
1829
partition p1 values less than maxvalue);
1831
partition by list(ascii(col1))
1832
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1833
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1834
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1835
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1836
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1837
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1840
partition by hash(ascii(col1));
1842
partition by range(colint)
1843
subpartition by hash(ascii(col1)) subpartitions 2
1844
(partition p0 values less than (15),
1845
partition p1 values less than maxvalue);
1847
partition by list(colint)
1848
subpartition by hash(ascii(col1)) subpartitions 2
1849
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
1850
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1851
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1852
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1853
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1854
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1857
partition by range(colint)
1858
(partition p0 values less than (ascii('5')),
1859
partition p1 values less than maxvalue);
1860
select * from t11 order by col1;
1864
select * from t22 order by col1;
1869
select * from t33 order by col1;
1874
select * from t44 order by colint;
1880
select * from t55 order by colint;
1886
select * from t66 order by colint;
1892
---------------------------
1893
---- some alter table begin
1894
---------------------------
1896
reorganize partition p0,p1 into
1897
(partition s1 values less than maxvalue);
1898
select * from t11 order by col1;
1903
reorganize partition s1 into
1904
(partition p0 values less than (15),
1905
partition p1 values less than maxvalue);
1906
select * from t11 order by col1;
1911
partition by list(colint)
1912
subpartition by hash(ascii(col1)) subpartitions 5
1913
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
1914
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1915
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1916
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1917
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1918
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1920
show create table t55;
1922
t55 CREATE TABLE `t55` (
1923
`colint` int(11) DEFAULT NULL,
1924
`col1` char(1) DEFAULT NULL
1925
) ENGINE=NDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (ascii(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = NDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = NDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = NDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = NDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = NDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = NDB) */
1926
select * from t55 order by colint;
1933
reorganize partition p0,p1 into
1934
(partition s1 values less than maxvalue);
1935
select * from t66 order by colint;
1942
reorganize partition s1 into
1943
(partition p0 values less than (ascii('5')),
1944
partition p1 values less than maxvalue);
1945
select * from t66 order by colint;
1952
reorganize partition p0,p1 into
1953
(partition s1 values less than maxvalue);
1954
select * from t66 order by colint;
1961
reorganize partition s1 into
1962
(partition p0 values less than (ascii('5')),
1963
partition p1 values less than maxvalue);
1964
select * from t66 order by colint;
1970
-------------------------------------------------------------------------
1971
--- Delete rows and partitions of tables with ascii(col1)
1972
-------------------------------------------------------------------------
1973
delete from t1 where col1='9';
1974
delete from t2 where col1='9';
1975
delete from t3 where col1='9';
1976
delete from t4 where col1='9';
1977
delete from t5 where col1='9';
1978
delete from t6 where col1='9';
1979
select * from t1 order by col1;
1982
select * from t2 order by col1;
1986
select * from t3 order by col1;
1990
select * from t4 order by colint;
1995
select * from t5 order by colint;
2000
insert into t1 values ('9');
2001
insert into t2 values ('9');
2002
insert into t3 values ('9');
2003
insert into t4 values (60,'9');
2004
insert into t5 values (60,'9');
2005
insert into t6 values (60,'9');
2006
select * from t1 order by col1;
2010
select * from t2 order by col1;
2015
select * from t3 order by col1;
2020
select * from t4 order by colint;
2026
select * from t5 order by colint;
2032
select * from t6 order by colint;
2038
alter table t1 drop partition p0;
2039
alter table t2 drop partition p0;
2040
alter table t4 drop partition p0;
2041
alter table t5 drop partition p0;
2042
alter table t6 drop partition p0;
2043
select * from t1 order by col1;
2047
select * from t2 order by col1;
2052
select * from t3 order by col1;
2057
select * from t4 order by colint;
2060
select * from t5 order by colint;
2063
select * from t6 order by colint;
2066
-------------------------------------------------------------------------
2067
--- Delete rows and partitions of tables with ascii(col1)
2068
-------------------------------------------------------------------------
2069
delete from t11 where col1='9';
2070
delete from t22 where col1='9';
2071
delete from t33 where col1='9';
2072
delete from t44 where col1='9';
2073
delete from t55 where col1='9';
2074
delete from t66 where col1='9';
2075
select * from t11 order by col1;
2078
select * from t22 order by col1;
2082
select * from t33 order by col1;
2086
select * from t44 order by colint;
2091
select * from t55 order by colint;
2096
insert into t11 values ('9');
2097
insert into t22 values ('9');
2098
insert into t33 values ('9');
2099
insert into t44 values (60,'9');
2100
insert into t55 values (60,'9');
2101
insert into t66 values (60,'9');
2102
select * from t11 order by col1;
2106
select * from t22 order by col1;
2111
select * from t33 order by col1;
2116
select * from t44 order by colint;
2122
select * from t55 order by colint;
2128
select * from t66 order by colint;
2134
alter table t11 drop partition p0;
2135
alter table t22 drop partition p0;
2136
alter table t44 drop partition p0;
2137
alter table t55 drop partition p0;
2138
alter table t66 drop partition p0;
2139
select * from t11 order by col1;
2143
select * from t22 order by col1;
2148
select * from t33 order by col1;
2153
select * from t44 order by colint;
2156
select * from t55 order by colint;
2159
select * from t66 order by colint;
2162
-------------------------
2163
---- some alter table end
2164
-------------------------
2165
drop table if exists t1 ;
2166
drop table if exists t2 ;
2167
drop table if exists t3 ;
2168
drop table if exists t4 ;
2169
drop table if exists t5 ;
2170
drop table if exists t6 ;
2171
drop table if exists t11 ;
2172
drop table if exists t22 ;
2173
drop table if exists t33 ;
2174
drop table if exists t44 ;
2175
drop table if exists t55 ;
2176
drop table if exists t66 ;
2177
-------------------------------------------------------------------------
2178
--- cast(ceiling(col1) as signed integer) in partition with coltype float(7,4)
2179
-------------------------------------------------------------------------
2180
drop table if exists t1 ;
2181
drop table if exists t2 ;
2182
drop table if exists t3 ;
2183
drop table if exists t4 ;
2184
drop table if exists t5 ;
2185
drop table if exists t6 ;
2186
-------------------------------------------------------------------------
2187
--- Create tables with cast(ceiling(col1) as signed integer)
2188
-------------------------------------------------------------------------
2189
create table t1 (col1 float(7,4)) engine='NDB'
2190
partition by range(cast(ceiling(col1) as signed integer))
2191
(partition p0 values less than (15),
2192
partition p1 values less than maxvalue);
2193
create table t2 (col1 float(7,4)) engine='NDB'
2194
partition by list(cast(ceiling(col1) as signed integer))
2195
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2196
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2197
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2198
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2199
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2200
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2202
create table t3 (col1 float(7,4)) engine='NDB'
2203
partition by hash(cast(ceiling(col1) as signed integer));
2204
create table t4 (colint int, col1 float(7,4)) engine='NDB'
2205
partition by range(colint)
2206
subpartition by hash(cast(ceiling(col1) as signed integer)) subpartitions 2
2207
(partition p0 values less than (15),
2208
partition p1 values less than maxvalue);
2209
create table t5 (colint int, col1 float(7,4)) engine='NDB'
2210
partition by list(colint)
2211
subpartition by hash(cast(ceiling(col1) as signed integer)) subpartitions 2
2212
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2213
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2214
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2215
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2216
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2217
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2219
create table t6 (colint int, col1 float(7,4)) engine='NDB'
2220
partition by range(colint)
2221
(partition p0 values less than (cast(ceiling(15) as signed integer)),
2222
partition p1 values less than maxvalue);
2223
-------------------------------------------------------------------------
2224
--- Access tables with cast(ceiling(col1) as signed integer)
2225
-------------------------------------------------------------------------
2226
insert into t1 values (5.1230);
2227
insert into t1 values (13.345);
2228
insert into t2 values (5.1230);
2229
insert into t2 values (13.345);
2230
insert into t2 values (17.987);
2231
insert into t3 values (5.1230);
2232
insert into t3 values (13.345);
2233
insert into t3 values (17.987);
2234
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t4;
2235
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t5;
2236
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t6;
2237
select cast(ceiling(col1) as signed integer) from t1 order by col1;
2238
cast(ceiling(col1) as signed integer)
2241
select * from t1 order by col1;
2245
select * from t2 order by col1;
2250
select * from t3 order by col1;
2255
select * from t4 order by colint;
2261
select * from t5 order by colint;
2267
select * from t6 order by colint;
2273
update t1 set col1=15.654 where col1=5.1230;
2274
update t2 set col1=15.654 where col1=5.1230;
2275
update t3 set col1=15.654 where col1=5.1230;
2276
update t4 set col1=15.654 where col1=5.1230;
2277
update t5 set col1=15.654 where col1=5.1230;
2278
update t6 set col1=15.654 where col1=5.1230;
2279
select * from t1 order by col1;
2283
select * from t2 order by col1;
2288
select * from t3 order by col1;
2293
select * from t4 order by colint;
2299
select * from t5 order by colint;
2305
select * from t6 order by colint;
2311
-------------------------------------------------------------------------
2312
--- Alter tables with cast(ceiling(col1) as signed integer)
2313
-------------------------------------------------------------------------
2314
drop table if exists t11 ;
2315
drop table if exists t22 ;
2316
drop table if exists t33 ;
2317
drop table if exists t44 ;
2318
drop table if exists t55 ;
2319
drop table if exists t66 ;
2320
create table t11 engine='NDB' as select * from t1;
2321
create table t22 engine='NDB' as select * from t2;
2322
create table t33 engine='NDB' as select * from t3;
2323
create table t44 engine='NDB' as select * from t4;
2324
create table t55 engine='NDB' as select * from t5;
2325
create table t66 engine='NDB' as select * from t6;
2327
partition by range(cast(ceiling(col1) as signed integer))
2328
(partition p0 values less than (15),
2329
partition p1 values less than maxvalue);
2331
partition by list(cast(ceiling(col1) as signed integer))
2332
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2333
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2334
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2335
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2336
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2337
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2340
partition by hash(cast(ceiling(col1) as signed integer));
2342
partition by range(colint)
2343
subpartition by hash(cast(ceiling(col1) as signed integer)) subpartitions 2
2344
(partition p0 values less than (15),
2345
partition p1 values less than maxvalue);
2347
partition by list(colint)
2348
subpartition by hash(cast(ceiling(col1) as signed integer)) subpartitions 2
2349
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2350
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2351
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2352
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2353
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2354
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2357
partition by range(colint)
2358
(partition p0 values less than (cast(ceiling(15) as signed integer)),
2359
partition p1 values less than maxvalue);
2360
select * from t11 order by col1;
2364
select * from t22 order by col1;
2369
select * from t33 order by col1;
2374
select * from t44 order by colint;
2380
select * from t55 order by colint;
2386
select * from t66 order by colint;
2392
---------------------------
2393
---- some alter table begin
2394
---------------------------
2396
reorganize partition p0,p1 into
2397
(partition s1 values less than maxvalue);
2398
select * from t11 order by col1;
2403
reorganize partition s1 into
2404
(partition p0 values less than (15),
2405
partition p1 values less than maxvalue);
2406
select * from t11 order by col1;
2411
partition by list(colint)
2412
subpartition by hash(cast(ceiling(col1) as signed integer)) subpartitions 5
2413
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2414
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2415
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2416
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2417
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2418
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2420
show create table t55;
2422
t55 CREATE TABLE `t55` (
2423
`colint` int(11) DEFAULT NULL,
2424
`col1` float(7,4) DEFAULT NULL
2425
) ENGINE=NDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (cast(ceiling(col1) as signed integer)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = NDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = NDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = NDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = NDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = NDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = NDB) */
2426
select * from t55 order by colint;
2433
reorganize partition p0,p1 into
2434
(partition s1 values less than maxvalue);
2435
select * from t66 order by colint;
2442
reorganize partition s1 into
2443
(partition p0 values less than (cast(ceiling(15) as signed integer)),
2444
partition p1 values less than maxvalue);
2445
select * from t66 order by colint;
2452
reorganize partition p0,p1 into
2453
(partition s1 values less than maxvalue);
2454
select * from t66 order by colint;
2461
reorganize partition s1 into
2462
(partition p0 values less than (cast(ceiling(15) as signed integer)),
2463
partition p1 values less than maxvalue);
2464
select * from t66 order by colint;
2470
-------------------------------------------------------------------------
2471
--- Delete rows and partitions of tables with cast(ceiling(col1) as signed integer)
2472
-------------------------------------------------------------------------
2473
delete from t1 where col1=13.345;
2474
delete from t2 where col1=13.345;
2475
delete from t3 where col1=13.345;
2476
delete from t4 where col1=13.345;
2477
delete from t5 where col1=13.345;
2478
delete from t6 where col1=13.345;
2479
select * from t1 order by col1;
2482
select * from t2 order by col1;
2486
select * from t3 order by col1;
2490
select * from t4 order by colint;
2495
select * from t5 order by colint;
2500
insert into t1 values (13.345);
2501
insert into t2 values (13.345);
2502
insert into t3 values (13.345);
2503
insert into t4 values (60,13.345);
2504
insert into t5 values (60,13.345);
2505
insert into t6 values (60,13.345);
2506
select * from t1 order by col1;
2510
select * from t2 order by col1;
2515
select * from t3 order by col1;
2520
select * from t4 order by colint;
2526
select * from t5 order by colint;
2532
select * from t6 order by colint;
2538
alter table t1 drop partition p0;
2539
alter table t2 drop partition p0;
2540
alter table t4 drop partition p0;
2541
alter table t5 drop partition p0;
2542
alter table t6 drop partition p0;
2543
select * from t1 order by col1;
2546
select * from t2 order by col1;
2551
select * from t3 order by col1;
2556
select * from t4 order by colint;
2559
select * from t5 order by colint;
2562
select * from t6 order by colint;
2565
-------------------------------------------------------------------------
2566
--- Delete rows and partitions of tables with cast(ceiling(col1) as signed integer)
2567
-------------------------------------------------------------------------
2568
delete from t11 where col1=13.345;
2569
delete from t22 where col1=13.345;
2570
delete from t33 where col1=13.345;
2571
delete from t44 where col1=13.345;
2572
delete from t55 where col1=13.345;
2573
delete from t66 where col1=13.345;
2574
select * from t11 order by col1;
2577
select * from t22 order by col1;
2581
select * from t33 order by col1;
2585
select * from t44 order by colint;
2590
select * from t55 order by colint;
2595
insert into t11 values (13.345);
2596
insert into t22 values (13.345);
2597
insert into t33 values (13.345);
2598
insert into t44 values (60,13.345);
2599
insert into t55 values (60,13.345);
2600
insert into t66 values (60,13.345);
2601
select * from t11 order by col1;
2605
select * from t22 order by col1;
2610
select * from t33 order by col1;
2615
select * from t44 order by colint;
2621
select * from t55 order by colint;
2627
select * from t66 order by colint;
2633
alter table t11 drop partition p0;
2634
alter table t22 drop partition p0;
2635
alter table t44 drop partition p0;
2636
alter table t55 drop partition p0;
2637
alter table t66 drop partition p0;
2638
select * from t11 order by col1;
2641
select * from t22 order by col1;
2646
select * from t33 order by col1;
2651
select * from t44 order by colint;
2654
select * from t55 order by colint;
2657
select * from t66 order by colint;
2660
-------------------------
2661
---- some alter table end
2662
-------------------------
2663
drop table if exists t1 ;
2664
drop table if exists t2 ;
2665
drop table if exists t3 ;
2666
drop table if exists t4 ;
2667
drop table if exists t5 ;
2668
drop table if exists t6 ;
2669
drop table if exists t11 ;
2670
drop table if exists t22 ;
2671
drop table if exists t33 ;
2672
drop table if exists t44 ;
2673
drop table if exists t55 ;
2674
drop table if exists t66 ;
2675
-------------------------------------------------------------------------
2676
--- cast(floor(col1) as signed) in partition with coltype float(7,4)
2677
-------------------------------------------------------------------------
2678
drop table if exists t1 ;
2679
drop table if exists t2 ;
2680
drop table if exists t3 ;
2681
drop table if exists t4 ;
2682
drop table if exists t5 ;
2683
drop table if exists t6 ;
2684
-------------------------------------------------------------------------
2685
--- Create tables with cast(floor(col1) as signed)
2686
-------------------------------------------------------------------------
2687
create table t1 (col1 float(7,4)) engine='NDB'
2688
partition by range(cast(floor(col1) as signed))
2689
(partition p0 values less than (15),
2690
partition p1 values less than maxvalue);
2691
create table t2 (col1 float(7,4)) engine='NDB'
2692
partition by list(cast(floor(col1) as signed))
2693
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2694
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2695
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2696
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2697
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2698
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2700
create table t3 (col1 float(7,4)) engine='NDB'
2701
partition by hash(cast(floor(col1) as signed));
2702
create table t4 (colint int, col1 float(7,4)) engine='NDB'
2703
partition by range(colint)
2704
subpartition by hash(cast(floor(col1) as signed)) subpartitions 2
2705
(partition p0 values less than (15),
2706
partition p1 values less than maxvalue);
2707
create table t5 (colint int, col1 float(7,4)) engine='NDB'
2708
partition by list(colint)
2709
subpartition by hash(cast(floor(col1) as signed)) subpartitions 2
2710
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2711
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2712
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2713
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2714
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2715
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2717
create table t6 (colint int, col1 float(7,4)) engine='NDB'
2718
partition by range(colint)
2719
(partition p0 values less than (cast(floor(15.123) as signed)),
2720
partition p1 values less than maxvalue);
2721
-------------------------------------------------------------------------
2722
--- Access tables with cast(floor(col1) as signed)
2723
-------------------------------------------------------------------------
2724
insert into t1 values (5.1230);
2725
insert into t1 values (13.345);
2726
insert into t2 values (5.1230);
2727
insert into t2 values (13.345);
2728
insert into t2 values (17.987);
2729
insert into t3 values (5.1230);
2730
insert into t3 values (13.345);
2731
insert into t3 values (17.987);
2732
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t4;
2733
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t5;
2734
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t6;
2735
select cast(floor(col1) as signed) from t1 order by col1;
2736
cast(floor(col1) as signed)
2739
select * from t1 order by col1;
2743
select * from t2 order by col1;
2748
select * from t3 order by col1;
2753
select * from t4 order by colint;
2759
select * from t5 order by colint;
2765
select * from t6 order by colint;
2771
update t1 set col1=15.654 where col1=5.1230;
2772
update t2 set col1=15.654 where col1=5.1230;
2773
update t3 set col1=15.654 where col1=5.1230;
2774
update t4 set col1=15.654 where col1=5.1230;
2775
update t5 set col1=15.654 where col1=5.1230;
2776
update t6 set col1=15.654 where col1=5.1230;
2777
select * from t1 order by col1;
2781
select * from t2 order by col1;
2786
select * from t3 order by col1;
2791
select * from t4 order by colint;
2797
select * from t5 order by colint;
2803
select * from t6 order by colint;
2809
-------------------------------------------------------------------------
2810
--- Alter tables with cast(floor(col1) as signed)
2811
-------------------------------------------------------------------------
2812
drop table if exists t11 ;
2813
drop table if exists t22 ;
2814
drop table if exists t33 ;
2815
drop table if exists t44 ;
2816
drop table if exists t55 ;
2817
drop table if exists t66 ;
2818
create table t11 engine='NDB' as select * from t1;
2819
create table t22 engine='NDB' as select * from t2;
2820
create table t33 engine='NDB' as select * from t3;
2821
create table t44 engine='NDB' as select * from t4;
2822
create table t55 engine='NDB' as select * from t5;
2823
create table t66 engine='NDB' as select * from t6;
2825
partition by range(cast(floor(col1) as signed))
2826
(partition p0 values less than (15),
2827
partition p1 values less than maxvalue);
2829
partition by list(cast(floor(col1) as signed))
2830
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
2831
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2832
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2833
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2834
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2835
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2838
partition by hash(cast(floor(col1) as signed));
2840
partition by range(colint)
2841
subpartition by hash(cast(floor(col1) as signed)) subpartitions 2
2842
(partition p0 values less than (15),
2843
partition p1 values less than maxvalue);
2845
partition by list(colint)
2846
subpartition by hash(cast(floor(col1) as signed)) subpartitions 2
2847
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2848
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2849
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2850
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2851
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2852
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2855
partition by range(colint)
2856
(partition p0 values less than (cast(floor(15.123) as signed)),
2857
partition p1 values less than maxvalue);
2858
select * from t11 order by col1;
2862
select * from t22 order by col1;
2867
select * from t33 order by col1;
2872
select * from t44 order by colint;
2878
select * from t55 order by colint;
2884
select * from t66 order by colint;
2890
---------------------------
2891
---- some alter table begin
2892
---------------------------
2894
reorganize partition p0,p1 into
2895
(partition s1 values less than maxvalue);
2896
select * from t11 order by col1;
2901
reorganize partition s1 into
2902
(partition p0 values less than (15),
2903
partition p1 values less than maxvalue);
2904
select * from t11 order by col1;
2909
partition by list(colint)
2910
subpartition by hash(cast(floor(col1) as signed)) subpartitions 5
2911
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
2912
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
2913
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
2914
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
2915
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
2916
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
2918
show create table t55;
2920
t55 CREATE TABLE `t55` (
2921
`colint` int(11) DEFAULT NULL,
2922
`col1` float(7,4) DEFAULT NULL
2923
) ENGINE=NDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (cast(floor(col1) as signed)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = NDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = NDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = NDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = NDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = NDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = NDB) */
2924
select * from t55 order by colint;
2931
reorganize partition p0,p1 into
2932
(partition s1 values less than maxvalue);
2933
select * from t66 order by colint;
2940
reorganize partition s1 into
2941
(partition p0 values less than (cast(floor(15.123) as signed)),
2942
partition p1 values less than maxvalue);
2943
select * from t66 order by colint;
2950
reorganize partition p0,p1 into
2951
(partition s1 values less than maxvalue);
2952
select * from t66 order by colint;
2959
reorganize partition s1 into
2960
(partition p0 values less than (cast(floor(15.123) as signed)),
2961
partition p1 values less than maxvalue);
2962
select * from t66 order by colint;
2968
-------------------------------------------------------------------------
2969
--- Delete rows and partitions of tables with cast(floor(col1) as signed)
2970
-------------------------------------------------------------------------
2971
delete from t1 where col1=13.345;
2972
delete from t2 where col1=13.345;
2973
delete from t3 where col1=13.345;
2974
delete from t4 where col1=13.345;
2975
delete from t5 where col1=13.345;
2976
delete from t6 where col1=13.345;
2977
select * from t1 order by col1;
2980
select * from t2 order by col1;
2984
select * from t3 order by col1;
2988
select * from t4 order by colint;
2993
select * from t5 order by colint;
2998
insert into t1 values (13.345);
2999
insert into t2 values (13.345);
3000
insert into t3 values (13.345);
3001
insert into t4 values (60,13.345);
3002
insert into t5 values (60,13.345);
3003
insert into t6 values (60,13.345);
3004
select * from t1 order by col1;
3008
select * from t2 order by col1;
3013
select * from t3 order by col1;
3018
select * from t4 order by colint;
3024
select * from t5 order by colint;
3030
select * from t6 order by colint;
3036
alter table t1 drop partition p0;
3037
alter table t2 drop partition p0;
3038
alter table t4 drop partition p0;
3039
alter table t5 drop partition p0;
3040
alter table t6 drop partition p0;
3041
select * from t1 order by col1;
3044
select * from t2 order by col1;
3049
select * from t3 order by col1;
3054
select * from t4 order by colint;
3057
select * from t5 order by colint;
3060
select * from t6 order by colint;
3063
-------------------------------------------------------------------------
3064
--- Delete rows and partitions of tables with cast(floor(col1) as signed)
3065
-------------------------------------------------------------------------
3066
delete from t11 where col1=13.345;
3067
delete from t22 where col1=13.345;
3068
delete from t33 where col1=13.345;
3069
delete from t44 where col1=13.345;
3070
delete from t55 where col1=13.345;
3071
delete from t66 where col1=13.345;
3072
select * from t11 order by col1;
3075
select * from t22 order by col1;
3079
select * from t33 order by col1;
3083
select * from t44 order by colint;
3088
select * from t55 order by colint;
3093
insert into t11 values (13.345);
3094
insert into t22 values (13.345);
3095
insert into t33 values (13.345);
3096
insert into t44 values (60,13.345);
3097
insert into t55 values (60,13.345);
3098
insert into t66 values (60,13.345);
3099
select * from t11 order by col1;
3103
select * from t22 order by col1;
3108
select * from t33 order by col1;
3113
select * from t44 order by colint;
3119
select * from t55 order by colint;
3125
select * from t66 order by colint;
3131
alter table t11 drop partition p0;
3132
alter table t22 drop partition p0;
3133
alter table t44 drop partition p0;
3134
alter table t55 drop partition p0;
3135
alter table t66 drop partition p0;
3136
select * from t11 order by col1;
3139
select * from t22 order by col1;
3144
select * from t33 order by col1;
3149
select * from t44 order by colint;
3152
select * from t55 order by colint;
3155
select * from t66 order by colint;
3158
-------------------------
3159
---- some alter table end
3160
-------------------------
3161
drop table if exists t1 ;
3162
drop table if exists t2 ;
3163
drop table if exists t3 ;
3164
drop table if exists t4 ;
3165
drop table if exists t5 ;
3166
drop table if exists t6 ;
3167
drop table if exists t11 ;
3168
drop table if exists t22 ;
3169
drop table if exists t33 ;
3170
drop table if exists t44 ;
3171
drop table if exists t55 ;
3172
drop table if exists t66 ;
3173
-------------------------------------------------------------------------
3174
--- cast(mod(col1,10) as signed) in partition with coltype float(7,4)
3175
-------------------------------------------------------------------------
3176
drop table if exists t1 ;
3177
drop table if exists t2 ;
3178
drop table if exists t3 ;
3179
drop table if exists t4 ;
3180
drop table if exists t5 ;
3181
drop table if exists t6 ;
3182
-------------------------------------------------------------------------
3183
--- Create tables with cast(mod(col1,10) as signed)
3184
-------------------------------------------------------------------------
3185
create table t1 (col1 float(7,4)) engine='NDB'
3186
partition by range(cast(mod(col1,10) as signed))
3187
(partition p0 values less than (15),
3188
partition p1 values less than maxvalue);
3189
create table t2 (col1 float(7,4)) engine='NDB'
3190
partition by list(cast(mod(col1,10) as signed))
3191
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3192
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3193
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3194
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3195
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3196
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3198
create table t3 (col1 float(7,4)) engine='NDB'
3199
partition by hash(cast(mod(col1,10) as signed));
3200
create table t4 (colint int, col1 float(7,4)) engine='NDB'
3201
partition by range(colint)
3202
subpartition by hash(cast(mod(col1,10) as signed)) subpartitions 2
3203
(partition p0 values less than (15),
3204
partition p1 values less than maxvalue);
3205
create table t5 (colint int, col1 float(7,4)) engine='NDB'
3206
partition by list(colint)
3207
subpartition by hash(cast(mod(col1,10) as signed)) subpartitions 2
3208
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3209
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3210
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3211
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3212
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3213
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3215
create table t6 (colint int, col1 float(7,4)) engine='NDB'
3216
partition by range(colint)
3217
(partition p0 values less than (cast(mod(15,10) as signed)),
3218
partition p1 values less than maxvalue);
3219
-------------------------------------------------------------------------
3220
--- Access tables with cast(mod(col1,10) as signed)
3221
-------------------------------------------------------------------------
3222
insert into t1 values (5.0000);
1189
-------------------------
1190
---- some alter table end
1191
-------------------------
1192
drop table if exists t1 ;
1193
drop table if exists t2 ;
1194
drop table if exists t3 ;
1195
drop table if exists t4 ;
1196
drop table if exists t5 ;
1197
drop table if exists t6 ;
1198
drop table if exists t11 ;
1199
drop table if exists t22 ;
1200
drop table if exists t33 ;
1201
drop table if exists t44 ;
1202
drop table if exists t55 ;
1203
drop table if exists t66 ;
1204
-------------------------------------------------------------------------
1205
--- mod(col1,10) in partition with coltype int
1206
-------------------------------------------------------------------------
1207
drop table if exists t1 ;
1208
drop table if exists t2 ;
1209
drop table if exists t3 ;
1210
drop table if exists t4 ;
1211
drop table if exists t5 ;
1212
drop table if exists t6 ;
1213
-------------------------------------------------------------------------
1214
--- Create tables with mod(col1,10)
1215
-------------------------------------------------------------------------
1216
create table t1 (col1 int) engine='NDB'
1217
partition by range(mod(col1,10))
1218
(partition p0 values less than (15),
1219
partition p1 values less than maxvalue);
1220
create table t2 (col1 int) engine='NDB'
1221
partition by list(mod(col1,10))
1222
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
1223
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
1224
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
1225
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
1226
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
1227
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
1229
create table t3 (col1 int) engine='NDB'
1230
partition by hash(mod(col1,10));
1231
create table t4 (colint int, col1 int) engine='NDB'
1232
partition by range(colint)
1233
subpartition by hash(mod(col1,10)) subpartitions 2
1234
(partition p0 values less than (15),
1235
partition p1 values less than maxvalue);
1236
create table t5 (colint int, col1 int) engine='NDB'
1237
partition by list(colint)
1238
subpartition by hash(mod(col1,10)) subpartitions 2
1239
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
1240
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
1241
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
1242
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
1244
create table t6 (colint int, col1 int) engine='NDB'
1245
partition by range(colint)
1246
(partition p0 values less than (mod(15,10)),
1247
partition p1 values less than maxvalue);
1248
-------------------------------------------------------------------------
1249
--- Access tables with mod(col1,10)
1250
-------------------------------------------------------------------------
1251
insert into t1 values (5);
3223
1252
insert into t1 values (19);
3224
insert into t2 values (5.0000);
1253
insert into t2 values (5);
3225
1254
insert into t2 values (19);
3226
1255
insert into t2 values (17);
3227
insert into t3 values (5.0000);
1256
insert into t3 values (5);
3228
1257
insert into t3 values (19);
3229
1258
insert into t3 values (17);
3230
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t4;
3231
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t5;
3232
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_float.in' into table t6;
3233
select cast(mod(col1,10) as signed) from t1 order by col1;
3234
cast(mod(col1,10) as signed)
1259
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t4;
1260
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t5;
1261
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_int.inc' into table t6;
1262
select mod(col1,10) from t1 order by col1;
3237
1266
select * from t1 order by col1;
3241
1270
select * from t2 order by col1;
3246
1275
select * from t3 order by col1;
3251
1280
select * from t4 order by colint;
3257
1327
select * from t5 order by colint;
3263
1374
select * from t6 order by colint;
3269
update t1 set col1=15 where col1=5.0000;
3270
update t2 set col1=15 where col1=5.0000;
3271
update t3 set col1=15 where col1=5.0000;
3272
update t4 set col1=15 where col1=5.0000;
3273
update t5 set col1=15 where col1=5.0000;
3274
update t6 set col1=15 where col1=5.0000;
1421
update t1 set col1=15 where col1=5;
1422
update t2 set col1=15 where col1=5;
1423
update t3 set col1=15 where col1=5;
1424
update t4 set col1=15 where col1=5;
1425
update t5 set col1=15 where col1=5;
1426
update t6 set col1=15 where col1=5;
3275
1427
select * from t1 order by col1;
3279
1431
select * from t2 order by col1;
3284
1436
select * from t3 order by col1;
3289
1441
select * from t4 order by colint;
3295
1488
select * from t5 order by colint;
3301
1535
select * from t6 order by colint;
3307
1582
-------------------------------------------------------------------------
3308
--- Alter tables with cast(mod(col1,10) as signed)
1583
--- Alter tables with mod(col1,10)
3309
1584
-------------------------------------------------------------------------
3310
1585
drop table if exists t11 ;
3311
1586
drop table if exists t22 ;
3599
2243
insert into t66 values (60,19);
3600
2244
select * from t11 order by col1;
3604
select * from t22 order by col1;
3609
select * from t33 order by col1;
3614
select * from t44 order by colint;
3621
select * from t55 order by colint;
3628
select * from t66 order by colint;
3635
alter table t11 drop partition p0;
3636
alter table t22 drop partition p0;
3637
alter table t44 drop partition p0;
3638
alter table t55 drop partition p0;
3639
alter table t66 drop partition p0;
3640
select * from t11 order by col1;
3642
select * from t22 order by col1;
3644
select * from t33 order by col1;
3649
select * from t44 order by colint;
3652
select * from t55 order by colint;
3655
select * from t66 order by colint;
3658
-------------------------
3659
---- some alter table end
3660
-------------------------
3661
drop table if exists t1 ;
3662
drop table if exists t2 ;
3663
drop table if exists t3 ;
3664
drop table if exists t4 ;
3665
drop table if exists t5 ;
3666
drop table if exists t6 ;
3667
drop table if exists t11 ;
3668
drop table if exists t22 ;
3669
drop table if exists t33 ;
3670
drop table if exists t44 ;
3671
drop table if exists t55 ;
3672
drop table if exists t66 ;
3673
-------------------------------------------------------------------------
3674
--- ord(col1) in partition with coltype char(3)
3675
-------------------------------------------------------------------------
3676
drop table if exists t1 ;
3677
drop table if exists t2 ;
3678
drop table if exists t3 ;
3679
drop table if exists t4 ;
3680
drop table if exists t5 ;
3681
drop table if exists t6 ;
3682
-------------------------------------------------------------------------
3683
--- Create tables with ord(col1)
3684
-------------------------------------------------------------------------
3685
create table t1 (col1 char(3)) engine='NDB'
3686
partition by range(ord(col1))
3687
(partition p0 values less than (15),
3688
partition p1 values less than maxvalue);
3689
create table t2 (col1 char(3)) engine='NDB'
3690
partition by list(ord(col1))
3691
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3692
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3693
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3694
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3695
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3696
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3698
create table t3 (col1 char(3)) engine='NDB'
3699
partition by hash(ord(col1));
3700
create table t4 (colint int, col1 char(3)) engine='NDB'
3701
partition by range(colint)
3702
subpartition by hash(ord(col1)) subpartitions 2
3703
(partition p0 values less than (15),
3704
partition p1 values less than maxvalue);
3705
create table t5 (colint int, col1 char(3)) engine='NDB'
3706
partition by list(colint)
3707
subpartition by hash(ord(col1)) subpartitions 2
3708
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3709
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3710
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3711
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3712
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3713
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3715
create table t6 (colint int, col1 char(3)) engine='NDB'
3716
partition by range(colint)
3717
(partition p0 values less than (ord('a')),
3718
partition p1 values less than maxvalue);
3719
-------------------------------------------------------------------------
3720
--- Access tables with ord(col1)
3721
-------------------------------------------------------------------------
3722
insert into t1 values ('1');
3723
insert into t1 values ('9');
3724
insert into t2 values ('1');
3725
insert into t2 values ('9');
3726
insert into t2 values ('3');
3727
insert into t3 values ('1');
3728
insert into t3 values ('9');
3729
insert into t3 values ('3');
3730
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_ch1.in' into table t4;
3731
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_ch1.in' into table t5;
3732
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_ch1.in' into table t6;
3733
select ord(col1) from t1 order by col1;
3737
select * from t1 order by col1;
3741
select * from t2 order by col1;
3746
select * from t3 order by col1;
3751
select * from t4 order by colint;
3757
select * from t5 order by colint;
3763
select * from t6 order by colint;
3769
update t1 set col1='8' where col1='1';
3770
update t2 set col1='8' where col1='1';
3771
update t3 set col1='8' where col1='1';
3772
update t4 set col1='8' where col1='1';
3773
update t5 set col1='8' where col1='1';
3774
update t6 set col1='8' where col1='1';
3775
select * from t1 order by col1;
3779
select * from t2 order by col1;
3784
select * from t3 order by col1;
3789
select * from t4 order by colint;
3795
select * from t5 order by colint;
3801
select * from t6 order by colint;
3807
-------------------------------------------------------------------------
3808
--- Alter tables with ord(col1)
3809
-------------------------------------------------------------------------
3810
drop table if exists t11 ;
3811
drop table if exists t22 ;
3812
drop table if exists t33 ;
3813
drop table if exists t44 ;
3814
drop table if exists t55 ;
3815
drop table if exists t66 ;
3816
create table t11 engine='NDB' as select * from t1;
3817
create table t22 engine='NDB' as select * from t2;
3818
create table t33 engine='NDB' as select * from t3;
3819
create table t44 engine='NDB' as select * from t4;
3820
create table t55 engine='NDB' as select * from t5;
3821
create table t66 engine='NDB' as select * from t6;
3823
partition by range(ord(col1))
3824
(partition p0 values less than (15),
3825
partition p1 values less than maxvalue);
3827
partition by list(ord(col1))
3828
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
3829
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3830
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3831
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3832
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3833
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3836
partition by hash(ord(col1));
3838
partition by range(colint)
3839
subpartition by hash(ord(col1)) subpartitions 2
3840
(partition p0 values less than (15),
3841
partition p1 values less than maxvalue);
3843
partition by list(colint)
3844
subpartition by hash(ord(col1)) subpartitions 2
3845
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3846
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3847
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3848
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3849
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3850
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3853
partition by range(colint)
3854
(partition p0 values less than (ord('a')),
3855
partition p1 values less than maxvalue);
3856
select * from t11 order by col1;
3860
select * from t22 order by col1;
3865
select * from t33 order by col1;
3870
select * from t44 order by colint;
3876
select * from t55 order by colint;
3882
select * from t66 order by colint;
3888
---------------------------
3889
---- some alter table begin
3890
---------------------------
3892
reorganize partition p0,p1 into
3893
(partition s1 values less than maxvalue);
3894
select * from t11 order by col1;
3899
reorganize partition s1 into
3900
(partition p0 values less than (15),
3901
partition p1 values less than maxvalue);
3902
select * from t11 order by col1;
3907
partition by list(colint)
3908
subpartition by hash(ord(col1)) subpartitions 5
3909
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
3910
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
3911
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
3912
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
3913
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
3914
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
3916
show create table t55;
3918
t55 CREATE TABLE `t55` (
3919
`colint` int(11) DEFAULT NULL,
3920
`col1` char(3) DEFAULT NULL
3921
) ENGINE=NDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (ord(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = NDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = NDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = NDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = NDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = NDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = NDB) */
3922
select * from t55 order by colint;
3929
reorganize partition p0,p1 into
3930
(partition s1 values less than maxvalue);
3931
select * from t66 order by colint;
3938
reorganize partition s1 into
3939
(partition p0 values less than (ord('a')),
3940
partition p1 values less than maxvalue);
3941
select * from t66 order by colint;
3948
reorganize partition p0,p1 into
3949
(partition s1 values less than maxvalue);
3950
select * from t66 order by colint;
3957
reorganize partition s1 into
3958
(partition p0 values less than (ord('a')),
3959
partition p1 values less than maxvalue);
3960
select * from t66 order by colint;
3966
-------------------------------------------------------------------------
3967
--- Delete rows and partitions of tables with ord(col1)
3968
-------------------------------------------------------------------------
3969
delete from t1 where col1='9';
3970
delete from t2 where col1='9';
3971
delete from t3 where col1='9';
3972
delete from t4 where col1='9';
3973
delete from t5 where col1='9';
3974
delete from t6 where col1='9';
3975
select * from t1 order by col1;
3978
select * from t2 order by col1;
3982
select * from t3 order by col1;
3986
select * from t4 order by colint;
3991
select * from t5 order by colint;
3996
insert into t1 values ('9');
3997
insert into t2 values ('9');
3998
insert into t3 values ('9');
3999
insert into t4 values (60,'9');
4000
insert into t5 values (60,'9');
4001
insert into t6 values (60,'9');
4002
select * from t1 order by col1;
4006
select * from t2 order by col1;
4011
select * from t3 order by col1;
4016
select * from t4 order by colint;
4022
select * from t5 order by colint;
4028
select * from t6 order by colint;
4034
alter table t1 drop partition p0;
4035
alter table t2 drop partition p0;
4036
alter table t4 drop partition p0;
4037
alter table t5 drop partition p0;
4038
alter table t6 drop partition p0;
4039
select * from t1 order by col1;
4043
select * from t2 order by col1;
4048
select * from t3 order by col1;
4053
select * from t4 order by colint;
4056
select * from t5 order by colint;
4059
select * from t6 order by colint;
4061
-------------------------------------------------------------------------
4062
--- Delete rows and partitions of tables with ord(col1)
4063
-------------------------------------------------------------------------
4064
delete from t11 where col1='9';
4065
delete from t22 where col1='9';
4066
delete from t33 where col1='9';
4067
delete from t44 where col1='9';
4068
delete from t55 where col1='9';
4069
delete from t66 where col1='9';
4070
select * from t11 order by col1;
4073
select * from t22 order by col1;
4077
select * from t33 order by col1;
4081
select * from t44 order by colint;
4086
select * from t55 order by colint;
4091
insert into t11 values ('9');
4092
insert into t22 values ('9');
4093
insert into t33 values ('9');
4094
insert into t44 values (60,'9');
4095
insert into t55 values (60,'9');
4096
insert into t66 values (60,'9');
4097
select * from t11 order by col1;
4101
select * from t22 order by col1;
4106
select * from t33 order by col1;
4111
select * from t44 order by colint;
4117
select * from t55 order by colint;
4123
select * from t66 order by colint;
4129
alter table t11 drop partition p0;
4130
alter table t22 drop partition p0;
4131
alter table t44 drop partition p0;
4132
alter table t55 drop partition p0;
4133
alter table t66 drop partition p0;
4134
select * from t11 order by col1;
4138
select * from t22 order by col1;
4143
select * from t33 order by col1;
4148
select * from t44 order by colint;
4151
select * from t55 order by colint;
4154
select * from t66 order by colint;
2248
select * from t22 order by col1;
2253
select * from t33 order by col1;
2258
select * from t44 order by colint;
2306
select * from t55 order by colint;
2354
select * from t66 order by colint;
4156
2402
-------------------------
4157
2403
---- some alter table end
4158
2404
-------------------------
11370
8034
(partition p0 values less than (15),
11371
8035
partition p1 values less than maxvalue);
11372
8036
alter table t55
11373
partition by list(colint)
8037
partition by list(colint)
11374
8038
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 2
11375
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11376
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11377
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11378
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11379
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11380
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11383
partition by range(colint)
11384
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
11385
partition p1 values less than maxvalue);
11386
select * from t11 order by col1;
11390
select * from t22 order by col1;
11395
select * from t33 order by col1;
11400
select * from t44 order by colint;
11406
select * from t55 order by colint;
11412
select * from t66 order by colint;
11418
---------------------------
11419
---- some alter table begin
11420
---------------------------
11422
reorganize partition p0,p1 into
11423
(partition s1 values less than maxvalue);
11424
select * from t11 order by col1;
11429
reorganize partition s1 into
11430
(partition p0 values less than (15),
11431
partition p1 values less than maxvalue);
11432
select * from t11 order by col1;
11437
partition by list(colint)
11438
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 5
11439
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
11440
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
11441
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
11442
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
11443
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
11444
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
11446
show create table t55;
11448
t55 CREATE TABLE `t55` (
11449
`colint` int(11) DEFAULT NULL,
11450
`col1` date DEFAULT NULL
11451
) ENGINE=NDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01')) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = NDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = NDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = NDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = NDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = NDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = NDB) */
11452
select * from t55 order by colint;
11459
reorganize partition p0,p1 into
11460
(partition s1 values less than maxvalue);
11461
select * from t66 order by colint;
11468
reorganize partition s1 into
11469
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
11470
partition p1 values less than maxvalue);
11471
select * from t66 order by colint;
11478
reorganize partition p0,p1 into
11479
(partition s1 values less than maxvalue);
11480
select * from t66 order by colint;
11487
reorganize partition s1 into
11488
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
11489
partition p1 values less than maxvalue);
11490
select * from t66 order by colint;
11496
-------------------------------------------------------------------------
11497
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
11498
-------------------------------------------------------------------------
11499
delete from t1 where col1='2006-01-17';
11500
delete from t2 where col1='2006-01-17';
11501
delete from t3 where col1='2006-01-17';
11502
delete from t4 where col1='2006-01-17';
11503
delete from t5 where col1='2006-01-17';
11504
delete from t6 where col1='2006-01-17';
11505
select * from t1 order by col1;
11508
select * from t2 order by col1;
11512
select * from t3 order by col1;
11516
select * from t4 order by colint;
11521
select * from t5 order by colint;
11526
insert into t1 values ('2006-01-17');
11527
insert into t2 values ('2006-01-17');
11528
insert into t3 values ('2006-01-17');
11529
insert into t4 values (60,'2006-01-17');
11530
insert into t5 values (60,'2006-01-17');
11531
insert into t6 values (60,'2006-01-17');
11532
select * from t1 order by col1;
11536
select * from t2 order by col1;
11541
select * from t3 order by col1;
11546
select * from t4 order by colint;
11552
select * from t5 order by colint;
11558
select * from t6 order by colint;
11564
alter table t1 drop partition p0;
11565
alter table t2 drop partition p0;
11566
alter table t4 drop partition p0;
11567
alter table t5 drop partition p0;
11568
alter table t6 drop partition p0;
11569
select * from t1 order by col1;
11573
select * from t2 order by col1;
11578
select * from t3 order by col1;
11583
select * from t4 order by colint;
11586
select * from t5 order by colint;
11589
select * from t6 order by colint;
11592
-------------------------------------------------------------------------
11593
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
11594
-------------------------------------------------------------------------
11595
delete from t11 where col1='2006-01-17';
11596
delete from t22 where col1='2006-01-17';
11597
delete from t33 where col1='2006-01-17';
11598
delete from t44 where col1='2006-01-17';
11599
delete from t55 where col1='2006-01-17';
11600
delete from t66 where col1='2006-01-17';
11601
select * from t11 order by col1;
11604
select * from t22 order by col1;
11608
select * from t33 order by col1;
11612
select * from t44 order by colint;
11617
select * from t55 order by colint;
11622
insert into t11 values ('2006-01-17');
11623
insert into t22 values ('2006-01-17');
11624
insert into t33 values ('2006-01-17');
11625
insert into t44 values (60,'2006-01-17');
11626
insert into t55 values (60,'2006-01-17');
11627
insert into t66 values (60,'2006-01-17');
11628
select * from t11 order by col1;
11632
select * from t22 order by col1;
11637
select * from t33 order by col1;
11642
select * from t44 order by colint;
11648
select * from t55 order by colint;
11654
select * from t66 order by colint;
11660
alter table t11 drop partition p0;
11661
alter table t22 drop partition p0;
11662
alter table t44 drop partition p0;
11663
alter table t55 drop partition p0;
11664
alter table t66 drop partition p0;
11665
select * from t11 order by col1;
11669
select * from t22 order by col1;
11674
select * from t33 order by col1;
11679
select * from t44 order by colint;
11682
select * from t55 order by colint;
11685
select * from t66 order by colint;
8039
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8040
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8041
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8042
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8045
partition by range(colint)
8046
(partition p0 values less than (to_days('2006-02-02')-to_days('2006-01-01')),
8047
partition p1 values less than maxvalue);
8048
select * from t11 order by col1;
8052
select * from t22 order by col1;
8057
select * from t33 order by col1;
8062
select * from t44 order by colint;
8068
select * from t55 order by colint;
8074
select * from t66 order by colint;
8080
---------------------------
8081
---- some alter table begin
8082
---------------------------
8084
partition by list(colint)
8085
subpartition by hash(to_days(col1)-to_days('2006-01-01')) subpartitions 4
8086
(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),
8087
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)
8089
show create table t55;
8091
t55 CREATE TABLE `t55` (
8092
`colint` int(11) DEFAULT NULL,
8093
`col1` date DEFAULT NULL
8094
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (to_days(col1)-to_days('2006-01-01')) SUBPARTITIONS 4 (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, 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) */
8095
select * from t55 order by colint;
8101
-------------------------------------------------------------------------
8102
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
8103
-------------------------------------------------------------------------
8104
delete from t1 where col1='2006-01-17';
8105
delete from t2 where col1='2006-01-17';
8106
delete from t3 where col1='2006-01-17';
8107
delete from t4 where col1='2006-01-17';
8108
delete from t5 where col1='2006-01-17';
8109
delete from t6 where col1='2006-01-17';
8110
select * from t1 order by col1;
8113
select * from t2 order by col1;
8117
select * from t3 order by col1;
8121
select * from t4 order by colint;
8126
select * from t5 order by colint;
8131
insert into t1 values ('2006-01-17');
8132
insert into t2 values ('2006-01-17');
8133
insert into t3 values ('2006-01-17');
8134
insert into t4 values (60,'2006-01-17');
8135
insert into t5 values (60,'2006-01-17');
8136
insert into t6 values (60,'2006-01-17');
8137
select * from t1 order by col1;
8141
select * from t2 order by col1;
8146
select * from t3 order by col1;
8151
select * from t4 order by colint;
8157
select * from t5 order by colint;
8163
select * from t6 order by colint;
8169
-------------------------------------------------------------------------
8170
--- Delete rows and partitions of tables with to_days(col1)-to_days('2006-01-01')
8171
-------------------------------------------------------------------------
8172
delete from t11 where col1='2006-01-17';
8173
delete from t22 where col1='2006-01-17';
8174
delete from t33 where col1='2006-01-17';
8175
delete from t44 where col1='2006-01-17';
8176
delete from t55 where col1='2006-01-17';
8177
delete from t66 where col1='2006-01-17';
8178
select * from t11 order by col1;
8181
select * from t22 order by col1;
8185
select * from t33 order by col1;
8189
select * from t44 order by colint;
8194
select * from t55 order by colint;
8199
insert into t11 values ('2006-01-17');
8200
insert into t22 values ('2006-01-17');
8201
insert into t33 values ('2006-01-17');
8202
insert into t44 values (60,'2006-01-17');
8203
insert into t55 values (60,'2006-01-17');
8204
insert into t66 values (60,'2006-01-17');
8205
select * from t11 order by col1;
8209
select * from t22 order by col1;
8214
select * from t33 order by col1;
8219
select * from t44 order by colint;
8225
select * from t55 order by colint;
8231
select * from t66 order by colint;
8237
-------------------------
8238
---- some alter table end
8239
-------------------------
8240
drop table if exists t1 ;
8241
drop table if exists t2 ;
8242
drop table if exists t3 ;
8243
drop table if exists t4 ;
8244
drop table if exists t5 ;
8245
drop table if exists t6 ;
8246
drop table if exists t11 ;
8247
drop table if exists t22 ;
8248
drop table if exists t33 ;
8249
drop table if exists t44 ;
8250
drop table if exists t55 ;
8251
drop table if exists t66 ;
8252
-------------------------------------------------------------------------
8253
--- datediff(col1, '2006-01-01') in partition with coltype date
8254
-------------------------------------------------------------------------
8255
drop table if exists t1 ;
8256
drop table if exists t2 ;
8257
drop table if exists t3 ;
8258
drop table if exists t4 ;
8259
drop table if exists t5 ;
8260
drop table if exists t6 ;
8261
-------------------------------------------------------------------------
8262
--- Create tables with datediff(col1, '2006-01-01')
8263
-------------------------------------------------------------------------
8264
create table t1 (col1 date) engine='NDB'
8265
partition by range(datediff(col1, '2006-01-01'))
8266
(partition p0 values less than (15),
8267
partition p1 values less than maxvalue);
8268
create table t2 (col1 date) engine='NDB'
8269
partition by list(datediff(col1, '2006-01-01'))
8270
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8271
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8272
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8273
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8274
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8275
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8277
create table t3 (col1 date) engine='NDB'
8278
partition by hash(datediff(col1, '2006-01-01'));
8279
create table t4 (colint int, col1 date) engine='NDB'
8280
partition by range(colint)
8281
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8282
(partition p0 values less than (15),
8283
partition p1 values less than maxvalue);
8284
create table t5 (colint int, col1 date) engine='NDB'
8285
partition by list(colint)
8286
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8287
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8288
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8289
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8290
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8292
create table t6 (colint int, col1 date) engine='NDB'
8293
partition by range(colint)
8294
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
8295
partition p1 values less than maxvalue);
8296
-------------------------------------------------------------------------
8297
--- Access tables with datediff(col1, '2006-01-01')
8298
-------------------------------------------------------------------------
8299
insert into t1 values ('2006-02-03');
8300
insert into t1 values ('2006-01-17');
8301
insert into t2 values ('2006-02-03');
8302
insert into t2 values ('2006-01-17');
8303
insert into t2 values ('2006-01-25');
8304
insert into t3 values ('2006-02-03');
8305
insert into t3 values ('2006-01-17');
8306
insert into t3 values ('2006-01-25');
8307
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t4;
8308
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t5;
8309
load data infile '../std_data_ln/parts/part_supported_sql_funcs_int_date.inc' into table t6;
8310
select datediff(col1, '2006-01-01') from t1 order by col1;
8311
datediff(col1, '2006-01-01')
8314
select * from t1 order by col1;
8318
select * from t2 order by col1;
8323
select * from t3 order by col1;
8328
select * from t4 order by colint;
8334
select * from t5 order by colint;
8340
select * from t6 order by colint;
8346
update t1 set col1='2006-02-06' where col1='2006-02-03';
8347
update t2 set col1='2006-02-06' where col1='2006-02-03';
8348
update t3 set col1='2006-02-06' where col1='2006-02-03';
8349
update t4 set col1='2006-02-06' where col1='2006-02-03';
8350
update t5 set col1='2006-02-06' where col1='2006-02-03';
8351
update t6 set col1='2006-02-06' where col1='2006-02-03';
8352
select * from t1 order by col1;
8356
select * from t2 order by col1;
8361
select * from t3 order by col1;
8366
select * from t4 order by colint;
8372
select * from t5 order by colint;
8378
select * from t6 order by colint;
8384
-------------------------------------------------------------------------
8385
--- Alter tables with datediff(col1, '2006-01-01')
8386
-------------------------------------------------------------------------
8387
drop table if exists t11 ;
8388
drop table if exists t22 ;
8389
drop table if exists t33 ;
8390
drop table if exists t44 ;
8391
drop table if exists t55 ;
8392
drop table if exists t66 ;
8393
create table t11 engine='NDB' as select * from t1;
8394
create table t22 engine='NDB' as select * from t2;
8395
create table t33 engine='NDB' as select * from t3;
8396
create table t44 engine='NDB' as select * from t4;
8397
create table t55 engine='NDB' as select * from t5;
8398
create table t66 engine='NDB' as select * from t6;
8400
partition by range(datediff(col1, '2006-01-01'))
8401
(partition p0 values less than (15),
8402
partition p1 values less than maxvalue);
8404
partition by list(datediff(col1, '2006-01-01'))
8405
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
8406
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
8407
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
8408
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
8409
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
8410
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
8413
partition by hash(datediff(col1, '2006-01-01'));
8415
partition by range(colint)
8416
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8417
(partition p0 values less than (15),
8418
partition p1 values less than maxvalue);
8420
partition by list(colint)
8421
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 2
8422
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
8423
partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
8424
partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
8425
partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
8428
partition by range(colint)
8429
(partition p0 values less than (datediff('2006-02-02', '2006-01-01')),
8430
partition p1 values less than maxvalue);
8431
select * from t11 order by col1;
8435
select * from t22 order by col1;
8440
select * from t33 order by col1;
8445
select * from t44 order by colint;
8451
select * from t55 order by colint;
8457
select * from t66 order by colint;
8463
---------------------------
8464
---- some alter table begin
8465
---------------------------
8467
partition by list(colint)
8468
subpartition by hash(datediff(col1, '2006-01-01')) subpartitions 4
8469
(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),
8470
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)
8472
show create table t55;
8474
t55 CREATE TABLE `t55` (
8475
`colint` int(11) DEFAULT NULL,
8476
`col1` date DEFAULT NULL
8477
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (datediff(col1, '2006-01-01')) SUBPARTITIONS 4 (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, 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) */
8478
select * from t55 order by colint;
8484
-------------------------------------------------------------------------
8485
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
8486
-------------------------------------------------------------------------
8487
delete from t1 where col1='2006-01-17';
8488
delete from t2 where col1='2006-01-17';
8489
delete from t3 where col1='2006-01-17';
8490
delete from t4 where col1='2006-01-17';
8491
delete from t5 where col1='2006-01-17';
8492
delete from t6 where col1='2006-01-17';
8493
select * from t1 order by col1;
8496
select * from t2 order by col1;
8500
select * from t3 order by col1;
8504
select * from t4 order by colint;
8509
select * from t5 order by colint;
8514
insert into t1 values ('2006-01-17');
8515
insert into t2 values ('2006-01-17');
8516
insert into t3 values ('2006-01-17');
8517
insert into t4 values (60,'2006-01-17');
8518
insert into t5 values (60,'2006-01-17');
8519
insert into t6 values (60,'2006-01-17');
8520
select * from t1 order by col1;
8524
select * from t2 order by col1;
8529
select * from t3 order by col1;
8534
select * from t4 order by colint;
8540
select * from t5 order by colint;
8546
select * from t6 order by colint;
8552
-------------------------------------------------------------------------
8553
--- Delete rows and partitions of tables with datediff(col1, '2006-01-01')
8554
-------------------------------------------------------------------------
8555
delete from t11 where col1='2006-01-17';
8556
delete from t22 where col1='2006-01-17';
8557
delete from t33 where col1='2006-01-17';
8558
delete from t44 where col1='2006-01-17';
8559
delete from t55 where col1='2006-01-17';
8560
delete from t66 where col1='2006-01-17';
8561
select * from t11 order by col1;
8564
select * from t22 order by col1;
8568
select * from t33 order by col1;
8572
select * from t44 order by colint;
8577
select * from t55 order by colint;
8582
insert into t11 values ('2006-01-17');
8583
insert into t22 values ('2006-01-17');
8584
insert into t33 values ('2006-01-17');
8585
insert into t44 values (60,'2006-01-17');
8586
insert into t55 values (60,'2006-01-17');
8587
insert into t66 values (60,'2006-01-17');
8588
select * from t11 order by col1;
8592
select * from t22 order by col1;
8597
select * from t33 order by col1;
8602
select * from t44 order by colint;
8608
select * from t55 order by colint;
8614
select * from t66 order by colint;
11688
8620
-------------------------
11689
8621
---- some alter table end
12165
alter table t11 drop partition p0;
12166
alter table t22 drop partition p0;
12167
alter table t44 drop partition p0;
12168
alter table t55 drop partition p0;
12169
alter table t66 drop partition p0;
12170
select * from t11 order by col1;
12172
select * from t22 order by col1;
12174
select * from t33 order by col1;
12179
select * from t44 order by colint;
12182
select * from t55 order by colint;
12185
select * from t66 order by colint;
12188
-------------------------
12189
---- some alter table end
12190
-------------------------
12191
drop table if exists t1 ;
12192
drop table if exists t2 ;
12193
drop table if exists t3 ;
12194
drop table if exists t4 ;
12195
drop table if exists t5 ;
12196
drop table if exists t6 ;
12197
drop table if exists t11 ;
12198
drop table if exists t22 ;
12199
drop table if exists t33 ;
12200
drop table if exists t44 ;
12201
drop table if exists t55 ;
12202
drop table if exists t66 ;
12203
-------------------------------------------------------------------------
12204
--- weekofyear(col1) in partition with coltype date
12205
-------------------------------------------------------------------------
12206
drop table if exists t1 ;
12207
drop table if exists t2 ;
12208
drop table if exists t3 ;
12209
drop table if exists t4 ;
12210
drop table if exists t5 ;
12211
drop table if exists t6 ;
12212
-------------------------------------------------------------------------
12213
--- Create tables with weekofyear(col1)
12214
-------------------------------------------------------------------------
12215
create table t1 (col1 date) engine='NDB'
12216
partition by range(weekofyear(col1))
12217
(partition p0 values less than (15),
12218
partition p1 values less than maxvalue);
12219
create table t2 (col1 date) engine='NDB'
12220
partition by list(weekofyear(col1))
12221
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12222
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12223
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12224
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12225
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12226
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12228
create table t3 (col1 date) engine='NDB'
12229
partition by hash(weekofyear(col1));
12230
create table t4 (colint int, col1 date) engine='NDB'
12231
partition by range(colint)
12232
subpartition by hash(weekofyear(col1)) subpartitions 2
12233
(partition p0 values less than (15),
12234
partition p1 values less than maxvalue);
12235
create table t5 (colint int, col1 date) engine='NDB'
12236
partition by list(colint)
12237
subpartition by hash(weekofyear(col1)) subpartitions 2
12238
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12239
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12240
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12241
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12242
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12243
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12245
create table t6 (colint int, col1 date) engine='NDB'
12246
partition by range(colint)
12247
(partition p0 values less than (weekofyear('2006-02-14')),
12248
partition p1 values less than maxvalue);
12249
-------------------------------------------------------------------------
12250
--- Access tables with weekofyear(col1)
12251
-------------------------------------------------------------------------
12252
insert into t1 values ('2006-01-03');
12253
insert into t1 values ('2006-03-17');
12254
insert into t2 values ('2006-01-03');
12255
insert into t2 values ('2006-03-17');
12256
insert into t2 values ('2006-05-25');
12257
insert into t3 values ('2006-01-03');
12258
insert into t3 values ('2006-03-17');
12259
insert into t3 values ('2006-05-25');
12260
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_date.in' into table t4;
12261
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_date.in' into table t5;
12262
load data infile 'MYSQL_TEST_DIR/suite/partitions/include/partition_supported_sql_funcs_int_date.in' into table t6;
12263
select weekofyear(col1) from t1 order by col1;
12267
select * from t1 order by col1;
12271
select * from t2 order by col1;
12276
select * from t3 order by col1;
12281
select * from t4 order by colint;
12287
select * from t5 order by colint;
12293
select * from t6 order by colint;
12299
update t1 set col1='2006-09-06' where col1='2006-01-03';
12300
update t2 set col1='2006-09-06' where col1='2006-01-03';
12301
update t3 set col1='2006-09-06' where col1='2006-01-03';
12302
update t4 set col1='2006-09-06' where col1='2006-01-03';
12303
update t5 set col1='2006-09-06' where col1='2006-01-03';
12304
update t6 set col1='2006-09-06' where col1='2006-01-03';
12305
select * from t1 order by col1;
12309
select * from t2 order by col1;
12314
select * from t3 order by col1;
12319
select * from t4 order by colint;
12325
select * from t5 order by colint;
12331
select * from t6 order by colint;
12337
-------------------------------------------------------------------------
12338
--- Alter tables with weekofyear(col1)
12339
-------------------------------------------------------------------------
12340
drop table if exists t11 ;
12341
drop table if exists t22 ;
12342
drop table if exists t33 ;
12343
drop table if exists t44 ;
12344
drop table if exists t55 ;
12345
drop table if exists t66 ;
12346
create table t11 engine='NDB' as select * from t1;
12347
create table t22 engine='NDB' as select * from t2;
12348
create table t33 engine='NDB' as select * from t3;
12349
create table t44 engine='NDB' as select * from t4;
12350
create table t55 engine='NDB' as select * from t5;
12351
create table t66 engine='NDB' as select * from t6;
12353
partition by range(weekofyear(col1))
12354
(partition p0 values less than (15),
12355
partition p1 values less than maxvalue);
12357
partition by list(weekofyear(col1))
12358
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
12359
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12360
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12361
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12362
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12363
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12366
partition by hash(weekofyear(col1));
12368
partition by range(colint)
12369
subpartition by hash(weekofyear(col1)) subpartitions 2
12370
(partition p0 values less than (15),
12371
partition p1 values less than maxvalue);
12373
partition by list(colint)
12374
subpartition by hash(weekofyear(col1)) subpartitions 2
12375
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12376
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12377
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12378
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12379
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12380
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12383
partition by range(colint)
12384
(partition p0 values less than (weekofyear('2006-02-14')),
12385
partition p1 values less than maxvalue);
12386
select * from t11 order by col1;
12390
select * from t22 order by col1;
12395
select * from t33 order by col1;
12400
select * from t44 order by colint;
12406
select * from t55 order by colint;
12412
select * from t66 order by colint;
12418
---------------------------
12419
---- some alter table begin
12420
---------------------------
12422
reorganize partition p0,p1 into
12423
(partition s1 values less than maxvalue);
12424
select * from t11 order by col1;
12429
reorganize partition s1 into
12430
(partition p0 values less than (15),
12431
partition p1 values less than maxvalue);
12432
select * from t11 order by col1;
12437
partition by list(colint)
12438
subpartition by hash(weekofyear(col1)) subpartitions 5
12439
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
12440
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
12441
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
12442
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
12443
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
12444
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
12446
show create table t55;
12448
t55 CREATE TABLE `t55` (
12449
`colint` int(11) DEFAULT NULL,
12450
`col1` date DEFAULT NULL
12451
) ENGINE=NDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (colint) SUBPARTITION BY HASH (weekofyear(col1)) SUBPARTITIONS 5 (PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = NDB, PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = NDB, PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = NDB, PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = NDB, PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = NDB, PARTITION p5 VALUES IN (51,52,53,54,55,56,57,58,59,60) ENGINE = NDB) */
12452
select * from t55 order by colint;
12459
reorganize partition p0,p1 into
12460
(partition s1 values less than maxvalue);
12461
select * from t66 order by colint;
12468
reorganize partition s1 into
12469
(partition p0 values less than (weekofyear('2006-02-14')),
12470
partition p1 values less than maxvalue);
12471
select * from t66 order by colint;
12478
reorganize partition p0,p1 into
12479
(partition s1 values less than maxvalue);
12480
select * from t66 order by colint;
12487
reorganize partition s1 into
12488
(partition p0 values less than (weekofyear('2006-02-14')),
12489
partition p1 values less than maxvalue);
12490
select * from t66 order by colint;
12496
-------------------------------------------------------------------------
12497
--- Delete rows and partitions of tables with weekofyear(col1)
12498
-------------------------------------------------------------------------
12499
delete from t1 where col1='2006-03-17';
12500
delete from t2 where col1='2006-03-17';
12501
delete from t3 where col1='2006-03-17';
12502
delete from t4 where col1='2006-03-17';
12503
delete from t5 where col1='2006-03-17';
12504
delete from t6 where col1='2006-03-17';
12505
select * from t1 order by col1;
12508
select * from t2 order by col1;
12512
select * from t3 order by col1;
12516
select * from t4 order by colint;
12522
select * from t5 order by colint;
12528
insert into t1 values ('2006-03-17');
12529
insert into t2 values ('2006-03-17');
12530
insert into t3 values ('2006-03-17');
12531
insert into t4 values (60,'2006-03-17');
12532
insert into t5 values (60,'2006-03-17');
12533
insert into t6 values (60,'2006-03-17');
12534
select * from t1 order by col1;
12538
select * from t2 order by col1;
12543
select * from t3 order by col1;
12548
select * from t4 order by colint;
12555
select * from t5 order by colint;
12562
select * from t6 order by colint;
12569
alter table t1 drop partition p0;
12570
alter table t2 drop partition p0;
12571
alter table t4 drop partition p0;
12572
alter table t5 drop partition p0;
12573
alter table t6 drop partition p0;
12574
select * from t1 order by col1;
12577
select * from t2 order by col1;
12582
select * from t3 order by col1;
12587
select * from t4 order by colint;
12590
select * from t5 order by colint;
12593
select * from t6 order by colint;
12596
-------------------------------------------------------------------------
12597
--- Delete rows and partitions of tables with weekofyear(col1)
12598
-------------------------------------------------------------------------
12599
delete from t11 where col1='2006-03-17';
12600
delete from t22 where col1='2006-03-17';
12601
delete from t33 where col1='2006-03-17';
12602
delete from t44 where col1='2006-03-17';
12603
delete from t55 where col1='2006-03-17';
12604
delete from t66 where col1='2006-03-17';
12605
select * from t11 order by col1;
12608
select * from t22 order by col1;
12612
select * from t33 order by col1;
12616
select * from t44 order by colint;
12622
select * from t55 order by colint;
12628
insert into t11 values ('2006-03-17');
12629
insert into t22 values ('2006-03-17');
12630
insert into t33 values ('2006-03-17');
12631
insert into t44 values (60,'2006-03-17');
12632
insert into t55 values (60,'2006-03-17');
12633
insert into t66 values (60,'2006-03-17');
12634
select * from t11 order by col1;
12638
select * from t22 order by col1;
12643
select * from t33 order by col1;
12648
select * from t44 order by colint;
12655
select * from t55 order by colint;
12662
select * from t66 order by colint;
12669
alter table t11 drop partition p0;
12670
alter table t22 drop partition p0;
12671
alter table t44 drop partition p0;
12672
alter table t55 drop partition p0;
12673
alter table t66 drop partition p0;
12674
select * from t11 order by col1;
12677
select * from t22 order by col1;
12682
select * from t33 order by col1;
12687
select * from t44 order by colint;
12690
select * from t55 order by colint;
12693
select * from t66 order by colint;
12696
9013
-------------------------
12697
9014
---- some alter table end
12698
9015
-------------------------
added
removed
mysql-test/suite/parts/r/part_supported_sql_func_ndb.result
