Mysql group by returns wrong result
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.
Subject
Views
Written By
Posted
Mysql group by returns wrong result
3277
March 04, 2013 01:01AM
1473
March 04, 2013 09:19PM
1543
March 08, 2013 01:15AM
1706
March 10, 2013 07:28PM
1397
March 14, 2013 04:38AM
1593
March 26, 2013 12:25AM
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.