github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/partition_pruning.result (about) 1 set @@stochastik.milevadb_enable_block_partition=1; 2 drop causet if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 3 # 4 # Bug#53806: Wrong estimates for range query in partitioned MyISAM causet 5 # Bug#46754: 'rows' field doesn't reflect partition pruning 6 # 7 CREATE TABLE t1 (a INT PRIMARY KEY) 8 PARTITION BY RANGE (a) ( 9 PARTITION p0 VALUES LESS THAN (1), 10 PARTITION p1 VALUES LESS THAN (2), 11 PARTITION p2 VALUES LESS THAN (3), 12 PARTITION p3 VALUES LESS THAN (4), 13 PARTITION p4 VALUES LESS THAN (5), 14 PARTITION p5 VALUES LESS THAN (6), 15 PARTITION max VALUES LESS THAN MAXVALUE); 16 INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8); 17 EXPLAIN SELECT * FROM t1 WHERE a <= 1; 18 id estRows task access object operator info 19 TableReader_6 3333.33 root partition:p0,p1 data:TableRangeScan_5 20 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,1], keep order:false, stats:pseudo 21 EXPLAIN SELECT * FROM t1 WHERE a < 7; 22 id estRows task access object operator info 23 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 24 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,7), keep order:false, stats:pseudo 25 EXPLAIN SELECT * FROM t1 WHERE a <= 1; 26 id estRows task access object operator info 27 TableReader_6 3333.33 root partition:p0,p1 data:TableRangeScan_5 28 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,1], keep order:false, stats:pseudo 29 DROP TABLE t1; 30 # 31 # Bug#49742: Partition Pruning not working correctly for RANGE 32 # 33 CREATE TABLE t1 (a INT PRIMARY KEY) 34 PARTITION BY RANGE (a) ( 35 PARTITION p0 VALUES LESS THAN (1), 36 PARTITION p1 VALUES LESS THAN (2), 37 PARTITION p2 VALUES LESS THAN (3), 38 PARTITION p3 VALUES LESS THAN (4), 39 PARTITION p4 VALUES LESS THAN (5), 40 PARTITION p5 VALUES LESS THAN (6), 41 PARTITION max VALUES LESS THAN MAXVALUE); 42 INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8); 43 SELECT * FROM t1 WHERE a < 1 order by a; 44 a 45 -1 46 0 47 EXPLAIN SELECT * FROM t1 WHERE a < 1; 48 id estRows task access object operator info 49 TableReader_6 3333.33 root partition:p0 data:TableRangeScan_5 50 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,1), keep order:false, stats:pseudo 51 SELECT * FROM t1 WHERE a < 2 order by a; 52 a 53 -1 54 0 55 1 56 EXPLAIN SELECT * FROM t1 WHERE a < 2; 57 id estRows task access object operator info 58 TableReader_6 3333.33 root partition:p0,p1 data:TableRangeScan_5 59 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,2), keep order:false, stats:pseudo 60 SELECT * FROM t1 WHERE a < 3 order by a; 61 a 62 -1 63 0 64 1 65 2 66 EXPLAIN SELECT * FROM t1 WHERE a < 3; 67 id estRows task access object operator info 68 TableReader_6 3333.33 root partition:p0,p1,p2 data:TableRangeScan_5 69 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,3), keep order:false, stats:pseudo 70 SELECT * FROM t1 WHERE a < 4 order by a; 71 a 72 -1 73 0 74 1 75 2 76 3 77 EXPLAIN SELECT * FROM t1 WHERE a < 4; 78 id estRows task access object operator info 79 TableReader_6 3333.33 root partition:p0,p1,p2,p3 data:TableRangeScan_5 80 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,4), keep order:false, stats:pseudo 81 SELECT * FROM t1 WHERE a < 5 order by a; 82 a 83 -1 84 0 85 1 86 2 87 3 88 4 89 EXPLAIN SELECT * FROM t1 WHERE a < 5; 90 id estRows task access object operator info 91 TableReader_6 3333.33 root partition:p0,p1,p2,p3,p4 data:TableRangeScan_5 92 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,5), keep order:false, stats:pseudo 93 SELECT * FROM t1 WHERE a < 6 order by a; 94 a 95 -1 96 0 97 1 98 2 99 3 100 4 101 5 102 EXPLAIN SELECT * FROM t1 WHERE a < 6; 103 id estRows task access object operator info 104 TableReader_6 3333.33 root partition:p0,p1,p2,p3,p4,p5 data:TableRangeScan_5 105 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,6), keep order:false, stats:pseudo 106 SELECT * FROM t1 WHERE a < 7 order by a; 107 a 108 -1 109 0 110 1 111 2 112 3 113 4 114 5 115 6 116 EXPLAIN SELECT * FROM t1 WHERE a < 7; 117 id estRows task access object operator info 118 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 119 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,7), keep order:false, stats:pseudo 120 SELECT * FROM t1 WHERE a <= 1 order by a; 121 a 122 -1 123 0 124 1 125 EXPLAIN SELECT * FROM t1 WHERE a <= 1; 126 id estRows task access object operator info 127 TableReader_6 3333.33 root partition:p0,p1 data:TableRangeScan_5 128 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,1], keep order:false, stats:pseudo 129 SELECT * FROM t1 WHERE a <= 2 order by a; 130 a 131 -1 132 0 133 1 134 2 135 EXPLAIN SELECT * FROM t1 WHERE a <= 2; 136 id estRows task access object operator info 137 TableReader_6 3333.33 root partition:p0,p1,p2 data:TableRangeScan_5 138 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,2], keep order:false, stats:pseudo 139 SELECT * FROM t1 WHERE a <= 3 order by a; 140 a 141 -1 142 0 143 1 144 2 145 3 146 EXPLAIN SELECT * FROM t1 WHERE a <= 3; 147 id estRows task access object operator info 148 TableReader_6 3333.33 root partition:p0,p1,p2,p3 data:TableRangeScan_5 149 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,3], keep order:false, stats:pseudo 150 SELECT * FROM t1 WHERE a <= 4 order by a; 151 a 152 -1 153 0 154 1 155 2 156 3 157 4 158 EXPLAIN SELECT * FROM t1 WHERE a <= 4; 159 id estRows task access object operator info 160 TableReader_6 3333.33 root partition:p0,p1,p2,p3,p4 data:TableRangeScan_5 161 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,4], keep order:false, stats:pseudo 162 SELECT * FROM t1 WHERE a <= 5 order by a; 163 a 164 -1 165 0 166 1 167 2 168 3 169 4 170 5 171 EXPLAIN SELECT * FROM t1 WHERE a <= 5; 172 id estRows task access object operator info 173 TableReader_6 3333.33 root partition:p0,p1,p2,p3,p4,p5 data:TableRangeScan_5 174 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,5], keep order:false, stats:pseudo 175 SELECT * FROM t1 WHERE a <= 6 order by a; 176 a 177 -1 178 0 179 1 180 2 181 3 182 4 183 5 184 6 185 EXPLAIN SELECT * FROM t1 WHERE a <= 6; 186 id estRows task access object operator info 187 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 188 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,6], keep order:false, stats:pseudo 189 SELECT * FROM t1 WHERE a <= 7 order by a; 190 a 191 -1 192 0 193 1 194 2 195 3 196 4 197 5 198 6 199 7 200 EXPLAIN SELECT * FROM t1 WHERE a <= 7; 201 id estRows task access object operator info 202 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 203 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,7], keep order:false, stats:pseudo 204 SELECT * FROM t1 WHERE a = 1 order by a; 205 a 206 1 207 EXPLAIN SELECT * FROM t1 WHERE a = 1; 208 id estRows task access object operator info 209 TableReader_6 1.00 root partition:p1 data:TableRangeScan_5 210 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[1,1], keep order:false, stats:pseudo 211 SELECT * FROM t1 WHERE a = 2 order by a; 212 a 213 2 214 EXPLAIN SELECT * FROM t1 WHERE a = 2; 215 id estRows task access object operator info 216 TableReader_6 1.00 root partition:p2 data:TableRangeScan_5 217 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[2,2], keep order:false, stats:pseudo 218 SELECT * FROM t1 WHERE a = 3 order by a; 219 a 220 3 221 EXPLAIN SELECT * FROM t1 WHERE a = 3; 222 id estRows task access object operator info 223 TableReader_6 1.00 root partition:p3 data:TableRangeScan_5 224 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[3,3], keep order:false, stats:pseudo 225 SELECT * FROM t1 WHERE a = 4 order by a; 226 a 227 4 228 EXPLAIN SELECT * FROM t1 WHERE a = 4; 229 id estRows task access object operator info 230 TableReader_6 1.00 root partition:p4 data:TableRangeScan_5 231 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[4,4], keep order:false, stats:pseudo 232 SELECT * FROM t1 WHERE a = 5 order by a; 233 a 234 5 235 EXPLAIN SELECT * FROM t1 WHERE a = 5; 236 id estRows task access object operator info 237 TableReader_6 1.00 root partition:p5 data:TableRangeScan_5 238 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[5,5], keep order:false, stats:pseudo 239 SELECT * FROM t1 WHERE a = 6 order by a; 240 a 241 6 242 EXPLAIN SELECT * FROM t1 WHERE a = 6; 243 id estRows task access object operator info 244 TableReader_6 1.00 root partition:max data:TableRangeScan_5 245 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[6,6], keep order:false, stats:pseudo 246 SELECT * FROM t1 WHERE a = 7 order by a; 247 a 248 7 249 EXPLAIN SELECT * FROM t1 WHERE a = 7; 250 id estRows task access object operator info 251 TableReader_6 1.00 root partition:max data:TableRangeScan_5 252 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[7,7], keep order:false, stats:pseudo 253 SELECT * FROM t1 WHERE a >= 1 order by a; 254 a 255 1 256 2 257 3 258 4 259 5 260 6 261 7 262 8 263 EXPLAIN SELECT * FROM t1 WHERE a >= 1; 264 id estRows task access object operator info 265 TableReader_6 3333.33 root partition:p1,p2,p3,p4,p5,max data:TableRangeScan_5 266 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[1,+inf], keep order:false, stats:pseudo 267 SELECT * FROM t1 WHERE a >= 2 order by a; 268 a 269 2 270 3 271 4 272 5 273 6 274 7 275 8 276 EXPLAIN SELECT * FROM t1 WHERE a >= 2; 277 id estRows task access object operator info 278 TableReader_6 3333.33 root partition:p2,p3,p4,p5,max data:TableRangeScan_5 279 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[2,+inf], keep order:false, stats:pseudo 280 SELECT * FROM t1 WHERE a >= 3 order by a; 281 a 282 3 283 4 284 5 285 6 286 7 287 8 288 EXPLAIN SELECT * FROM t1 WHERE a >= 3; 289 id estRows task access object operator info 290 TableReader_6 3333.33 root partition:p3,p4,p5,max data:TableRangeScan_5 291 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[3,+inf], keep order:false, stats:pseudo 292 SELECT * FROM t1 WHERE a >= 4 order by a; 293 a 294 4 295 5 296 6 297 7 298 8 299 EXPLAIN SELECT * FROM t1 WHERE a >= 4; 300 id estRows task access object operator info 301 TableReader_6 3333.33 root partition:p4,p5,max data:TableRangeScan_5 302 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[4,+inf], keep order:false, stats:pseudo 303 SELECT * FROM t1 WHERE a >= 5 order by a; 304 a 305 5 306 6 307 7 308 8 309 EXPLAIN SELECT * FROM t1 WHERE a >= 5; 310 id estRows task access object operator info 311 TableReader_6 3333.33 root partition:p5,max data:TableRangeScan_5 312 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[5,+inf], keep order:false, stats:pseudo 313 SELECT * FROM t1 WHERE a >= 6 order by a; 314 a 315 6 316 7 317 8 318 EXPLAIN SELECT * FROM t1 WHERE a >= 6; 319 id estRows task access object operator info 320 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 321 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[6,+inf], keep order:false, stats:pseudo 322 SELECT * FROM t1 WHERE a >= 7 order by a; 323 a 324 7 325 8 326 EXPLAIN SELECT * FROM t1 WHERE a >= 7; 327 id estRows task access object operator info 328 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 329 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[7,+inf], keep order:false, stats:pseudo 330 SELECT * FROM t1 WHERE a > 1 order by a; 331 a 332 2 333 3 334 4 335 5 336 6 337 7 338 8 339 EXPLAIN SELECT * FROM t1 WHERE a > 1; 340 id estRows task access object operator info 341 TableReader_6 3333.33 root partition:p2,p3,p4,p5,max data:TableRangeScan_5 342 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(1,+inf], keep order:false, stats:pseudo 343 SELECT * FROM t1 WHERE a > 2 order by a; 344 a 345 3 346 4 347 5 348 6 349 7 350 8 351 EXPLAIN SELECT * FROM t1 WHERE a > 2; 352 id estRows task access object operator info 353 TableReader_6 3333.33 root partition:p3,p4,p5,max data:TableRangeScan_5 354 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(2,+inf], keep order:false, stats:pseudo 355 SELECT * FROM t1 WHERE a > 3 order by a; 356 a 357 4 358 5 359 6 360 7 361 8 362 EXPLAIN SELECT * FROM t1 WHERE a > 3; 363 id estRows task access object operator info 364 TableReader_6 3333.33 root partition:p4,p5,max data:TableRangeScan_5 365 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(3,+inf], keep order:false, stats:pseudo 366 SELECT * FROM t1 WHERE a > 4 order by a; 367 a 368 5 369 6 370 7 371 8 372 EXPLAIN SELECT * FROM t1 WHERE a > 4; 373 id estRows task access object operator info 374 TableReader_6 3333.33 root partition:p5,max data:TableRangeScan_5 375 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(4,+inf], keep order:false, stats:pseudo 376 SELECT * FROM t1 WHERE a > 5 order by a; 377 a 378 6 379 7 380 8 381 EXPLAIN SELECT * FROM t1 WHERE a > 5; 382 id estRows task access object operator info 383 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 384 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(5,+inf], keep order:false, stats:pseudo 385 SELECT * FROM t1 WHERE a > 6 order by a; 386 a 387 7 388 8 389 EXPLAIN SELECT * FROM t1 WHERE a > 6; 390 id estRows task access object operator info 391 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 392 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(6,+inf], keep order:false, stats:pseudo 393 SELECT * FROM t1 WHERE a > 7 order by a; 394 a 395 8 396 EXPLAIN SELECT * FROM t1 WHERE a > 7; 397 id estRows task access object operator info 398 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 399 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(7,+inf], keep order:false, stats:pseudo 400 DROP TABLE t1; 401 CREATE TABLE t1 (a INT PRIMARY KEY) 402 PARTITION BY RANGE (a) ( 403 PARTITION p0 VALUES LESS THAN (1), 404 PARTITION p1 VALUES LESS THAN (2), 405 PARTITION p2 VALUES LESS THAN (3), 406 PARTITION p3 VALUES LESS THAN (4), 407 PARTITION p4 VALUES LESS THAN (5), 408 PARTITION max VALUES LESS THAN MAXVALUE); 409 INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7); 410 SELECT * FROM t1 WHERE a < 1 order by a; 411 a 412 -1 413 0 414 EXPLAIN SELECT * FROM t1 WHERE a < 1; 415 id estRows task access object operator info 416 TableReader_6 3333.33 root partition:p0 data:TableRangeScan_5 417 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,1), keep order:false, stats:pseudo 418 SELECT * FROM t1 WHERE a < 2 order by a; 419 a 420 -1 421 0 422 1 423 EXPLAIN SELECT * FROM t1 WHERE a < 2; 424 id estRows task access object operator info 425 TableReader_6 3333.33 root partition:p0,p1 data:TableRangeScan_5 426 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,2), keep order:false, stats:pseudo 427 SELECT * FROM t1 WHERE a < 3 order by a; 428 a 429 -1 430 0 431 1 432 2 433 EXPLAIN SELECT * FROM t1 WHERE a < 3; 434 id estRows task access object operator info 435 TableReader_6 3333.33 root partition:p0,p1,p2 data:TableRangeScan_5 436 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,3), keep order:false, stats:pseudo 437 SELECT * FROM t1 WHERE a < 4 order by a; 438 a 439 -1 440 0 441 1 442 2 443 3 444 EXPLAIN SELECT * FROM t1 WHERE a < 4; 445 id estRows task access object operator info 446 TableReader_6 3333.33 root partition:p0,p1,p2,p3 data:TableRangeScan_5 447 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,4), keep order:false, stats:pseudo 448 SELECT * FROM t1 WHERE a < 5 order by a; 449 a 450 -1 451 0 452 1 453 2 454 3 455 4 456 EXPLAIN SELECT * FROM t1 WHERE a < 5; 457 id estRows task access object operator info 458 TableReader_6 3333.33 root partition:p0,p1,p2,p3,p4 data:TableRangeScan_5 459 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,5), keep order:false, stats:pseudo 460 SELECT * FROM t1 WHERE a < 6 order by a; 461 a 462 -1 463 0 464 1 465 2 466 3 467 4 468 5 469 EXPLAIN SELECT * FROM t1 WHERE a < 6; 470 id estRows task access object operator info 471 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 472 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,6), keep order:false, stats:pseudo 473 SELECT * FROM t1 WHERE a <= 1 order by a; 474 a 475 -1 476 0 477 1 478 EXPLAIN SELECT * FROM t1 WHERE a <= 1; 479 id estRows task access object operator info 480 TableReader_6 3333.33 root partition:p0,p1 data:TableRangeScan_5 481 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,1], keep order:false, stats:pseudo 482 SELECT * FROM t1 WHERE a <= 2 order by a; 483 a 484 -1 485 0 486 1 487 2 488 EXPLAIN SELECT * FROM t1 WHERE a <= 2; 489 id estRows task access object operator info 490 TableReader_6 3333.33 root partition:p0,p1,p2 data:TableRangeScan_5 491 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,2], keep order:false, stats:pseudo 492 SELECT * FROM t1 WHERE a <= 3 order by a; 493 a 494 -1 495 0 496 1 497 2 498 3 499 EXPLAIN SELECT * FROM t1 WHERE a <= 3; 500 id estRows task access object operator info 501 TableReader_6 3333.33 root partition:p0,p1,p2,p3 data:TableRangeScan_5 502 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,3], keep order:false, stats:pseudo 503 SELECT * FROM t1 WHERE a <= 4 order by a; 504 a 505 -1 506 0 507 1 508 2 509 3 510 4 511 EXPLAIN SELECT * FROM t1 WHERE a <= 4; 512 id estRows task access object operator info 513 TableReader_6 3333.33 root partition:p0,p1,p2,p3,p4 data:TableRangeScan_5 514 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,4], keep order:false, stats:pseudo 515 SELECT * FROM t1 WHERE a <= 5 order by a; 516 a 517 -1 518 0 519 1 520 2 521 3 522 4 523 5 524 EXPLAIN SELECT * FROM t1 WHERE a <= 5; 525 id estRows task access object operator info 526 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 527 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,5], keep order:false, stats:pseudo 528 SELECT * FROM t1 WHERE a <= 6 order by a; 529 a 530 -1 531 0 532 1 533 2 534 3 535 4 536 5 537 6 538 EXPLAIN SELECT * FROM t1 WHERE a <= 6; 539 id estRows task access object operator info 540 TableReader_6 3333.33 root partition:all data:TableRangeScan_5 541 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[-inf,6], keep order:false, stats:pseudo 542 SELECT * FROM t1 WHERE a = 1; 543 a 544 1 545 EXPLAIN SELECT * FROM t1 WHERE a = 1; 546 id estRows task access object operator info 547 TableReader_6 1.00 root partition:p1 data:TableRangeScan_5 548 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[1,1], keep order:false, stats:pseudo 549 SELECT * FROM t1 WHERE a = 2; 550 a 551 2 552 EXPLAIN SELECT * FROM t1 WHERE a = 2; 553 id estRows task access object operator info 554 TableReader_6 1.00 root partition:p2 data:TableRangeScan_5 555 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[2,2], keep order:false, stats:pseudo 556 SELECT * FROM t1 WHERE a = 3; 557 a 558 3 559 EXPLAIN SELECT * FROM t1 WHERE a = 3; 560 id estRows task access object operator info 561 TableReader_6 1.00 root partition:p3 data:TableRangeScan_5 562 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[3,3], keep order:false, stats:pseudo 563 SELECT * FROM t1 WHERE a = 4; 564 a 565 4 566 EXPLAIN SELECT * FROM t1 WHERE a = 4; 567 id estRows task access object operator info 568 TableReader_6 1.00 root partition:p4 data:TableRangeScan_5 569 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[4,4], keep order:false, stats:pseudo 570 SELECT * FROM t1 WHERE a = 5; 571 a 572 5 573 EXPLAIN SELECT * FROM t1 WHERE a = 5; 574 id estRows task access object operator info 575 TableReader_6 1.00 root partition:max data:TableRangeScan_5 576 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[5,5], keep order:false, stats:pseudo 577 SELECT * FROM t1 WHERE a = 6; 578 a 579 6 580 EXPLAIN SELECT * FROM t1 WHERE a = 6; 581 id estRows task access object operator info 582 TableReader_6 1.00 root partition:max data:TableRangeScan_5 583 └─TableRangeScan_5 1.00 cop[einsteindb] causet:t1 range:[6,6], keep order:false, stats:pseudo 584 SELECT * FROM t1 WHERE a >= 1 order by a; 585 a 586 1 587 2 588 3 589 4 590 5 591 6 592 7 593 EXPLAIN SELECT * FROM t1 WHERE a >= 1; 594 id estRows task access object operator info 595 TableReader_6 3333.33 root partition:p1,p2,p3,p4,max data:TableRangeScan_5 596 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[1,+inf], keep order:false, stats:pseudo 597 SELECT * FROM t1 WHERE a >= 2 order by a; 598 a 599 2 600 3 601 4 602 5 603 6 604 7 605 EXPLAIN SELECT * FROM t1 WHERE a >= 2; 606 id estRows task access object operator info 607 TableReader_6 3333.33 root partition:p2,p3,p4,max data:TableRangeScan_5 608 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[2,+inf], keep order:false, stats:pseudo 609 SELECT * FROM t1 WHERE a >= 3 order by a; 610 a 611 3 612 4 613 5 614 6 615 7 616 EXPLAIN SELECT * FROM t1 WHERE a >= 3; 617 id estRows task access object operator info 618 TableReader_6 3333.33 root partition:p3,p4,max data:TableRangeScan_5 619 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[3,+inf], keep order:false, stats:pseudo 620 SELECT * FROM t1 WHERE a >= 4 order by a; 621 a 622 4 623 5 624 6 625 7 626 EXPLAIN SELECT * FROM t1 WHERE a >= 4; 627 id estRows task access object operator info 628 TableReader_6 3333.33 root partition:p4,max data:TableRangeScan_5 629 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[4,+inf], keep order:false, stats:pseudo 630 SELECT * FROM t1 WHERE a >= 5 order by a; 631 a 632 5 633 6 634 7 635 EXPLAIN SELECT * FROM t1 WHERE a >= 5; 636 id estRows task access object operator info 637 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 638 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[5,+inf], keep order:false, stats:pseudo 639 SELECT * FROM t1 WHERE a >= 6 order by a; 640 a 641 6 642 7 643 EXPLAIN SELECT * FROM t1 WHERE a >= 6; 644 id estRows task access object operator info 645 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 646 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:[6,+inf], keep order:false, stats:pseudo 647 SELECT * FROM t1 WHERE a > 1 order by a; 648 a 649 2 650 3 651 4 652 5 653 6 654 7 655 EXPLAIN SELECT * FROM t1 WHERE a > 1; 656 id estRows task access object operator info 657 TableReader_6 3333.33 root partition:p2,p3,p4,max data:TableRangeScan_5 658 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(1,+inf], keep order:false, stats:pseudo 659 SELECT * FROM t1 WHERE a > 2 order by a; 660 a 661 3 662 4 663 5 664 6 665 7 666 EXPLAIN SELECT * FROM t1 WHERE a > 2; 667 id estRows task access object operator info 668 TableReader_6 3333.33 root partition:p3,p4,max data:TableRangeScan_5 669 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(2,+inf], keep order:false, stats:pseudo 670 SELECT * FROM t1 WHERE a > 3 order by a; 671 a 672 4 673 5 674 6 675 7 676 EXPLAIN SELECT * FROM t1 WHERE a > 3; 677 id estRows task access object operator info 678 TableReader_6 3333.33 root partition:p4,max data:TableRangeScan_5 679 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(3,+inf], keep order:false, stats:pseudo 680 SELECT * FROM t1 WHERE a > 4 order by a; 681 a 682 5 683 6 684 7 685 EXPLAIN SELECT * FROM t1 WHERE a > 4; 686 id estRows task access object operator info 687 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 688 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(4,+inf], keep order:false, stats:pseudo 689 SELECT * FROM t1 WHERE a > 5 order by a; 690 a 691 6 692 7 693 EXPLAIN SELECT * FROM t1 WHERE a > 5; 694 id estRows task access object operator info 695 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 696 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(5,+inf], keep order:false, stats:pseudo 697 SELECT * FROM t1 WHERE a > 6 order by a; 698 a 699 7 700 EXPLAIN SELECT * FROM t1 WHERE a > 6; 701 id estRows task access object operator info 702 TableReader_6 3333.33 root partition:max data:TableRangeScan_5 703 └─TableRangeScan_5 3333.33 cop[einsteindb] causet:t1 range:(6,+inf], keep order:false, stats:pseudo 704 DROP TABLE t1; 705 # test of RANGE and index 706 CREATE TABLE t1 (a DATE, KEY(a)) 707 PARTITION BY RANGE (TO_DAYS(a)) 708 (PARTITION `pNULL` VALUES LESS THAN (0), 709 PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1), 710 PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1), 711 PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1)); 712 SET ALLEGROSQL_MODE = ''; 713 INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), 714 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); 715 # test without index 716 ALTER TABLE t1 DROP KEY a; 717 DROP TABLE t1; 718 # Test with DATETIME column NOT NULL 719 CREATE TABLE t1 ( 720 a int(10) unsigned NOT NULL, 721 b DATETIME NOT NULL, 722 PRIMARY KEY (a, b) 723 ) PARTITION BY RANGE (TO_DAYS(b)) 724 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 725 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 726 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 727 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 728 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 729 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 730 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), 731 (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), 732 (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); 733 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 734 id estRows task access object operator info 735 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 736 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00) 737 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 738 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 739 id estRows task access object operator info 740 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 741 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00) 742 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 743 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 744 id estRows task access object operator info 745 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 746 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00) 747 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 748 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 749 id estRows task access object operator info 750 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 751 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00) 752 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 753 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 754 id estRows task access object operator info 755 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 756 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00) 757 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 758 EXPLAIN SELECT * FROM t1 759 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 760 id estRows task access object operator info 761 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 762 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59) 763 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 764 EXPLAIN SELECT * FROM t1 765 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 766 id estRows task access object operator info 767 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 768 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59) 769 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 770 EXPLAIN SELECT * FROM t1 771 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 772 id estRows task access object operator info 773 IndexReader_10 10.00 root partition:p20090402 index:Selection_9 774 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59) 775 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 776 EXPLAIN SELECT * FROM t1 777 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 778 id estRows task access object operator info 779 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 780 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59) 781 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 782 EXPLAIN SELECT * FROM t1 783 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 784 id estRows task access object operator info 785 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 786 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59) 787 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 788 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 789 id estRows task access object operator info 790 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 791 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03) 792 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 793 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 794 id estRows task access object operator info 795 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 796 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03) 797 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 798 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 799 id estRows task access object operator info 800 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 801 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03) 802 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 803 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 804 id estRows task access object operator info 805 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 806 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03) 807 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 808 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 809 id estRows task access object operator info 810 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 811 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03) 812 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 813 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 814 id estRows task access object operator info 815 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 816 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 817 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 818 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 819 id estRows task access object operator info 820 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 821 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 822 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 823 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 824 id estRows task access object operator info 825 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 826 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 827 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 828 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 829 id estRows task access object operator info 830 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 831 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 832 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 833 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 834 id estRows task access object operator info 835 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 836 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 837 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 838 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 839 id estRows task access object operator info 840 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 841 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59.000000) 842 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 843 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 844 id estRows task access object operator info 845 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 846 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59.000000) 847 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 848 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 849 id estRows task access object operator info 850 IndexReader_10 10.00 root partition:p20090402 index:Selection_9 851 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59.000000) 852 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 853 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 854 id estRows task access object operator info 855 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 856 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59.000000) 857 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 858 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 859 id estRows task access object operator info 860 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 861 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59.000000) 862 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 863 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03'; 864 id estRows task access object operator info 865 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 866 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 867 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 868 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03'; 869 id estRows task access object operator info 870 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 871 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 872 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 873 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03'; 874 id estRows task access object operator info 875 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 876 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 877 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 878 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03'; 879 id estRows task access object operator info 880 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 881 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 882 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 883 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03'; 884 id estRows task access object operator info 885 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 886 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 887 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 888 EXPLAIN SELECT * FROM t1 889 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 890 id estRows task access object operator info 891 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 892 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:01) 893 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 894 EXPLAIN SELECT * FROM t1 895 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 896 id estRows task access object operator info 897 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 898 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:01) 899 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 900 EXPLAIN SELECT * FROM t1 901 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 902 id estRows task access object operator info 903 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 904 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:01) 905 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 906 EXPLAIN SELECT * FROM t1 907 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 908 id estRows task access object operator info 909 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 910 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:01) 911 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 912 EXPLAIN SELECT * FROM t1 913 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 914 id estRows task access object operator info 915 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 916 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:01) 917 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 918 EXPLAIN SELECT * FROM t1 919 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 920 id estRows task access object operator info 921 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 922 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:58) 923 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 924 EXPLAIN SELECT * FROM t1 925 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 926 id estRows task access object operator info 927 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 928 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:58) 929 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 930 EXPLAIN SELECT * FROM t1 931 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 932 id estRows task access object operator info 933 IndexReader_10 10.00 root partition:p20090402 index:Selection_9 934 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:58) 935 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 936 EXPLAIN SELECT * FROM t1 937 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 938 id estRows task access object operator info 939 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 940 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:58) 941 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 942 EXPLAIN SELECT * FROM t1 943 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 944 id estRows task access object operator info 945 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 946 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:58) 947 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 948 DROP TABLE t1; 949 # Test with DATE column NOT NULL 950 CREATE TABLE t1 ( 951 a int(10) unsigned NOT NULL, 952 b DATE NOT NULL, 953 PRIMARY KEY (a, b) 954 ) PARTITION BY RANGE (TO_DAYS(b)) 955 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 956 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 957 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 958 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 959 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 960 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 961 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), 962 (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), 963 (1, '2009-04-07'); 964 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 965 id estRows task access object operator info 966 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 967 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00) 968 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 969 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 970 id estRows task access object operator info 971 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 972 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00) 973 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 974 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 975 id estRows task access object operator info 976 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 977 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00) 978 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 979 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 980 id estRows task access object operator info 981 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 982 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00) 983 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 984 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 985 id estRows task access object operator info 986 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 987 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00) 988 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 989 EXPLAIN SELECT * FROM t1 990 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 991 id estRows task access object operator info 992 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 993 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59) 994 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 995 EXPLAIN SELECT * FROM t1 996 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 997 id estRows task access object operator info 998 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 999 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59) 1000 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1001 EXPLAIN SELECT * FROM t1 1002 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 1003 id estRows task access object operator info 1004 IndexReader_10 0.00 root partition:p20090402 index:Selection_9 1005 └─Selection_9 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59) 1006 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1007 EXPLAIN SELECT * FROM t1 1008 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 1009 id estRows task access object operator info 1010 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 1011 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59) 1012 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1013 EXPLAIN SELECT * FROM t1 1014 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 1015 id estRows task access object operator info 1016 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 1017 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59) 1018 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1019 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 1020 id estRows task access object operator info 1021 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1022 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03) 1023 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1024 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 1025 id estRows task access object operator info 1026 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1027 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03) 1028 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1029 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 1030 id estRows task access object operator info 1031 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 1032 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03) 1033 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1034 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 1035 id estRows task access object operator info 1036 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1037 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03) 1038 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1039 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 1040 id estRows task access object operator info 1041 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1042 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03) 1043 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1044 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 1045 id estRows task access object operator info 1046 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1047 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 1048 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1049 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 1050 id estRows task access object operator info 1051 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1052 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 1053 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1054 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 1055 id estRows task access object operator info 1056 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 1057 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 1058 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1059 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 1060 id estRows task access object operator info 1061 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1062 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 1063 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1064 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 1065 id estRows task access object operator info 1066 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1067 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 1068 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1069 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 1070 id estRows task access object operator info 1071 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 1072 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59.000000) 1073 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1074 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 1075 id estRows task access object operator info 1076 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 1077 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59.000000) 1078 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1079 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 1080 id estRows task access object operator info 1081 IndexReader_10 0.00 root partition:p20090402 index:Selection_9 1082 └─Selection_9 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59.000000) 1083 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1084 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 1085 id estRows task access object operator info 1086 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 1087 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59.000000) 1088 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1089 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 1090 id estRows task access object operator info 1091 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 1092 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59.000000) 1093 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1094 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03'; 1095 id estRows task access object operator info 1096 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1097 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 1098 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1099 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03'; 1100 id estRows task access object operator info 1101 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1102 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 1103 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1104 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03'; 1105 id estRows task access object operator info 1106 IndexReader_10 10.00 root partition:p20090403 index:Selection_9 1107 └─Selection_9 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 1108 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1109 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03'; 1110 id estRows task access object operator info 1111 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1112 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 1113 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1114 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03'; 1115 id estRows task access object operator info 1116 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1117 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 1118 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1119 EXPLAIN SELECT * FROM t1 1120 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 1121 id estRows task access object operator info 1122 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1123 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:01) 1124 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1125 EXPLAIN SELECT * FROM t1 1126 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 1127 id estRows task access object operator info 1128 IndexReader_10 3323.33 root partition:p20090401,p20090402,p20090403 index:Selection_9 1129 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:01) 1130 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1131 EXPLAIN SELECT * FROM t1 1132 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 1133 id estRows task access object operator info 1134 IndexReader_10 0.00 root partition:p20090403 index:Selection_9 1135 └─Selection_9 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:01) 1136 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1137 EXPLAIN SELECT * FROM t1 1138 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 1139 id estRows task access object operator info 1140 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1141 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:01) 1142 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1143 EXPLAIN SELECT * FROM t1 1144 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 1145 id estRows task access object operator info 1146 IndexReader_10 3333.33 root partition:p20090403,p20090404,p20090405 index:Selection_9 1147 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:01) 1148 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1149 EXPLAIN SELECT * FROM t1 1150 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 1151 id estRows task access object operator info 1152 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 1153 └─Selection_9 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:58) 1154 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1155 EXPLAIN SELECT * FROM t1 1156 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 1157 id estRows task access object operator info 1158 IndexReader_10 3323.33 root partition:p20090401,p20090402 index:Selection_9 1159 └─Selection_9 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:58) 1160 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1161 EXPLAIN SELECT * FROM t1 1162 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 1163 id estRows task access object operator info 1164 IndexReader_10 0.00 root partition:p20090402 index:Selection_9 1165 └─Selection_9 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:58) 1166 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1167 EXPLAIN SELECT * FROM t1 1168 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 1169 id estRows task access object operator info 1170 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 1171 └─Selection_9 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:58) 1172 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1173 EXPLAIN SELECT * FROM t1 1174 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 1175 id estRows task access object operator info 1176 IndexReader_10 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 index:Selection_9 1177 └─Selection_9 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:58) 1178 └─IndexFullScan_8 10000.00 cop[einsteindb] causet:t1, index:PRIMARY(a, b) keep order:false, stats:pseudo 1179 DROP TABLE t1; 1180 # Test with DATETIME column NULL 1181 CREATE TABLE t1 ( 1182 a int(10) unsigned NOT NULL, 1183 b DATETIME NULL 1184 ) PARTITION BY RANGE (TO_DAYS(b)) 1185 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 1186 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 1187 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 1188 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 1189 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 1190 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 1191 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), 1192 (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), 1193 (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); 1194 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 1195 id estRows task access object operator info 1196 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1197 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00) 1198 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1199 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 1200 id estRows task access object operator info 1201 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1202 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00) 1203 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1204 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 1205 id estRows task access object operator info 1206 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1207 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00) 1208 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1209 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 1210 id estRows task access object operator info 1211 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1212 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00) 1213 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1214 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 1215 id estRows task access object operator info 1216 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1217 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00) 1218 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1219 EXPLAIN SELECT * FROM t1 1220 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 1221 id estRows task access object operator info 1222 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1223 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59) 1224 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1225 EXPLAIN SELECT * FROM t1 1226 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 1227 id estRows task access object operator info 1228 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1229 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59) 1230 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1231 EXPLAIN SELECT * FROM t1 1232 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 1233 id estRows task access object operator info 1234 TableReader_7 10.00 root partition:p20090402 data:Selection_6 1235 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59) 1236 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1237 EXPLAIN SELECT * FROM t1 1238 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 1239 id estRows task access object operator info 1240 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1241 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59) 1242 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1243 EXPLAIN SELECT * FROM t1 1244 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 1245 id estRows task access object operator info 1246 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1247 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59) 1248 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1249 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 1250 id estRows task access object operator info 1251 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1252 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03) 1253 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1254 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 1255 id estRows task access object operator info 1256 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1257 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03) 1258 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1259 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 1260 id estRows task access object operator info 1261 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1262 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03) 1263 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1264 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 1265 id estRows task access object operator info 1266 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1267 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03) 1268 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1269 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 1270 id estRows task access object operator info 1271 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1272 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03) 1273 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1274 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 1275 id estRows task access object operator info 1276 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1277 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 1278 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1279 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 1280 id estRows task access object operator info 1281 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1282 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 1283 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1284 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 1285 id estRows task access object operator info 1286 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1287 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 1288 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1289 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 1290 id estRows task access object operator info 1291 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1292 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 1293 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1294 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 1295 id estRows task access object operator info 1296 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1297 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 1298 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1299 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 1300 id estRows task access object operator info 1301 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1302 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59.000000) 1303 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1304 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 1305 id estRows task access object operator info 1306 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1307 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59.000000) 1308 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1309 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 1310 id estRows task access object operator info 1311 TableReader_7 10.00 root partition:p20090402 data:Selection_6 1312 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59.000000) 1313 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1314 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 1315 id estRows task access object operator info 1316 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1317 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59.000000) 1318 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1319 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 1320 id estRows task access object operator info 1321 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1322 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59.000000) 1323 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1324 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03'; 1325 id estRows task access object operator info 1326 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1327 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 1328 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1329 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03'; 1330 id estRows task access object operator info 1331 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1332 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 1333 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1334 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03'; 1335 id estRows task access object operator info 1336 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1337 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 1338 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1339 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03'; 1340 id estRows task access object operator info 1341 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1342 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 1343 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1344 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03'; 1345 id estRows task access object operator info 1346 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1347 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 1348 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1349 EXPLAIN SELECT * FROM t1 1350 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 1351 id estRows task access object operator info 1352 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1353 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:01) 1354 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1355 EXPLAIN SELECT * FROM t1 1356 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 1357 id estRows task access object operator info 1358 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1359 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:01) 1360 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1361 EXPLAIN SELECT * FROM t1 1362 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 1363 id estRows task access object operator info 1364 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1365 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:01) 1366 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1367 EXPLAIN SELECT * FROM t1 1368 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 1369 id estRows task access object operator info 1370 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1371 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:01) 1372 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1373 EXPLAIN SELECT * FROM t1 1374 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 1375 id estRows task access object operator info 1376 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1377 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:01) 1378 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1379 EXPLAIN SELECT * FROM t1 1380 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 1381 id estRows task access object operator info 1382 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1383 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:58) 1384 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1385 EXPLAIN SELECT * FROM t1 1386 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 1387 id estRows task access object operator info 1388 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1389 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:58) 1390 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1391 EXPLAIN SELECT * FROM t1 1392 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 1393 id estRows task access object operator info 1394 TableReader_7 10.00 root partition:p20090402 data:Selection_6 1395 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:58) 1396 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1397 EXPLAIN SELECT * FROM t1 1398 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 1399 id estRows task access object operator info 1400 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1401 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:58) 1402 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1403 EXPLAIN SELECT * FROM t1 1404 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 1405 id estRows task access object operator info 1406 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1407 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:58) 1408 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1409 DROP TABLE t1; 1410 # Test with DATE column NULL 1411 CREATE TABLE t1 ( 1412 a int(10) unsigned NOT NULL, 1413 b DATE NULL 1414 ) PARTITION BY RANGE (TO_DAYS(b)) 1415 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 1416 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 1417 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 1418 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 1419 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 1420 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), 1421 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), 1422 (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), 1423 (1, '2009-04-07'); 1424 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); 1425 id estRows task access object operator info 1426 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1427 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00) 1428 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1429 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); 1430 id estRows task access object operator info 1431 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1432 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00) 1433 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1434 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); 1435 id estRows task access object operator info 1436 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1437 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00) 1438 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1439 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); 1440 id estRows task access object operator info 1441 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1442 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00) 1443 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1444 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); 1445 id estRows task access object operator info 1446 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1447 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00) 1448 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1449 EXPLAIN SELECT * FROM t1 1450 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); 1451 id estRows task access object operator info 1452 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1453 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59) 1454 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1455 EXPLAIN SELECT * FROM t1 1456 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); 1457 id estRows task access object operator info 1458 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1459 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59) 1460 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1461 EXPLAIN SELECT * FROM t1 1462 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); 1463 id estRows task access object operator info 1464 TableReader_7 0.00 root partition:p20090402 data:Selection_6 1465 └─Selection_6 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59) 1466 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1467 EXPLAIN SELECT * FROM t1 1468 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); 1469 id estRows task access object operator info 1470 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1471 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59) 1472 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1473 EXPLAIN SELECT * FROM t1 1474 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); 1475 id estRows task access object operator info 1476 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1477 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59) 1478 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1479 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); 1480 id estRows task access object operator info 1481 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1482 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03) 1483 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1484 EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); 1485 id estRows task access object operator info 1486 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1487 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03) 1488 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1489 EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); 1490 id estRows task access object operator info 1491 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1492 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03) 1493 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1494 EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); 1495 id estRows task access object operator info 1496 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1497 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03) 1498 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1499 EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); 1500 id estRows task access object operator info 1501 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1502 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03) 1503 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1504 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; 1505 id estRows task access object operator info 1506 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1507 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 1508 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1509 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; 1510 id estRows task access object operator info 1511 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1512 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 1513 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1514 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; 1515 id estRows task access object operator info 1516 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1517 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 1518 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1519 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; 1520 id estRows task access object operator info 1521 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1522 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 1523 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1524 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; 1525 id estRows task access object operator info 1526 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1527 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 1528 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1529 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; 1530 id estRows task access object operator info 1531 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1532 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:59.000000) 1533 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1534 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; 1535 id estRows task access object operator info 1536 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1537 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:59.000000) 1538 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1539 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; 1540 id estRows task access object operator info 1541 TableReader_7 0.00 root partition:p20090402 data:Selection_6 1542 └─Selection_6 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:59.000000) 1543 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1544 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; 1545 id estRows task access object operator info 1546 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1547 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:59.000000) 1548 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1549 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; 1550 id estRows task access object operator info 1551 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1552 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:59.000000) 1553 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1554 EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03'; 1555 id estRows task access object operator info 1556 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1557 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:00.000000) 1558 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1559 EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03'; 1560 id estRows task access object operator info 1561 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1562 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:00.000000) 1563 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1564 EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03'; 1565 id estRows task access object operator info 1566 TableReader_7 10.00 root partition:p20090403 data:Selection_6 1567 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:00.000000) 1568 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1569 EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03'; 1570 id estRows task access object operator info 1571 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1572 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:00.000000) 1573 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1574 EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03'; 1575 id estRows task access object operator info 1576 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1577 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:00.000000) 1578 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1579 EXPLAIN SELECT * FROM t1 1580 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); 1581 id estRows task access object operator info 1582 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1583 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-03 00:00:01) 1584 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1585 EXPLAIN SELECT * FROM t1 1586 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); 1587 id estRows task access object operator info 1588 TableReader_7 3323.33 root partition:p20090401,p20090402,p20090403 data:Selection_6 1589 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-03 00:00:01) 1590 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1591 EXPLAIN SELECT * FROM t1 1592 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); 1593 id estRows task access object operator info 1594 TableReader_7 0.00 root partition:p20090403 data:Selection_6 1595 └─Selection_6 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-03 00:00:01) 1596 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1597 EXPLAIN SELECT * FROM t1 1598 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); 1599 id estRows task access object operator info 1600 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1601 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-03 00:00:01) 1602 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1603 EXPLAIN SELECT * FROM t1 1604 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); 1605 id estRows task access object operator info 1606 TableReader_7 3333.33 root partition:p20090403,p20090404,p20090405 data:Selection_6 1607 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-03 00:00:01) 1608 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1609 EXPLAIN SELECT * FROM t1 1610 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); 1611 id estRows task access object operator info 1612 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1613 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.b, 2009-04-02 23:59:58) 1614 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1615 EXPLAIN SELECT * FROM t1 1616 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); 1617 id estRows task access object operator info 1618 TableReader_7 3323.33 root partition:p20090401,p20090402 data:Selection_6 1619 └─Selection_6 3323.33 cop[einsteindb] le(test.t1.b, 2009-04-02 23:59:58) 1620 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1621 EXPLAIN SELECT * FROM t1 1622 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); 1623 id estRows task access object operator info 1624 TableReader_7 0.00 root partition:p20090402 data:Selection_6 1625 └─Selection_6 0.00 cop[einsteindb] eq(test.t1.b, 2009-04-02 23:59:58) 1626 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1627 EXPLAIN SELECT * FROM t1 1628 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); 1629 id estRows task access object operator info 1630 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1631 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.b, 2009-04-02 23:59:58) 1632 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1633 EXPLAIN SELECT * FROM t1 1634 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); 1635 id estRows task access object operator info 1636 TableReader_7 3333.33 root partition:p20090402,p20090403,p20090404,p20090405 data:Selection_6 1637 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.b, 2009-04-02 23:59:58) 1638 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1639 DROP TABLE t1; 1640 # For better code coverage of the patch 1641 CREATE TABLE t1 ( 1642 a int(10) unsigned NOT NULL, 1643 b DATE 1644 ) PARTITION BY RANGE ( TO_DAYS(b) ) 1645 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), 1646 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), 1647 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), 1648 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), 1649 PARTITION p20090405 VALUES LESS THAN MAXVALUE); 1650 INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); 1651 # test with an invalid date, which lead to item->null_value is set. 1652 EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); 1653 id estRows task access object operator info 1654 TableReader_7 0.00 root partition:all data:Selection_6 1655 └─Selection_6 0.00 cop[einsteindb] lt(test.t1.b, NULL) 1656 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1657 DROP TABLE t1; 1658 CREATE TABLE t1 1659 (a INT NOT NULL AUTO_INCREMENT, 1660 b DATETIME, 1661 PRIMARY KEY (a,b), 1662 KEY (b)) 1663 PARTITION BY RANGE (to_days(b)) 1664 (PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01', 1665 PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01', 1666 PARTITION pX VALUES LESS THAN MAXVALUE); 1667 SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00'; 1668 a b 1669 DROP TABLE t1; 1670 create causet t3 ( 1671 a int 1672 ) 1673 partition by range (a*1) ( 1674 partition p0 values less than (10), 1675 partition p1 values less than (20) 1676 ); 1677 insert into t3 values (5),(15); 1678 explain select * from t3 where a=11; 1679 id estRows task access object operator info 1680 TableReader_7 10.00 root partition:p1 data:Selection_6 1681 └─Selection_6 10.00 cop[einsteindb] eq(test.t3.a, 11) 1682 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t3 keep order:false, stats:pseudo 1683 explain select * from t3 where a=10; 1684 id estRows task access object operator info 1685 TableReader_7 10.00 root partition:p1 data:Selection_6 1686 └─Selection_6 10.00 cop[einsteindb] eq(test.t3.a, 10) 1687 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t3 keep order:false, stats:pseudo 1688 explain select * from t3 where a=20; 1689 id estRows task access object operator info 1690 TableReader_7 10.00 root partition:dual data:Selection_6 1691 └─Selection_6 10.00 cop[einsteindb] eq(test.t3.a, 20) 1692 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t3 keep order:false, stats:pseudo 1693 explain select * from t3 where a=30; 1694 id estRows task access object operator info 1695 TableReader_7 10.00 root partition:dual data:Selection_6 1696 └─Selection_6 10.00 cop[einsteindb] eq(test.t3.a, 30) 1697 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t3 keep order:false, stats:pseudo 1698 create causet t7 (a int not null) partition by RANGE(a) ( 1699 partition p10 values less than (10), 1700 partition p30 values less than (30), 1701 partition p50 values less than (50), 1702 partition p70 values less than (70), 1703 partition p90 values less than (90) 1704 ); 1705 insert into t7 values (10),(30),(50); 1706 explain select * from t7 where a < 5; 1707 id estRows task access object operator info 1708 TableReader_7 3323.33 root partition:p10 data:Selection_6 1709 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 5) 1710 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1711 explain select * from t7 where a < 9; 1712 id estRows task access object operator info 1713 TableReader_7 3323.33 root partition:p10 data:Selection_6 1714 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 9) 1715 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1716 explain select * from t7 where a <= 9; 1717 id estRows task access object operator info 1718 TableReader_7 3323.33 root partition:p10 data:Selection_6 1719 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 9) 1720 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1721 explain select * from t7 where a = 9; 1722 id estRows task access object operator info 1723 TableReader_7 10.00 root partition:p10 data:Selection_6 1724 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 9) 1725 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1726 explain select * from t7 where a >= 9; 1727 id estRows task access object operator info 1728 TableReader_7 3333.33 root partition:all data:Selection_6 1729 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 9) 1730 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1731 explain select * from t7 where a > 9; 1732 id estRows task access object operator info 1733 TableReader_7 3333.33 root partition:p30,p50,p70,p90 data:Selection_6 1734 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 9) 1735 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1736 explain select * from t7 where a < 10; 1737 id estRows task access object operator info 1738 TableReader_7 3323.33 root partition:p10 data:Selection_6 1739 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 10) 1740 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1741 explain select * from t7 where a <= 10; 1742 id estRows task access object operator info 1743 TableReader_7 3323.33 root partition:p10,p30 data:Selection_6 1744 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 10) 1745 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1746 explain select * from t7 where a = 10; 1747 id estRows task access object operator info 1748 TableReader_7 10.00 root partition:p30 data:Selection_6 1749 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 10) 1750 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1751 explain select * from t7 where a >= 10; 1752 id estRows task access object operator info 1753 TableReader_7 3333.33 root partition:p30,p50,p70,p90 data:Selection_6 1754 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 10) 1755 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1756 explain select * from t7 where a > 10; 1757 id estRows task access object operator info 1758 TableReader_7 3333.33 root partition:p30,p50,p70,p90 data:Selection_6 1759 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 10) 1760 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1761 explain select * from t7 where a < 89; 1762 id estRows task access object operator info 1763 TableReader_7 3323.33 root partition:all data:Selection_6 1764 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 89) 1765 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1766 explain select * from t7 where a <= 89; 1767 id estRows task access object operator info 1768 TableReader_7 3323.33 root partition:all data:Selection_6 1769 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 89) 1770 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1771 explain select * from t7 where a = 89; 1772 id estRows task access object operator info 1773 TableReader_7 10.00 root partition:p90 data:Selection_6 1774 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 89) 1775 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1776 explain select * from t7 where a > 89; 1777 id estRows task access object operator info 1778 TableReader_7 3333.33 root partition:dual data:Selection_6 1779 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 89) 1780 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1781 explain select * from t7 where a >= 89; 1782 id estRows task access object operator info 1783 TableReader_7 3333.33 root partition:p90 data:Selection_6 1784 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 89) 1785 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1786 explain select * from t7 where a < 90; 1787 id estRows task access object operator info 1788 TableReader_7 3323.33 root partition:all data:Selection_6 1789 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 90) 1790 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1791 explain select * from t7 where a <= 90; 1792 id estRows task access object operator info 1793 TableReader_7 3323.33 root partition:all data:Selection_6 1794 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 90) 1795 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1796 explain select * from t7 where a = 90; 1797 id estRows task access object operator info 1798 TableReader_7 10.00 root partition:dual data:Selection_6 1799 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 90) 1800 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1801 explain select * from t7 where a > 90; 1802 id estRows task access object operator info 1803 TableReader_7 3333.33 root partition:dual data:Selection_6 1804 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 90) 1805 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1806 explain select * from t7 where a >= 90; 1807 id estRows task access object operator info 1808 TableReader_7 3333.33 root partition:dual data:Selection_6 1809 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 90) 1810 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1811 explain select * from t7 where a > 91; 1812 id estRows task access object operator info 1813 TableReader_7 3333.33 root partition:dual data:Selection_6 1814 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 91) 1815 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1816 explain select * from t7 where a > 11 and a < 29; 1817 id estRows task access object operator info 1818 TableReader_7 250.00 root partition:p30 data:Selection_6 1819 └─Selection_6 250.00 cop[einsteindb] gt(test.t7.a, 11), lt(test.t7.a, 29) 1820 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1821 drop causet t7; 1822 create causet t7 (a int unsigned not null) partition by RANGE(a) ( 1823 partition p10 values less than (10), 1824 partition p30 values less than (30), 1825 partition p50 values less than (50), 1826 partition p70 values less than (70), 1827 partition p90 values less than (90) 1828 ); 1829 insert into t7 values (10),(30),(50); 1830 explain select * from t7 where a < 5; 1831 id estRows task access object operator info 1832 TableReader_7 3323.33 root partition:p10 data:Selection_6 1833 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 5) 1834 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1835 explain select * from t7 where a < 9; 1836 id estRows task access object operator info 1837 TableReader_7 3323.33 root partition:p10 data:Selection_6 1838 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 9) 1839 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1840 explain select * from t7 where a <= 9; 1841 id estRows task access object operator info 1842 TableReader_7 3323.33 root partition:p10 data:Selection_6 1843 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 9) 1844 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1845 explain select * from t7 where a = 9; 1846 id estRows task access object operator info 1847 TableReader_7 10.00 root partition:p10 data:Selection_6 1848 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 9) 1849 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1850 explain select * from t7 where a >= 9; 1851 id estRows task access object operator info 1852 TableReader_7 3333.33 root partition:all data:Selection_6 1853 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 9) 1854 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1855 explain select * from t7 where a > 9; 1856 id estRows task access object operator info 1857 TableReader_7 3333.33 root partition:p30,p50,p70,p90 data:Selection_6 1858 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 9) 1859 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1860 explain select * from t7 where a < 10; 1861 id estRows task access object operator info 1862 TableReader_7 3323.33 root partition:p10 data:Selection_6 1863 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 10) 1864 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1865 explain select * from t7 where a <= 10; 1866 id estRows task access object operator info 1867 TableReader_7 3323.33 root partition:p10,p30 data:Selection_6 1868 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 10) 1869 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1870 explain select * from t7 where a = 10; 1871 id estRows task access object operator info 1872 TableReader_7 10.00 root partition:p30 data:Selection_6 1873 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 10) 1874 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1875 explain select * from t7 where a >= 10; 1876 id estRows task access object operator info 1877 TableReader_7 3333.33 root partition:p30,p50,p70,p90 data:Selection_6 1878 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 10) 1879 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1880 explain select * from t7 where a > 10; 1881 id estRows task access object operator info 1882 TableReader_7 3333.33 root partition:p30,p50,p70,p90 data:Selection_6 1883 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 10) 1884 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1885 explain select * from t7 where a < 89; 1886 id estRows task access object operator info 1887 TableReader_7 3323.33 root partition:all data:Selection_6 1888 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 89) 1889 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1890 explain select * from t7 where a <= 89; 1891 id estRows task access object operator info 1892 TableReader_7 3323.33 root partition:all data:Selection_6 1893 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 89) 1894 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1895 explain select * from t7 where a = 89; 1896 id estRows task access object operator info 1897 TableReader_7 10.00 root partition:p90 data:Selection_6 1898 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 89) 1899 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1900 explain select * from t7 where a > 89; 1901 id estRows task access object operator info 1902 TableReader_7 3333.33 root partition:dual data:Selection_6 1903 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 89) 1904 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1905 explain select * from t7 where a >= 89; 1906 id estRows task access object operator info 1907 TableReader_7 3333.33 root partition:p90 data:Selection_6 1908 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 89) 1909 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1910 explain select * from t7 where a < 90; 1911 id estRows task access object operator info 1912 TableReader_7 3323.33 root partition:all data:Selection_6 1913 └─Selection_6 3323.33 cop[einsteindb] lt(test.t7.a, 90) 1914 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1915 explain select * from t7 where a <= 90; 1916 id estRows task access object operator info 1917 TableReader_7 3323.33 root partition:all data:Selection_6 1918 └─Selection_6 3323.33 cop[einsteindb] le(test.t7.a, 90) 1919 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1920 explain select * from t7 where a = 90; 1921 id estRows task access object operator info 1922 TableReader_7 10.00 root partition:dual data:Selection_6 1923 └─Selection_6 10.00 cop[einsteindb] eq(test.t7.a, 90) 1924 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1925 explain select * from t7 where a > 90; 1926 id estRows task access object operator info 1927 TableReader_7 3333.33 root partition:dual data:Selection_6 1928 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 90) 1929 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1930 explain select * from t7 where a >= 90; 1931 id estRows task access object operator info 1932 TableReader_7 3333.33 root partition:dual data:Selection_6 1933 └─Selection_6 3333.33 cop[einsteindb] ge(test.t7.a, 90) 1934 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1935 explain select * from t7 where a > 91; 1936 id estRows task access object operator info 1937 TableReader_7 3333.33 root partition:dual data:Selection_6 1938 └─Selection_6 3333.33 cop[einsteindb] gt(test.t7.a, 91) 1939 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1940 explain select * from t7 where a > 11 and a < 29; 1941 id estRows task access object operator info 1942 TableReader_7 250.00 root partition:p30 data:Selection_6 1943 └─Selection_6 250.00 cop[einsteindb] gt(test.t7.a, 11), lt(test.t7.a, 29) 1944 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t7 keep order:false, stats:pseudo 1945 create causet t8 (a date not null) partition by RANGE(YEAR(a)) ( 1946 partition p0 values less than (1980), 1947 partition p1 values less than (1990), 1948 partition p2 values less than (2000) 1949 ); 1950 insert into t8 values ('1985-05-05'),('1995-05-05'); 1951 explain select * from t8 where a < '1980-02-02'; 1952 id estRows task access object operator info 1953 TableReader_7 3323.33 root partition:all data:Selection_6 1954 └─Selection_6 3323.33 cop[einsteindb] lt(test.t8.a, 1980-02-02 00:00:00.000000) 1955 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t8 keep order:false, stats:pseudo 1956 create causet t9 (a date not null) partition by RANGE(TO_DAYS(a)) ( 1957 partition p0 values less than (732299), -- 2004-12-19 1958 partition p1 values less than (732468), -- 2005-06-06 1959 partition p2 values less than (732664) -- 2005-12-19 1960 ); 1961 insert into t9 values ('2005-05-05'), ('2005-04-04'); 1962 explain select * from t9 where a < '2004-12-19'; 1963 id estRows task access object operator info 1964 TableReader_7 3323.33 root partition:p0,p1 data:Selection_6 1965 └─Selection_6 3323.33 cop[einsteindb] lt(test.t9.a, 2004-12-19 00:00:00.000000) 1966 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t9 keep order:false, stats:pseudo 1967 explain select * from t9 where a <= '2004-12-19'; 1968 id estRows task access object operator info 1969 TableReader_7 3323.33 root partition:p0,p1 data:Selection_6 1970 └─Selection_6 3323.33 cop[einsteindb] le(test.t9.a, 2004-12-19 00:00:00.000000) 1971 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t9 keep order:false, stats:pseudo 1972 drop causet t7,t8,t9; 1973 create causet t1 ( 1974 a1 int not null 1975 ) 1976 partition by range (a1) ( 1977 partition p0 values less than (3), 1978 partition p1 values less than (6), 1979 partition p2 values less than (9) 1980 ); 1981 insert into t1 values (1),(2),(3); 1982 explain select * from t1 where a1 > 3; 1983 id estRows task access object operator info 1984 TableReader_7 3333.33 root partition:p1,p2 data:Selection_6 1985 └─Selection_6 3333.33 cop[einsteindb] gt(test.t1.a1, 3) 1986 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1987 explain select * from t1 where a1 >= 3; 1988 id estRows task access object operator info 1989 TableReader_7 3333.33 root partition:p1,p2 data:Selection_6 1990 └─Selection_6 3333.33 cop[einsteindb] ge(test.t1.a1, 3) 1991 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1992 explain select * from t1 where a1 < 3 and a1 > 3; 1993 id estRows task access object operator info 1994 TableReader_7 0.00 root partition:dual data:Selection_6 1995 └─Selection_6 0.00 cop[einsteindb] gt(test.t1.a1, 3), lt(test.t1.a1, 3) 1996 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 1997 drop causet t1; 1998 CREATE TABLE `t1` ( 1999 `a` int(11) default NULL 2000 ); 2001 INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2002 CREATE TABLE `t2` ( 2003 `a` int(11) default NULL, 2004 KEY `a` (`a`) 2005 ) ; 2006 insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ; 2007 insert into t1 select a from t2; 2008 drop causet t2; 2009 CREATE TABLE `t2` ( 2010 `a` int(11) default NULL, 2011 `b` int(11) default NULL 2012 ) 2013 PARTITION BY RANGE (a) ( 2014 PARTITION p0 VALUES LESS THAN (200), 2015 PARTITION p1 VALUES LESS THAN (400), 2016 PARTITION p2 VALUES LESS THAN (600), 2017 PARTITION p3 VALUES LESS THAN (800), 2018 PARTITION p4 VALUES LESS THAN (1001)); 2019 insert into t2 select a,1 from t1 where a < 200; 2020 insert into t2 select a,2 from t1 where a >= 200 and a < 400; 2021 insert into t2 select a,3 from t1 where a >= 400 and a < 600; 2022 insert into t2 select a,4 from t1 where a >= 600 and a < 800; 2023 insert into t2 select a,5 from t1 where a >= 800 and a < 1001; 2024 explain select * from t2; 2025 id estRows task access object operator info 2026 TableReader_5 10000.00 root partition:all data:TableFullScan_4 2027 └─TableFullScan_4 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2028 explain select * from t2 where a < 801 and a > 200; 2029 id estRows task access object operator info 2030 TableReader_7 250.00 root partition:p1,p2,p3,p4 data:Selection_6 2031 └─Selection_6 250.00 cop[einsteindb] gt(test.t2.a, 200), lt(test.t2.a, 801) 2032 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2033 explain select * from t2 where a < 801 and a > 800; 2034 id estRows task access object operator info 2035 TableReader_7 0.00 root partition:p4 data:Selection_6 2036 └─Selection_6 0.00 cop[einsteindb] gt(test.t2.a, 800), lt(test.t2.a, 801) 2037 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2038 explain select * from t2 where a > 600; 2039 id estRows task access object operator info 2040 TableReader_7 3333.33 root partition:p3,p4 data:Selection_6 2041 └─Selection_6 3333.33 cop[einsteindb] gt(test.t2.a, 600) 2042 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2043 explain select * from t2 where a > 600 and b = 1; 2044 id estRows task access object operator info 2045 TableReader_7 3.33 root partition:p3,p4 data:Selection_6 2046 └─Selection_6 3.33 cop[einsteindb] eq(test.t2.b, 1), gt(test.t2.a, 600) 2047 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2048 explain select * from t2 where a > 600 and b = 4; 2049 id estRows task access object operator info 2050 TableReader_7 3.33 root partition:p3,p4 data:Selection_6 2051 └─Selection_6 3.33 cop[einsteindb] eq(test.t2.b, 4), gt(test.t2.a, 600) 2052 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2053 explain select * from t2 where a > 600 and b = 5; 2054 id estRows task access object operator info 2055 TableReader_7 3.33 root partition:p3,p4 data:Selection_6 2056 └─Selection_6 3.33 cop[einsteindb] eq(test.t2.b, 5), gt(test.t2.a, 600) 2057 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2058 explain select * from t2 where b = 5; 2059 id estRows task access object operator info 2060 TableReader_7 10.00 root partition:all data:Selection_6 2061 └─Selection_6 10.00 cop[einsteindb] eq(test.t2.b, 5) 2062 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2063 flush status; 2064 uFIDelate t2 set b = 100 where b = 6; 2065 show status like 'Handler_read_rnd_next'; 2066 Variable_name Value 2067 flush status; 2068 uFIDelate t2 set a = 1002 where a = 1001; 2069 show status like 'Handler_read_rnd_next'; 2070 Variable_name Value 2071 flush status; 2072 uFIDelate t2 set b = 6 where a = 600; 2073 show status like 'Handler_read_rnd_next'; 2074 Variable_name Value 2075 flush status; 2076 uFIDelate t2 set b = 6 where a > 600 and a < 800; 2077 show status like 'Handler_read_rnd_next'; 2078 Variable_name Value 2079 flush status; 2080 delete from t2 where a > 600; 2081 show status like 'Handler_read_rnd_next'; 2082 Variable_name Value 2083 drop causet t2; 2084 CREATE TABLE `t2` ( 2085 `a` int(11) default NULL, 2086 `b` int(11) default NULL, 2087 index (b) 2088 ) 2089 PARTITION BY RANGE (a) ( 2090 PARTITION p0 VALUES LESS THAN (200), 2091 PARTITION p1 VALUES LESS THAN (400), 2092 PARTITION p2 VALUES LESS THAN (600), 2093 PARTITION p3 VALUES LESS THAN (800), 2094 PARTITION p4 VALUES LESS THAN (1001)); 2095 insert into t2 select a,1 from t1 where a < 100; 2096 insert into t2 select a,2 from t1 where a >= 200 and a < 300; 2097 insert into t2 select a,3 from t1 where a >= 300 and a < 400; 2098 insert into t2 select a,4 from t1 where a >= 400 and a < 500; 2099 insert into t2 select a,5 from t1 where a >= 500 and a < 600; 2100 insert into t2 select a,6 from t1 where a >= 600 and a < 700; 2101 insert into t2 select a,7 from t1 where a >= 700 and a < 800; 2102 insert into t2 select a,8 from t1 where a >= 800 and a < 900; 2103 insert into t2 select a,9 from t1 where a >= 900 and a < 1001; 2104 explain select * from t2; 2105 id estRows task access object operator info 2106 TableReader_5 10000.00 root partition:all data:TableFullScan_4 2107 └─TableFullScan_4 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2108 explain select * from t2 where a = 101; 2109 id estRows task access object operator info 2110 TableReader_7 10.00 root partition:p0 data:Selection_6 2111 └─Selection_6 10.00 cop[einsteindb] eq(test.t2.a, 101) 2112 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2113 explain select * from t2 where a = 550; 2114 id estRows task access object operator info 2115 TableReader_7 10.00 root partition:p2 data:Selection_6 2116 └─Selection_6 10.00 cop[einsteindb] eq(test.t2.a, 550) 2117 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2118 explain select * from t2 where a = 833; 2119 id estRows task access object operator info 2120 TableReader_7 10.00 root partition:p4 data:Selection_6 2121 └─Selection_6 10.00 cop[einsteindb] eq(test.t2.a, 833) 2122 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2123 explain select * from t2 where a in (10,20,30); 2124 id estRows task access object operator info 2125 TableReader_7 30.00 root partition:p0 data:Selection_6 2126 └─Selection_6 30.00 cop[einsteindb] in(test.t2.a, 10, 20, 30) 2127 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2128 explain select * from t2 where (a = 100 OR a = 900); 2129 id estRows task access object operator info 2130 TableReader_7 20.00 root partition:p0,p4 data:Selection_6 2131 └─Selection_6 20.00 cop[einsteindb] or(eq(test.t2.a, 100), eq(test.t2.a, 900)) 2132 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2133 explain select * from t2 where (a > 100 AND a < 600); 2134 id estRows task access object operator info 2135 TableReader_7 250.00 root partition:p0,p1,p2 data:Selection_6 2136 └─Selection_6 250.00 cop[einsteindb] gt(test.t2.a, 100), lt(test.t2.a, 600) 2137 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2138 explain select * from t2 where b = 4; 2139 id estRows task access object operator info 2140 IndexLookUp_10 10.00 root partition:all 2141 ├─IndexRangeScan_8(Build) 10.00 cop[einsteindb] causet:t2, index:b(b) range:[4,4], keep order:false, stats:pseudo 2142 └─TableRowIDScan_9(Probe) 10.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2143 explain select * from t2 where b = 6; 2144 id estRows task access object operator info 2145 IndexLookUp_10 10.00 root partition:all 2146 ├─IndexRangeScan_8(Build) 10.00 cop[einsteindb] causet:t2, index:b(b) range:[6,6], keep order:false, stats:pseudo 2147 └─TableRowIDScan_9(Probe) 10.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2148 explain select * from t2 where b in (1,3,5); 2149 id estRows task access object operator info 2150 IndexLookUp_10 30.00 root partition:all 2151 ├─IndexRangeScan_8(Build) 30.00 cop[einsteindb] causet:t2, index:b(b) range:[1,1], [3,3], [5,5], keep order:false, stats:pseudo 2152 └─TableRowIDScan_9(Probe) 30.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2153 explain select * from t2 where b in (2,4,6); 2154 id estRows task access object operator info 2155 IndexLookUp_10 30.00 root partition:all 2156 ├─IndexRangeScan_8(Build) 30.00 cop[einsteindb] causet:t2, index:b(b) range:[2,2], [4,4], [6,6], keep order:false, stats:pseudo 2157 └─TableRowIDScan_9(Probe) 30.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2158 explain select * from t2 where b in (7,8,9); 2159 id estRows task access object operator info 2160 IndexLookUp_10 30.00 root partition:all 2161 ├─IndexRangeScan_8(Build) 30.00 cop[einsteindb] causet:t2, index:b(b) range:[7,7], [8,8], [9,9], keep order:false, stats:pseudo 2162 └─TableRowIDScan_9(Probe) 30.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2163 explain select * from t2 where b > 5; 2164 id estRows task access object operator info 2165 TableReader_7 3333.33 root partition:all data:Selection_6 2166 └─Selection_6 3333.33 cop[einsteindb] gt(test.t2.b, 5) 2167 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2168 explain select * from t2 where b > 5 and b < 8; 2169 id estRows task access object operator info 2170 IndexLookUp_10 250.00 root partition:all 2171 ├─IndexRangeScan_8(Build) 250.00 cop[einsteindb] causet:t2, index:b(b) range:(5,8), keep order:false, stats:pseudo 2172 └─TableRowIDScan_9(Probe) 250.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2173 explain select * from t2 where b > 5 and b < 7; 2174 id estRows task access object operator info 2175 IndexLookUp_10 250.00 root partition:all 2176 ├─IndexRangeScan_8(Build) 250.00 cop[einsteindb] causet:t2, index:b(b) range:(5,7), keep order:false, stats:pseudo 2177 └─TableRowIDScan_9(Probe) 250.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2178 explain select * from t2 where b > 0 and b < 5; 2179 id estRows task access object operator info 2180 IndexLookUp_10 250.00 root partition:all 2181 ├─IndexRangeScan_8(Build) 250.00 cop[einsteindb] causet:t2, index:b(b) range:(0,5), keep order:false, stats:pseudo 2182 └─TableRowIDScan_9(Probe) 250.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2183 flush status; 2184 uFIDelate t2 set a = 111 where b = 10; 2185 show status like 'Handler_read_rnd_next'; 2186 Variable_name Value 2187 show status like 'Handler_read_key'; 2188 Variable_name Value 2189 flush status; 2190 uFIDelate t2 set a = 111 where b in (5,6); 2191 show status like 'Handler_read_rnd_next'; 2192 Variable_name Value 2193 show status like 'Handler_read_key'; 2194 Variable_name Value 2195 flush status; 2196 uFIDelate t2 set a = 222 where b = 7; 2197 show status like 'Handler_read_rnd_next'; 2198 Variable_name Value 2199 show status like 'Handler_read_key'; 2200 Variable_name Value 2201 flush status; 2202 delete from t2 where b = 7; 2203 show status like 'Handler_read_rnd_next'; 2204 Variable_name Value 2205 show status like 'Handler_read_key'; 2206 Variable_name Value 2207 flush status; 2208 delete from t2 where b > 5; 2209 show status like 'Handler_read_rnd_next'; 2210 Variable_name Value 2211 show status like 'Handler_read_key'; 2212 Variable_name Value 2213 show status like 'Handler_read_prev'; 2214 Variable_name Value 2215 show status like 'Handler_read_next'; 2216 Variable_name Value 2217 flush status; 2218 delete from t2 where b < 5 or b > 3; 2219 show status like 'Handler_read_rnd_next'; 2220 Variable_name Value 2221 show status like 'Handler_read_key'; 2222 Variable_name Value 2223 show status like 'Handler_read_prev'; 2224 Variable_name Value 2225 show status like 'Handler_read_next'; 2226 Variable_name Value 2227 drop causet t1, t2; 2228 create causet t1 (s1 int); 2229 explain select 1 from t1 union all select 2; 2230 id estRows task access object operator info 2231 Union_8 10001.00 root 2232 ├─Projection_9 10000.00 root 1->DeferredCauset#5 2233 │ └─TableReader_11 10000.00 root data:TableFullScan_10 2234 │ └─TableFullScan_10 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2235 └─Projection_12 1.00 root 2->DeferredCauset#5 2236 └─TableDual_13 1.00 root rows:1 2237 drop causet t1; 2238 create causet t1 (a int) 2239 partition by range(a) ( 2240 partition p0 values less than (64), 2241 partition p1 values less than (128), 2242 partition p2 values less than (255) 2243 ); 2244 create causet t2 (a int) 2245 partition by range(a+0) ( 2246 partition p0 values less than (64), 2247 partition p1 values less than (128), 2248 partition p2 values less than (255) 2249 ); 2250 insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); 2251 insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); 2252 explain select * from t1 where a=0; 2253 id estRows task access object operator info 2254 TableReader_7 10.00 root partition:p0 data:Selection_6 2255 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.a, 0) 2256 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2257 explain select * from t2 where a=0; 2258 id estRows task access object operator info 2259 TableReader_7 10.00 root partition:p0 data:Selection_6 2260 └─Selection_6 10.00 cop[einsteindb] eq(test.t2.a, 0) 2261 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2262 explain select * from t1 where a=0xFE; 2263 id estRows task access object operator info 2264 TableReader_7 10.00 root partition:p2 data:Selection_6 2265 └─Selection_6 10.00 cop[einsteindb] eq(test.t1.a, 254) 2266 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2267 explain select * from t2 where a=0xFE; 2268 id estRows task access object operator info 2269 TableReader_7 10.00 root partition:p2 data:Selection_6 2270 └─Selection_6 10.00 cop[einsteindb] eq(test.t2.a, 254) 2271 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2272 explain select * from t1 where a > 0xFE AND a <= 0xFF; 2273 id estRows task access object operator info 2274 TableReader_7 250.00 root partition:dual data:Selection_6 2275 └─Selection_6 250.00 cop[einsteindb] gt(test.t1.a, 254), le(test.t1.a, 255) 2276 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2277 explain select * from t2 where a > 0xFE AND a <= 0xFF; 2278 id estRows task access object operator info 2279 TableReader_7 250.00 root partition:all data:Selection_6 2280 └─Selection_6 250.00 cop[einsteindb] gt(test.t2.a, 254), le(test.t2.a, 255) 2281 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2282 explain select * from t1 where a >= 0xFE AND a <= 0xFF; 2283 id estRows task access object operator info 2284 TableReader_7 250.00 root partition:p2 data:Selection_6 2285 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 254), le(test.t1.a, 255) 2286 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2287 explain select * from t2 where a >= 0xFE AND a <= 0xFF; 2288 id estRows task access object operator info 2289 TableReader_7 250.00 root partition:all data:Selection_6 2290 └─Selection_6 250.00 cop[einsteindb] ge(test.t2.a, 254), le(test.t2.a, 255) 2291 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2292 explain select * from t1 where a < 64 AND a >= 63; 2293 id estRows task access object operator info 2294 TableReader_7 250.00 root partition:p0 data:Selection_6 2295 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 63), lt(test.t1.a, 64) 2296 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2297 explain select * from t2 where a < 64 AND a >= 63; 2298 id estRows task access object operator info 2299 TableReader_7 250.00 root partition:all data:Selection_6 2300 └─Selection_6 250.00 cop[einsteindb] ge(test.t2.a, 63), lt(test.t2.a, 64) 2301 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2302 explain select * from t1 where a <= 64 AND a >= 63; 2303 id estRows task access object operator info 2304 TableReader_7 250.00 root partition:p0,p1 data:Selection_6 2305 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 63), le(test.t1.a, 64) 2306 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2307 explain select * from t2 where a <= 64 AND a >= 63; 2308 id estRows task access object operator info 2309 TableReader_7 250.00 root partition:all data:Selection_6 2310 └─Selection_6 250.00 cop[einsteindb] ge(test.t2.a, 63), le(test.t2.a, 64) 2311 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t2 keep order:false, stats:pseudo 2312 drop causet t1; 2313 drop causet t2; 2314 create causet t1(a bigint unsigned not null) partition by range(a+0) ( 2315 partition p1 values less than (10), 2316 partition p2 values less than (20), 2317 partition p3 values less than (2305561538531885056), 2318 partition p4 values less than (2305561538531950591) 2319 ); 2320 insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1); 2321 insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1); 2322 explain select * from t1 where 2323 a >= 2305561538531885056-10 and a <= 2305561538531885056-8; 2324 id estRows task access object operator info 2325 TableReader_7 250.00 root partition:all data:Selection_6 2326 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 2305561538531885046), le(test.t1.a, 2305561538531885048) 2327 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2328 explain select * from t1 where 2329 a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE; 2330 id estRows task access object operator info 2331 TableReader_7 250.00 root partition:all data:Selection_6 2332 └─Selection_6 250.00 cop[einsteindb] gt(test.t1.a, 18446744073709551596), lt(test.t1.a, 18446744073709551598) 2333 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2334 explain select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF; 2335 id estRows task access object operator info 2336 TableReader_7 250.00 root partition:all data:Selection_6 2337 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 0), le(test.t1.a, 18446744073709551615) 2338 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2339 drop causet t1; 2340 create causet t1 (a bigint) partition by range(a+0) ( 2341 partition p1 values less than (-1000), 2342 partition p2 values less than (-10), 2343 partition p3 values less than (10), 2344 partition p4 values less than (1000) 2345 ); 2346 insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15); 2347 explain select * from t1 where a>-2 and a <=0; 2348 id estRows task access object operator info 2349 TableReader_7 250.00 root partition:all data:Selection_6 2350 └─Selection_6 250.00 cop[einsteindb] gt(test.t1.a, -2), le(test.t1.a, 0) 2351 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2352 drop causet t1; 2353 CREATE TABLE t1 ( recdate DATETIME NOT NULL ) 2354 PARTITION BY RANGE( TO_DAYS(recdate) ) ( 2355 PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ), 2356 PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') ) 2357 ); 2358 INSERT INTO t1 VALUES ('2007-03-01 12:00:00'); 2359 INSERT INTO t1 VALUES ('2007-03-07 12:00:00'); 2360 INSERT INTO t1 VALUES ('2007-03-08 12:00:00'); 2361 INSERT INTO t1 VALUES ('2007-03-15 12:00:00'); 2362 explain select * from t1 where recdate < '2007-03-08 00:00:00'; 2363 id estRows task access object operator info 2364 TableReader_7 3323.33 root partition:all data:Selection_6 2365 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.recdate, 2007-03-08 00:00:00.000000) 2366 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2367 drop causet t1; 2368 CREATE TABLE t1 ( recdate DATETIME NOT NULL ) 2369 PARTITION BY RANGE( YEAR(recdate) ) ( 2370 PARTITION p0 VALUES LESS THAN (2006), 2371 PARTITION p1 VALUES LESS THAN (2007) 2372 ); 2373 INSERT INTO t1 VALUES ('2005-03-01 12:00:00'); 2374 INSERT INTO t1 VALUES ('2005-03-01 12:00:00'); 2375 INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); 2376 INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); 2377 explain select * from t1 where recdate < '2006-01-01 00:00:00'; 2378 id estRows task access object operator info 2379 TableReader_7 3323.33 root partition:all data:Selection_6 2380 └─Selection_6 3323.33 cop[einsteindb] lt(test.t1.recdate, 2006-01-01 00:00:00.000000) 2381 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2382 drop causet t1; 2383 create causet t0 (a int); 2384 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2385 create causet t1 (a int) 2386 partition by range(a+0) ( 2387 partition p0 values less than (64), 2388 partition p1 values less than (128), 2389 partition p2 values less than (255) 2390 ); 2391 insert into t1 select A.a + 10*B.a from t0 A, t0 B; 2392 explain select * from t1 where a between 10 and 13; 2393 id estRows task access object operator info 2394 TableReader_7 250.00 root partition:all data:Selection_6 2395 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 10), le(test.t1.a, 13) 2396 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2397 explain select * from t1 where a between 10 and 10+33; 2398 id estRows task access object operator info 2399 TableReader_7 250.00 root partition:all data:Selection_6 2400 └─Selection_6 250.00 cop[einsteindb] ge(test.t1.a, 10), le(test.t1.a, 43) 2401 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t1 keep order:false, stats:pseudo 2402 drop causet t0, t1; 2403 drop causet if exists t; 2404 create causet t(a timestamp) partition by range(unix_timestamp(a)) (partition p0 values less than(unix_timestamp('2020-02-16 14:20:00')), partition p1 values less than (maxvalue)); 2405 explain select * from t where a between timestamp'2020-02-16 14:19:00' and timestamp'2020-02-16 14:21:00'; 2406 id estRows task access object operator info 2407 TableReader_7 250.00 root partition:all data:Selection_6 2408 └─Selection_6 250.00 cop[einsteindb] ge(test.t.a, 2020-02-16 14:19:00), le(test.t.a, 2020-02-16 14:21:00) 2409 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2410 drop causet if exists t; 2411 create causet t(a int) partition by range(a) (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (300)); 2412 begin; 2413 explain select * from t; 2414 id estRows task access object operator info 2415 TableReader_5 10000.00 root partition:all data:TableFullScan_4 2416 └─TableFullScan_4 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2417 insert into t values(1); 2418 explain select * from t; 2419 id estRows task access object operator info 2420 Projection_4 10000.00 root test.t.a 2421 └─UnionScan_5 10000.00 root 2422 └─TableReader_7 10000.00 root partition:all data:TableFullScan_6 2423 └─TableFullScan_6 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2424 rollback; 2425 begin; 2426 insert into t values(101); 2427 explain select * from t; 2428 id estRows task access object operator info 2429 Projection_4 10000.00 root test.t.a 2430 └─UnionScan_5 10000.00 root 2431 └─TableReader_7 10000.00 root partition:all data:TableFullScan_6 2432 └─TableFullScan_6 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2433 rollback; 2434 begin; 2435 insert into t values(201); 2436 explain select * from t; 2437 id estRows task access object operator info 2438 Projection_4 10000.00 root test.t.a 2439 └─UnionScan_5 10000.00 root 2440 └─TableReader_7 10000.00 root partition:all data:TableFullScan_6 2441 └─TableFullScan_6 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2442 rollback; 2443 explain select * from t; 2444 id estRows task access object operator info 2445 TableReader_5 10000.00 root partition:all data:TableFullScan_4 2446 └─TableFullScan_4 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2447 drop causet if exists t; 2448 CREATE TABLE `t` ( 2449 `a` int(11) DEFAULT NULL, 2450 `b` int(11) DEFAULT NULL 2451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 2452 PARTITION BY RANGE COLUMNS(a) ( 2453 PARTITION p0 VALUES LESS THAN (1), 2454 PARTITION p1 VALUES LESS THAN (10), 2455 PARTITION p2 VALUES LESS THAN (100) 2456 ); 2457 desc select * from t where a = 11 and b = 1 or a = 12 and b = 1; 2458 id estRows task access object operator info 2459 TableReader_7 0.02 root partition:p2 data:Selection_6 2460 └─Selection_6 0.02 cop[einsteindb] or(and(eq(test.t.a, 11), eq(test.t.b, 1)), and(eq(test.t.a, 12), eq(test.t.b, 1))) 2461 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2462 drop causet if exists t; 2463 create causet t (ts timestamp(3) not null default current_timestamp(3)) 2464 partition by range (floor(unix_timestamp(ts))) ( 2465 partition p0 values less than (unix_timestamp('2020-04-05 00:00:00')), 2466 partition p1 values less than (unix_timestamp('2020-04-15 00:00:00')), 2467 partition p2 values less than (unix_timestamp('2020-04-25 00:00:00')) 2468 ); 2469 explain select * from t where ts = '2020-04-06 00:00:00' -- p1; 2470 id estRows task access object operator info 2471 TableReader_7 10.00 root partition:p1 data:Selection_6 2472 └─Selection_6 10.00 cop[einsteindb] eq(test.t.ts, 2020-04-06 00:00:00.000000) 2473 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2474 explain select * from t where ts = '2020-04-05 00:00:00.001' -- p1; 2475 id estRows task access object operator info 2476 TableReader_7 10.00 root partition:p1 data:Selection_6 2477 └─Selection_6 10.00 cop[einsteindb] eq(test.t.ts, 2020-04-05 00:00:00.001000) 2478 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2479 explain select * from t where ts > '2020-04-15 00:00:00' -- p2; 2480 id estRows task access object operator info 2481 TableReader_7 3333.33 root partition:p2 data:Selection_6 2482 └─Selection_6 3333.33 cop[einsteindb] gt(test.t.ts, 2020-04-15 00:00:00.000000) 2483 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2484 explain select * from t where ts > '2020-04-14 23:59:59.999' -- p1,p2; 2485 id estRows task access object operator info 2486 TableReader_7 3333.33 root partition:p1,p2 data:Selection_6 2487 └─Selection_6 3333.33 cop[einsteindb] gt(test.t.ts, 2020-04-14 23:59:59.999000) 2488 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2489 explain select * from t where ts > '2020-04-15 00:00:00.001' -- p2; 2490 id estRows task access object operator info 2491 TableReader_7 3333.33 root partition:p2 data:Selection_6 2492 └─Selection_6 3333.33 cop[einsteindb] gt(test.t.ts, 2020-04-15 00:00:00.001000) 2493 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2494 explain select * from t where ts > '2020-04-26 00:00:00.001' -- dual; 2495 id estRows task access object operator info 2496 TableReader_7 3333.33 root partition:dual data:Selection_6 2497 └─Selection_6 3333.33 cop[einsteindb] gt(test.t.ts, 2020-04-26 00:00:00.001000) 2498 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2499 explain select * from t where ts >= '2020-04-04 12:22:32' -- p0,p1,p2; 2500 id estRows task access object operator info 2501 TableReader_7 3333.33 root partition:all data:Selection_6 2502 └─Selection_6 3333.33 cop[einsteindb] ge(test.t.ts, 2020-04-04 12:22:32.000000) 2503 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2504 explain select * from t where ts >= '2020-04-05 00:00:00' -- p1,p2; 2505 id estRows task access object operator info 2506 TableReader_7 3333.33 root partition:p1,p2 data:Selection_6 2507 └─Selection_6 3333.33 cop[einsteindb] ge(test.t.ts, 2020-04-05 00:00:00.000000) 2508 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2509 explain select * from t where ts >= '2020-04-25 00:00:00' -- dual; 2510 id estRows task access object operator info 2511 TableReader_7 3333.33 root partition:dual data:Selection_6 2512 └─Selection_6 3333.33 cop[einsteindb] ge(test.t.ts, 2020-04-25 00:00:00.000000) 2513 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2514 explain select * from t where ts < '2020-04-25 00:00:00' -- p0,p1,p2; 2515 id estRows task access object operator info 2516 TableReader_7 3323.33 root partition:all data:Selection_6 2517 └─Selection_6 3323.33 cop[einsteindb] lt(test.t.ts, 2020-04-25 00:00:00.000000) 2518 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2519 explain select * from t where ts < '2020-04-15 00:00:00.001' -- p0,p1,p2; 2520 id estRows task access object operator info 2521 TableReader_7 3323.33 root partition:all data:Selection_6 2522 └─Selection_6 3323.33 cop[einsteindb] lt(test.t.ts, 2020-04-15 00:00:00.001000) 2523 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2524 explain select * from t where ts < '2020-04-15 00:00:00' -- expect perfect : p0,p1, obtain: p0,p1,p2; 2525 id estRows task access object operator info 2526 TableReader_7 3323.33 root partition:all data:Selection_6 2527 └─Selection_6 3323.33 cop[einsteindb] lt(test.t.ts, 2020-04-15 00:00:00.000000) 2528 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2529 explain select * from t where ts < '2020-04-14 23:59:59.999' -- p0,p1; 2530 id estRows task access object operator info 2531 TableReader_7 3323.33 root partition:p0,p1 data:Selection_6 2532 └─Selection_6 3323.33 cop[einsteindb] lt(test.t.ts, 2020-04-14 23:59:59.999000) 2533 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2534 explain select * from t where ts < '2020-04-03 00:00:00' -- p0; 2535 id estRows task access object operator info 2536 TableReader_7 3323.33 root partition:p0 data:Selection_6 2537 └─Selection_6 3323.33 cop[einsteindb] lt(test.t.ts, 2020-04-03 00:00:00.000000) 2538 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2539 explain select * from t where ts < '2021-05-03 00:00:00' -- p0,p1,p2; 2540 id estRows task access object operator info 2541 TableReader_7 3323.33 root partition:all data:Selection_6 2542 └─Selection_6 3323.33 cop[einsteindb] lt(test.t.ts, 2021-05-03 00:00:00.000000) 2543 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2544 explain select * from t where ts <= '2020-04-05 00:00:00' -- p0,p1; 2545 id estRows task access object operator info 2546 TableReader_7 3323.33 root partition:p0,p1 data:Selection_6 2547 └─Selection_6 3323.33 cop[einsteindb] le(test.t.ts, 2020-04-05 00:00:00.000000) 2548 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2549 explain select * from t where ts <= '2020-04-03 00:00:00' -- p0; 2550 id estRows task access object operator info 2551 TableReader_7 3323.33 root partition:p0 data:Selection_6 2552 └─Selection_6 3323.33 cop[einsteindb] le(test.t.ts, 2020-04-03 00:00:00.000000) 2553 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2554 explain select * from t where ts <= '2020-04-14 23:59:59.123' -- p0,p1; 2555 id estRows task access object operator info 2556 TableReader_7 3323.33 root partition:p0,p1 data:Selection_6 2557 └─Selection_6 3323.33 cop[einsteindb] le(test.t.ts, 2020-04-14 23:59:59.123000) 2558 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2559 explain select * from t where ts <= '2020-04-25 00:00:00' -- p0,p1,p2; 2560 id estRows task access object operator info 2561 TableReader_7 3323.33 root partition:all data:Selection_6 2562 └─Selection_6 3323.33 cop[einsteindb] le(test.t.ts, 2020-04-25 00:00:00.000000) 2563 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2564 explain select * from t where ts > '2020-04-25 00:00:00' or ts < '2020-01-02 00:00:00' -- p0; 2565 id estRows task access object operator info 2566 TableReader_7 6656.67 root partition:p0 data:Selection_6 2567 └─Selection_6 6656.67 cop[einsteindb] or(gt(test.t.ts, 2020-04-25 00:00:00.000000), lt(test.t.ts, 2020-01-02 00:00:00.000000)) 2568 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2569 explain select * from t where ts > '2020-04-02 00:00:00' and ts < '2020-04-07 00:00:00' -- p0,p1; 2570 id estRows task access object operator info 2571 TableReader_7 250.00 root partition:p0,p1 data:Selection_6 2572 └─Selection_6 250.00 cop[einsteindb] gt(test.t.ts, 2020-04-02 00:00:00.000000), lt(test.t.ts, 2020-04-07 00:00:00.000000) 2573 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo 2574 drop causet if exists t; 2575 create causet t (id int, name varchar(20)) partition by hash(id) partitions 128; 2576 explain SELECT * FROM t partition (p1) where name = '1'; 2577 id estRows task access object operator info 2578 TableReader_7 10.00 root partition:p1 data:Selection_6 2579 └─Selection_6 10.00 cop[einsteindb] eq(test.t.name, "1") 2580 └─TableFullScan_5 10000.00 cop[einsteindb] causet:t keep order:false, stats:pseudo