Debian Codegraph @

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

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