| Bug #37219 | MyISAM engine give wrong result when use PARTITION and IN | ||
|---|---|---|---|
| Submitted: | 5 Jun 2008 8:10 | Modified: | 23 Jun 2008 9:26 |
| Reporter: | Ben Li | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.1.24rc, 5.1.26bk | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | PARTITION MyISAM IN | ||
[5 Jun 2008 8:11]
Ben Li
data file
Attachment: 2008040csh.out.bz2 (application/x-bzip2, text), 403.86 KiB.
[5 Jun 2008 18:11]
Sveta Smirnova
Thank you for the report. Verified as described. Probably duplicate of bug #35745 Workaround: select count(1),defid from tsh where defid in (6077966,2,4) and counttype = 0 and day between 20080401 and 20080430 group by defid with rollup;
[6 Jun 2008 11:36]
Sveta Smirnova
There is related bug #37235
[23 Jun 2008 9:26]
Mattias Jonsson
This is a duplicate of bug#35931. (I have verified that the pushed patch do fix this bug.)
Description: when select data from MyISAM table with PARTITION, we got the wrong number and result. remove PARTITION or change table type to INNODB, everything works again. revert back to 5.1.18beta, MyISAM also works. How to repeat: mysql> CREATE TABLE tsh ( -> defid int unsigned NOT NULL, -> count int unsigned NOT NULL, -> counttype tinyint NOT NULL default '0', -> day int unsigned NOT NULL, -> hour tinyint unsigned NOT NULL, -> domid SMALLINT unsigned NOT NULL, -> PRIMARY KEY (defid, day, hour, counttype) -> ) ENGINE=MyISAM DEFAULT CHARSET=gbk -> partition by range (day) -> (PARTITION p2005 VALUES LESS THAN (20060101), -> PARTITION p2006 VALUES LESS THAN (20070101) , -> PARTITION p2007 VALUES LESS THAN (20080101) , -> PARTITION p2008 VALUES LESS THAN (20090101) , -> PARTITION p2009 VALUES LESS THAN (20100101) , -> PARTITION p2010 VALUES LESS THAN (20110101) , -> PARTITION pMAX VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.01 sec) mysql> load data infile '/usr/local/data/2008040csh.out' ignore into table tsh; Query OK, 108484 rows affected (0.74 sec) Records: 108484 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select count(1) from tsh where defid in (6077966, 2, 4) and counttype = 0 and day between 20080401 and 20080430; +----------+ | count(1) | +----------+ | 1296 | +----------+ 1 row in set (0.17 sec) mysql> select count(1),defid from tsh where defid in (6077966,2,4) and counttype = 0 and day between 20080401 and 20080430 group by defid; +----------+---------+ | count(1) | defid | +----------+---------+ | 216 | 2 | | 216 | 4 | | 216 | 6077966 | +----------+---------+ 3 rows in set (0.00 sec)