Hi! Ok, Monty said that this DISTINCT ... ORDER BY bug has already been fixed in 4.0.
Regards, Heikki Innobase Oy ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: "SDiZ (UHome)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, April 15, 2002 9:06 AM Subject: Re: How does DISTINCT really work ? > Hi! > > ----- Original Message ----- > From: "SDiZ (UHome)" <[EMAIL PROTECTED]> > To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, April 15, 2002 7:51 AM > Subject: Re: How does DISTINCT really work ? > > > > I think every thing are documented. > > > > http://www.mysql.com/doc/B/u/Bugs.html > > a.. The optimiser may handle DISTINCT differently if you are using > 'hidden' > > columns in a join or not. In a join, hidden columns are counted as part of > > the result (even if they are not shown) while in normal queries hidden > > columns doesn't participate in the DISTINCT comparison. We will probably > > change this in the future to never compare the hidden columns when > > executing DISTINCT An example of this is: > > SELECT DISTINCT mp3id FROM band_downloads > > WHERE userid = 9 ORDER BY id DESC; > > > > and > > SELECT DISTINCT band_downloads.mp3id > > FROM band_downloads,band_mp3 > > WHERE band_downloads.userid = 9 > > AND band_mp3.id = band_downloads.mp3id > > ORDER BY band_downloads.id DESC; > > > > In the second case you may in MySQL Server 3.23.x get two identical rows > > in the result set (because the hidden 'id' column may differ). Note that > the > > this only happens for queries where you don't have the ORDER BY > > columns in the result, something that is you are not allowed to do in ANSI > > SQL. > > Ok, thank you for noting this! > > We should update the manual so that it mentions this exception to the rule > on the proper page http://www.mysql.com/doc/S/E/SELECT.html > > And maybe also change the processing of DISTINCT so that there is no such > exception to the rule. > > Regards, > > Heikki > Innobase Oy > > > ----- Original Message ----- > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Monday, April 15, 2002 2:24 AM > > Subject: Fw: How does DISTINCT really work ? > > > > > > > Forwarded from the general mailing list. > > > > > > Mail filter bait: > > > > > > How-To-Repeat > > > > > > ----- Original Message ----- > > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > Cc: <[EMAIL PROTECTED]> > > > Sent: Sunday, April 14, 2002 9:14 PM > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > 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]> > > > > > > > --------------------------------------------------------------------- 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
