MySQL Bugs: #10207: Select distinct() returns duplicate rows

Bug #10207	Select distinct() returns duplicate rows
Submitted:	27 Apr 2005 15:17	Modified:	11 Jul 2005 8:54
Reporter:	Mattias Thorstensson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.4-beta-nt/5.0.6 BK Windows	OS:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When doing a 

SELECT DISTINCT(noseries) FROM distinctbug

the query returns several identical rows. Removing the index from the table fixes the problem, but this is far from desireable in our case.

The query was originally 

SELECT DISTINCT(noseries), MAX(serial) FROM distinctbug GROUP BY noseries

which gave the error "Duplicate entry 'B9R' for key 1". This is similar to bug #9404, but since then we have narrowed it down to a problem with distinct being, well, not distinct enough (which places this bug closer to bug #9520).

How to repeat:
I have uploaded a file containing the table I have used to

ftp://ftp.mysql.com/pub/mysql/upload/

The file is called "distinctbug-test-tabel.zip". Table name and column names have been altered from the original, but the problem should still be there. Just use the above query.

I tries to create a new table with just a few rows but could not duplicate the bug there, so it might only show up in somewhat bigger tables (our has just over 175.000).

verified with 5.0.4 on linux, doesn't happen with 4.1.11 or if i drop the indexes on 5.0.4

Also verified with latest BK source on Windows.

If I add a new index on the noseries column only the query works as expected. Also, if I add an index on noseries and serial, the second (original)P query also works.

Since this workaround exists I have lowered the severity to S3.

new bug in 5.0.4-beta-nt
update not work with multy table whane  i uesd it in stored procedure/Function and i did not  get any error .
but i now it work out side stored procedure .

like this:
create function save(...) retern int(10) 
begin
       update acc a LEFT JOIN JournalsD j on j.IDAcc=a.ID and j.ID_M=10
                  set a.Account=0;
       retern 1;
end;

or

create function save(...) retern int(10) 
begin
       update acc a ,journalsD
                 set a.Account=0
       where j.IDAcc=a.ID and j.ID_M=10
       retern 1;
end;

the result alyas nothing
and update statment do nothing.

pleass any body can anser me way.

Could you please extend this bug to version 4.1.7 and OS Linux ?

In my case i use this query:

SELECT DISTINCT siteid, `Location A` , `Location Code / Token`
FROM `FF-table`
WHERE siteid BETWEEN 55 AND 55
ORDER BY `siteid` ASC 

(i don't use the where clause just this time to show only the interesting result):

"55";"München";"MCH B KB2"
"55";"München";"MCH B KB2"
        
As you can see the last 2 rows are the same.
So if i want to insert the result in a table with a unique constraint it fails (even if i use the ignore keyword for creating the constraint).

The bug occurs with MyISAM tables, but does not occur with InnoDB. In addition,
if the test table is selected into another MyISAM table, created with latin1 charset,
distinct works as it should.

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.

Additional info:

This bug was submitted one again,
see Bug#11484, and fixed by Igor in
5.0.9.