Debian Codegraph @

mysql-test/suite/parts/t/partition_exch_qa_12.test

raw
1 # Author: Horst Hunger
2 # Created: 2010-07-13
3
4
5 let $engine_table= INNODB;
6 let $engine_part= INNODB;
7 let $engine_subpart= INNODB;
8
9 use test;
10
11 --disable_result_log
12 --disable_query_log
13 --disable_warnings
14 DROP TABLE IF EXISTS t_10;
15 DROP TABLE IF EXISTS t_100;
16 DROP TABLE IF EXISTS t_1000;
17 DROP TABLE IF EXISTS tp;
18 DROP TABLE IF EXISTS tsp;
19 DROP TABLE IF EXISTS t_empty;
20 DROP TABLE IF EXISTS t_null;
21 --enable_warnings
22
23 eval CREATE TABLE t_10 (a INT,
24 b VARCHAR(55),
25 PRIMARY KEY (a))
26 CHECKSUM= 1,
27 ENGINE = $engine_table;
28
29 eval CREATE TABLE t_100 (a INT,
30 b VARCHAR(55),
31 PRIMARY KEY (a))
32 COMMENT= 'comment',
33 ROW_FORMAT= COMPRESSED,
34 ENGINE = $engine_table;
35
36 eval CREATE TABLE t_1000 (a INT,
37 b VARCHAR(55),
38 PRIMARY KEY (a))
39 MIN_ROWS= 1,
40 MAX_ROWS= 200,
41 ENGINE = $engine_table;
42
43 eval CREATE TABLE t_empty (a INT,
44 b VARCHAR(55),
45 PRIMARY KEY (a))
46 ENGINE = $engine_table;
47
48 eval CREATE TABLE t_null (a INT,
49 b VARCHAR(55),
50 PRIMARY KEY (a))
51 ENGINE = $engine_table;
52
53 eval CREATE TABLE tp (a INT,
54 b VARCHAR(55),
55 PRIMARY KEY (a))
56 ENGINE = $engine_part
57 PARTITION BY RANGE (a)
58 (PARTITION p0 VALUES LESS THAN (10),
59 PARTITION p1 VALUES LESS THAN (100),
60 PARTITION p2 VALUES LESS THAN (1000));
61
62 eval CREATE TABLE tsp (a INT,
63 b VARCHAR(55),
64 PRIMARY KEY (a))
65 ENGINE = $engine_subpart
66 PARTITION BY RANGE (a)
67 SUBPARTITION BY HASH(a)
68 (PARTITION p0 VALUES LESS THAN (10)
69 (SUBPARTITION sp00,
70 SUBPARTITION sp01,
71 SUBPARTITION sp02,
72 SUBPARTITION sp03,
73 SUBPARTITION sp04),
74 PARTITION p1 VALUES LESS THAN (100)
75 (SUBPARTITION sp10,
76 SUBPARTITION sp11,
77 SUBPARTITION sp12,
78 SUBPARTITION sp13,
79 SUBPARTITION sp14),
80 PARTITION p2 VALUES LESS THAN (1000)
81 (SUBPARTITION sp20,
82 SUBPARTITION sp21,
83 SUBPARTITION sp22,
84 SUBPARTITION sp23,
85 SUBPARTITION sp24));
86
87 # Values t_10 (not partitioned)
88 INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
89
90 # Values t_100 (not partitioned)
91 INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
92 INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
93
94 # Values t_1000 (not partitioned)
95 INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
96 INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
97 INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
98 INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
99
100 # Values t_null (not partitioned)
101 INSERT INTO t_null VALUES (1, "NULL");
102
103 # Values tp (partitions)
104 INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
105 INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
106 INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
107 INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
108 INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
109 INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
110
111 # Values tps (subpartitions)
112 INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
113 INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
114 INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
115 INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
116 INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
117 INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
118
119 eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
120 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
121 eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
122 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
123 eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
124 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
125 eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
126 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
127 eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table
128 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
129
130 SHOW CREATE TABLE t_10;
131 SHOW CREATE TABLE t_100;
132 SHOW CREATE TABLE t_1000;
133 SHOW CREATE TABLE tp;
134 SHOW CREATE TABLE tsp;
135
136 --enable_result_log
137 --enable_query_log
138
139 --sorted_result
140 SELECT * FROM t_10;
141 --sorted_result
142 SELECT * FROM t_100;
143 --sorted_result
144 SELECT * FROM t_1000;
145 --sorted_result
146 SELECT * FROM tp;
147 --sorted_result
148 SELECT * FROM tsp;
149 --sorted_result
150 SELECT * FROM tsp_00;
151 --sorted_result
152 SELECT * FROM tsp_01;
153 --sorted_result
154 SELECT * FROM tsp_02;
155 --sorted_result
156 SELECT * FROM tsp_03;
157 --sorted_result
158 SELECT * FROM tsp_04;
159
160 # 13) Exchanges with different table options.
161 # IGNORE was removed in bug#57708.
162
163 # The alter statement below fails due to bug#23187557.
164 #--error ER_TABLES_DIFFERENT_METADATA
165 #ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
166
167 #--error ER_TABLES_DIFFERENT_METADATA
168 #ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10 IGNORE;
169
170 SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test'
171 AND TABLE_NAME IN ('tp', 't_100');
172 # With innodb, the alter statement below fails due to different row formats.
173 --error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
174 ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
175
176 #--error ER_TABLES_DIFFERENT_METADATA
177 #ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100 IGNORE;
178 --error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
179 ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000;
180 #--error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
181 #ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_1000 IGNORE;
182
183 --source suite/parts/inc/part_exch_drop_tabs.inc
184