Create 'main' test directory and move 't' and 'r' there

1 files changed, 182 insertions, 0 deletions

diff --git a/mysql-test/main/column_compression_parts.test b/mysql-test/main/column_compression_parts.test
new file mode 100644
index 00000000000..9a6f63d0cde
--- /dev/null
+++ b/mysql-test/main/column_compression_parts.test
@@ -0,0 +1,182 @@
+--source include/have_partition.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+--enable_warnings
+FLUSH STATUS;
+
+echo ---------------------------------------by range------------------------------------------;
+
+CREATE TABLE t1 (i int, a VARCHAR(1000) COMPRESSED DEFAULT "AAA")
+	PARTITION BY RANGE COLUMNS (a)(
+    PARTITION p0 VALUES LESS THAN ('g') COMMENT "p0",
+    PARTITION p1 VALUES LESS THAN ('m'),
+    PARTITION p2 VALUES LESS THAN ('t'),
+    PARTITION p3 VALUES LESS THAN ('w'));
+
+ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (MAXVALUE));
+
+INSERT INTO t1 VALUES (1,REPEAT('a',100)),(2,REPEAT('v',200)),(3,REPEAT('r',300)),(4,NULL);
+INSERT INTO t1 VALUES (5,REPEAT('k',500)),(6,'April'),(7,7),(8,""),(9,"M"),(10,DEFAULT);
+
+ALTER TABLE t1 ANALYZE PARTITION p1;
+ALTER TABLE t1 CHECK PARTITION p2;
+
+SELECT i,SUBSTRING(a,1,10) FROM t1 PARTITION (p2) ORDER BY i;
+SELECT i,SUBSTRING(a,1,10) FROM t1 where length(a)>=300 ORDER BY i;
+SELECT i,SUBSTRING(a,1,10) FROM t1 where a like "%k" ORDER BY i;
+
+DELETE FROM t1 where a="";
+DELETE FROM t1 where a=(REPEAT('a',100));
+DELETE FROM t1 where a like "%v";
+
+SELECT i,SUBSTRING(a,1,10) FROM t1 ORDER BY i;
+SELECT i,SUBSTRING(a,1,10) FROM t1 where a not like "%k" ORDER BY i;
+SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m') ORDER BY i;
+SELECT i,SUBSTRING(a,1,10) FROM t1 where (a between 'h' and 'z') and (i=9) ORDER BY i;
+
+EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
+EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a<'w');
+
+ALTER TABLE t1 TRUNCATE PARTITION p2;
+ALTER TABLE t1 DROP PARTITION p0;
+SHOW CREATE TABLE t1;
+
+ALTER TABLE t1 MODIFY COLUMN a VARCHAR(1000);
+SHOW CREATE TABLE t1;
+
+DROP TABLE t1;
+
+echo ---------------------------------------------------------------------------------------------;
+
+CREATE TABLE t1 (a VARCHAR(1000) COMPRESSED  NOT NULL, id INT)	
+	PARTITION BY RANGE COLUMNS(id,a)(
+	PARTITION p0 VALUES LESS THAN (100,'sss'),
+  	PARTITION p1 VALUES LESS THAN (MAXVALUE,MAXVALUE));
+
+INSERT INTO t1 VALUES (REPEAT('a',100), 23),(REPEAT('v',100),123),(REPEAT('z',100),24),(REPEAT('k',100),124);
+SELECT id,SUBSTRING(a,1,10) FROM t1 order by id;
+SELECT * from t1 partition (p0);
+SELECT * from t1 partition (p1);
+INSERT INTO t1 VALUES (REPEAT('a',100),101);
+SELECT * from t1 partition (p0);
+SELECT * from t1 partition (p1);
+ALTER TABLE t1 DROP PARTITION p1;
+SELECT id,SUBSTRING(a,1,10) FROM t1 WHERE id<50 order by id;
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT INTO t1 VALUES (REPEAT('a',100),101);
+
+ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 'qwerty';
+ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT;
+
+ALTER TABLE t1 MODIFY COLUMN a VARCHAR(1000);
+ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT;
+INSERT INTO t1 VALUES (REPEAT('b',100),11);
+--error ER_NO_DEFAULT_FOR_FIELD
+INSERT INTO t1 VALUES (default,10);
+
+ALTER TABLE t1 MODIFY COLUMN a VARCHAR(1000) COMPRESSED;
+SHOW CREATE TABLE t1;
+SELECT * from t1 ORDER BY id;
+
+DROP TABLE t1;
+
+echo ----------------------------1 partition--------------------------------------------------;
+
+CREATE TABLE t1 (a VARCHAR(1000) COMPRESSED DEFAULT "10-12-2010")
+	PARTITION BY RANGE COLUMNS (a) (partition p0 VALUES LESS THAN (MAXVALUE));
+INSERT INTO t1 VALUES (REPEAT('a',100)), (REPEAT('v',200)), (REPEAT('Z',300)),(NULL),(DEFAULT);
+INSERT INTO t1 VALUES (DEFAULT),(REPEAT('b',200)),(REPEAT('q',300)),(DEFAULT),("MAY");
+
+--sorted_result
+SELECT SUBSTRING(a,1,10) FROM t1;
+
+echo -----------------------------------------by key------------------------------------------;
+
+ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 6;
+SHOW CREATE TABLE t1;
+UPDATE t1 SET a="NEW" where length(a)<20;
+--sorted_result
+SELECT SUBSTRING(a,1,10) FROM t1;
+
+CREATE TABLE t2 (a VARBINARY(1000) COMPRESSED)
+	PARTITION BY KEY(a) PARTITIONS 3;
+ALTER TABLE t2 PARTITION BY LINEAR KEY ALGORITHM=2 (a) PARTITIONS 32;
+
+INSERT INTO t2 VALUES (REPEAT('a',100)),(REPEAT('v',6)),(REPEAT('z',13)),(REPEAT('k',900));
+--sorted_result
+SELECT SUBSTRING(a,1,10) FROM t2;
+
+CREATE TABLE t3 ( a VARCHAR(1000) COMPRESSED DEFAULT NULL)
+	PARTITION BY LINEAR KEY(a) PARTITIONS 3;
+INSERT INTO t3 VALUES (REPEAT('a',100)),(REPEAT('v',100)),(NULL),(REPEAT('k',100)),(DEFAULT);
+--sorted_result
+SELECT SUBSTRING(a,1,10) FROM t3 where a<>NULL;
+
+CREATE TABLE t4 (a VARBINARY(1000) COMPRESSED NOT NULL DEFAULT 0 COMMENT "QQ")
+	PARTITION BY LINEAR KEY(a)
+	PARTITIONS 3;
+INSERT INTO t4 VALUES (REPEAT('a',100)),('0'),(DEFAULT),(DEFAULT),(REPEAT('v',100)),(REPEAT('k',100));
+--sorted_result
+SELECT SUBSTRING(a,1,10) FROM t4 where length(a)>3;
+
+DROP TABLE t1,t2,t3,t4;
+
+echo -----------------------------------subpartitions------------------------------------------;
+
+CREATE TABLE t1 (a VARCHAR(1000) COMPRESSED, id INT NOT NULL)
+    PARTITION BY RANGE(id)
+    	SUBPARTITION BY KEY(a) SUBPARTITIONS 4
+    (PARTITION p0 VALUES LESS THAN (5),
+	 PARTITION p1 VALUES LESS THAN (MAXVALUE));
+INSERT INTO t1 VALUES (REPEAT('a',100),23),(REPEAT('v',100),123),(REPEAT('z',100),24),(REPEAT('k',100),124);
+SELECT id,SUBSTRING(a,1,10) FROM t1 where a=(REPEAT('k',100)) order by id;
+DROP TABLE t1;
+
+echo -------------------------------------------------------------------------------------------;
+
+--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR
+CREATE TABLE t1 (a BLOB COMPRESSED)
+ 	PARTITION BY KEY(a) partitions 30;
+
+CREATE TABLE t1 (a VARCHAR(200) COMPRESSED) PARTITION BY KEY(a) partitions 30;
+ALTER TABLE t1 COALESCE PARTITION 20;
+#ALTER TABLE t1 ADD PARTITION (PARTITION pm TABLESPACE = `innodb_file_per_table`);  --mdev MDEV-13584
+ALTER TABLE t1 ADD PARTITION (PARTITION pm);
+CREATE TABLE t2 like t1;
+ALTER TABLE t2  REMOVE PARTITIONING;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+ALTER TABLE t1 EXCHANGE PARTITION pm WITH TABLE t2;
+DROP TABLE t1,t2;
+echo -------------------------------------------------------------------------------------------;
+
+CREATE TABLE t1 (a BLOB COMPRESSED default 5,i int);
+INSERT INTO t1 VALUES (REPEAT('a',100),1),(REPEAT('v',100),2),(REPEAT('z',100),3),(REPEAT('k',100),2),(2,2);
+
+--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR
+ALTER TABLE t1 PARTITION BY KEY(a) partitions 3;
+
+ALTER TABLE t1 PARTITION BY HASH(i) PARTITIONS 8;
+--error ER_PARTITION_WRONG_VALUES_ERROR
+ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
+    PARTITION s0 VALUES LESS THAN (1960),
+    PARTITION s1 VALUES LESS THAN (1970)
+);
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 REMOVE PARTITIONING;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(500) COMPRESSED default 5,i int)
+	PARTITION BY RANGE COLUMNS(i)(
+    PARTITION p0 VALUES LESS THAN (10),
+    PARTITION p1 VALUES LESS THAN (100),
+    PARTITION p2 VALUES LESS THAN (1000));
+INSERT INTO t1 VALUES (REPEAT('a',100),1),("one",21),(REPEAT('3',100),34),(REPEAT('k',100),267),(2,278);
+
+ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (PARTITION p22 VALUES LESS THAN (MAXVALUE));
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 REBUILD PARTITION p22;
+
+DROP TABLE t1;
\ No newline at end of file