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