github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/partition4.sql (about)

     1  drop table if exists t1;
     2  CREATE TABLE t1 (
     3  col1 INT NOT NULL AUTO_INCREMENT,
     4  col2 DATE NOT NULL,
     5  col3 INT PRIMARY KEY
     6  )
     7  PARTITION BY KEY(col3)
     8  PARTITIONS 4;
     9  
    10  insert into t1 values
    11  (1, '1980-12-17', 7369),
    12  (2, '1981-02-20', 7499),
    13  (3, '1981-02-22', 7521),
    14  (4, '1981-04-02', 7566),
    15  (5, '1981-09-28', 7654),
    16  (6, '1981-05-01', 7698),
    17  (7, '1981-06-09', 7782),
    18  (8, '0087-07-13', 7788),
    19  (9, '1981-11-17', 7839),
    20  (10, '1981-09-08', 7844),
    21  (11, '2007-07-13', 7876),
    22  (12, '1981-12-03', 7900),
    23  (13, '1987-07-13', 7980),
    24  (14, '2001-11-17', 7981),
    25  (15, '1951-11-08', 7982),
    26  (16, '1927-10-13', 7983),
    27  (17, '1671-12-09', 7984),
    28  (18, '1981-11-06', 7985),
    29  (19, '1771-12-06', 7986),
    30  (20, '1985-10-06', 7987),
    31  (21, '1771-10-06', 7988),
    32  (22, '1981-10-05', 7989),
    33  (23, '2001-12-04', 7990),
    34  (24, '1999-08-01', 7991),
    35  (25, '1951-11-08', 7992),
    36  (26, '1927-10-13', 7993),
    37  (27, '1971-12-09', 7994),
    38  (28, '1981-12-09', 7995),
    39  (29, '2001-11-17', 7996),
    40  (30, '1981-12-09', 7997),
    41  (31, '2001-11-17', 7998),
    42  (32, '2001-11-17', 7999);
    43  
    44  select * from t1 order by col1;
    45  
    46  truncate table t1;
    47  select * from t1 order by col1;
    48  select * from `%!%p0%!%t1` order by col1;
    49  select * from `%!%p1%!%t1` order by col1;
    50  select * from `%!%p2%!%t1` order by col1;
    51  select * from `%!%p3%!%t1` order by col1;
    52  
    53  drop table t1;
    54  select * from t1;
    55  select * from `%!%p0%!%t1`;
    56  select * from `%!%p1%!%t1`;
    57  select * from `%!%p2%!%t1`;
    58  select * from `%!%p3%!%t1`;
    59  
    60  -- Partition table fault tolerance test
    61  create table insert_ignore_06 (
    62      sale_id INT AUTO_INCREMENT,
    63      product_id INT,
    64      sale_amount DECIMAL(10, 2),
    65      sale_date DATE,
    66      PRIMARY KEY (sale_id, sale_date)
    67  ) PARTITION BY RANGE (year(sale_date)) (
    68  PARTITION p0 VALUES LESS THAN (1991),
    69  PARTITION p1 VALUES LESS THAN (1992),
    70  PARTITION p2 VALUES LESS THAN (1993),
    71  PARTITION p3 VALUES LESS THAN (1994));
    72  
    73  -- should report error
    74  insert into insert_ignore_06 (product_id, sale_amount, sale_date) VALUES
    75  (1, 1000.00, '1990-04-01'),
    76  (2, 1500.00, '1992-05-01'),
    77  (3, 500.00, '1995-06-01'),
    78  (1, 2000.00, '1991-07-01');
    79  
    80  select * from insert_ignore_06 order by sale_id;
    81  -- should success
    82  insert into insert_ignore_06 (product_id, sale_amount, sale_date) VALUES
    83  (1, 1000.00, '1990-04-01'),
    84  (2, 1500.00, '1992-05-01'),
    85  (1, 2000.00, '1991-07-01');
    86  select * from insert_ignore_06 order by sale_id;
    87  drop table insert_ignore_06;