Bug #63609 Query with GROUP by , ORDER BY and LIMIT return one row , not expected count
Submitted: 5 Dec 2011 17:18 Modified: 12 Dec 2011 18:25
Reporter: Rumen Palov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:Distrib 5.5.17, for FreeBSD7.4 (amd64) OS:FreeBSD (not tested on other OS)
Assigned to: CPU Architecture:Any
Tags: GROUP, limit, order, SELECT

[5 Dec 2011 17:18] Rumen Palov
Description:
MySQL Client: mysql  Ver 14.14

We have table like this:

CREATE TABLE `log` (
  `recno` int(11) NOT NULL AUTO_INCREMENT,
  `number` char(9) DEFAULT NULL,
  `dt` date NOT NULL,
  PRIMARY KEY (`recno`),
  KEY `dt` (`dt`),
  KEY `number_dt` (`number`,`dt`)
) ENGINE=InnoDB ;

filled with 2.5 millions rows. The rows per date are nearly equal separated for about 90 days.

This query return not correct results. Some times it returns one row not 3, some times return 3 rows but with wrong counter values. 

SELECT COUNT(*) AS `counter` , `number` FROM log WHERE dt='2011-12-04' GROUP BY  `number` ORDER BY `counter` LIMIT 3 ;

More than 80% of number-es, are represented less than 100 times per date in table. 

Example 1: 
In this case the values of column `number` are totaly mess-up. It seems that the data shown is totaly broken.
We expect numbers here. The query was executed, right after table fill-up with data.
+---------+-----------+
| counter | number    |
+---------+-----------+
|    4767 | NULL      |
|    4767 |        w |
|    4767 |        w |
+---------+-----------+

Example 2: Here the values of `counter`  is wrong, we havent such count(*) for each of this `number`-es. 
+---------+-----------+
| counter | number    |
+---------+-----------+
|    4767 | 100000143 |
|    9534 | 100000065 |
|   23835 | 100000071 |
+---------+-----------+

Example 3: The Result is : 
+---------+-----------+
| counter | number    |
+---------+-----------+
|    4767 | 100000650 |
|    4767 | 100000942 |
|    4767 | 100000847 |
+---------+-----------+

Again not correct `counter` per `number`.

IF we remove LIMIT 3 and receive whole result set it's correct. 

OR IF we modify the qyery to :

SELECT COUNT(*) AS `counter` , `number` FROM log WHERE dt='2011-12-04' GROUP BY  `number`*1 ORDER BY `counter` LIMIT 3 ;

it works fine and the result is like this:

+---------+-----------+
| counter | number    |
+---------+-----------+
|       1 | 100000205 |
|       1 | 100000595 |
|       1 | 100000879 |
+---------+-----------+

this is happend on two different servers ( diff hdd, diff memory , diff cpu ) in replication. 

Master is with 96G RAM , Replication is with 32G of RAM. 
If we force query to use different index it change the query behavior.

This is explain of the "buggy" query: 

mysql> explain select count(*) AS `counter` , `number` from log where dt='2011-12-04'  group by `number` order by `counter` limit 10   \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log
         type: index
possible_keys: dt
          key: number_dt
      key_len: 13
          ref: NULL
         rows: 2250611
        Extra: Using where; Using index; Using temporary; Using filesort

How to repeat:
Eeach select with LIMIT like this is producing incorrect result set:

SELECT COUNT(*) AS `counter` , `number` FROM log WHERE dt='2011-12-04' GROUP BY  `number` ORDER BY `counter` LIMIT 3 ;

Suggested fix:
Column `number`*1 OR remove LIMIT
[5 Dec 2011 17:24] Valeriy Kravchuk
Looks like a duplicate of bug #61399. Please, check (try to use IGNORE INDEX hint, for example, to prevent use of that multiple column index...)
[6 Dec 2011 14:29] Valeriy Kravchuk
Had you tried to use IGNORE INDEX() hint or otherwise prevent use of that index? I wonder if this changes anything.
[6 Dec 2011 14:56] Rumen Palov
In these cases query work correct: 

*) "number*1" in GROUP  - changes the index to `dt` 

*) USE INDEX(dt) 

*) Do not use LIMIT - just get all result set
[6 Dec 2011 14:58] Rumen Palov
Yes, if I prevent usage of multiple index number_dt it works correct.
[6 Dec 2011 15:12] Valeriy Kravchuk
Do you agree that this is a duplicate of bug #61399 then?
[6 Dec 2011 15:25] Rumen Palov
Yes It seems to have similliar like bug #61399 , but in bug 61399 aren't returned any rows in result set. In our case there are some rows with wrong values. 

In first case described by me, there are some strange resutls - not digits( what they must to be , but some strange bytes in place of them )

If you think that they are equal - OK , I just want to help with debuging.
[12 Dec 2011 18:25] Sveta Smirnova
Thank you for the feedback.

Closed as duplicate of bug #61399