MySQL Bugs: #18068: SELECT DISTINCT

Bug #18068	SELECT DISTINCT
Submitted:	8 Mar 2006 11:18	Modified:	10 May 2006 16:25
Reporter:	Maciej Zagozda	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0 BK/5.1BK	OS:	Linux (Suse Linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
I'm experiencing a problem with SELECT DISTINCT command. I believe something has changed in version 5.0.18 in comparison to version 5.0.16.

How to repeat:
I have created a very simple table of districts with one field, which is holding district name. 

CREATE TABLE `cwgctd_districts` (
  `DNME` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`DNME`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO `cwgctd_districts` (`DNME`) VALUES 
  (''),
  ('CENTRAL'),
  ('EASTERN'),
  ('GREATER LONDON'),
  ('NORTH CENTRAL'),
  ('NORTH EAST'),
  ('NORTH WEST'),
  ('SCOTLAND'),
  ('SOUTH EAST'),
  ('SOUTH WEST'),
  ('WESTERN');

COMMIT;

I should not have any duplicate entries in my data. To make sure I will get a list with a district name used once, I use SELECT DISTINCT syntax as follows:

SELECT DISTINCT cwgctd_districts.DNME,cwgctd_districts.DNME FROM cwgc.cwgctd_districts ORDER BY DNME

If I execute above query on mySQL 5.0.16 community edition it returns two columns with all district names. 

If I execute above query on mySQL 5.0.18 community edition it returns only last district name - "WESTERN"

Suggested fix:
I'm sure the query worked fine with the mySQL 4.0.18 and 5.0.16 returning valid records. I would expect version 5.0.18 work same way.

I can reproduce this too on MySQL version 5.0.18 running on Windows 2000 Server. Following is the example I tried:

CREATE TABLE districts (
  `DNME` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`DNME`)
) ENGINE=MyISAM;

INSERT INTO districts (`DNME`) VALUES
  (''),
  ('CENTRAL'),
  ('EASTERN'),
  ('GREATER LONDON'),
  ('NORTH CENTRAL'),
  ('NORTH EAST'),
  ('NORTH WEST'),
  ('SCOTLAND'),
  ('SOUTH EAST'),
  ('SOUTH WEST'),
  ('WESTERN');
  
SELECT DISTINCT DNME, DNME FROM districts ORDER BY DNME;

This returns 11 records with two fields 'DNME' and 'DNME_1'. Both fields on all 11 records show 'WESTERN'. When I remove 'ORDER BY DNME' I get the correct results. 

Version 5.0.16 doesn't have this new feature. Possible fix is to add a new clause to SQL called 'INDISTINCT' that returns the above type of results. (sarcasm)

Thank you for the bug report I was able to repeat.

This bug just affects MyISAM storage engine on server version 5.0/5.1:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.20-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE districts (
    ->   `DNME` varchar(64) NOT NULL default '',
    ->   PRIMARY KEY  (`DNME`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO districts (`DNME`) VALUES
    ->   (''),
    ->   ('CENTRAL'),
    ->   ('EASTERN'),
    ->   ('GREATER LONDON'),
    ->   ('NORTH CENTRAL'),
    ->   ('NORTH EAST'),
    ->   ('NORTH WEST'),
    ->   ('SCOTLAND'),
    ->   ('SOUTH EAST'),
    ->   ('SOUTH WEST'),
    ->   ('WESTERN');
Query OK, 11 rows affected (0.02 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT DNME, DNME FROM districts ORDER BY DNME;
+---------+---------+
| DNME    | DNME    |
+---------+---------+
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
| WESTERN | WESTERN |
+---------+---------+
11 rows in set (0.00 sec)

mysql> alter table districts drop primary key;
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT DNME, DNME FROM districts ORDER BY DNME;
+----------------+----------------+
| DNME           | DNME           |
+----------------+----------------+
|                |                |
| CENTRAL        | CENTRAL        |
| EASTERN        | EASTERN        |
| GREATER LONDON | GREATER LONDON |
| NORTH CENTRAL  | NORTH CENTRAL  |
| NORTH EAST     | NORTH EAST     |
| NORTH WEST     | NORTH WEST     |
| SCOTLAND       | SCOTLAND       |
| SOUTH EAST     | SOUTH EAST     |
| SOUTH WEST     | SOUTH WEST     |
| WESTERN        | WESTERN        |
+----------------+----------------+
11 rows in set (0.01 sec)

mysql> drop table districts;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE districts (
    ->   `DNME` varchar(64) NOT NULL default '',
    ->   PRIMARY KEY  (`DNME`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO districts (`DNME`) VALUES
    ->   (''),
    ->   ('CENTRAL'),
    ->   ('EASTERN'),
    ->   ('GREATER LONDON'),
    ->   ('NORTH CENTRAL'),
    ->   ('NORTH EAST'),
    ->   ('NORTH WEST'),
    ->   ('SCOTLAND'),
    ->   ('SOUTH EAST'),
    ->   ('SOUTH WEST'),
    ->   ('WESTERN');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT DNME, DNME FROM districts ORDER BY DNME;
+----------------+----------------+
| DNME           | DNME           |
+----------------+----------------+
|                |                |
| CENTRAL        | CENTRAL        |
| EASTERN        | EASTERN        |
| GREATER LONDON | GREATER LONDON |
| NORTH CENTRAL  | NORTH CENTRAL  |
| NORTH EAST     | NORTH EAST     |
| NORTH WEST     | NORTH WEST     |
| SCOTLAND       | SCOTLAND       |
| SOUTH EAST     | SOUTH EAST     |
| SOUTH WEST     | SOUTH WEST     |
| WESTERN        | WESTERN        |
+----------------+----------------+
11 rows in set (0.01 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6145

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6146

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6151

pushed in version 5.0.22/5.1.10-beta

Noted in 5.0.22, 5.1.10 changelogs.

<literal>SELECT DISTINCT</literal> queries sometimes returned
only the last row.