github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/ddl/table_partition.test (about)

     1  CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;
     2  show create table tp1;
     3  CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3);
     4  show create table tp2;
     5  CREATE TABLE tp3 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5;
     6  show create table tp3;
     7  CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY ALGORITHM = 1 (col3);
     8  show create table tp4;
     9  CREATE TABLE tp5 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5;
    10  show create table tp5;
    11  CREATE TABLE tp6 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1, col2) PARTITIONS 4;
    12  show create table tp6;
    13  CREATE TABLE tp7 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY KEY(col1) PARTITIONS 4;
    14  show create table tp7;
    15  CREATE TABLE tp8 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1);
    16  show create table tp8;
    17  CREATE TABLE tp9 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1) PARTITIONS 4;
    18  show create table tp9;
    19  CREATE TABLE tp10 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH (YEAR(col3));
    20  show create table tp10;
    21  CREATE TABLE tp11 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6;
    22  show create table tp11;
    23  CREATE TABLE tp12 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY HASH(col1) PARTITIONS 4;
    24  show create table tp12;
    25  CREATE TABLE tp13 (
    26  id INT NOT NULL PRIMARY KEY,
    27  fname VARCHAR(30),
    28  lname VARCHAR(30),
    29  hired DATE NOT NULL DEFAULT '1970-01-01',
    30  separated DATE NOT NULL DEFAULT '9999-12-31',
    31  job_code INT NOT NULL,
    32  store_id INT NOT NULL
    33  )
    34  PARTITION BY RANGE (id) (
    35  PARTITION p0 VALUES LESS THAN (6),
    36  PARTITION p1 VALUES LESS THAN (11),
    37  PARTITION p2 VALUES LESS THAN (16),
    38  PARTITION p3 VALUES LESS THAN (21)
    39  );
    40  show create table tp13;
    41  CREATE TABLE tp14 (
    42  id INT NOT NULL,
    43  fname VARCHAR(30),
    44  lname VARCHAR(30),
    45  hired DATE NOT NULL DEFAULT '1970-01-01',
    46  separated DATE NOT NULL DEFAULT '9999-12-31',
    47  job_code INT,
    48  store_id INT
    49  )
    50  PARTITION BY RANGE ( YEAR(separated) ) (
    51  PARTITION p0 VALUES LESS THAN (1991),
    52  PARTITION p1 VALUES LESS THAN (1996),
    53  PARTITION p2 VALUES LESS THAN (2001),
    54  PARTITION p3 VALUES LESS THAN MAXVALUE
    55  );
    56  show create table tp14;
    57  CREATE TABLE tp15 (
    58  a INT NOT NULL,
    59  b INT NOT NULL
    60  )
    61  PARTITION BY RANGE COLUMNS(a,b) PARTITIONS 4 (
    62  PARTITION p0 VALUES LESS THAN (10,5),
    63  PARTITION p1 VALUES LESS THAN (20,10),
    64  PARTITION p2 VALUES LESS THAN (50,20),
    65  PARTITION p3 VALUES LESS THAN (65,30)
    66  );
    67  show create table tp15;
    68  CREATE TABLE tp16 (
    69  id   INT PRIMARY KEY,
    70  name VARCHAR(35),
    71  age INT unsigned
    72  )
    73  PARTITION BY LIST (id) (
    74  PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    75  PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    76  PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    77  PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
    78  );
    79  show create table tp16;
    80  CREATE TABLE tp17 (
    81  id   INT,
    82  name VARCHAR(35),
    83  age INT unsigned
    84  )
    85  PARTITION BY LIST (id) (
    86  PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    87  PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    88  PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    89  PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
    90  );
    91  show create table tp17;
    92  CREATE TABLE tp18 (
    93  a INT NULL,
    94  b INT NULL
    95  )
    96  PARTITION BY LIST COLUMNS(a,b) (
    97  PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    98  PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    99  PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
   100  PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
   101  );
   102  show create table tp18;
   103  
   104  CREATE TABLE tp19 (
   105  col1 INT NOT NULL,
   106  col2 DATE NOT NULL,
   107  col3 INT NOT NULL UNIQUE,
   108  col4 INT NOT NULL
   109  )
   110  PARTITION BY KEY(col3)
   111  PARTITIONS 4;
   112  show create table tp19;
   113  
   114  CREATE TABLE tp20 (
   115  col1 INT NOT NULL,
   116  col2 DATE NOT NULL,
   117  col3 INT NOT NULL UNIQUE,
   118  col4 INT NOT NULL
   119  )
   120  PARTITION BY HASH(col3)
   121  PARTITIONS 4;
   122  show create table tp20;
   123  
   124  drop table tp1;
   125  drop table tp2;
   126  drop table tp3;
   127  drop table tp4;
   128  drop table tp5;
   129  drop table tp6;
   130  drop table tp7;
   131  drop table tp8;
   132  drop table tp9;
   133  drop table tp10;
   134  drop table tp11;
   135  drop table tp12;
   136  drop table tp13;
   137  drop table tp14;
   138  drop table tp15;
   139  drop table tp16;
   140  drop table tp17;
   141  drop table tp18;
   142  drop table tp19;
   143  drop table tp20;