github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/partition_prune.result (about) 1 drop database if exists db1; 2 create database db1; 3 use db1; 4 drop table if exists t1; 5 CREATE TABLE t1 ( 6 col1 INT NOT NULL, 7 col2 DATE NOT NULL, 8 col3 INT PRIMARY KEY 9 ) PARTITION BY KEY(col3) PARTITIONS 4; 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 select * from t1 where col3 = 7990; 44 col1 col2 col3 45 23 2001-12-04 7990 46 select * from t1 where col3 = 7990 or col3 = 7988; 47 col1 col2 col3 48 23 2001-12-04 7990 49 21 1771-10-06 7988 50 select * from t1 where col3 in (7990, 7698, 7988); 51 col1 col2 col3 52 23 2001-12-04 7990 53 6 1981-05-01 7698 54 21 1771-10-06 7988 55 select * from t1 where col3 = 7996 and col1 > 25; 56 col1 col2 col3 57 29 2001-11-17 7996 58 select * from t1 where col1 = 24 and col3 = 7991 or col3 = 7990; 59 col1 col2 col3 60 24 1999-08-01 7991 61 23 2001-12-04 7990 62 select * from t1 where col3 > 7992; 63 col1 col2 col3 64 27 1971-12-09 7994 65 28 1981-12-09 7995 66 29 2001-11-17 7996 67 31 2001-11-17 7998 68 26 1927-10-13 7993 69 30 1981-12-09 7997 70 32 2001-11-17 7999 71 select * from t1 where col3 >= 7992; 72 col1 col2 col3 73 25 1951-11-08 7992 74 27 1971-12-09 7994 75 28 1981-12-09 7995 76 29 2001-11-17 7996 77 31 2001-11-17 7998 78 26 1927-10-13 7993 79 30 1981-12-09 7997 80 32 2001-11-17 7999 81 select * from t1 where col1 > 25; 82 col1 col2 col3 83 27 1971-12-09 7994 84 28 1981-12-09 7995 85 29 2001-11-17 7996 86 31 2001-11-17 7998 87 26 1927-10-13 7993 88 30 1981-12-09 7997 89 32 2001-11-17 7999 90 select * from t1 where col3 != 7782 and col3 != 7980; 91 col1 col2 col3 92 9 1981-11-17 7839 93 12 1981-12-03 7900 94 20 1985-10-06 7987 95 23 2001-12-04 7990 96 25 1951-11-08 7992 97 27 1971-12-09 7994 98 28 1981-12-09 7995 99 29 2001-11-17 7996 100 31 2001-11-17 7998 101 1 1980-12-17 7369 102 19 1771-12-06 7986 103 21 1771-10-06 7988 104 22 1981-10-05 7989 105 5 1981-09-28 7654 106 6 1981-05-01 7698 107 10 1981-09-08 7844 108 16 1927-10-13 7983 109 17 1671-12-09 7984 110 24 1999-08-01 7991 111 26 1927-10-13 7993 112 30 1981-12-09 7997 113 32 2001-11-17 7999 114 2 1981-02-20 7499 115 3 1981-02-22 7521 116 4 1981-04-02 7566 117 8 0087-07-13 7788 118 11 2007-07-13 7876 119 14 2001-11-17 7981 120 15 1951-11-08 7982 121 18 1981-11-06 7985 122 select * from t1 where col3 not in (7990, 7698, 7983,7980, 7988, 7995); 123 col1 col2 col3 124 9 1981-11-17 7839 125 12 1981-12-03 7900 126 20 1985-10-06 7987 127 25 1951-11-08 7992 128 27 1971-12-09 7994 129 29 2001-11-17 7996 130 31 2001-11-17 7998 131 1 1980-12-17 7369 132 19 1771-12-06 7986 133 22 1981-10-05 7989 134 5 1981-09-28 7654 135 10 1981-09-08 7844 136 17 1671-12-09 7984 137 24 1999-08-01 7991 138 26 1927-10-13 7993 139 30 1981-12-09 7997 140 32 2001-11-17 7999 141 2 1981-02-20 7499 142 3 1981-02-22 7521 143 4 1981-04-02 7566 144 7 1981-06-09 7782 145 8 0087-07-13 7788 146 11 2007-07-13 7876 147 14 2001-11-17 7981 148 15 1951-11-08 7982 149 18 1981-11-06 7985 150 select * from t1 where col3 between 7988 and 7990; 151 col1 col2 col3 152 23 2001-12-04 7990 153 21 1771-10-06 7988 154 22 1981-10-05 7989 155 select * from t1 where col3 = 7996 or col1 > 25; 156 col1 col2 col3 157 27 1971-12-09 7994 158 28 1981-12-09 7995 159 29 2001-11-17 7996 160 31 2001-11-17 7998 161 26 1927-10-13 7993 162 30 1981-12-09 7997 163 32 2001-11-17 7999 164 drop table if exists t2; 165 CREATE TABLE t2 ( 166 col1 INT NOT NULL, 167 col2 DATE NOT NULL, 168 col3 INT NOT NULL, 169 PRIMARY KEY(col1, col3) 170 ) PARTITION BY KEY(col1, col3) PARTITIONS 4; 171 insert into `t2` values 172 (1, '1980-12-17', 7369), 173 (2, '1981-02-20', 7499), 174 (3, '1981-02-22', 7521), 175 (4, '1981-04-02', 7566), 176 (5, '1981-09-28', 7654), 177 (6, '1981-05-01', 7698), 178 (7, '1981-06-09', 7782), 179 (8, '0087-07-13', 7788), 180 (9, '1981-11-17', 7839), 181 (10, '1981-09-08', 7844), 182 (11, '2007-07-13', 7876), 183 (12, '1981-12-03', 7900), 184 (13, '1987-07-13', 7980), 185 (14, '2001-11-17', 7981), 186 (15, '1951-11-08', 7982), 187 (16, '1927-10-13', 7983), 188 (17, '1671-12-09', 7984), 189 (18, '1981-11-06', 7985), 190 (19, '1771-12-06', 7986), 191 (20, '1985-10-06', 7987), 192 (21, '1771-10-06', 7988), 193 (22, '1981-10-05', 7989), 194 (23, '2001-12-04', 7990), 195 (24, '1999-08-01', 7991), 196 (25, '1951-11-08', 7992), 197 (26, '1927-10-13', 7993), 198 (27, '1971-12-09', 7994), 199 (28, '1981-12-09', 7995), 200 (29, '2001-11-17', 7996), 201 (30, '1981-12-09', 7997), 202 (31, '2001-11-17', 7998), 203 (32, '2001-11-17', 7999); 204 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)); 205 col1 col2 col3 206 1 1980-12-17 7369 207 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)); 208 col1 col2 col3 209 1 1980-12-17 7369 210 select * from t2 where col1 = 23 and col3 = 7990; 211 col1 col2 col3 212 23 2001-12-04 7990 213 select * from t2 where col1 = 1 and col3 = 7990; 214 col1 col2 col3 215 select * from t2 where col1 = 23 and col3 = 7990 or col1 = 30; 216 col1 col2 col3 217 23 2001-12-04 7990 218 30 1981-12-09 7997 219 select * from t2 where col1 in(23, 6) and col3 in (7990, 7698, 7988); 220 col1 col2 col3 221 6 1981-05-01 7698 222 23 2001-12-04 7990 223 select * from t2 where col3 = 7996 and col1 > 25; 224 col1 col2 col3 225 29 2001-11-17 7996 226 select * from t2 where col3 = 7990 or col3 = 7988; 227 col1 col2 col3 228 21 1771-10-06 7988 229 23 2001-12-04 7990 230 select * from t2 where (col1 = 1 and col3 = 7369) or (col1 = 27 and col3 = 7994); 231 col1 col2 col3 232 1 1980-12-17 7369 233 27 1971-12-09 7994 234 drop table if exists employees; 235 CREATE TABLE employees ( 236 id INT NOT NULL, 237 fname VARCHAR(30), 238 lname VARCHAR(30), 239 hired DATE NOT NULL DEFAULT '1970-01-01', 240 separated DATE NOT NULL DEFAULT '9999-12-31', 241 job_code INT, 242 store_id INT 243 ) PARTITION BY HASH(store_id) PARTITIONS 4; 244 INSERT INTO employees VALUES 245 (10001, 'Georgi', 'Facello', '1953-09-02','1986-06-26',120, 1), 246 (10002, 'Bezalel', 'Simmel', '1964-06-02','1985-11-21',150, 7), 247 (10003, 'Parto', 'Bamford', '1959-12-03','1986-08-28',140, 3), 248 (10004, 'Chirstian', 'Koblick', '1954-05-01','1986-12-01',150, 3), 249 (10005, 'Kyoichi', 'Maliniak', '1955-01-21','1989-09-12',150, 18), 250 (10006, 'Anneke', 'Preusig', '1953-04-20','1989-06-02',150, 15), 251 (10007, 'Tzvetan', 'Zielinski', '1957-05-23','1989-02-10',110, 6), 252 (10008, 'Saniya', 'Kalloufi', '1958-02-19','1994-09-15',170, 10), 253 (10009, 'Sumant', 'Peac', '1952-04-19','1985-02-18',110, 13), 254 (10010, 'Duangkaew', 'Piveteau', '1963-06-01','1989-08-24',160, 10), 255 (10011, 'Mary', 'Sluis', '1953-11-07','1990-01-22',120, 8), 256 (10012, 'Patricio', 'Bridgland', '1960-10-04','1992-12-18',120, 7), 257 (10013, 'Eberhardt', 'Terkki', '1963-06-07','1985-10-20',160, 17), 258 (10014, 'Berni', 'Genin', '1956-02-12','1987-03-11',120, 15), 259 (10015, 'Guoxiang', 'Nooteboom', '1959-08-19','1987-07-02',140, 8), 260 (10016, 'Kazuhito', 'Cappelletti', '1961-05-02','1995-01-27',140, 2), 261 (10017, 'Cristinel', 'Bouloucos', '1958-07-06','1993-08-03',170, 10), 262 (10018, 'Kazuhide', 'Peha', '1954-06-19','1987-04-03',170, 2), 263 (10019, 'Lillian', 'Haddadi', '1953-01-23','1999-04-30',170, 13), 264 (10020, 'Mayuko', 'Warwick', '1952-12-24','1991-01-26',120, 1), 265 (10021, 'Ramzi', 'Erde', '1960-02-20','1988-02-10',120, 9), 266 (10022, 'Shahaf', 'Famili', '1952-07-08','1995-08-22',130, 10), 267 (10023, 'Bojan', 'Montemayor', '1953-09-29','1989-12-17',120, 5), 268 (10024, 'Suzette', 'Pettey', '1958-09-05','1997-05-19',130, 4), 269 (10025, 'Prasadram', 'Heyers', '1958-10-31','1987-08-17',180, 8), 270 (10026, 'Yongqiao', 'Berztiss', '1953-04-03','1995-03-20',170, 4), 271 (10027, 'Divier', 'Reistad', '1962-07-10','1989-07-07',180, 10), 272 (10028, 'Domenick', 'Tempesti', '1963-11-26','1991-10-22',110, 11), 273 (10029, 'Otmar', 'Herbst', '1956-12-13','1985-11-20',110, 12), 274 (10030, 'Elvis', 'Demeyer', '1958-07-14','1994-02-17',110, 1), 275 (10031, 'Karsten', 'Joslin', '1959-01-27','1991-09-01',110, 10), 276 (10032, 'Jeong', 'Reistad', '1960-08-09','1990-06-20',120, 19), 277 (10033, 'Arif', 'Merlo', '1956-11-14','1987-03-18',120, 14), 278 (10034, 'Bader', 'Swan', '1962-12-29','1988-09-21',130, 16), 279 (10035, 'Alain', 'Chappelet', '1953-02-08','1988-09-05',130, 3), 280 (10036, 'Adamantios', 'Portugali', '1959-08-10','1992-01-03',130, 14), 281 (10037, 'Pradeep', 'Makrucki', '1963-07-22','1990-12-05',140, 12), 282 (10038, 'Huan', 'Lortz', '1960-07-20','1989-09-20',140, 7), 283 (10039, 'Alejandro', 'Brender', '1959-10-01','1988-01-19',110, 20), 284 (10040, 'Weiyi', 'Meriste', '1959-09-13','1993-02-14',140, 17); 285 select * from employees where store_id = 8; 286 id fname lname hired separated job_code store_id 287 10011 Mary Sluis 1953-11-07 1990-01-22 120 8 288 10015 Guoxiang Nooteboom 1959-08-19 1987-07-02 140 8 289 10025 Prasadram Heyers 1958-10-31 1987-08-17 180 8 290 select * from employees where store_id = 8 or store_id = 10; 291 id fname lname hired separated job_code store_id 292 10011 Mary Sluis 1953-11-07 1990-01-22 120 8 293 10015 Guoxiang Nooteboom 1959-08-19 1987-07-02 140 8 294 10025 Prasadram Heyers 1958-10-31 1987-08-17 180 8 295 10008 Saniya Kalloufi 1958-02-19 1994-09-15 170 10 296 10010 Duangkaew Piveteau 1963-06-01 1989-08-24 160 10 297 10017 Cristinel Bouloucos 1958-07-06 1993-08-03 170 10 298 10022 Shahaf Famili 1952-07-08 1995-08-22 130 10 299 10027 Divier Reistad 1962-07-10 1989-07-07 180 10 300 10031 Karsten Joslin 1959-01-27 1991-09-01 110 10 301 select * from employees where store_id in (1, 2, 11); 302 id fname lname hired separated job_code store_id 303 10001 Georgi Facello 1953-09-02 1986-06-26 120 1 304 10016 Kazuhito Cappelletti 1961-05-02 1995-01-27 140 2 305 10018 Kazuhide Peha 1954-06-19 1987-04-03 170 2 306 10020 Mayuko Warwick 1952-12-24 1991-01-26 120 1 307 10028 Domenick Tempesti 1963-11-26 1991-10-22 110 11 308 10030 Elvis Demeyer 1958-07-14 1994-02-17 110 1 309 select * from employees where store_id in (1, 2, 6, 7); 310 id fname lname hired separated job_code store_id 311 10001 Georgi Facello 1953-09-02 1986-06-26 120 1 312 10016 Kazuhito Cappelletti 1961-05-02 1995-01-27 140 2 313 10018 Kazuhide Peha 1954-06-19 1987-04-03 170 2 314 10020 Mayuko Warwick 1952-12-24 1991-01-26 120 1 315 10030 Elvis Demeyer 1958-07-14 1994-02-17 110 1 316 10002 Bezalel Simmel 1964-06-02 1985-11-21 150 7 317 10007 Tzvetan Zielinski 1957-05-23 1989-02-10 110 6 318 10012 Patricio Bridgland 1960-10-04 1992-12-18 120 7 319 10038 Huan Lortz 1960-07-20 1989-09-20 140 7 320 select * from employees where store_id in (1, 2, 11) or store_id in (6, 7, 18); 321 id fname lname hired separated job_code store_id 322 10001 Georgi Facello 1953-09-02 1986-06-26 120 1 323 10016 Kazuhito Cappelletti 1961-05-02 1995-01-27 140 2 324 10018 Kazuhide Peha 1954-06-19 1987-04-03 170 2 325 10020 Mayuko Warwick 1952-12-24 1991-01-26 120 1 326 10028 Domenick Tempesti 1963-11-26 1991-10-22 110 11 327 10030 Elvis Demeyer 1958-07-14 1994-02-17 110 1 328 10002 Bezalel Simmel 1964-06-02 1985-11-21 150 7 329 10005 Kyoichi Maliniak 1955-01-21 1989-09-12 150 18 330 10007 Tzvetan Zielinski 1957-05-23 1989-02-10 110 6 331 10012 Patricio Bridgland 1960-10-04 1992-12-18 120 7 332 10038 Huan Lortz 1960-07-20 1989-09-20 140 7 333 select * from employees where store_id = 3 and id = 10004 or store_id = 10; 334 id fname lname hired separated job_code store_id 335 10004 Chirstian Koblick 1954-05-01 1986-12-01 150 3 336 10008 Saniya Kalloufi 1958-02-19 1994-09-15 170 10 337 10010 Duangkaew Piveteau 1963-06-01 1989-08-24 160 10 338 10017 Cristinel Bouloucos 1958-07-06 1993-08-03 170 10 339 10022 Shahaf Famili 1952-07-08 1995-08-22 130 10 340 10027 Divier Reistad 1962-07-10 1989-07-07 180 10 341 10031 Karsten Joslin 1959-01-27 1991-09-01 110 10 342 select * from employees where (store_id = 3 and id = 10004) or (store_id = 10 and id = 10022); 343 id fname lname hired separated job_code store_id 344 10004 Chirstian Koblick 1954-05-01 1986-12-01 150 3 345 10022 Shahaf Famili 1952-07-08 1995-08-22 130 10 346 select * from employees where store_id > 15; 347 id fname lname hired separated job_code store_id 348 10034 Bader Swan 1962-12-29 1988-09-21 130 16 349 10039 Alejandro Brender 1959-10-01 1988-01-19 110 20 350 10013 Eberhardt Terkki 1963-06-07 1985-10-20 160 17 351 10040 Weiyi Meriste 1959-09-13 1993-02-14 140 17 352 10005 Kyoichi Maliniak 1955-01-21 1989-09-12 150 18 353 10032 Jeong Reistad 1960-08-09 1990-06-20 120 19 354 select * from employees where store_id = 10 or id = 10004; 355 id fname lname hired separated job_code store_id 356 10008 Saniya Kalloufi 1958-02-19 1994-09-15 170 10 357 10010 Duangkaew Piveteau 1963-06-01 1989-08-24 160 10 358 10017 Cristinel Bouloucos 1958-07-06 1993-08-03 170 10 359 10022 Shahaf Famili 1952-07-08 1995-08-22 130 10 360 10027 Divier Reistad 1962-07-10 1989-07-07 180 10 361 10031 Karsten Joslin 1959-01-27 1991-09-01 110 10 362 10004 Chirstian Koblick 1954-05-01 1986-12-01 150 3 363 drop database db1;