1
1
drop table if exists t1, t2;
3
# Bug#56287: crash when using Partition datetime in sub in query
6
(c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
7
c2 varchar(40) not null default '',
12
PARTITION BY RANGE (TO_DAYS(c3))
13
(PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')),
14
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
15
PARTITION p201912 VALUES LESS THAN MAXVALUE);
16
insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00');
17
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
18
PARTITION_NAME TABLE_ROWS
22
SELECT count(*) FROM t1 p where c3 in
23
(select c3 from t1 t where t.c3 < date '2011-04-26 19:19:44'
24
and t.c3 > date '2011-04-26 19:18:44') ;
3
29
# Bug#51830: Incorrect partition pruning on range partition (regression)
5
31
CREATE TABLE t1 (a INT NOT NULL)
22
48
ERROR HY000: Table has no partition for value 110
23
49
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90;
24
50
id select_type table partitions type possible_keys key key_len ref rows Extra
25
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
51
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
26
52
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90;
27
53
id select_type table partitions type possible_keys key key_len ref rows Extra
28
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
54
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
29
55
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90;
30
56
id select_type table partitions type possible_keys key key_len ref rows Extra
31
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
57
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
32
58
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89;
33
59
id select_type table partitions type possible_keys key key_len ref rows Extra
34
1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 7 Using where
60
1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where
35
61
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89;
36
62
id select_type table partitions type possible_keys key key_len ref rows Extra
37
1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 7 Using where
63
1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where
38
64
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89;
39
65
id select_type table partitions type possible_keys key key_len ref rows Extra
40
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
66
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
41
67
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100;
42
68
id select_type table partitions type possible_keys key key_len ref rows Extra
43
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
69
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
44
70
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100;
45
71
id select_type table partitions type possible_keys key key_len ref rows Extra
46
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
72
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
47
73
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100;
48
74
id select_type table partitions type possible_keys key key_len ref rows Extra
49
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
75
1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where
52
78
# Bug#50104: Partitioned table with just 1 partion works with fk
416
CREATE TABLE t1 (i1 int NOT NULL primary key, f1 int) ENGINE = InnoDB
417
PARTITION BY HASH(i1) PARTITIONS 2;
418
INSERT INTO t1 VALUES (1,1), (2,2);
419
SELECT * FROM t1 WHERE i1 = ( SELECT i1 FROM t1 WHERE f1=0 LIMIT 1 );