1# We use a separate test wrapper for engines not supporting native partitioning
2# since the test results will be different due to deprecation warnings. This test
3# expects to either be executed directly with InnoDB as default engine, or to be
4# executed indirectly by being included in a test wrapper  setting the ENGINE
5# variable.
6--disable_warnings
7if (!$ENGINE)
8{
9  let $ENGINE=`SELECT variable_value FROM performance_schema.global_variables
10               WHERE variable_name = 'DEFAULT_STORAGE_ENGINE'`;
11  if ($ENGINE != "InnoDB")
12  {
13    skip Engines not supporting native partitioning are tested using a test wrapper;
14  }
15}
16--enable_warnings
17
18--disable_warnings
19DROP TABLE IF EXISTS t1;
20--enable_warnings
21SET sql_mode='NO_ENGINE_SUBSTITUTION';
22CREATE TABLE t1 (c1 TINYINT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
23INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
24SELECT * FROM t1 ORDER BY c1;
25SHOW TABLES;
26--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
27 SHOW CREATE TABLE t1;
28ALTER TABLE t1 REMOVE PARTITIONING;
29SELECT * FROM t1 ORDER BY c1;
30SHOW TABLES;
31--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
32 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
33CREATE TABLE t1 (c1 SMALLINT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
34INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
35SELECT * FROM t1 ORDER BY c1;
36SHOW TABLES;
37--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
38 SHOW CREATE TABLE t1;
39ALTER TABLE t1 REMOVE PARTITIONING;
40SELECT * FROM t1 ORDER BY c1;
41SHOW TABLES;
42--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
43 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
44CREATE TABLE t1 (c1 MEDIUMINT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
45INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
46SELECT * FROM t1 ORDER BY c1;
47SHOW TABLES;
48--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
49 SHOW CREATE TABLE t1;
50ALTER TABLE t1 REMOVE PARTITIONING;
51SELECT * FROM t1 ORDER BY c1;
52SHOW TABLES;
53--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
54 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
55CREATE TABLE t1 (c1 INT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
56INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
57SELECT * FROM t1 ORDER BY c1;
58SHOW TABLES;
59--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
60 SHOW CREATE TABLE t1;
61ALTER TABLE t1 REMOVE PARTITIONING;
62SELECT * FROM t1 ORDER BY c1;
63SHOW TABLES;
64--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
65 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
66CREATE TABLE t1 (c1 INTEGER NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
67INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
68SELECT * FROM t1 ORDER BY c1;
69SHOW TABLES;
70--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
71 SHOW CREATE TABLE t1;
72ALTER TABLE t1 REMOVE PARTITIONING;
73SELECT * FROM t1 ORDER BY c1;
74SHOW TABLES;
75--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
76 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
77CREATE TABLE t1 (c1 BIGINT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
78INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
79SELECT * FROM t1 ORDER BY c1;
80SHOW TABLES;
81--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
82 SHOW CREATE TABLE t1;
83ALTER TABLE t1 REMOVE PARTITIONING;
84SELECT * FROM t1 ORDER BY c1;
85SHOW TABLES;
86--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
87 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
88CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
89INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
90SELECT * FROM t1 ORDER BY c1;
91SHOW TABLES;
92--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
93 SHOW CREATE TABLE t1;
94ALTER TABLE t1 REMOVE PARTITIONING;
95SELECT * FROM t1 ORDER BY c1;
96SHOW TABLES;
97--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
98 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
99CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
100INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
101SELECT * FROM t1 ORDER BY c1;
102SHOW TABLES;
103--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
104 SHOW CREATE TABLE t1;
105ALTER TABLE t1 REMOVE PARTITIONING;
106SELECT * FROM t1 ORDER BY c1;
107SHOW TABLES;
108--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
109 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
110CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
111INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
112SELECT * FROM t1 ORDER BY c1;
113SHOW TABLES;
114--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
115 SHOW CREATE TABLE t1;
116ALTER TABLE t1 REMOVE PARTITIONING;
117SELECT * FROM t1 ORDER BY c1;
118SHOW TABLES;
119--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
120 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
121CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
122INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
123SELECT * FROM t1 ORDER BY c1;
124SHOW TABLES;
125--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
126 SHOW CREATE TABLE t1;
127ALTER TABLE t1 REMOVE PARTITIONING;
128SELECT * FROM t1 ORDER BY c1;
129SHOW TABLES;
130--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
131 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
132CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
133INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
134SELECT * FROM t1 ORDER BY c1;
135SHOW TABLES;
136--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
137 SHOW CREATE TABLE t1;
138ALTER TABLE t1 REMOVE PARTITIONING;
139SELECT * FROM t1 ORDER BY c1;
140SHOW TABLES;
141--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
142 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
143CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY HASH(c1);
144INSERT INTO t1 VALUES(0,'abc'),(100,'abc'),(200,'abc'),(300,'abc'),(400,'abc');
145SELECT * FROM t1 ORDER BY c1;
146SHOW TABLES;
147--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
148 SHOW CREATE TABLE t1;
149ALTER TABLE t1 REMOVE PARTITIONING;
150SELECT * FROM t1 ORDER BY c1;
151SHOW TABLES;
152--replace_result $ENGINE ENGINE " PAGE_CHECKSUM=0" ""
153 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES;
154SET sql_mode=default;
155