--source include/have_rocksdb.inc
--source include/have_partition.inc

#
# Create tables with partitions and try to update/select from them.
#
--disable_warnings
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;

--enable_warnings

CREATE TABLE t1 (i INT, j INT, k INT, PRIMARY KEY (i)) ENGINE = ROCKSDB PARTITION BY KEY(i) PARTITIONS 4;

--disable_query_log
let $max = 1000;
let $i = 1;
while ($i <= $max) {
  let $insert = INSERT INTO t1 VALUES ($i, $i, $i);
  inc $i;
  eval $insert;
}

ALTER TABLE t1 REBUILD PARTITION p0, p1;
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
ALTER TABLE t1 ANALYZE PARTITION p3;
ALTER TABLE t1 REPAIR PARTITION p0,p1;
ALTER TABLE t1 CHECK PARTITION p1;

# Parition string is "#P#". To verify that parsing is done correctly then we'll
# verify if tables containing "P" somwhere can be created correctly.
CREATE TABLE VAR_POP (a int) ENGINE = ROCKSDB;
CREATE TABLE TEMP0 (a int) ENGINE = ROCKSDB PARTITION BY HASH (a) PARTITIONS 3;
CREATE TABLE VAR_SAMP (a int) ENGINE = ROCKSDB PARTITION BY HASH (a) PARTITIONS 10;

--enable_query_log

select lower(table_name) as tname
from information_schema.tables 
where table_schema=database()
order by tname;

SELECT * FROM t1 ORDER BY i LIMIT 10;
SELECT COUNT(*) FROM t1;

#
# Test-cases above are copied from
# https://dev.mysql.com/doc/refman/5.6/en/partitioning.html to validate that the
# partitioning related examples work with MyRocks.
#

# Create a table that is partitioned by hash into 6 partitions.
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
  );

# Partition names are not case-sensitive.
--error 1517
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)
  );

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)
  );

# ERROR 1526 (HY000): Table has no partition for value 3
--error 1526
INSERT INTO h2 VALUES (3, 5);

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);

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);

-- ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

--error 1493
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)
  );

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
      )
    );

--error 1064
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
      )
    );

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';

ALTER TABLE tr DROP PARTITION p2;

SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

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));

# ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
--error 1493
ALTER TABLE members_3 ADD PARTITION (PARTITION n VALUES LESS THAN (1960));

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;

# ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead
--error 1508
ALTER TABLE clients COALESCE PARTITION 18;

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);

# Clean up.
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;