Jocelyn, the below shows a bug in the MySQL DISTINCT operation. If you specify DISTINCT, you should not get duplicate rows.
I have forwarded this email to Monty. Have you tried myisamcheck on the tables? If the tables are small, could you please upload them to the 'secret' directory in the MySQL support ftp server. I do not remember the directory exactly, but you have reported so many bugs in MySQL that maybe you remember :). Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB ----- Original Message ----- From: ""DL Neil"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, April 14, 2002 8:01 PM Subject: Re: How does DISTINCT really work ? > Hi Jocelyn, > > I don't have a quick/sure answer for you, but am wondering about the > relationship between the date and topic columns - I note that the '429s > are interspersed by other values. > > I'm a great believer in the KISS principle (simple things amuse simple > minds!) > May I suggest taking out the last three clauses (ORDER BY, DESC, and > LIMIT) and trying that. > Then if it works, put the clauses back, one at a time (from left to > right), observing the effect. > > Please let me know how you get on, > =dn > > > > So why does I obtain duplicates numbers for 'topic' with the first > query > > with DISTINCT ? (perhaps a bug ?) > > > > Regards, > > > > Jocelyn Fournier > > > > ----- Original Message ----- > > From: "DL Neil" <[EMAIL PROTECTED]> > > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Sunday, April 14, 2002 5:54 PM > > Subject: Re: How does DISTINCT really work ? > > > > > > > Hi Jocelyn, > > > > > > > I wonder how works DISTINCT when using it with the following join. > > > > Ex : > > > > > > > > mysql> SELECT DISTINCT topic FROM > > > > searchmainhardwarefr7,searchjoinhardwarefr7 WHERE > > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse > AND > > > > (mot='test') AND date >= '2002-03-24' ORDER BY date DESC LIMIT > 0,20; > > > > +--------+ > > > > | topic | > > > > +--------+ > > > > | 108499 | > > > > | 108341 | > > > > | 108425 | > > > > | 108425 | > > > > | 108425 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108427 | > > > > | 108427 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > +--------+ > > > > 20 rows in set (0.08 sec) > > > > > > > > In this case I was expecting only distinct topic number list, like > > > with the > > > > following query : > > > > > > > > mysql> SELECT topic FROM > searchmainhardwarefr7,searchjoinhardwarefr7 > > > WHERE > > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse > AND > > > > (mot='test') AND date >= '2002-03-24' GROUP BY topic ORDER BY date > > > DESC > > > > LIMIT 0,20; > > > > +--------+ > > > > | topic | > > > > +--------+ > > > > | 108499 | > > > > | 108429 | > > > > | 108427 | > > > > | 108425 | > > > > | 108417 | > > > > | 108341 | > > > > | 108312 | > > > > | 108303 | > > > > | 108275 | > > > > | 108268 | > > > > | 108167 | > > > > | 108233 | > > > > | 108231 | > > > > | 108172 | > > > > | 108052 | > > > > | 108102 | > > > > | 107835 | > > > > | 108122 | > > > > | 108096 | > > > > | 106794 | > > > > +--------+ > > > > 20 rows in set (0.10 sec) > > > > > > > > Does DISTINCT implicitely check columns included in the WHERE > clause ? > > > > Thanks you. > > > > > > > > > AFAIK the WHERE clause (join) is carried out earlier (etc) and the > > > DISTINCT carried out on the resultset, almost at the end of the > process. > > > > > > Regards, > > > =dn > > > > > > > > > > --------------------------------------------------------------------- > > > 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 > > > > > > > > > > > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
