| 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: | |
| 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:46]
Federico Giampietro
I forgot to tell that tables are MyISAM.
[21 Jun 2005 19:25]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
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 !!!