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

     1  drop database if exists db1;
     2  create database db1;
     3  use db1;
     4  
     5  drop table if exists t1;
     6  CREATE TABLE t1 (
     7                      col1 INT NOT NULL,
     8                      col2 DATE NOT NULL,
     9                      col3 INT PRIMARY KEY
    10  ) PARTITION BY KEY(col3) PARTITIONS 4;
    11  
    12  insert into `t1` values
    13  (1, '1980-12-17', 7369),
    14  (2, '1981-02-20', 7499),
    15  (3, '1981-02-22', 7521),
    16  (4, '1981-04-02', 7566),
    17  (5, '1981-09-28', 7654),
    18  (6, '1981-05-01', 7698),
    19  (7, '1981-06-09', 7782),
    20  (8, '0087-07-13', 7788),
    21  (9, '1981-11-17', 7839),
    22  (10, '1981-09-08', 7844),
    23  (11, '2007-07-13', 7876),
    24  (12, '1981-12-03', 7900),
    25  (13, '1987-07-13', 7980),
    26  (14, '2001-11-17', 7981),
    27  (15, '1951-11-08', 7982),
    28  (16, '1927-10-13', 7983),
    29  (17, '1671-12-09', 7984),
    30  (18, '1981-11-06', 7985),
    31  (19, '1771-12-06', 7986),
    32  (20, '1985-10-06', 7987),
    33  (21, '1771-10-06', 7988),
    34  (22, '1981-10-05', 7989),
    35  (23, '2001-12-04', 7990),
    36  (24, '1999-08-01', 7991),
    37  (25, '1951-11-08', 7992),
    38  (26, '1927-10-13', 7993),
    39  (27, '1971-12-09', 7994),
    40  (28, '1981-12-09', 7995),
    41  (29, '2001-11-17', 7996),
    42  (30, '1981-12-09', 7997),
    43  (31, '2001-11-17', 7998),
    44  (32, '2001-11-17', 7999);
    45  
    46  --命中p0
    47  select * from t1 where col3 = 7990;
    48  --命中p0, p1
    49  select * from t1 where col3 = 7990 or col3 = 7988;
    50  --命中p0, p1, p2
    51  select * from t1 where col3 in (7990, 7698, 7988);
    52  --命中p0
    53  select * from t1 where col3 = 7996 and col1 > 25;
    54  --命中p0, p2
    55  select * from t1 where col1 = 24 and col3 = 7991 or col3 = 7990;
    56  
    57  --无分区裁剪
    58  select * from t1 where col3 > 7992;
    59  --无分区裁剪
    60  select * from t1 where col3 >= 7992;
    61  --无分区裁剪
    62  select * from t1 where col1 > 25;
    63  --无分区裁剪
    64  select * from t1 where col3 != 7782 and col3 != 7980;
    65  --无分区裁剪
    66  select * from t1 where col3 not in (7990, 7698, 7983,7980, 7988, 7995);
    67  --无分区裁剪
    68  select * from t1 where col3 between 7988 and 7990;
    69  --无分区裁剪
    70  select * from t1 where col3 = 7996 or col1 > 25;
    71  
    72  --------------------------------------------------------------------------------------
    73  drop table if exists t2;
    74  CREATE TABLE t2 (
    75                      col1 INT NOT NULL,
    76                      col2 DATE NOT NULL,
    77                      col3 INT NOT NULL,
    78                      PRIMARY KEY(col1, col3)
    79  ) PARTITION BY KEY(col1, col3) PARTITIONS 4;
    80  
    81  insert into `t2` values
    82  (1, '1980-12-17', 7369),
    83  (2, '1981-02-20', 7499),
    84  (3, '1981-02-22', 7521),
    85  (4, '1981-04-02', 7566),
    86  (5, '1981-09-28', 7654),
    87  (6, '1981-05-01', 7698),
    88  (7, '1981-06-09', 7782),
    89  (8, '0087-07-13', 7788),
    90  (9, '1981-11-17', 7839),
    91  (10, '1981-09-08', 7844),
    92  (11, '2007-07-13', 7876),
    93  (12, '1981-12-03', 7900),
    94  (13, '1987-07-13', 7980),
    95  (14, '2001-11-17', 7981),
    96  (15, '1951-11-08', 7982),
    97  (16, '1927-10-13', 7983),
    98  (17, '1671-12-09', 7984),
    99  (18, '1981-11-06', 7985),
   100  (19, '1771-12-06', 7986),
   101  (20, '1985-10-06', 7987),
   102  (21, '1771-10-06', 7988),
   103  (22, '1981-10-05', 7989),
   104  (23, '2001-12-04', 7990),
   105  (24, '1999-08-01', 7991),
   106  (25, '1951-11-08', 7992),
   107  (26, '1927-10-13', 7993),
   108  (27, '1971-12-09', 7994),
   109  (28, '1981-12-09', 7995),
   110  (29, '2001-11-17', 7996),
   111  (30, '1981-12-09', 7997),
   112  (31, '2001-11-17', 7998),
   113  (32, '2001-11-17', 7999);
   114  
   115  --Hit Partition: p0, p2
   116  select * from t2 where ((col1 = 1 and col3 = 7369) or (col1 = 27 and col3 = 7994)) and ((col1 = 1 and col3 = 7369) or (col1 = 29 and col3 = 7996));
   117  --Hit Partition: p0
   118  select * from t2 where ((col1 = 1 and col3 = 7369) or (col1 = 12 and col3 = 7900)) and ((col1 = 1 and col3 = 7369) or (col1 = 29 and col3 = 7996));
   119  --命中,p2
   120  select * from t2 where col1 = 23 and col3 = 7990;
   121  --命中,p2
   122  select * from t2 where col1 = 1 and col3 = 7990;
   123  --无分区裁剪
   124  select * from t2 where col1 = 23 and col3 = 7990 or col1 = 30;
   125  --无分区裁剪
   126  select * from t2 where col1 in(23, 6) and col3 in (7990, 7698, 7988);
   127  --无分区裁剪
   128  select * from t2 where col3 = 7996 and col1 > 25;
   129  --无分区裁剪
   130  select * from t2 where col3 = 7990 or col3 = 7988;
   131  --命中p0, p2
   132  select * from t2 where (col1 = 1 and col3 = 7369) or (col1 = 27 and col3 = 7994);
   133  
   134  ------------------------------------------------------------------------------------------------------------------------
   135  drop table if exists employees;
   136  CREATE TABLE employees (
   137                             id INT NOT NULL,
   138                             fname VARCHAR(30),
   139                             lname VARCHAR(30),
   140                             hired DATE NOT NULL DEFAULT '1970-01-01',
   141                             separated DATE NOT NULL DEFAULT '9999-12-31',
   142                             job_code INT,
   143                             store_id INT
   144  ) PARTITION BY HASH(store_id) PARTITIONS 4;
   145  
   146  
   147  INSERT INTO employees VALUES
   148  (10001, 'Georgi', 'Facello', '1953-09-02','1986-06-26',120, 1),
   149  (10002, 'Bezalel', 'Simmel', '1964-06-02','1985-11-21',150, 7),
   150  (10003, 'Parto', 'Bamford', '1959-12-03','1986-08-28',140, 3),
   151  (10004, 'Chirstian', 'Koblick', '1954-05-01','1986-12-01',150, 3),
   152  (10005, 'Kyoichi', 'Maliniak', '1955-01-21','1989-09-12',150, 18),
   153  (10006, 'Anneke', 'Preusig', '1953-04-20','1989-06-02',150, 15),
   154  (10007, 'Tzvetan', 'Zielinski', '1957-05-23','1989-02-10',110, 6),
   155  (10008, 'Saniya', 'Kalloufi', '1958-02-19','1994-09-15',170, 10),
   156  (10009, 'Sumant', 'Peac', '1952-04-19','1985-02-18',110, 13),
   157  (10010, 'Duangkaew', 'Piveteau', '1963-06-01','1989-08-24',160, 10),
   158  (10011, 'Mary', 'Sluis', '1953-11-07','1990-01-22',120, 8),
   159  (10012, 'Patricio', 'Bridgland', '1960-10-04','1992-12-18',120, 7),
   160  (10013, 'Eberhardt', 'Terkki', '1963-06-07','1985-10-20',160, 17),
   161  (10014, 'Berni', 'Genin', '1956-02-12','1987-03-11',120, 15),
   162  (10015, 'Guoxiang', 'Nooteboom', '1959-08-19','1987-07-02',140, 8),
   163  (10016, 'Kazuhito', 'Cappelletti', '1961-05-02','1995-01-27',140, 2),
   164  (10017, 'Cristinel', 'Bouloucos', '1958-07-06','1993-08-03',170, 10),
   165  (10018, 'Kazuhide', 'Peha', '1954-06-19','1987-04-03',170, 2),
   166  (10019, 'Lillian', 'Haddadi', '1953-01-23','1999-04-30',170, 13),
   167  (10020, 'Mayuko', 'Warwick', '1952-12-24','1991-01-26',120, 1),
   168  (10021, 'Ramzi', 'Erde', '1960-02-20','1988-02-10',120, 9),
   169  (10022, 'Shahaf', 'Famili', '1952-07-08','1995-08-22',130, 10),
   170  (10023, 'Bojan', 'Montemayor', '1953-09-29','1989-12-17',120, 5),
   171  (10024, 'Suzette', 'Pettey', '1958-09-05','1997-05-19',130, 4),
   172  (10025, 'Prasadram', 'Heyers', '1958-10-31','1987-08-17',180, 8),
   173  (10026, 'Yongqiao', 'Berztiss', '1953-04-03','1995-03-20',170, 4),
   174  (10027, 'Divier', 'Reistad', '1962-07-10','1989-07-07',180, 10),
   175  (10028, 'Domenick', 'Tempesti', '1963-11-26','1991-10-22',110, 11),
   176  (10029, 'Otmar', 'Herbst', '1956-12-13','1985-11-20',110, 12),
   177  (10030, 'Elvis', 'Demeyer', '1958-07-14','1994-02-17',110, 1),
   178  (10031, 'Karsten', 'Joslin', '1959-01-27','1991-09-01',110, 10),
   179  (10032, 'Jeong', 'Reistad', '1960-08-09','1990-06-20',120, 19),
   180  (10033, 'Arif', 'Merlo', '1956-11-14','1987-03-18',120, 14),
   181  (10034, 'Bader', 'Swan', '1962-12-29','1988-09-21',130, 16),
   182  (10035, 'Alain', 'Chappelet', '1953-02-08','1988-09-05',130, 3),
   183  (10036, 'Adamantios', 'Portugali', '1959-08-10','1992-01-03',130, 14),
   184  (10037, 'Pradeep', 'Makrucki', '1963-07-22','1990-12-05',140, 12),
   185  (10038, 'Huan', 'Lortz', '1960-07-20','1989-09-20',140, 7),
   186  (10039, 'Alejandro', 'Brender', '1959-10-01','1988-01-19',110, 20),
   187  (10040, 'Weiyi', 'Meriste', '1959-09-13','1993-02-14',140, 17);
   188  
   189  --命中p1
   190  select * from employees where store_id = 8;
   191  --命中 p0, p1
   192  select * from employees where store_id = 8 or store_id = 10;
   193  --命中p3
   194  select * from employees where store_id in (1, 2, 11);
   195  --命中p2, p3
   196  select * from employees where store_id in (1, 2, 6, 7);
   197  --命中p2, p3
   198  select * from employees where store_id in (1, 2, 11) or store_id in (6, 7, 18);
   199  --命中p2, p0
   200  select * from employees where store_id = 3 and id = 10004 or store_id = 10;
   201  --命中p2, p0
   202  select * from employees where (store_id = 3 and id = 10004) or (store_id = 10 and id = 10022);
   203  --无分区裁剪
   204  select * from employees where store_id > 15;
   205  --无分区裁剪
   206  select * from employees where store_id = 10 or id = 10004;
   207  
   208  drop database db1;