Description:
We are using the following mysql version
+-------------------------+
| version() |
+-------------------------+
| 5.1.24-rc-community-log |
+-------------------------+
WIth MyISAM tables with partitions
We are doing queries from the following TABLE
+-------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| FTIME_SECS | int(10) unsigned | NO | PRI | 0 | |
| FTIME_USECS | mediumint(8) unsigned | NO | PRI | 0 | |
| CTIME_SECS | int(10) unsigned | NO | PRI | 0 | |
| CTIME_USECS | mediumint(8) unsigned | NO | PRI | 0 | |
| CREATE_ID | int(10) unsigned | NO | PRI | 0 | |
| SPID | int(10) unsigned | NO | | 0 | |
| DSTREAM | smallint(5) unsigned | NO | PRI | 0 | |
| VERSION | tinyint(3) unsigned | NO | | 0 | |
| GSID | smallint(5) unsigned | NO | | 0 | |
| COUNTER | int(10) unsigned | NO | | 0 | |
| IDENT | tinyint(3) unsigned | NO | | 0 | |
| GROUP_ID | int(10) unsigned | NO | | 0 | |
| FLAGS | mediumint(8) unsigned | NO | | 0 | |
| VALIDITY | int(10) unsigned | NO | | 0 | |
| TPSD | int(11) | NO | | 0 | |
| ROUTEID | smallint(5) unsigned | NO | | 0 | |
| PUS_APID | smallint(5) unsigned | NO | | 0 | |
| PUS_SSC | smallint(5) unsigned | NO | | 0 | |
| PUS_ST | tinyint(3) unsigned | NO | | 0 | |
| PUS_SST | tinyint(3) unsigned | NO | | 0 | |
| DATA | blob | YES | | NULL | |
+-------------+-----------------------+------+-----+---------+-------+
21 rows in set (0.73 sec)
Our query is the following:
SELECT FTIME_SECS, FTIME_USECS, CTIME_SECS, CTIME_USECS, CREATE_ID, SPID, DSTREAM, VERSION, GSID FROM TM_DATA FORCE INDEX(SPID_KEY) WHERE SPID=10001 AND (FTIME_SECS > 1229444705 OR (FTIME_SECS = 1229444705 AND FTIME_USECS > 741944)) AND (DSTREAM = 65535) ORDER BY DSTREAM, FTIME_SECS, FTIME_USECS LIMIT 2
And MYSQL response is the following:
| FTIME_SECS | FTIME_USECS | CTIME_SECS | CTIME_USECS | CREATE_ID | SPID | DSTREAM | VERSION | GSID |
+------------+-------------+------------+-------------+-----------+-------+---------+---------+------+
| 1229444741 | 0 | 1229444747 | 742986 | 0 | 10001 | 65535 | 4 | 83 |
| 1229444741 | 0 | 1229444747 | 742986 | 0 | 10001 | 65535 | 4 | 83 |
+------------+-------------+------------+-------------+-----------+-------+---------+---------+------+
This response shows two rows with primary key duplicated, we does not understand this behavior.
We have check to include DISTINCT and DISTINCTROW in the query but the result is exactly the same.
We can resolve the problem using group by, but adding the group the queries are slower. (This delay blocks our system)
We think that this point is a bug. Could you provide us a workaround to fix the problem that not includes the use of GROUP BY.
We have 146.000 rows in the table
Thanks in advance
How to repeat:
Insert rows in the table and check the query
