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 21CREATE TABLE t1 (c1 TINYINT,name VARCHAR(30), purchased DATE) 22 PARTITION BY RANGE( YEAR(purchased) ) 23 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 24 PARTITION p0 VALUES LESS THAN (1990) ( 25 SUBPARTITION s0, 26 SUBPARTITION s1 27 ), 28 PARTITION p1 VALUES LESS THAN (2000) ( 29 SUBPARTITION s2, 30 SUBPARTITION s3 31 ), 32 PARTITION p2 VALUES LESS THAN MAXVALUE ( 33 SUBPARTITION s4, 34 SUBPARTITION s5 35 ) 36 ); 37INSERT INTO t1 VALUES(1,'abc','1994-01-01'); 38INSERT INTO t1 VALUES(2,'abc','1995-01-01'); 39INSERT INTO t1 VALUES(3,'abc','1996-01-01'); 40INSERT INTO t1 VALUES(4,'abc','1997-01-01'); 41INSERT INTO t1 VALUES(5,'abc','1998-01-01'); 42INSERT INTO t1 VALUES(6,'abc','1999-01-01'); 43INSERT INTO t1 VALUES(7,'abc','2000-01-01'); 44INSERT INTO t1 VALUES(8,'abc','2001-01-01'); 45INSERT INTO t1 VALUES(9,'abc','2002-01-01'); 46INSERT INTO t1 VALUES(10,'abc','2003-01-01'); 47INSERT INTO t1 VALUES(11,'abc','2004-01-01'); 48INSERT INTO t1 VALUES(12,'abc','2005-01-01'); 49INSERT INTO t1 VALUES(13,'abc','2006-01-01'); 50SELECT * FROM t1 ORDER BY c1; 51SHOW TABLES; 52--replace_result $ENGINE ENGINE 53 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES; 54CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE) 55 PARTITION BY RANGE( YEAR(purchased) ) 56 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 57 PARTITION p0 VALUES LESS THAN (1990) ( 58 SUBPARTITION s0, 59 SUBPARTITION s1 60 ), 61 PARTITION p1 VALUES LESS THAN (2000) ( 62 SUBPARTITION s2, 63 SUBPARTITION s3 64 ), 65 PARTITION p2 VALUES LESS THAN MAXVALUE ( 66 SUBPARTITION s4, 67 SUBPARTITION s5 68 ) 69 ); 70INSERT INTO t1 VALUES(1,'abc','1994-01-01'); 71INSERT INTO t1 VALUES(2,'abc','1995-01-01'); 72INSERT INTO t1 VALUES(3,'abc','1996-01-01'); 73INSERT INTO t1 VALUES(4,'abc','1997-01-01'); 74INSERT INTO t1 VALUES(5,'abc','1998-01-01'); 75INSERT INTO t1 VALUES(6,'abc','1999-01-01'); 76INSERT INTO t1 VALUES(7,'abc','2000-01-01'); 77INSERT INTO t1 VALUES(8,'abc','2001-01-01'); 78INSERT INTO t1 VALUES(9,'abc','2002-01-01'); 79INSERT INTO t1 VALUES(10,'abc','2003-01-01'); 80INSERT INTO t1 VALUES(11,'abc','2004-01-01'); 81INSERT INTO t1 VALUES(12,'abc','2005-01-01'); 82INSERT INTO t1 VALUES(13,'abc','2006-01-01'); 83SELECT * FROM t1 ORDER BY c1; 84SHOW TABLES; 85--replace_result $ENGINE ENGINE 86 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES; 87CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE) 88 PARTITION BY RANGE( YEAR(purchased) ) 89 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 90 PARTITION p0 VALUES LESS THAN (1990) ( 91 SUBPARTITION s0, 92 SUBPARTITION s1 93 ), 94 PARTITION p1 VALUES LESS THAN (2000) ( 95 SUBPARTITION s2, 96 SUBPARTITION s3 97 ), 98 PARTITION p2 VALUES LESS THAN MAXVALUE ( 99 SUBPARTITION s4, 100 SUBPARTITION s5 101 ) 102 ); 103INSERT INTO t1 VALUES(1,'abc','1994-01-01'); 104INSERT INTO t1 VALUES(2,'abc','1995-01-01'); 105INSERT INTO t1 VALUES(3,'abc','1996-01-01'); 106INSERT INTO t1 VALUES(4,'abc','1997-01-01'); 107INSERT INTO t1 VALUES(5,'abc','1998-01-01'); 108INSERT INTO t1 VALUES(6,'abc','1999-01-01'); 109INSERT INTO t1 VALUES(7,'abc','2000-01-01'); 110INSERT INTO t1 VALUES(8,'abc','2001-01-01'); 111INSERT INTO t1 VALUES(9,'abc','2002-01-01'); 112INSERT INTO t1 VALUES(10,'abc','2003-01-01'); 113INSERT INTO t1 VALUES(11,'abc','2004-01-01'); 114INSERT INTO t1 VALUES(12,'abc','2005-01-01'); 115INSERT INTO t1 VALUES(13,'abc','2006-01-01'); 116SELECT * FROM t1 ORDER BY c1; 117SHOW TABLES; 118--replace_result $ENGINE ENGINE 119 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES; 120CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE) 121 PARTITION BY RANGE( YEAR(purchased) ) 122 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 123 PARTITION p0 VALUES LESS THAN (1990) ( 124 SUBPARTITION s0, 125 SUBPARTITION s1 126 ), 127 PARTITION p1 VALUES LESS THAN (2000) ( 128 SUBPARTITION s2, 129 SUBPARTITION s3 130 ), 131 PARTITION p2 VALUES LESS THAN MAXVALUE ( 132 SUBPARTITION s4, 133 SUBPARTITION s5 134 ) 135 ); 136INSERT INTO t1 VALUES(1,'abc','1994-01-01'); 137INSERT INTO t1 VALUES(2,'abc','1995-01-01'); 138INSERT INTO t1 VALUES(3,'abc','1996-01-01'); 139INSERT INTO t1 VALUES(4,'abc','1997-01-01'); 140INSERT INTO t1 VALUES(5,'abc','1998-01-01'); 141INSERT INTO t1 VALUES(6,'abc','1999-01-01'); 142INSERT INTO t1 VALUES(7,'abc','2000-01-01'); 143INSERT INTO t1 VALUES(8,'abc','2001-01-01'); 144INSERT INTO t1 VALUES(9,'abc','2002-01-01'); 145INSERT INTO t1 VALUES(10,'abc','2003-01-01'); 146INSERT INTO t1 VALUES(11,'abc','2004-01-01'); 147INSERT INTO t1 VALUES(12,'abc','2005-01-01'); 148INSERT INTO t1 VALUES(13,'abc','2006-01-01'); 149SELECT * FROM t1 ORDER BY c1; 150SHOW TABLES; 151--replace_result $ENGINE ENGINE 152 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES; 153CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE) 154 PARTITION BY RANGE( YEAR(purchased) ) 155 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 156 PARTITION p0 VALUES LESS THAN (1990) ( 157 SUBPARTITION s0, 158 SUBPARTITION s1 159 ), 160 PARTITION p1 VALUES LESS THAN (2000) ( 161 SUBPARTITION s2, 162 SUBPARTITION s3 163 ), 164 PARTITION p2 VALUES LESS THAN MAXVALUE ( 165 SUBPARTITION s4, 166 SUBPARTITION s5 167 ) 168 ); 169INSERT INTO t1 VALUES(1,'abc','1994-01-01'); 170INSERT INTO t1 VALUES(2,'abc','1995-01-01'); 171INSERT INTO t1 VALUES(3,'abc','1996-01-01'); 172INSERT INTO t1 VALUES(4,'abc','1997-01-01'); 173INSERT INTO t1 VALUES(5,'abc','1998-01-01'); 174INSERT INTO t1 VALUES(6,'abc','1999-01-01'); 175INSERT INTO t1 VALUES(7,'abc','2000-01-01'); 176INSERT INTO t1 VALUES(8,'abc','2001-01-01'); 177INSERT INTO t1 VALUES(9,'abc','2002-01-01'); 178INSERT INTO t1 VALUES(10,'abc','2003-01-01'); 179INSERT INTO t1 VALUES(11,'abc','2004-01-01'); 180INSERT INTO t1 VALUES(12,'abc','2005-01-01'); 181INSERT INTO t1 VALUES(13,'abc','2006-01-01'); 182SELECT * FROM t1 ORDER BY c1; 183SHOW TABLES; 184--replace_result $ENGINE ENGINE 185 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES; 186CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE) 187 PARTITION BY RANGE( YEAR(purchased) ) 188 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 189 PARTITION p0 VALUES LESS THAN (1990) ( 190 SUBPARTITION s0, 191 SUBPARTITION s1 192 ), 193 PARTITION p1 VALUES LESS THAN (2000) ( 194 SUBPARTITION s2, 195 SUBPARTITION s3 196 ), 197 PARTITION p2 VALUES LESS THAN MAXVALUE ( 198 SUBPARTITION s4, 199 SUBPARTITION s5 200 ) 201 ); 202INSERT INTO t1 VALUES(1,'abc','1994-01-01'); 203INSERT INTO t1 VALUES(2,'abc','1995-01-01'); 204INSERT INTO t1 VALUES(3,'abc','1996-01-01'); 205INSERT INTO t1 VALUES(4,'abc','1997-01-01'); 206INSERT INTO t1 VALUES(5,'abc','1998-01-01'); 207INSERT INTO t1 VALUES(6,'abc','1999-01-01'); 208INSERT INTO t1 VALUES(7,'abc','2000-01-01'); 209INSERT INTO t1 VALUES(8,'abc','2001-01-01'); 210INSERT INTO t1 VALUES(9,'abc','2002-01-01'); 211INSERT INTO t1 VALUES(10,'abc','2003-01-01'); 212INSERT INTO t1 VALUES(11,'abc','2004-01-01'); 213INSERT INTO t1 VALUES(12,'abc','2005-01-01'); 214INSERT INTO t1 VALUES(13,'abc','2006-01-01'); 215SELECT * FROM t1 ORDER BY c1; 216SHOW TABLES; 217--replace_result $ENGINE ENGINE 218 SHOW CREATE TABLE t1; DROP TABLE t1; SHOW TABLES; 219 220