> >Hi Benjamin, Arjen > >I did some testing again. I have 75,000,000 rows table, after using group >by I got 62,300,000 rows. >If I use *nix -u sort, I got 74800,00 rows. > >I compared the two results, I feel I trust my *nix result. Some lines just >miss in "group by' result, look like no reason at all. >I think this is a bug. >If you like to see my two result, I can attach to you. > >Thanks. > > >Arjen, > > >I don't have any NULL field in my table, but I have empty field which is > ' ' in my some fields. > >Is that reason the different between SELECT DISTINCT and *nix sort? > > >If the answer is yes, I have to dump the table out then use the *nix sort. > > >Thanks > >At 01:05 PM 7/24/2002 +1000, you wrote: >Hi Benjamin, Vivian, > >On Wed, 2002-07-24 at 05:23, Benjamin Pflugmann wrote: > > On Tue 2002-07-23 at 14:58:16 -0400, [EMAIL PROTECTED] wrote: > > > I have 14 fields are chars (like char(8), char(9)), and one field is > int(4). > > > All char fields are mixed with upper case letters, numbers(0-9) and > > > sign(like $). > > > Because there is no low case char, I don't have to put -f option for > unix > > > sort. > > > > Well, it never hurts to try anyhow... *hint* > > > > Does the query use indexes? If so, please check they are not corrupted > > (e.g. using CHECK TABLE). > > > > Which character set is MySQL set to, which "locale" is set in your > > shell? Maybe the collections are different and consider different > > characters the same. > > > > Please try to find some rows which "disappear". For example, dump the > > result from the query (62,000,000 rows) to a file like you have done > > for the 76,500,000 rows and find out which rows got removed > > (e.g. sorting the files and using "comm" on the 76,000,000 and > > 62,000,000 version). > > > > In short, try to make a reproducable test case. Thanks. > >Note that SELECT DISTINCT only grabs rows where none of the distinct >columns is NULL. > >A *nix sort on a dump would not do the same. Try doing a "grep -v NULL" >first before sorting. Then see what you have left. >However, if your table dump also contains NULLs in fields that are not >part of the DISINCT selection, then you'll need to make the filtering >slightly more complicated to have it produce an equivalent result. > > >Regards, >Arjen.
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
