MySQL Forums
Forum List  »  MyISAM

Mysql group by returns wrong result
Posted by: Pradeep mar
Date: March 04, 2013 01:01AM

I have an issue in one of mySql query. Issue is not reproducible on any of our local machines. I have a simple query

SELECT ID
FROM TABLE_NAME
WHERE ID IN (15920,15921)
GROUP BY ID

returns result –

ID
15920

Which is unexpected result since there is data for both the ids in database.

Using explain command returned the following result for this query

+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+
| id | select_type | table | type | possible_keys | Key | key_len | Ref | rows | Extra |
+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+
| 1 | SIMPLE | TABLE_NAME | range | CUST_SID_SRUN_INDX | CUST_SID_SRUN_INDX | 4 | | 1 | Using where; Using index for group-by |
+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+

For this issue I have tried following solutions -

• Forcing a derived table –

SELECT ID
FROM (SELECT ID
FROM TABLE_NAME
WHERE ID IN (15920,15921)) CUST
GROUP BY ID

• Using having clause instead of where clause

SELECT ID
FROM TABLE_NAME
GROUP BY ID
HAVING ID IN (15920,15921)

• Ignoring the index used in this table –

SELECT ID
FROM TABLE_NAME IGNORE INDEX (CUST_SID_SRUN_INDX)
WHERE ID IN (15920,15921)
GROUP BY ID

All the above queries return the expected result as follow :-

ID
15920
15921

I am trying to analyze the unexpected behavior of group by clause when indexes are used. Please let me know if I could try something else. FYI…The UAT box where the issue occurs is a linux machine with Mysql 5.1.30. The difference that we see is the version of Mysql. We are using Mysql 5.1.52 on our machines. The table which has this issue uses MyISAM databse engine.

Index definition for the table is

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
rm_st_ex_customer 1 CUST_SID_SRUN_INDX 1 SIMULATION_ID A 68 BTREE
rm_st_ex_customer 1 CUST_SID_SRUN_INDX 2 SIMULATION_RUN A 428 BTREE


Please let me know if any other input is required.

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql group by returns wrong result
3277
March 04, 2013 01:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.