| blob | 58945b563dccb1b9346c374dfebbaf9479a3ec2b |
1 drop table if exists t1, t2;
2 #
3 # Bug#48229: group by performance issue of partitioned table
4 #
5 CREATE TABLE t1 (
6 a INT,
7 b INT,
8 KEY a (a,b)
9 )
10 PARTITION BY HASH (a) PARTITIONS 1;
11 INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020);
12 EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a;
13 id select_type table type possible_keys key key_len ref rows Extra
14 1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index
15 DROP TABLE t1;
16 create table t1 (a int)
17 partition by range (a)
18 ( partition p0 values less than (maxvalue));
19 alter table t1 add partition (partition p1 values less than (100000));
20 ERROR HY000: MAXVALUE can only be used in last partition definition
21 show create table t1;
22 Table Create Table
23 t1 CREATE TABLE `t1` (
24 `a` int(11) DEFAULT NULL
25 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
26 /*!50100 PARTITION BY RANGE (a)
27 (PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
28 drop table t1;
29 create table t1 (a integer)
30 partition by range (a)
31 ( partition p0 values less than (4),
32 partition p1 values less than (100));
33 create trigger tr1 before insert on t1
34 for each row begin
35 set @a = 1;
36 end|
37 alter table t1 drop partition p0;
38 drop table t1;
39 create table t1 (a integer)
40 partition by range (a)
41 ( partition p0 values less than (4),
42 partition p1 values less than (100));
43 LOCK TABLES t1 WRITE;
44 alter table t1 drop partition p0;
45 alter table t1 reorganize partition p1 into
46 ( partition p0 values less than (4),
47 partition p1 values less than (100));
48 alter table t1 add partition ( partition p2 values less than (200));
49 UNLOCK TABLES;
50 drop table t1;
51 create table t1 (a int unsigned)
52 partition by range (a)
53 (partition pnull values less than (0),
54 partition p0 values less than (1),
55 partition p1 values less than(2));
56 insert into t1 values (null),(0),(1);
57 select * from t1 where a is null;
58 a
59 NULL
60 select * from t1 where a >= 0;
61 a
62 0
63 1
64 select * from t1 where a < 0;
65 a
66 select * from t1 where a <= 0;
67 a
68 0
69 select * from t1 where a > 1;
70 a
71 explain partitions select * from t1 where a is null;
72 id select_type table partitions type possible_keys key key_len ref rows Extra
73 1 SIMPLE t1 pnull system NULL NULL NULL NULL 1
74 explain partitions select * from t1 where a >= 0;
75 id select_type table partitions type possible_keys key key_len ref rows Extra
76 1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
77 explain partitions select * from t1 where a < 0;
78 id select_type table partitions type possible_keys key key_len ref rows Extra
79 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
80 explain partitions select * from t1 where a <= 0;
81 id select_type table partitions type possible_keys key key_len ref rows Extra
82 1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where
83 explain partitions select * from t1 where a > 1;
84 id select_type table partitions type possible_keys key key_len ref rows Extra
85 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
86 drop table t1;
87 create table t1 (a int unsigned, b int unsigned)
88 partition by range (a)
89 subpartition by hash (b)
90 subpartitions 2
91 (partition pnull values less than (0),
92 partition p0 values less than (1),
93 partition p1 values less than(2));
94 insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1);
95 select * from t1 where a is null;
96 a b
97 NULL 0
98 NULL 1
99 select * from t1 where a >= 0;
100 a b
101 0 0
102 0 1
103 1 0
104 1 1
105 select * from t1 where a < 0;
106 a b
107 select * from t1 where a <= 0;
108 a b
109 0 0
110 0 1
111 select * from t1 where a > 1;
112 a b
113 explain partitions select * from t1 where a is null;
114 id select_type table partitions type possible_keys key key_len ref rows Extra
115 1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
116 explain partitions select * from t1 where a >= 0;
117 id select_type table partitions type possible_keys key key_len ref rows Extra
118 1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
119 explain partitions select * from t1 where a < 0;
120 id select_type table partitions type possible_keys key key_len ref rows Extra
121 1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
122 explain partitions select * from t1 where a <= 0;
123 id select_type table partitions type possible_keys key key_len ref rows Extra
124 1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where
125 explain partitions select * from t1 where a > 1;
126 id select_type table partitions type possible_keys key key_len ref rows Extra
127 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
128 drop table t1;
129 CREATE TABLE t1 (
130 a int not null,
131 b int not null,
132 c int not null,
133 primary key(a,b))
134 partition by range (a)
135 partitions 3
136 (partition x1 values less than (5) tablespace ts1,
137 partition x2 values less than (10) tablespace ts2,
138 partition x3 values less than maxvalue tablespace ts3);
139 INSERT into t1 values (1, 1, 1);
140 INSERT into t1 values (6, 1, 1);
141 INSERT into t1 values (10, 1, 1);
142 INSERT into t1 values (15, 1, 1);
143 select * from t1;
144 a b c
145 1 1 1
146 6 1 1
147 10 1 1
148 15 1 1
149 show create table t1;
150 Table Create Table
151 t1 CREATE TABLE `t1` (
152 `a` int(11) NOT NULL,
153 `b` int(11) NOT NULL,
154 `c` int(11) NOT NULL,
155 PRIMARY KEY (`a`,`b`)
156 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
157 /*!50100 PARTITION BY RANGE (a)
158 (PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM,
159 PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM,
160 PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */
161 ALTER TABLE t1
162 partition by range (a)
163 partitions 3
164 (partition x1 values less than (5) tablespace ts1,
165 partition x2 values less than (10) tablespace ts2,
166 partition x3 values less than maxvalue tablespace ts3);
167 select * from t1;
168 a b c
169 1 1 1
170 6 1 1
171 10 1 1
172 15 1 1
173 show create table t1;
174 Table Create Table
175 t1 CREATE TABLE `t1` (
176 `a` int(11) NOT NULL,
177 `b` int(11) NOT NULL,
178 `c` int(11) NOT NULL,
179 PRIMARY KEY (`a`,`b`)
180 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
181 /*!50100 PARTITION BY RANGE (a)
182 (PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM,
183 PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM,
184 PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */
185 drop table if exists t1;
186 CREATE TABLE t1 (
187 a int not null,
188 b int not null,
189 c int not null)
190 partition by range (a)
191 partitions 3
192 (partition x1 values less than (5) tablespace ts1,
193 partition x2 values less than (10) tablespace ts2,
194 partition x3 values less than maxvalue tablespace ts3);
195 INSERT into t1 values (1, 1, 1);
196 INSERT into t1 values (6, 1, 1);
197 INSERT into t1 values (10, 1, 1);
198 INSERT into t1 values (15, 1, 1);
199 select * from t1;
200 a b c
201 1 1 1
202 6 1 1
203 10 1 1
204 15 1 1
205 ALTER TABLE t1
206 partition by range (a)
207 partitions 3
208 (partition x1 values less than (5) tablespace ts1,
209 partition x2 values less than (10) tablespace ts2,
210 partition x3 values less than maxvalue tablespace ts3);
211 select * from t1;
212 a b c
213 1 1 1
214 6 1 1
215 10 1 1
216 15 1 1
217 drop table if exists t1;
218 CREATE TABLE t1 (
219 a int not null,
220 b int not null,
221 c int not null,
222 primary key(a,b))
223 partition by range (a)
224 partitions 3
225 (partition x1 values less than (5) tablespace ts1,
226 partition x2 values less than (10) tablespace ts2,
227 partition x3 values less than (15) tablespace ts3);
228 INSERT into t1 values (1, 1, 1);
229 INSERT into t1 values (6, 1, 1);
230 INSERT into t1 values (10, 1, 1);
231 INSERT into t1 values (15, 1, 1);
232 ERROR HY000: Table has no partition for value 15
233 select * from t1;
234 a b c
235 1 1 1
236 6 1 1
237 10 1 1
238 ALTER TABLE t1
239 partition by range (a)
240 partitions 3
241 (partition x1 values less than (5) tablespace ts1,
242 partition x2 values less than (10) tablespace ts2,
243 partition x3 values less than (15) tablespace ts3);
244 select * from t1;
245 a b c
246 1 1 1
247 6 1 1
248 10 1 1
249 drop table t1;
250 CREATE TABLE t1 (
251 a int not null,
252 b int not null,
253 c int not null,
254 primary key(a,b))
255 partition by range (a)
256 (partition x1 values less than (1));
257 drop table t1;
258 CREATE TABLE t1 (
259 a int not null,
260 b int not null,
261 c int not null,
262 primary key (a,b))
263 partition by range (a)
264 subpartition by hash (a+b)
265 ( partition x1 values less than (1)
266 ( subpartition x11,
267 subpartition x12),
268 partition x2 values less than (5)
269 ( subpartition x21,
270 subpartition x22)
271 );
272 SELECT * from t1;
273 a b c
274 show create table t1;
275 Table Create Table
276 t1 CREATE TABLE `t1` (
277 `a` int(11) NOT NULL,
278 `b` int(11) NOT NULL,
279 `c` int(11) NOT NULL,
280 PRIMARY KEY (`a`,`b`)
281 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
282 /*!50100 PARTITION BY RANGE (a)
283 SUBPARTITION BY HASH (a+b)
284 (PARTITION x1 VALUES LESS THAN (1)
285 (SUBPARTITION x11 ENGINE = MyISAM,
286 SUBPARTITION x12 ENGINE = MyISAM),
287 PARTITION x2 VALUES LESS THAN (5)
288 (SUBPARTITION x21 ENGINE = MyISAM,
289 SUBPARTITION x22 ENGINE = MyISAM)) */
290 ALTER TABLE t1 ADD COLUMN d int;
291 show create table t1;
292 Table Create Table
293 t1 CREATE TABLE `t1` (
294 `a` int(11) NOT NULL,
295 `b` int(11) NOT NULL,
296 `c` int(11) NOT NULL,
297 `d` int(11) DEFAULT NULL,
298 PRIMARY KEY (`a`,`b`)
299 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
300 /*!50100 PARTITION BY RANGE (a)
301 SUBPARTITION BY HASH (a+b)
302 (PARTITION x1 VALUES LESS THAN (1)
303 (SUBPARTITION x11 ENGINE = MyISAM,
304 SUBPARTITION x12 ENGINE = MyISAM),
305 PARTITION x2 VALUES LESS THAN (5)
306 (SUBPARTITION x21 ENGINE = MyISAM,
307 SUBPARTITION x22 ENGINE = MyISAM)) */
308 drop table t1;
309 CREATE TABLE t1 (
310 a int not null,
311 b int not null,
312 c int not null,
313 primary key (a,b))
314 partition by range (a)
315 subpartition by hash (a+b)
316 ( partition x1 values less than (1)
317 ( subpartition x11 tablespace t1 engine myisam nodegroup 0,
318 subpartition x12 tablespace t2 engine myisam nodegroup 1),
319 partition x2 values less than (5)
320 ( subpartition x21 tablespace t1 engine myisam nodegroup 0,
321 subpartition x22 tablespace t2 engine myisam nodegroup 1)
322 );
323 SELECT * from t1;
324 a b c
325 drop table t1;
326 CREATE TABLE t1 (
327 a int not null,
328 b int not null,
329 c int not null,
330 primary key (a,b))
331 partition by range (a)
332 subpartition by hash (a+b)
333 ( partition x1 values less than (1)
334 ( subpartition x11 tablespace t1 nodegroup 0,
335 subpartition x12 tablespace t2 nodegroup 1),
336 partition x2 values less than (5)
337 ( subpartition x21 tablespace t1 nodegroup 0,
338 subpartition x22 tablespace t2 nodegroup 1)
339 );
340 SELECT * from t1;
341 a b c
342 drop table t1;
343 CREATE TABLE t1 (
344 a int not null,
345 b int not null,
346 c int not null,
347 primary key (a,b))
348 partition by range (a)
349 subpartition by hash (a+b)
350 ( partition x1 values less than (1)
351 ( subpartition x11 engine myisam nodegroup 0,
352 subpartition x12 engine myisam nodegroup 1),
353 partition x2 values less than (5)
354 ( subpartition x21 engine myisam nodegroup 0,
355 subpartition x22 engine myisam nodegroup 1)
356 );
357 INSERT into t1 VALUES (1,1,1);
358 INSERT into t1 VALUES (4,1,1);
359 INSERT into t1 VALUES (5,1,1);
360 ERROR HY000: Table has no partition for value 5
361 SELECT * from t1;
362 a b c
363 1 1 1
364 4 1 1
365 ALTER TABLE t1
366 partition by range (a)
367 subpartition by hash (a+b)
368 ( partition x1 values less than (1)
369 ( subpartition x11 engine myisam nodegroup 0,
370 subpartition x12 engine myisam nodegroup 1),
371 partition x2 values less than (5)
372 ( subpartition x21 engine myisam nodegroup 0,
373 subpartition x22 engine myisam nodegroup 1)
374 );
375 SELECT * from t1;
376 a b c
377 1 1 1
378 4 1 1
379 drop table t1;
380 CREATE TABLE t1 (
381 a int not null,
382 b int not null,
383 c int not null,
384 primary key (a,b))
385 partition by range (a)
386 subpartition by hash (a+b)
387 ( partition x1 values less than (1)
388 ( subpartition x11 tablespace t1 engine myisam,
389 subpartition x12 tablespace t2 engine myisam),
390 partition x2 values less than (5)
391 ( subpartition x21 tablespace t1 engine myisam,
392 subpartition x22 tablespace t2 engine myisam)
393 );
394 INSERT into t1 VALUES (1,1,1);
395 INSERT into t1 VALUES (4,1,1);
396 INSERT into t1 VALUES (5,1,1);
397 ERROR HY000: Table has no partition for value 5
398 SELECT * from t1;
399 a b c
400 1 1 1
401 4 1 1
402 ALTER TABLE t1
403 partition by range (a)
404 subpartition by hash (a+b)
405 ( partition x1 values less than (1)
406 ( subpartition x11 tablespace t1 engine myisam,
407 subpartition x12 tablespace t2 engine myisam),
408 partition x2 values less than (5)
409 ( subpartition x21 tablespace t1 engine myisam,
410 subpartition x22 tablespace t2 engine myisam)
411 );
412 SELECT * from t1;
413 a b c
414 1 1 1
415 4 1 1
416 drop table t1;
417 CREATE TABLE t1 (
418 a int not null,
419 b int not null,
420 c int not null,
421 primary key (a,b))
422 partition by range (a)
423 subpartition by hash (a+b)
424 ( partition x1 values less than (1)
425 ( subpartition x11 tablespace t1,
426 subpartition x12 tablespace t2),
427 partition x2 values less than (5)
428 ( subpartition x21 tablespace t1,
429 subpartition x22 tablespace t2)
430 );
431 INSERT into t1 VALUES (1,1,1);
432 INSERT into t1 VALUES (4,1,1);
433 INSERT into t1 VALUES (5,1,1);
434 ERROR HY000: Table has no partition for value 5
435 SELECT * from t1;
436 a b c
437 1 1 1
438 4 1 1
439 ALTER TABLE t1
440 partition by range (a)
441 subpartition by hash (a+b)
442 ( partition x1 values less than (1)
443 ( subpartition x11 tablespace t1 engine myisam,
444 subpartition x12 tablespace t2 engine myisam),
445 partition x2 values less than (5)
446 ( subpartition x21 tablespace t1 engine myisam,
447 subpartition x22 tablespace t2 engine myisam)
448 );
449 SELECT * from t1;
450 a b c
451 1 1 1
452 4 1 1
453 drop table t1;
454 CREATE TABLE t1 (
455 a int not null,
456 b int not null,
457 c int not null,
458 primary key (a,b))
459 partition by range (a)
460 subpartition by hash (a+b)
461 ( partition x1 values less than (1)
462 ( subpartition x11 engine myisam,
463 subpartition x12 engine myisam),
464 partition x2 values less than (5)
465 ( subpartition x21 engine myisam,
466 subpartition x22 engine myisam)
467 );
468 INSERT into t1 VALUES (1,1,1);
469 INSERT into t1 VALUES (4,1,1);
470 INSERT into t1 VALUES (5,1,1);
471 ERROR HY000: Table has no partition for value 5
472 SELECT * from t1;
473 a b c
474 1 1 1
475 4 1 1
476 ALTER TABLE t1
477 partition by range (a)
478 subpartition by hash (a+b)
479 ( partition x1 values less than (1)
480 ( subpartition x11 engine myisam,
481 subpartition x12 engine myisam),
482 partition x2 values less than (5)
483 ( subpartition x21 engine myisam,
484 subpartition x22 engine myisam)
485 );
486 SELECT * from t1;
487 a b c
488 1 1 1
489 4 1 1
490 drop table t1;
491 CREATE TABLE t1 (c1 int default NULL, c2 varchar(30) default NULL,
492 c3 date default NULL) engine=myisam
493 PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
494 PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
495 PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
496 PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
497 PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
498 PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
499 PARTITION p11 VALUES LESS THAN MAXVALUE );
500 INSERT INTO t1 VALUES (1, 'testing partitions', '1995-07-17'),
501 (3, 'testing partitions','1995-07-31'),
502 (5, 'testing partitions','1995-08-13'),
503 (7, 'testing partitions','1995-08-26'),
504 (9, 'testing partitions','1995-09-09'),
505 (0, 'testing partitions','2000-07-10'),
506 (2, 'testing partitions','2000-07-23'),
507 (4, 'testing partitions','2000-08-05'),
508 (6, 'testing partitions','2000-08-19'),
509 (8, 'testing partitions','2000-09-01');
510 SELECT COUNT(*) FROM t1 WHERE c3 BETWEEN '1996-12-31' AND '2000-12-31';
511 COUNT(*)
512 5
513 SELECT COUNT(*) FROM t1 WHERE c3 < '2000-12-31';
514 COUNT(*)
515 10
516 DROP TABLE t1;
517 create table t1 (a bigint unsigned)
518 partition by range (a)
519 (partition p0 values less than (10),
520 partition p1 values less than (0));
521 ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
522 create table t1 (a bigint unsigned)
523 partition by range (a)
524 (partition p0 values less than (0),
525 partition p1 values less than (10));
526 show create table t1;
527 Table Create Table
528 t1 CREATE TABLE `t1` (
529 `a` bigint(20) unsigned DEFAULT NULL
530 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
531 /*!50100 PARTITION BY RANGE (a)
532 (PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM,
533 PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */
534 drop table t1;
535 create table t1 (a bigint unsigned)
536 partition by range (a)
537 (partition p0 values less than (2),
538 partition p1 values less than (10));
539 show create table t1;
540 Table Create Table
541 t1 CREATE TABLE `t1` (
542 `a` bigint(20) unsigned DEFAULT NULL
543 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
544 /*!50100 PARTITION BY RANGE (a)
545 (PARTITION p0 VALUES LESS THAN (2) ENGINE = MyISAM,
546 PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */
547 insert into t1 values (0xFFFFFFFFFFFFFFFF);
548 ERROR HY000: Table has no partition for value 18446744073709551615
549 drop table t1;
550 create table t1 (a int)
551 partition by range (MOD(a,3))
552 subpartition by hash(a)
553 subpartitions 2
554 (partition p0 values less than (1),
555 partition p1 values less than (2),
556 partition p2 values less than (3),
557 partition p3 values less than (4));
558 ALTER TABLE t1 DROP PARTITION p3;
559 ALTER TABLE t1 DROP PARTITION p1;
560 ALTER TABLE t1 DROP PARTITION p2;
561 drop table t1;
562 create table t1 (a int)
563 partition by range (MOD(a,3))
564 subpartition by hash(a)
565 subpartitions 2
566 (partition p0 values less than (1),
567 partition p1 values less than (2),
568 partition p2 values less than (3),
569 partition p3 values less than (4));
570 ALTER TABLE t1 DROP PARTITION p0;
571 ALTER TABLE t1 DROP PARTITION p1;
572 ALTER TABLE t1 DROP PARTITION p2;
573 drop table t1;
574 create table t1 (a int DEFAULT NULL,
575 b varchar(30) DEFAULT NULL,
576 c date DEFAULT NULL)
577 ENGINE=MYISAM DEFAULT CHARSET=latin1;
578 insert into t1 values (1, 'abc', '1995-01-01');
579 insert into t1 values (1, 'abc', '1995-01-02');
580 insert into t1 values (1, 'abc', '1995-01-03');
581 insert into t1 values (1, 'abc', '1995-01-04');
582 insert into t1 values (1, 'abc', '1995-01-05');
583 insert into t1 values (1, 'abc', '1995-01-06');
584 insert into t1 values (1, 'abc', '1995-01-07');
585 insert into t1 values (1, 'abc', '1995-01-08');
586 insert into t1 values (1, 'abc', '1995-01-09');
587 insert into t1 values (1, 'abc', '1995-01-10');
588 insert into t1 values (1, 'abc', '1995-01-11');
589 insert into t1 values (1, 'abc', '1995-01-12');
590 insert into t1 values (1, 'abc', '1995-01-13');
591 insert into t1 values (1, 'abc', '1995-01-14');
592 insert into t1 values (1, 'abc', '1995-01-15');
593 insert into t1 values (1, 'abc', '1997-01-01');
594 insert into t1 values (1, 'abc', '1997-01-02');
595 insert into t1 values (1, 'abc', '1997-01-03');
596 insert into t1 values (1, 'abc', '1997-01-04');
597 insert into t1 values (1, 'abc', '1997-01-05');
598 insert into t1 values (1, 'abc', '1997-01-06');
599 insert into t1 values (1, 'abc', '1997-01-07');
600 insert into t1 values (1, 'abc', '1997-01-08');
601 insert into t1 values (1, 'abc', '1997-01-09');
602 insert into t1 values (1, 'abc', '1997-01-10');
603 insert into t1 values (1, 'abc', '1997-01-11');
604 insert into t1 values (1, 'abc', '1997-01-12');
605 insert into t1 values (1, 'abc', '1997-01-13');
606 insert into t1 values (1, 'abc', '1997-01-14');
607 insert into t1 values (1, 'abc', '1997-01-15');
608 insert into t1 values (1, 'abc', '1998-01-01');
609 insert into t1 values (1, 'abc', '1998-01-02');
610 insert into t1 values (1, 'abc', '1998-01-03');
611 insert into t1 values (1, 'abc', '1998-01-04');
612 insert into t1 values (1, 'abc', '1998-01-05');
613 insert into t1 values (1, 'abc', '1998-01-06');
614 insert into t1 values (1, 'abc', '1998-01-07');
615 insert into t1 values (1, 'abc', '1998-01-08');
616 insert into t1 values (1, 'abc', '1998-01-09');
617 insert into t1 values (1, 'abc', '1998-01-10');
618 insert into t1 values (1, 'abc', '1998-01-11');
619 insert into t1 values (1, 'abc', '1998-01-12');
620 insert into t1 values (1, 'abc', '1998-01-13');
621 insert into t1 values (1, 'abc', '1998-01-14');
622 insert into t1 values (1, 'abc', '1998-01-15');
623 insert into t1 values (1, 'abc', '1999-01-01');
624 insert into t1 values (1, 'abc', '1999-01-02');
625 insert into t1 values (1, 'abc', '1999-01-03');
626 insert into t1 values (1, 'abc', '1999-01-04');
627 insert into t1 values (1, 'abc', '1999-01-05');
628 insert into t1 values (1, 'abc', '1999-01-06');
629 insert into t1 values (1, 'abc', '1999-01-07');
630 insert into t1 values (1, 'abc', '1999-01-08');
631 insert into t1 values (1, 'abc', '1999-01-09');
632 insert into t1 values (1, 'abc', '1999-01-10');
633 insert into t1 values (1, 'abc', '1999-01-11');
634 insert into t1 values (1, 'abc', '1999-01-12');
635 insert into t1 values (1, 'abc', '1999-01-13');
636 insert into t1 values (1, 'abc', '1999-01-14');
637 insert into t1 values (1, 'abc', '1999-01-15');
638 insert into t1 values (1, 'abc', '2000-01-01');
639 insert into t1 values (1, 'abc', '2000-01-02');
640 insert into t1 values (1, 'abc', '2000-01-03');
641 insert into t1 values (1, 'abc', '2000-01-04');
642 insert into t1 values (1, 'abc', '2000-01-05');
643 insert into t1 values (1, 'abc', '2000-01-06');
644 insert into t1 values (1, 'abc', '2000-01-07');
645 insert into t1 values (1, 'abc', '2000-01-08');
646 insert into t1 values (1, 'abc', '2000-01-09');
647 insert into t1 values (1, 'abc', '2000-01-15');
648 insert into t1 values (1, 'abc', '2000-01-11');
649 insert into t1 values (1, 'abc', '2000-01-12');
650 insert into t1 values (1, 'abc', '2000-01-13');
651 insert into t1 values (1, 'abc', '2000-01-14');
652 insert into t1 values (1, 'abc', '2000-01-15');
653 insert into t1 values (1, 'abc', '2001-01-01');
654 insert into t1 values (1, 'abc', '2001-01-02');
655 insert into t1 values (1, 'abc', '2001-01-03');
656 insert into t1 values (1, 'abc', '2001-01-04');
657 insert into t1 values (1, 'abc', '2001-01-05');
658 insert into t1 values (1, 'abc', '2001-01-06');
659 insert into t1 values (1, 'abc', '2001-01-07');
660 insert into t1 values (1, 'abc', '2001-01-08');
661 insert into t1 values (1, 'abc', '2001-01-09');
662 insert into t1 values (1, 'abc', '2001-01-15');
663 insert into t1 values (1, 'abc', '2001-01-11');
664 insert into t1 values (1, 'abc', '2001-01-12');
665 insert into t1 values (1, 'abc', '2001-01-13');
666 insert into t1 values (1, 'abc', '2001-01-14');
667 insert into t1 values (1, 'abc', '2001-01-15');
668 alter table t1
669 partition by range (year(c))
670 (partition p5 values less than (2000), partition p10 values less than (2010));
671 alter table t1
672 reorganize partition p5 into
673 (partition p1 values less than (1996),
674 partition p2 values less than (1997),
675 partition p3 values less than (1998),
676 partition p4 values less than (1999),
677 partition p5 values less than (2000));
678 drop table t1;
679 CREATE TABLE t1 (a date)
680 PARTITION BY RANGE (TO_DAYS(a))
681 (PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01')),
682 PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01')),
683 PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01')),
684 PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01')),
685 PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01')),
686 PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01')),
687 PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01')),
688 PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01')),
689 PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01')),
690 PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01')),
691 PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01')),
692 PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01')),
693 PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01')),
694 PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01')),
695 PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01')),
696 PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01')),
697 PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01')),
698 PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01')),
699 PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01')),
700 PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01')),
701 PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01')),
702 PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01')),
703 PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01')),
704 PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01')),
705 PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01')),
706 PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01')),
707 PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01')),
708 PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01')),
709 PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01')),
710 PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01')),
711 PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01')),
712 PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01')));
713 INSERT INTO t1 VALUES ('2003-01-13'),('2003-06-20'),('2003-08-30');
714 INSERT INTO t1 VALUES ('2003-04-13'),('2003-07-20'),('2003-10-30');
715 INSERT INTO t1 VALUES ('2003-05-13'),('2003-11-20'),('2003-12-30');
716 INSERT INTO t1 VALUES ('2004-01-13'),('2004-01-20'),('2004-01-30');
717 INSERT INTO t1 VALUES ('2004-02-13'),('2004-02-20'),('2004-02-28');
718 INSERT INTO t1 VALUES ('2004-03-13'),('2004-03-20'),('2004-03-30');
719 INSERT INTO t1 VALUES ('2004-04-13'),('2004-04-20'),('2004-04-30');
720 INSERT INTO t1 VALUES ('2004-05-13'),('2004-05-20'),('2004-05-30');
721 INSERT INTO t1 VALUES ('2004-06-13'),('2004-06-20'),('2004-06-30');
722 INSERT INTO t1 VALUES ('2004-07-13'),('2004-07-20'),('2004-07-30');
723 INSERT INTO t1 VALUES ('2004-08-13'),('2004-08-20'),('2004-08-30');
724 INSERT INTO t1 VALUES ('2004-09-13'),('2004-09-20'),('2004-09-30');
725 INSERT INTO t1 VALUES ('2004-10-13'),('2004-10-20'),('2004-10-30');
726 INSERT INTO t1 VALUES ('2004-11-13'),('2004-11-20'),('2004-11-30');
727 INSERT INTO t1 VALUES ('2004-12-13'),('2004-12-20'),('2004-12-30');
728 INSERT INTO t1 VALUES ('2005-01-13'),('2005-01-20'),('2005-01-30');
729 INSERT INTO t1 VALUES ('2005-02-13'),('2005-02-20'),('2005-02-28');
730 INSERT INTO t1 VALUES ('2005-03-13'),('2005-03-20'),('2005-03-30');
731 INSERT INTO t1 VALUES ('2005-04-13'),('2005-04-20'),('2005-04-30');
732 INSERT INTO t1 VALUES ('2005-05-13'),('2005-05-20'),('2005-05-30');
733 INSERT INTO t1 VALUES ('2005-06-13'),('2005-06-20'),('2005-06-30');
734 INSERT INTO t1 VALUES ('2005-07-13'),('2005-07-20'),('2005-07-30');
735 INSERT INTO t1 VALUES ('2005-08-13'),('2005-08-20'),('2005-08-30');
736 INSERT INTO t1 VALUES ('2005-09-13'),('2005-09-20'),('2005-09-30');
737 INSERT INTO t1 VALUES ('2005-10-13'),('2005-10-20'),('2005-10-30');
738 INSERT INTO t1 VALUES ('2005-11-13'),('2005-11-20'),('2005-11-30');
739 INSERT INTO t1 VALUES ('2005-12-13'),('2005-12-20'),('2005-12-30');
740 INSERT INTO t1 VALUES ('2006-01-13'),('2006-01-20'),('2006-01-30');
741 INSERT INTO t1 VALUES ('2006-02-13'),('2006-02-20'),('2006-02-28');
742 INSERT INTO t1 VALUES ('2006-03-13'),('2006-03-20'),('2006-03-30');
743 INSERT INTO t1 VALUES ('2006-04-13'),('2006-04-20'),('2006-04-30');
744 INSERT INTO t1 VALUES ('2006-05-13'),('2006-05-20'),('2006-05-30');
745 INSERT INTO t1 VALUES ('2006-06-13'),('2006-06-20'),('2006-06-30');
746 INSERT INTO t1 VALUES ('2006-07-13'),('2006-07-20'),('2006-07-30');
747 SELECT * FROM t1
748 WHERE a >= '2004-07-01' AND a <= '2004-09-30';
749 a
750 2004-07-13
751 2004-07-20
752 2004-07-30
753 2004-08-13
754 2004-08-20
755 2004-08-30
756 2004-09-13
757 2004-09-20
758 2004-09-30
759 EXPLAIN PARTITIONS SELECT * FROM t1
760 WHERE a >= '2004-07-01' AND a <= '2004-09-30';
761 id select_type table partitions type possible_keys key key_len ref rows Extra
762 1 SIMPLE t1 p3xx,p407,p408,p409 ALL NULL NULL NULL NULL 18 Using where
763 SELECT * from t1
764 WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
765 (a >= '2005-07-01' AND a <= '2005-09-30');
766 a
767 2004-07-13
768 2004-07-20
769 2004-07-30
770 2004-08-13
771 2004-08-20
772 2004-08-30
773 2004-09-13
774 2004-09-20
775 2004-09-30
776 2005-07-13
777 2005-07-20
778 2005-07-30
779 2005-08-13
780 2005-08-20
781 2005-08-30
782 2005-09-13
783 2005-09-20
784 2005-09-30
785 EXPLAIN PARTITIONS SELECT * from t1
786 WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
787 (a >= '2005-07-01' AND a <= '2005-09-30');
788 id select_type table partitions type possible_keys key key_len ref rows Extra
789 1 SIMPLE t1 p3xx,p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 27 Using where
790 DROP TABLE t1;
791 create table t1 (a int);
792 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
793 CREATE TABLE t2 (
794 defid int(10) unsigned NOT NULL,
795 day int(10) unsigned NOT NULL,
796 count int(10) unsigned NOT NULL,
797 filler char(200),
798 KEY (defid,day)
799 )
800 PARTITION BY RANGE (day) (
801 PARTITION p7 VALUES LESS THAN (20070401) ,
802 PARTITION p8 VALUES LESS THAN (20070501));
803 insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B;
804 insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B;
805 insert into t2 values(52, 20070321, 123, 'filler') ;
806 insert into t2 values(52, 20070322, 456, 'filler') ;
807 select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid;
808 sum(count)
809 579
810 drop table t1, t2;
811 #
812 # Bug#50939: Loose Index Scan unduly relies on engine to remember range
813 # endpoints
814 #
815 CREATE TABLE t1 (
816 a INT,
817 b INT,
818 KEY ( a, b )
819 ) PARTITION BY HASH (a) PARTITIONS 1;
820 CREATE TABLE t2 (
821 a INT,
822 b INT,
823 KEY ( a, b )
824 );
825 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
826 INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
827 INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
828 INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
829 INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
830 INSERT INTO t2 SELECT * FROM t1;
831 # plans should be identical
832 EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
833 id select_type table type possible_keys key key_len ref rows Extra
834 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by
835 EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
836 id select_type table type possible_keys key key_len ref rows Extra
837 1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by
838 FLUSH status;
839 SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
840 a MAX(b)
841 10 10
842 # Should be no more than 4 reads.
843 SHOW status LIKE 'handler_read_key';
844 Variable_name Value
845 Handler_read_key 4
846 FLUSH status;
847 SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
848 a MAX(b)
849 10 10
850 # Should be no more than 4 reads.
851 SHOW status LIKE 'handler_read_key';
852 Variable_name Value
853 Handler_read_key 4
854 DROP TABLE t1, t2;