MySQL Bugs: #55734: Attribute 'columns' doesn't work with 'datetime' field

Bug #55734	Attribute 'columns' doesn't work with 'datetime' field
Submitted:	4 Aug 2010 9:24	Modified:	16 Aug 2010 21:04
Reporter:	Dmitry Zhilin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.5-m3	OS:	FreeBSD (8.0 amd64)
Assigned to:		CPU Architecture:	Any
Tags:	datetime partition

Description:

Attribute 'columns' should work with 'datetime' field, but it does not work.
(in accordance with article: http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html )

mysql> show create table rc\G
*************************** 1. row ***************************
       Table: rc
Create Table: CREATE TABLE `rc` (
  `tm` datetime NOT NULL,
  `m_id` int(10) unsigned NOT NULL DEFAULT '0',
  `cgr_id` mediumint(8) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=koi8r
/*!50500 PARTITION BY RANGE  COLUMNS(tm)
(PARTITION p20100701 VALUES LESS THAN ('2010-07-01') ENGINE = MyISAM,
 PARTITION p20100801 VALUES LESS THAN ('2010-08-01') ENGINE = MyISAM,
 PARTITION p20100901 VALUES LESS THAN ('2010-09-01') ENGINE = MyISAM,
 PARTITION p20101001 VALUES LESS THAN ('2010-10-01') ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> explain partitions select count(*) from rc where tm between '2010-07-11' AND '2010-07-21'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rc
   partitions: p20100701,p20100801,p20100901,p20101001
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1293980
        Extra: Using where
1 row in set (0.00 sec)

In my opinion the result of explain partitions should only be: p20100801.

Please help.

Dmitry

How to repeat:
Run explain statement and you get the same result.

Thanks for the bug report.

Verified as described, and also with the same table used in the example in http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html :

CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
    PARTITION p0 VALUES LESS THAN('1990-01-01'),
    PARTITION p1 VALUES LESS THAN('1995-01-01'),
    PARTITION p2 VALUES LESS THAN('2000-01-01'),
    PARTITION p3 VALUES LESS THAN('2005-01-01'),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

INSERT INTO rc(c2) VALUES ('1991-01-01'),('1996-01-01'),('2001-01-01'),('2006-01-01');
INSERT INTO rc SELECT * FROM rc;
...
INSERT INTO rc SELECT * FROM rc;

mysql> EXPLAIN PARTITIONS SELECT COUNT(*) FROM rc WHERE c2='2001-01-01';
+----+-------------+-------+----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | partitions     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+----------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | rc    | p0,p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL    | NULL | 32768 | Using where |
+----+-------------+-------+----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM rc WHERE c2='2001-01-01';
+----------+
| COUNT(*) |
+----------+
|     8192 |
+----------+
1 row in set (0.01 sec)

If this is on 5.5.5, then I need a more explicit 'How to repeat'. If it is 5.5.4 or 5.5.3 then it is a duplicate of bug#53685. (I did not manage to repeat it with the current mysql-trunk or the released 5.5.5-m3 64 bit mac os x tar version, using a mysql-test test.)

Can you please re-verify with 5.5.5-m3 as set in the 'version' field, and if repeatable, write a more informative instruction on how to repeat it, including how you start the server, how you run the test and what configuration you use etc?

Mattias, you are right. This error behaves like a bug described at the link <a href='http://bugs.mysql.com/bug.php?id=53685'>bug#53685</a>.

>[mysqld]
>character-set-server=latin1
>
>[mysql]
>default-character-set=latin1

If I add these lines to my configuration file, the bug is not repeatable.

mysql> SELECT VERSION()\G
VERSION(): 5.5.5-m3-log

Dmitry, Mattias,

Thanks, I am marking this as a duplicate of bug #53685 .