MySQL Bugs: #63609: Query with GROUP by , ORDER BY and LIMIT return one row , not expected count

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

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

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...)

Had you tried to use IGNORE INDEX() hint or otherwise prevent use of that index? I wonder if this changes anything.

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

Yes, if I prevent usage of multiple index number_dt it works correct.

Do you agree that this is a duplicate of bug #61399 then?

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.

Thank you for the feedback.

Closed as duplicate of bug #61399