Bug #7309 SELECT DISTINCT doesn't remove duplicates
Submitted: 15 Dec 2004 9:05 Modified: 15 Dec 2004 9:12
Reporter: Jan Kneschke Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.1.3 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[15 Dec 2004 9:05] Jan Kneschke
Description:
using the world db as datasource:

> SELECT DISTINCT region FROM country;

results in 239 rows instead of 25.

How to repeat:

> SELECT DISTINCT region FROM country;

results in

041215  9:40:03       3 Connect     root@localhost on world
                      3 Query       SET SESSION interactive_timeout=1000000
                      3 Query       SET SESSION sql_mode=''
                      3 Query       SET CHARACTER SET utf8
                      3 Query       SELECT @@SQL_MODE
                      3 Query       SHOW FULL COLUMNS FROM `world`.`country`
                      3 Query       SHOW CREATE TABLE `world`.`country`
                      3 Query       select distinct region, Code from country
                      3 Quit

The PK is added to the query and destroys the result of the DISTINCT.

Suggested fix:
SELECTs with DISTINCT should NOT be editable and should not query the PK.
[15 Dec 2004 9:12] Michael G. Zinner
This is not a bug but a feature. Please see

http://bugs.mysql.com/bug.php?id=7086