Bug #22057 SELECT DISTINCT RETURNING ERRONEOUS NUMBER OF ROWS

• Submitted: 6 Sep 2006 16:29
• Modified: 6 Sep 2006 20:06
• Reporter: Anália Lourenço
• Status: Duplicate
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.24
• OS: Windows (WIN XP)
• CPU Architecture: Any
• Tags: distinct, SELECT

[6 Sep 2006 16:29] Anália Lourenço

Description:
This bug seems very similar to bug#15102, but, apparently, it still persists.
I have a MyISAM table with more than 1 million rows with the following schema:

CREATE TABLE `february2006` (
  `indice` int(11) NOT NULL auto_increment,
  `remotehost` varchar(25) default NULL,
  `rfc931` varchar(250) default NULL,
  `authuser` varchar(250) default NULL,
  `date` datetime default NULL,
  `request` varchar(1000) default NULL,
  `status` int(11) default NULL,
  `bytes` int(11) default NULL,
  `referrer` varchar(2000) default NULL,
  `userAgent` varchar(500) default NULL,
  PRIMARY KEY  (`indice`),
  KEY `ipIndex` (`remotehost`),
  KEY `reqIndex` (`request`),
  KEY `refIndex` (`referrer`(1000)),
  KEY `uaIndex` (`userAgent`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 AVG_ROW_LENGTH=300; 

I wanted to perform a search over the request field, collecting its different values. Surprisingly, the query didn't work out as it should...

SELECT request 
FROM february2006 
WHERE request LIKE '%/natura/viewcvs.cgi/x%'

Returns 30550 rows.

SELECT DISTINCT february2006.request
FROM february2006
WHERE february2006.request LIKE '%/natura/viewcvs.cgi/x%'

Returns 0 rows

SELECT DISTINCT february2006.request
FROM (february2006 INNER JOIN february2006 AS f ON february2006.indice=f.indice)
WHERE february2006.request LIKE '%/natura/viewcvs.cgi/x%'

Returns 27422 rows

How to repeat:
Not easy due to data volume.

[6 Sep 2006 20:06] Sveta Smirnova

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

"A patch for this bug has been committed." means you should wait next release of the MySQL. No relased versions have affected.

This bug also look like bug ##21456 and duplicate of previous bug #21559.