Description:
I use partition by TO_DAYS(time), select data from time range. from SELECT PARTITIONS result, SELECT always use the first partition, even the time range is not include the first partition.
How to repeat:
CREATE TABLE `t2` (
`fname` varchar(50) NOT NULL,
`lname` varchar(50) NOT NULL,
`region_code` tinyint(3) unsigned NOT NULL,
`dob` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(dob))
SUBPARTITION BY HASH (region_code)
(PARTITION p734145 VALUES LESS THAN (734145) ENGINE = InnoDB,
PARTITION p734146 VALUES LESS THAN (734146) ENGINE = InnoDB,
PARTITION p734147 VALUES LESS THAN (734147) ENGINE = InnoDB,
PARTITION p734148 VALUES LESS THAN (734148) ENGINE = InnoDB,
PARTITION p734149 VALUES LESS THAN (734149) ENGINE = InnoDB,
PARTITION p734150 VALUES LESS THAN (734150) ENGINE = InnoDB,
PARTITION p734151 VALUES LESS THAN (734151) ENGINE = InnoDB,
PARTITION p734152 VALUES LESS THAN (734152) ENGINE = InnoDB,
PARTITION p734153 VALUES LESS THAN (734153) ENGINE = InnoDB,
PARTITION p734154 VALUES LESS THAN (734154) ENGINE = InnoDB,
PARTITION p734155 VALUES LESS THAN (734155) ENGINE = InnoDB,
PARTITION p734156 VALUES LESS THAN (734156) ENGINE = InnoDB,
PARTITION p734157 VALUES LESS THAN (734157) ENGINE = InnoDB,
PARTITION p734158 VALUES LESS THAN (734158) ENGINE = InnoDB,
PARTITION p734159 VALUES LESS THAN (734159) ENGINE = InnoDB,
PARTITION p734160 VALUES LESS THAN (734160) ENGINE = InnoDB,
PARTITION p734161 VALUES LESS THAN (734161) ENGINE = InnoDB,
PARTITION p734162 VALUES LESS THAN (734162) ENGINE = InnoDB,
PARTITION p734163 VALUES LESS THAN (734163) ENGINE = InnoDB,
PARTITION p734164 VALUES LESS THAN (734164) ENGINE = InnoDB,
PARTITION p734165 VALUES LESS THAN (734165) ENGINE = InnoDB,
PARTITION p734166 VALUES LESS THAN (734166) ENGINE = InnoDB,
PARTITION p734167 VALUES LESS THAN (734167) ENGINE = InnoDB,
PARTITION p734168 VALUES LESS THAN (734168) ENGINE = InnoDB,
PARTITION p734169 VALUES LESS THAN (734169) ENGINE = InnoDB,
PARTITION p734170 VALUES LESS THAN (734170) ENGINE = InnoDB,
PARTITION p734171 VALUES LESS THAN (734171) ENGINE = InnoDB);
mysql> explain partitions SELECT * FROM t2 WHERE dob BETWEEN '2010-01-17' AND '2010-01-18';
+----+-------------+-------+----------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2 | p734145_p734145sp0,p734155_p734155sp0,p734156_p734156sp0 | ALL | NULL | NULL | NULL | NULL | 36 | Using where |
+----+-------------+-------+----------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
mysql> select to_days('2010-01-17'), to_days('2010-01-18');
+-----------------------+-----------------------+
| to_days('2010-01-17') | to_days('2010-01-18') |
+-----------------------+-----------------------+
| 734154 | 734155 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)
Suggested fix:
None.
Description: I use partition by TO_DAYS(time), select data from time range. from SELECT PARTITIONS result, SELECT always use the first partition, even the time range is not include the first partition. How to repeat: CREATE TABLE `t2` ( `fname` varchar(50) NOT NULL, `lname` varchar(50) NOT NULL, `region_code` tinyint(3) unsigned NOT NULL, `dob` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE ( to_days(dob)) SUBPARTITION BY HASH (region_code) (PARTITION p734145 VALUES LESS THAN (734145) ENGINE = InnoDB, PARTITION p734146 VALUES LESS THAN (734146) ENGINE = InnoDB, PARTITION p734147 VALUES LESS THAN (734147) ENGINE = InnoDB, PARTITION p734148 VALUES LESS THAN (734148) ENGINE = InnoDB, PARTITION p734149 VALUES LESS THAN (734149) ENGINE = InnoDB, PARTITION p734150 VALUES LESS THAN (734150) ENGINE = InnoDB, PARTITION p734151 VALUES LESS THAN (734151) ENGINE = InnoDB, PARTITION p734152 VALUES LESS THAN (734152) ENGINE = InnoDB, PARTITION p734153 VALUES LESS THAN (734153) ENGINE = InnoDB, PARTITION p734154 VALUES LESS THAN (734154) ENGINE = InnoDB, PARTITION p734155 VALUES LESS THAN (734155) ENGINE = InnoDB, PARTITION p734156 VALUES LESS THAN (734156) ENGINE = InnoDB, PARTITION p734157 VALUES LESS THAN (734157) ENGINE = InnoDB, PARTITION p734158 VALUES LESS THAN (734158) ENGINE = InnoDB, PARTITION p734159 VALUES LESS THAN (734159) ENGINE = InnoDB, PARTITION p734160 VALUES LESS THAN (734160) ENGINE = InnoDB, PARTITION p734161 VALUES LESS THAN (734161) ENGINE = InnoDB, PARTITION p734162 VALUES LESS THAN (734162) ENGINE = InnoDB, PARTITION p734163 VALUES LESS THAN (734163) ENGINE = InnoDB, PARTITION p734164 VALUES LESS THAN (734164) ENGINE = InnoDB, PARTITION p734165 VALUES LESS THAN (734165) ENGINE = InnoDB, PARTITION p734166 VALUES LESS THAN (734166) ENGINE = InnoDB, PARTITION p734167 VALUES LESS THAN (734167) ENGINE = InnoDB, PARTITION p734168 VALUES LESS THAN (734168) ENGINE = InnoDB, PARTITION p734169 VALUES LESS THAN (734169) ENGINE = InnoDB, PARTITION p734170 VALUES LESS THAN (734170) ENGINE = InnoDB, PARTITION p734171 VALUES LESS THAN (734171) ENGINE = InnoDB); mysql> explain partitions SELECT * FROM t2 WHERE dob BETWEEN '2010-01-17' AND '2010-01-18'; +----+-------------+-------+----------------------------------------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+----------------------------------------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | p734145_p734145sp0,p734155_p734155sp0,p734156_p734156sp0 | ALL | NULL | NULL | NULL | NULL | 36 | Using where | +----+-------------+-------+----------------------------------------------------------+------+---------------+------+---------+------+------+-------------+ mysql> select to_days('2010-01-17'), to_days('2010-01-18'); +-----------------------+-----------------------+ | to_days('2010-01-17') | to_days('2010-01-18') | +-----------------------+-----------------------+ | 734154 | 734155 | +-----------------------+-----------------------+ 1 row in set (0.00 sec) Suggested fix: None.