Bug #11484 SELECT DISTINCT don't seems to work
Submitted: 21 Jun 2005 14:41 Modified: 8 Jul 2005 18:01
Reporter: Federico Giampietro Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.7/5.0.8 OS:Server 2003/Linux
Assigned to: Igor Babaev CPU Architecture:Any

[21 Jun 2005 14:41] Federico Giampietro
Description:
Hello,
I have problems with SELECT DISTINCT (on 4.x it works fine).
Command runs like a normal SELECT without "Distinct" clause.

Bye, Federico.

How to repeat:

This is a C# line, using your .NET connector:

strrarray = dbops.GetRows("SELECT DISTINCT userid FROM users ORDER BY userid");

but is the same with Navicat's query builder.

Suggested fix:
Please work on it, it is very important !!!
[21 Jun 2005 14:46] Federico Giampietro
I forgot to tell that tables are MyISAM.
[21 Jun 2005 19:25] Miguel Solorzano
Could you please provide a test case with table schema and data
for to test if it is a server bug.

Thanks in advance.
[21 Jun 2005 20:12] Federico Giampietro
Hello Miguel,
maybe resolved.
I'm tryng to add an index to a column with duplicated values, and DISTINCT seems to work.
on 4.x it works also without indexing. Tomorrow I'll a complete test, then I post the result to tell you to close the bug.

Bye, Federico.

P.S.: how many time to final 5 version ?

P.S. II: sorry for my english.
[22 Jun 2005 11:20] Federico Giampietro
Confirmed,
the column MUST BE indexed or SELECT DISTINCT dont works.
On 4.x it works also with unindexed column.

I suggest to report this difference on incompatibility list and/or on changelog.

Bye, Federico.
[22 Jun 2005 12:37] Miguel Solorzano
Hello Federico,

Thank you for the feedback, however still I need a test case from
you with the table definition and some data because testing with
my own table and data i was unable to repeat the behavior reported
with the latest source server.
[22 Jun 2005 15:01] Federico Giampietro
No problem,
please send me your email and I'll send you a copy of this DB.

Federico.
[22 Jun 2005 15:10] Federico Giampietro
With indexing:
7505P
7506P
7506PA
7525P
7525PA
BLUE-CANOL
BLUE-CS
BLUE-MERLI
FLEX-ABAX
FLEX-API
FLEX-CANOL
FLEX-CS
FLEX-DYSEN
FLEX-MERLI
PROF-API
PROF-CANOL
PROF-MERLI
PROFIT-CS

Without indexing:
7505P
7505P
7506P
7506PA
7506PA
7506PA
7525P
7525P
7525PA
BLUE-CANOL
BLUE-CS
BLUE-CS
BLUE-MERLI
FLEX-ABAX
FLEX-API
FLEX-CANOL
FLEX-CS
FLEX-DYSEN
FLEX-MERLI
PROF-API
PROF-CANOL
PROF-MERLI
PROFIT-CS

The query is:
Select distinct users.userid
From users
Order By users.userid Asc

Fede.
[22 Jun 2005 17:31] Miguel Solorzano
You can upload your db into a zip file and please use
a file name identifying this bug report number, at:

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

Thanks in advance.
[22 Jun 2005 19:15] Federico Giampietro
File upped.

Try SELECT DISTINCT userid FROM users ORDER BY userid
with and without indexing on field userid.

Bye, Fede
[22 Jun 2005 21:06] Miguel Solorzano
Thank you for the feedback.
[22 Jun 2005 21:49] Federico Giampietro
No problem Miguel.
I heavy using 5.0x because I switched it on production server (duplicted work with another 4.x). Is possible tu get a 5.0.8 preview ?

Federico.
[23 Jun 2005 4:12] Heikki Tuuri
Hi!

This is a MyISAM-only bug. I tested on a 5.0.8 built yesterday.

I ALTERed the table to InnoDB and HEAP. Both worked ok.

Changing the Category to 'MyISAM'. I am raising the priority to P2.

Regards,

Heikki
[23 Jun 2005 13:50] Sinisa Milivojevic
I would just like to thank you, once again, signor Federico for your test case and fine bug report.
[8 Jul 2005 9:12] Bugs System
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/internals/26807
[8 Jul 2005 9:28] Igor Babaev
This bug can be demonstrated on the following simple table:
CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa');

With the above contents when executing the query
SELECT DISTINCT id FROM t1;
the server failed to compare correctly two varchar keys formed by two
occurences of string 'aa'. It was due to a bug in the function
hp_rec_key_cmp that miscalculated the length of the strings.
It did not cause any problem for InnoDb tables as there varchar strings
were padded by spaces.
[8 Jul 2005 17:12] Igor Babaev
ChangeSet
  1.1891 05/07/08 02:12:31 igor@igor-inspiron.creware.com +3 -0
  ctype_utf8.test:
    Added a test case for bug #11484.
  hp_hash.c:
    Fixed bug #11484.
    This bug in the function hp_rec_key_cmp resulted in wrong
    comparison of varchar multibyte keys if the bytes after 
    string values happened to be different. This caused wrong
    results for queries returning DISTINCT varchar fields in
    multibyte charsets (e.g. in utf8).

The fix will appear in 5.0.9
[8 Jul 2005 18:01] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.9 change history; closed.