EVOLUTION-MANAGER

Edit File: part_file.result

set @@global.connect_exact_info=ON; # This will be used to see what data files are created CREATE TABLE dr1 ( fname VARCHAR(256) NOT NULL FLAG=2, ftype CHAR(8) NOT NULL FLAG=3 # ,FSIZE INT(6) NOT NULL FLAG=5 removed because Unix size != Windows size ) engine=CONNECT table_type=DIR file_name='t1#P#*.*'; # # Testing partitioning on inward table # CREATE TABLE t1 ( id INT NOT NULL, msg VARCHAR(32) ) ENGINE=CONNECT TABLE_TYPE=CSV AVG_ROW_LENGTH=10 PARTITION BY RANGE(id) ( PARTITION first VALUES LESS THAN(10), PARTITION middle VALUES LESS THAN(50), PARTITION last VALUES LESS THAN(MAXVALUE)); INSERT INTO t1 VALUES(4, 'four'),(24, 'twenty four'); INSERT INTO t1 VALUES(7,'seven'),(10,'ten'),(40,'forty'),(60,'sixty'),(81,'eighty one'); SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1'; partition_name table_rows first 2 middle 3 last 2 SELECT * FROM t1; id msg 4 four 7 seven 24 twenty four 10 ten 40 forty 60 sixty 81 eighty one EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id > 50; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 last ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t1 WHERE id > 50; id msg 60 sixty 81 eighty one UPDATE t1 set id = 41 WHERE msg = 'four'; ERROR HY000: Got error 174 'Cannot update column id because it is used for partitioning' from CONNECT UPDATE t1 set msg = 'quatre' WHERE id = 4; SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype t1#P#first .csv t1#P#last .csv t1#P#middle .csv # # Altering partitioning on inward table # ALTER TABLE t1 PARTITION by range(id) ( PARTITION first VALUES LESS THAN(11), PARTITION middle VALUES LESS THAN(50), PARTITION last VALUES LESS THAN(MAXVALUE)); SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1'; partition_name table_rows first 3 middle 2 last 2 SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype t1#P#first .csv t1#P#last .csv t1#P#middle .csv EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id=10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 first ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t1 WHERE id=10; id msg 10 ten DELETE FROM t1 WHERE id in (4,60); SELECT * FROM t1; id msg 7 seven 10 ten 24 twenty four 40 forty 81 eighty one DROP TABLE t1; # # Testing partitioning on a void outward table # ALTER TABLE dr1 FILE_NAME='part*.*'; CREATE TABLE t1 ( rwid INT(6) DEFAULT 0 SPECIAL=ROWID, rnum INT(6) DEFAULT 0 SPECIAL=ROWNUM, prtn VARCHAR(64) DEFAULT '' SPECIAL=PARTID, tbn VARCHAR(64) DEFAULT '' SPECIAL=TABID, fid VARCHAR(256) DEFAULT '' SPECIAL=FNAME, id INT KEY NOT NULL, msg VARCHAR(32) ) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='part%s.txt'; ALTER TABLE t1 PARTITION by range columns(id) ( PARTITION `1` VALUES LESS THAN(10), PARTITION `2` VALUES LESS THAN(50), PARTITION `3` VALUES LESS THAN(MAXVALUE)); SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 id A NULL NULL NULL XINDEX INSERT INTO t1(id,msg) VALUES(4, 'four'); SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt INSERT INTO t1(id,msg) VALUES(7,'seven'),(10,'ten'),(40,'forty'),(60,'sixty'),(81,'eighty one'); INSERT INTO t1(id,msg) VALUES(72,'seventy two'),(20,'twenty'),(1,'one'),(35,'thirty five'),(8,'eight'); SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1'; partition_name table_rows 1 4 2 4 3 3 SELECT * FROM t1; rwid rnum prtn tbn fid id msg 1 1 1 t1 part1 4 four 2 2 1 t1 part1 7 seven 3 3 1 t1 part1 1 one 4 4 1 t1 part1 8 eight 1 1 2 t1 part2 10 ten 2 2 2 t1 part2 40 forty 3 3 2 t1 part2 20 twenty 4 4 2 t1 part2 35 thirty five 1 1 3 t1 part3 60 sixty 2 2 3 t1 part3 81 eighty one 3 3 3 t1 part3 72 seventy two SELECT * FROM t1 order by id; rwid rnum prtn tbn fid id msg 3 3 1 t1 part1 1 one 1 1 1 t1 part1 4 four 2 2 1 t1 part1 7 seven 4 4 1 t1 part1 8 eight 1 1 2 t1 part2 10 ten 3 3 2 t1 part2 20 twenty 4 4 2 t1 part2 35 thirty five 2 2 2 t1 part2 40 forty 1 1 3 t1 part3 60 sixty 3 3 3 t1 part3 72 seventy two 2 2 3 t1 part3 81 eighty one EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 2 const PRIMARY PRIMARY 4 const 1 SELECT * FROM t1 WHERE id = 10; rwid rnum prtn tbn fid id msg 1 1 2 t1 part2 10 ten EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id >= 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 2,3 range PRIMARY PRIMARY 4 NULL 7 Using where SELECT * FROM t1 WHERE id >= 10; rwid rnum prtn tbn fid id msg 1 1 2 t1 part2 10 ten 3 3 2 t1 part2 20 twenty 4 4 2 t1 part2 35 thirty five 2 2 2 t1 part2 40 forty 1 1 3 t1 part3 60 sixty 3 3 3 t1 part3 72 seventy two 2 2 3 t1 part3 81 eighty one SELECT count(*) FROM t1 WHERE id < 10; count(*) 4 SELECT case when id < 10 then 1 when id < 50 then 2 else 3 end as pn, count(*) FROM t1 group by pn; pn count(*) 1 4 2 4 3 3 SELECT prtn, count(*) FROM t1 group by prtn; prtn count(*) 1 4 2 4 3 3 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id > 50; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 3 range PRIMARY PRIMARY 4 NULL 3 Using where SELECT * FROM t1 WHERE id = 35; rwid rnum prtn tbn fid id msg 4 4 2 t1 part2 35 thirty five SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt part2 .fnx part2 .txt part3 .fnx part3 .txt # This does not change the partition file data and is WRONG ALTER TABLE t1 PARTITION by range columns(id) ( PARTITION `1` VALUES LESS THAN(11), PARTITION `2` VALUES LESS THAN(70), PARTITION `3` VALUES LESS THAN(MAXVALUE)); Warnings: Warning 1105 Data repartition in 1 is unchecked Warning 1105 Data repartition in 2 is unchecked Warning 1105 Data repartition in 3 is unchecked SELECT CASE WHEN id < 11 THEN 1 WHEN id < 70 THEN 2 ELSE 3 END AS pn, COUNT(*) FROM t1 GROUP BY pn; pn COUNT(*) 1 5 2 4 3 2 SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1'; partition_name table_rows 1 4 2 4 3 3 SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt part2 .fnx part2 .txt part3 .fnx part3 .txt # # This is the correct way to change partitioning: # Save table values, erase the table, then re-insert saved values in modified table # CREATE TABLE t2 ( id INT NOT NULL, msg VARCHAR(32) ) ENGINE=CONNECT TABLE_TYPE=FIX; Warnings: Warning 1105 No file name. Table will use t2.fix INSERT INTO t2 SELECT id, msg FROM t1; DELETE FROM t1; INSERT INTO t1(id,msg) SELECT * FROM t2; SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1'; partition_name table_rows 1 5 2 4 3 2 SELECT * FROM t1; rwid rnum prtn tbn fid id msg 1 1 1 t1 part1 4 four 2 2 1 t1 part1 7 seven 3 3 1 t1 part1 1 one 4 4 1 t1 part1 8 eight 5 5 1 t1 part1 10 ten 1 1 2 t1 part2 40 forty 2 2 2 t1 part2 20 twenty 3 3 2 t1 part2 35 thirty five 4 4 2 t1 part2 60 sixty 1 1 3 t1 part3 81 eighty one 2 2 3 t1 part3 72 seventy two SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt part2 .fnx part2 .txt part3 .fnx part3 .txt DROP TABLE t2; DROP TABLE t1; # # Testing partitioning on a populated outward table # CREATE TABLE t1 ( id INT NOT NULL, msg VARCHAR(32) ) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='part%s.txt' PARTITION by range columns(id) ( PARTITION `1` VALUES LESS THAN(11), PARTITION `2` VALUES LESS THAN(70), PARTITION `3` VALUES LESS THAN(MAXVALUE)); Warnings: Warning 1105 Data repartition in 1 is unchecked Warning 1105 Data repartition in 2 is unchecked Warning 1105 Data repartition in 3 is unchecked SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1'; partition_name table_rows 1 5 2 4 3 2 SELECT * FROM t1 WHERE id < 11; id msg 4 four 7 seven 1 one 8 eight 10 ten SELECT * FROM t1 WHERE id >= 70; id msg 81 eighty one 72 seventy two SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt part2 .fnx part2 .txt part3 .fnx part3 .txt # # Testing indexing on a partitioned table # CREATE INDEX XID ON t1(id); SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 1 XID 1 id A NULL NULL NULL XINDEX SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt part2 .fnx part2 .txt part3 .fnx part3 .txt EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 1 ref XID XID 4 const 1 DROP INDEX XID ON t1; SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .txt part2 .txt part3 .txt ALTER TABLE t1 ADD PRIMARY KEY (id); SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 id A NULL NULL NULL XINDEX SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .fnx part1 .txt part2 .fnx part2 .txt part3 .fnx part3 .txt EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 1 const PRIMARY PRIMARY 4 const 1 ALTER TABLE t1 DROP PRIMARY KEY; SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment SELECT * FROM dr1 ORDER BY fname, ftype; fname ftype part1 .txt part2 .txt part3 .txt DROP TABLE t1; DROP TABLE dr1; set @@global.connect_exact_info=OFF;