Debian Codegraph @

mysql-test/suite/archive/partition_archive.result

raw
1 CREATE TABLE t1 (f1 DATE NOT NULL)
2 ENGINE = ARCHIVE PARTITION BY RANGE (TO_DAYS(f1))
3 (partition p1 values less than (733751),
4 partition p2 values less than MAXVALUE);
5 INSERT INTO t1 VALUES(CURRENT_DATE);
6 SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
7 DATA_LENGTH INDEX_LENGTH
8 190 0
9 SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
10 DATA_LENGTH INDEX_LENGTH
11 190 0
12 DROP TABLE t1;
13 CREATE TABLE t1 (f1 DATE NOT NULL)
14 ENGINE = ARCHIVE;
15 INSERT INTO t1 VALUES(CURRENT_DATE);
16 SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
17 DATA_LENGTH INDEX_LENGTH
18 535 0
19 SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
20 DATA_LENGTH INDEX_LENGTH
21 535 0
22 DROP TABLE t1;
23 drop database if exists db99;
24 drop table if exists t1;
25 create database db99;
26 use db99;
27 create table t1 (a int not null)
28 engine=archive
29 partition by list (a)
30 (partition p0 values in (1), partition p1 values in (2));
31 insert into t1 values (1), (2);
32 create index inx on t1 (a);
33 ERROR HY000: Can't create table `db99`.`t1` (errno: 140 "Wrong create options")
34 alter table t1 add partition (partition p2 values in (3));
35 alter table t1 drop partition p2;
36 use test;
37 drop database db99;
38 create table t1 (f1 integer) engine= ARCHIVE partition by list(f1)
39 (
40 partition p1 values in (1),
41 partition p2 values in (NULL),
42 partition p3 values in (2),
43 partition p4 values in (3),
44 partition p5 values in (4)
45 );
46 insert into t1 values (1),(2),(3),(4),(null);
47 select * from t1;
48 f1
49 1
50 NULL
51 2
52 3
53 4
54 select * from t1 where f1 < 3;
55 f1
56 1
57 2
58 drop table t1;
59 CREATE TABLE t1 (
60 a int not null,
61 b int not null,
62 c int not null) engine=ARCHIVE
63 partition by hash (a + 2)
64 partitions 3
65 (partition x1 tablespace ts1,
66 partition x2 tablespace ts2,
67 partition x3 tablespace ts3);
68 insert into t1 values (1,1,1);
69 insert into t1 values (2,1,1);
70 insert into t1 values (3,1,1);
71 insert into t1 values (4,1,1);
72 insert into t1 values (5,1,1);
73 select * from t1;
74 a b c
75 1 1 1
76 4 1 1
77 2 1 1
78 5 1 1
79 3 1 1
80 drop table t1;
81 create table t1 (a int) engine=archive partition by hash(a);
82 show create table t1;
83 Table Create Table
84 t1 CREATE TABLE `t1` (
85 `a` int(11) DEFAULT NULL
86 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
87 PARTITION BY HASH (`a`)
88 drop table t1;
89 CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
90 f1 VARCHAR(25),
91 PRIMARY KEY(id)) ENGINE=ARCHIVE
92 PARTITION BY RANGE(id)
93 SUBPARTITION BY hash(id) subpartitions 2
94 (PARTITION pa1 values less than (10),
95 PARTITION pa2 values less than (20),
96 PARTITION pa3 values less than (30),
97 PARTITION pa4 values less than (40),
98 PARTITION pa5 values less than (50),
99 PARTITION pa6 values less than (60),
100 PARTITION pa7 values less than (70),
101 PARTITION pa8 values less than (80),
102 PARTITION pa9 values less than (90),
103 PARTITION pa10 values less than (100),
104 PARTITION pa11 values less than MAXVALUE);
105 show create table t1;
106 Table Create Table
107 t1 CREATE TABLE `t1` (
108 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
109 `f1` varchar(25) DEFAULT NULL,
110 PRIMARY KEY (`id`)
111 ) ENGINE=ARCHIVE AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
112 PARTITION BY RANGE (`id`)
113 SUBPARTITION BY HASH (`id`)
114 SUBPARTITIONS 2
115 (PARTITION `pa1` VALUES LESS THAN (10) ENGINE = ARCHIVE,
116 PARTITION `pa2` VALUES LESS THAN (20) ENGINE = ARCHIVE,
117 PARTITION `pa3` VALUES LESS THAN (30) ENGINE = ARCHIVE,
118 PARTITION `pa4` VALUES LESS THAN (40) ENGINE = ARCHIVE,
119 PARTITION `pa5` VALUES LESS THAN (50) ENGINE = ARCHIVE,
120 PARTITION `pa6` VALUES LESS THAN (60) ENGINE = ARCHIVE,
121 PARTITION `pa7` VALUES LESS THAN (70) ENGINE = ARCHIVE,
122 PARTITION `pa8` VALUES LESS THAN (80) ENGINE = ARCHIVE,
123 PARTITION `pa9` VALUES LESS THAN (90) ENGINE = ARCHIVE,
124 PARTITION `pa10` VALUES LESS THAN (100) ENGINE = ARCHIVE,
125 PARTITION `pa11` VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
126 select count(*) from t1;
127 count(*)
128 100
129 drop table t1;
130 #
131 #BUG 18618561: FAILED ALTER TABLE ENGINE CHANGE WITH PARTITIONS
132 # CORRUPTS FRM
133 CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= MYISAM PARTITION BY HASH(fld1)
134 PARTITIONS 5;
135 SHOW CREATE TABLE t1;
136 Table Create Table
137 t1 CREATE TABLE `t1` (
138 `fld1` int(11) NOT NULL,
139 PRIMARY KEY (`fld1`)
140 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
141 PARTITION BY HASH (`fld1`)
142 PARTITIONS 5
143 ALTER TABLE t1 ENGINE= ARCHIVE;
144 ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options")
145 #After the patch, the ENGINE is correctly displayed as MyISAM
146 SHOW CREATE TABLE t1;
147 Table Create Table
148 t1 CREATE TABLE `t1` (
149 `fld1` int(11) NOT NULL,
150 PRIMARY KEY (`fld1`)
151 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
152 PARTITION BY HASH (`fld1`)
153 PARTITIONS 5
154 #Cleanup.
155 DROP TABLE t1;
156 create database mysqltest1;
157 create table mysqltest1.t1 (a int not null, b int not null) engine=archive
158 partition by list(a) subpartition by hash(b)
159 (partition p1 values in (1),
160 partition p2 values in (2));
161 drop database mysqltest1;