diff options
Diffstat (limited to 'storage/rocksdb/mysql-test/rocksdb/r/partition.result')
-rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/r/partition.result | 689 |
1 files changed, 689 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/partition.result b/storage/rocksdb/mysql-test/rocksdb/r/partition.result new file mode 100644 index 00000000..a7f2a611 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/partition.result @@ -0,0 +1,689 @@ +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS VAR_POP; +DROP TABLE IF EXISTS TEMP0; +DROP TABLE IF EXISTS VAR_SAMP; +DROP TABLE IF EXISTS ti; +DROP TABLE IF EXISTS members; +DROP TABLE IF EXISTS members_2; +DROP TABLE IF EXISTS employees; +DROP TABLE IF EXISTS employees_2; +DROP TABLE IF EXISTS employees_3; +DROP TABLE IF EXISTS quarterly_report_status; +DROP TABLE IF EXISTS employees_4; +DROP TABLE IF EXISTS h2; +DROP TABLE IF EXISTS rcx; +DROP TABLE IF EXISTS r1; +DROP TABLE IF EXISTS rc1; +DROP TABLE IF EXISTS rx; +DROP TABLE IF EXISTS rc2; +DROP TABLE IF EXISTS rc3; +DROP TABLE IF EXISTS rc4; +DROP TABLE IF EXISTS employees_by_lname; +DROP TABLE IF EXISTS customers_1; +DROP TABLE IF EXISTS customers_2; +DROP TABLE IF EXISTS customers_3; +DROP TABLE IF EXISTS employees_hash; +DROP TABLE IF EXISTS employees_hash_1; +DROP TABLE IF EXISTS t1_hash; +DROP TABLE IF EXISTS employees_linear_hash; +DROP TABLE IF EXISTS t1_linear_hash; +DROP TABLE IF EXISTS k1; +DROP TABLE IF EXISTS k2; +DROP TABLE IF EXISTS tm1; +DROP TABLE IF EXISTS tk; +DROP TABLE IF EXISTS ts; +DROP TABLE IF EXISTS ts_1; +DROP TABLE IF EXISTS ts_3; +DROP TABLE IF EXISTS ts_4; +DROP TABLE IF EXISTS ts_5; +DROP TABLE IF EXISTS trb3; +DROP TABLE IF EXISTS tr; +DROP TABLE IF EXISTS members_3; +DROP TABLE IF EXISTS clients; +DROP TABLE IF EXISTS clients_lk; +DROP TABLE IF EXISTS trb1; +CREATE TABLE t1 (i INT, j INT, k INT, PRIMARY KEY (i)) ENGINE = ROCKSDB PARTITION BY KEY(i) PARTITIONS 4; +Table Op Msg_type Msg_text +test.t1 optimize status OK +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +Table Op Msg_type Msg_text +test.t1 repair status OK +Table Op Msg_type Msg_text +test.t1 check status OK +select lower(table_name) as tname +from information_schema.tables +where table_schema=database() +order by tname; +tname +t1 +temp0 +var_pop +var_samp +SELECT * FROM t1 ORDER BY i LIMIT 10; +i j k +1 1 1 +2 2 2 +3 3 3 +4 4 4 +5 5 5 +6 6 6 +7 7 7 +8 8 8 +9 9 9 +10 10 10 +SELECT COUNT(*) FROM t1; +COUNT(*) +1000 +CREATE TABLE ti( +id INT, +amount DECIMAL(7,2), +tr_date DATE +) ENGINE=ROCKSDB +PARTITION BY HASH(MONTH(tr_date)) +PARTITIONS 6; +CREATE TABLE members ( +firstname VARCHAR(25) NOT NULL, +lastname VARCHAR(25) NOT NULL, +username VARCHAR(16) NOT NULL, +email VARCHAR(35), +joined DATE NOT NULL +) ENGINE=ROCKSDB +PARTITION BY KEY(joined) +PARTITIONS 6; +CREATE TABLE members_2 ( +firstname VARCHAR(25) NOT NULL, +lastname VARCHAR(25) NOT NULL, +username VARCHAR(16) NOT NULL, +email VARCHAR(35), +joined DATE NOT NULL +) ENGINE=ROCKSDB +PARTITION BY RANGE(YEAR(joined)) ( +PARTITION p0 VALUES LESS THAN (1960), +PARTITION p1 VALUES LESS THAN (1970), +PARTITION p2 VALUES LESS THAN (1980), +PARTITION p3 VALUES LESS THAN (1990), +PARTITION p4 VALUES LESS THAN MAXVALUE +); +CREATE TABLE t2 (val INT) +ENGINE=ROCKSDB +PARTITION BY LIST(val)( +PARTITION mypart VALUES IN (1,3,5), +PARTITION MyPart VALUES IN (2,4,6) +); +ERROR HY000: Duplicate partition name MyPart +CREATE TABLE employees ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT NOT NULL, +store_id INT NOT NULL +) ENGINE=ROCKSDB +PARTITION BY RANGE (store_id) ( +PARTITION p0 VALUES LESS THAN (6), +PARTITION p1 VALUES LESS THAN (11), +PARTITION p2 VALUES LESS THAN (16), +PARTITION p3 VALUES LESS THAN MAXVALUE +); +CREATE TABLE employees_2 ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT NOT NULL, +store_id INT NOT NULL +) ENGINE=ROCKSDB +PARTITION BY RANGE (job_code) ( +PARTITION p0 VALUES LESS THAN (100), +PARTITION p1 VALUES LESS THAN (1000), +PARTITION p2 VALUES LESS THAN (10000) +); +CREATE TABLE employees_3 ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT, +store_id INT +) ENGINE=ROCKSDB +PARTITION BY RANGE (YEAR(separated)) ( +PARTITION p0 VALUES LESS THAN (1991), +PARTITION p1 VALUES LESS THAN (1996), +PARTITION p2 VALUES LESS THAN (2001), +PARTITION p3 VALUES LESS THAN MAXVALUE +); +CREATE TABLE quarterly_report_status ( +report_id INT NOT NULL, +report_status VARCHAR(20) NOT NULL, +report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP +) ENGINE=ROCKSDB +PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) ( +PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), +PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), +PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), +PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), +PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), +PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), +PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), +PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), +PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), +PARTITION p9 VALUES LESS THAN (MAXVALUE) +); +CREATE TABLE employees_4 ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT, +store_id INT +) ENGINE=ROCKSDB +PARTITION BY LIST(store_id) ( +PARTITION pNorth VALUES IN (3,5,6,9,17), +PARTITION pEast VALUES IN (1,2,10,11,19,20), +PARTITION pWest VALUES IN (4,12,13,14,18), +PARTITION pCentral VALUES IN (7,8,15,16) +); +CREATE TABLE h2 ( +c1 INT, +c2 INT +) ENGINE=ROCKSDB +PARTITION BY LIST(c1) ( +PARTITION p0 VALUES IN (1, 4, 7), +PARTITION p1 VALUES IN (2, 5, 8) +); +INSERT INTO h2 VALUES (3, 5); +ERROR HY000: Table has no partition for value 3 +CREATE TABLE rcx ( +a INT, +b INT, +c CHAR(3), +d INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(a,d,c) ( +PARTITION p0 VALUES LESS THAN (5,10,'ggg'), +PARTITION p1 VALUES LESS THAN (10,20,'mmm'), +PARTITION p2 VALUES LESS THAN (15,30,'sss'), +PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) +); +CREATE TABLE r1 ( +a INT, +b INT +) ENGINE=ROCKSDB +PARTITION BY RANGE (a) ( +PARTITION p0 VALUES LESS THAN (5), +PARTITION p1 VALUES LESS THAN (MAXVALUE) +); +INSERT INTO r1 VALUES (5,10), (5,11), (5,12); +CREATE TABLE rc1 ( +a INT, +b INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(a, b) ( +PARTITION p0 VALUES LESS THAN (5, 12), +PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE) +); +INSERT INTO rc1 VALUES (5,10), (5,11), (5,12); +SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12); +(5,10) < (5,12) (5,11) < (5,12) (5,12) < (5,12) +1 1 0 +CREATE TABLE rx ( +a INT, +b INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS (a) ( +PARTITION p0 VALUES LESS THAN (5), +PARTITION p1 VALUES LESS THAN (MAXVALUE) +); +INSERT INTO rx VALUES (5,10), (5,11), (5,12); +CREATE TABLE rc2 ( +a INT, +b INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(a,b) ( +PARTITION p0 VALUES LESS THAN (0,10), +PARTITION p1 VALUES LESS THAN (10,20), +PARTITION p2 VALUES LESS THAN (10,30), +PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) +); +CREATE TABLE rc3 ( +a INT, +b INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(a,b) ( +PARTITION p0 VALUES LESS THAN (0,10), +PARTITION p1 VALUES LESS THAN (10,20), +PARTITION p2 VALUES LESS THAN (10,30), +PARTITION p3 VALUES LESS THAN (10,35), +PARTITION p4 VALUES LESS THAN (20,40), +PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE) +); +CREATE TABLE rc4 ( +a INT, +b INT, +c INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(a,b,c) ( +PARTITION p0 VALUES LESS THAN (0,25,50), +PARTITION p1 VALUES LESS THAN (10,20,100), +PARTITION p2 VALUES LESS THAN (10,30,50), +PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) +); +SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50); +(0,25,50) < (10,20,100) (10,20,100) < (10,30,50) +1 1 +CREATE TABLE rcf ( +a INT, +b INT, +c INT +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(a,b,c) ( +PARTITION p0 VALUES LESS THAN (0,25,50), +PARTITION p1 VALUES LESS THAN (20,20,100), +PARTITION p2 VALUES LESS THAN (10,30,50), +PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) +); +ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition +CREATE TABLE employees_by_lname ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT NOT NULL, +store_id INT NOT NULL +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS (lname) ( +PARTITION p0 VALUES LESS THAN ('g'), +PARTITION p1 VALUES LESS THAN ('m'), +PARTITION p2 VALUES LESS THAN ('t'), +PARTITION p3 VALUES LESS THAN (MAXVALUE) +); +ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname) ( +PARTITION p0 VALUES LESS THAN ('g'), +PARTITION p1 VALUES LESS THAN ('m'), +PARTITION p2 VALUES LESS THAN ('t'), +PARTITION p3 VALUES LESS THAN (MAXVALUE) +); +ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (hired) ( +PARTITION p0 VALUES LESS THAN ('1970-01-01'), +PARTITION p1 VALUES LESS THAN ('1980-01-01'), +PARTITION p2 VALUES LESS THAN ('1990-01-01'), +PARTITION p3 VALUES LESS THAN ('2000-01-01'), +PARTITION p4 VALUES LESS THAN ('2010-01-01'), +PARTITION p5 VALUES LESS THAN (MAXVALUE) +); +CREATE TABLE customers_1 ( +first_name VARCHAR(25), +last_name VARCHAR(25), +street_1 VARCHAR(30), +street_2 VARCHAR(30), +city VARCHAR(15), +renewal DATE +) ENGINE=ROCKSDB +PARTITION BY LIST COLUMNS(city) ( +PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'), +PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'), +PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), +PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo') +); +CREATE TABLE customers_2 ( +first_name VARCHAR(25), +last_name VARCHAR(25), +street_1 VARCHAR(30), +street_2 VARCHAR(30), +city VARCHAR(15), +renewal DATE +) ENGINE=ROCKSDB +PARTITION BY LIST COLUMNS(renewal) ( +PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03', +'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'), +PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10', +'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'), +PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17', +'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'), +PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24', +'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28') +); +CREATE TABLE customers_3 ( +first_name VARCHAR(25), +last_name VARCHAR(25), +street_1 VARCHAR(30), +street_2 VARCHAR(30), +city VARCHAR(15), +renewal DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE COLUMNS(renewal) ( +PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'), +PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'), +PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'), +PARTITION pWeek_4 VALUES LESS THAN('2010-03-01') +); +CREATE TABLE employees_hash ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT, +store_id INT +) ENGINE=ROCKSDB +PARTITION BY HASH(store_id) +PARTITIONS 4; +CREATE TABLE employees_hash_1 ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT, +store_id INT +) ENGINE=ROCKSDB +PARTITION BY HASH( YEAR(hired) ) +PARTITIONS 4; +CREATE TABLE t1_hash ( +col1 INT, +col2 CHAR(5), +col3 DATE +) ENGINE=ROCKSDB +PARTITION BY HASH( YEAR(col3) ) +PARTITIONS 4; +CREATE TABLE employees_linear_hash ( +id INT NOT NULL, +fname VARCHAR(30), +lname VARCHAR(30), +hired DATE NOT NULL DEFAULT '1970-01-01', +separated DATE NOT NULL DEFAULT '9999-12-31', +job_code INT, +store_id INT +) ENGINE=ROCKSDB +PARTITION BY LINEAR HASH( YEAR(hired) ) +PARTITIONS 4; +CREATE TABLE t1_linear_hash ( +col1 INT, +col2 CHAR(5), +col3 DATE +) ENGINE=ROCKSDB +PARTITION BY LINEAR HASH( YEAR(col3) ) +PARTITIONS 6; +CREATE TABLE k1 ( +id INT NOT NULL PRIMARY KEY, +name VARCHAR(20) +) ENGINE=ROCKSDB +PARTITION BY KEY() +PARTITIONS 2; +CREATE TABLE k2 ( +id INT NOT NULL, +name VARCHAR(20), +UNIQUE KEY (id) +) ENGINE=ROCKSDB +PARTITION BY KEY() +PARTITIONS 2; +CREATE TABLE tm1 ( +s1 CHAR(32) PRIMARY KEY +) ENGINE=ROCKSDB +PARTITION BY KEY(s1) +PARTITIONS 10; +CREATE TABLE tk ( +col1 INT NOT NULL, +col2 CHAR(5), +col3 DATE +) ENGINE=ROCKSDB +PARTITION BY LINEAR KEY (col1) +PARTITIONS 3; +CREATE TABLE ts ( +id INT, +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) +SUBPARTITION BY HASH( TO_DAYS(purchased) ) +SUBPARTITIONS 2 ( +PARTITION p0 VALUES LESS THAN (1990), +PARTITION p1 VALUES LESS THAN (2000), +PARTITION p2 VALUES LESS THAN MAXVALUE +); +CREATE TABLE ts_1 ( +id INT, +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) +SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0, +SUBPARTITION s1 +), +PARTITION p1 VALUES LESS THAN (2000) ( +SUBPARTITION s2, +SUBPARTITION s3 +), +PARTITION p2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION s4, +SUBPARTITION s5 +) +); +CREATE TABLE ts_2 ( +id INT, +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) +SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0, +SUBPARTITION s1 +), +PARTITION p1 VALUES LESS THAN (2000), +PARTITION p2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION s2, +SUBPARTITION s3 +) +); +ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' +PARTITION p2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION s2, +SUBPARTITION s3 +) +)' at line 11 +CREATE TABLE ts_3 ( +id INT, +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) +SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0, +SUBPARTITION s1 +), +PARTITION p1 VALUES LESS THAN (2000) ( +SUBPARTITION s2, +SUBPARTITION s3 +), +PARTITION p2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION s4, +SUBPARTITION s5 +) +); +CREATE TABLE ts_4 ( +id INT, +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) +SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0, +SUBPARTITION s1 +), +PARTITION p1 VALUES LESS THAN (2000) ( +SUBPARTITION s2, +SUBPARTITION s3 +), +PARTITION p2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION s4, +SUBPARTITION s5 +) +); +CREATE TABLE ts_5 ( +id INT, +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE(YEAR(purchased)) +SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0a, +SUBPARTITION s0b +), +PARTITION p1 VALUES LESS THAN (2000) ( +SUBPARTITION s1a, +SUBPARTITION s1b +), +PARTITION p2 VALUES LESS THAN MAXVALUE ( +SUBPARTITION s2a, +SUBPARTITION s2b +) +); +CREATE TABLE trb3 ( +id INT, +name VARCHAR(50), +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990), +PARTITION p1 VALUES LESS THAN (1995), +PARTITION p2 VALUES LESS THAN (2000), +PARTITION p3 VALUES LESS THAN (2005) +); +ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; +CREATE TABLE tr ( +id INT, +name VARCHAR(50), +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(purchased) ) ( +PARTITION p0 VALUES LESS THAN (1990), +PARTITION p1 VALUES LESS THAN (1995), +PARTITION p2 VALUES LESS THAN (2000), +PARTITION p3 VALUES LESS THAN (2005) +); +INSERT INTO tr VALUES +(1, 'desk organiser', '2003-10-15'), +(2, 'CD player', '1993-11-05'), +(3, 'TV set', '1996-03-10'), +(4, 'bookcase', '1982-01-10'), +(5, 'exercise bike', '2004-05-09'), +(6, 'sofa', '1987-06-05'), +(7, 'popcorn maker', '2001-11-22'), +(8, 'aquarium', '1992-08-04'), +(9, 'study desk', '1984-09-16'), +(10, 'lava lamp', '1998-12-25'); +SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'; +id name purchased +3 TV set 1996-03-10 +10 lava lamp 1998-12-25 +ALTER TABLE tr DROP PARTITION p2; +SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'; +id name purchased +CREATE TABLE members_3 ( +id INT, +fname VARCHAR(25), +lname VARCHAR(25), +dob DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE( YEAR(dob) ) ( +PARTITION p0 VALUES LESS THAN (1970), +PARTITION p1 VALUES LESS THAN (1980), +PARTITION p2 VALUES LESS THAN (1990) +); +ALTER TABLE members_3 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)); +ALTER TABLE members_3 ADD PARTITION (PARTITION n VALUES LESS THAN (1960)); +ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition +CREATE TABLE clients ( +id INT, +fname VARCHAR(30), +lname VARCHAR(30), +signed DATE +) ENGINE=ROCKSDB +PARTITION BY HASH( MONTH(signed) ) +PARTITIONS 12; +ALTER TABLE clients COALESCE PARTITION 4; +CREATE TABLE clients_lk ( +id INT, +fname VARCHAR(30), +lname VARCHAR(30), +signed DATE +) ENGINE=ROCKSDB +PARTITION BY LINEAR KEY(signed) +PARTITIONS 12; +ALTER TABLE clients COALESCE PARTITION 18; +ERROR HY000: Cannot remove all partitions, use DROP TABLE instead +ALTER TABLE clients ADD PARTITION PARTITIONS 6; +CREATE TABLE trb1 ( +id INT, +name VARCHAR(50), +purchased DATE +) ENGINE=ROCKSDB +PARTITION BY RANGE(id) ( +PARTITION p0 VALUES LESS THAN (3), +PARTITION p1 VALUES LESS THAN (7), +PARTITION p2 VALUES LESS THAN (9), +PARTITION p3 VALUES LESS THAN (11) +); +INSERT INTO trb1 VALUES +(1, 'desk organiser', '2003-10-15'), +(2, 'CD player', '1993-11-05'), +(3, 'TV set', '1996-03-10'), +(4, 'bookcase', '1982-01-10'), +(5, 'exercise bike', '2004-05-09'), +(6, 'sofa', '1987-06-05'), +(7, 'popcorn maker', '2001-11-22'), +(8, 'aquarium', '1992-08-04'), +(9, 'study desk', '1984-09-16'), +(10, 'lava lamp', '1998-12-25'); +ALTER TABLE trb1 ADD PRIMARY KEY (id); +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS VAR_POP; +DROP TABLE IF EXISTS TEMP0; +DROP TABLE IF EXISTS VAR_SAMP; +DROP TABLE IF EXISTS ti; +DROP TABLE IF EXISTS members; +DROP TABLE IF EXISTS members_2; +DROP TABLE IF EXISTS employees; +DROP TABLE IF EXISTS employees_2; +DROP TABLE IF EXISTS employees_3; +DROP TABLE IF EXISTS quarterly_report_status; +DROP TABLE IF EXISTS employees_4; +DROP TABLE IF EXISTS h2; +DROP TABLE IF EXISTS rcx; +DROP TABLE IF EXISTS r1; +DROP TABLE IF EXISTS rc1; +DROP TABLE IF EXISTS rx; +DROP TABLE IF EXISTS rc2; +DROP TABLE IF EXISTS rc3; +DROP TABLE IF EXISTS rc4; +DROP TABLE IF EXISTS employees_by_lname; +DROP TABLE IF EXISTS customers_1; +DROP TABLE IF EXISTS customers_2; +DROP TABLE IF EXISTS customers_3; +DROP TABLE IF EXISTS employees_hash; +DROP TABLE IF EXISTS employees_hash_1; +DROP TABLE IF EXISTS t1_hash; +DROP TABLE IF EXISTS employees_linear_hash; +DROP TABLE IF EXISTS t1_linear_hash; +DROP TABLE IF EXISTS k1; +DROP TABLE IF EXISTS k2; +DROP TABLE IF EXISTS tm1; +DROP TABLE IF EXISTS tk; +DROP TABLE IF EXISTS ts; +DROP TABLE IF EXISTS ts_1; +DROP TABLE IF EXISTS ts_3; +DROP TABLE IF EXISTS ts_4; +DROP TABLE IF EXISTS ts_5; +DROP TABLE IF EXISTS trb3; +DROP TABLE IF EXISTS tr; +DROP TABLE IF EXISTS members_3; +DROP TABLE IF EXISTS clients; +DROP TABLE IF EXISTS clients_lk; +DROP TABLE IF EXISTS trb1; |