Re: How does DISTINCT really work ?
Hi! > "Fournier" == Fournier Jocelyn <[Presence-PC]" <[EMAIL PROTECTED]>> writes: Fournier> Hi, Fournier> So I assume in this case the only way to have distinct results is to use Fournier> "GROUP BY topic" clause ? If you do this, you can't still use 'ORDER BY date' as 'date' is not part of the GROUP BY columns. >> > mysql> SELECT DISTINCT topic FROM >> > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE >> > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND >> > (mot='les') ORDER BY date DESC LIMIT 0,40; The main problem is what you try to do is not legal in SQL. What you probably want to do is the following: SELECT DISTINCT topic,max(date) as d FROM forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND (mot='les') group by topic ORDER BY d DESC LIMIT 0,40; And just ignore the d column from the result Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - 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
Re: How does DISTINCT really work ?
Hi, So I assume in this case the only way to have distinct results is to use "GROUP BY topic" clause ? Regards, Jocelyn - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, April 26, 2002 4:45 PM Subject: Re: How does DISTINCT really work ? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > I have the latest 4.0.2 source installed, and the problem is still here > > AFAIK : > > > > mysql> \s > > -- > > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > > <(13:29:52) > > # This is a BitKeeper generated patch for the following project: > > # Project Name: MySQL - fast and reliable SQL database > > # This patch format is intended for GNU patch command version 2.5 or higher. > > # This patch includes the following deltas: > > # ChangeSet1.1235 -> 1.1236 > > # Docs/manual.texi1.865 -> 1.866 > > # > > # The following is the BitKeeper ChangeSet Log > > # > > # 02/04/15 [EMAIL PROTECTED] 1.1236 > > # Documentation fix. > > # > > > > mysql> SELECT DISTINCT topic FROM > > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > > (mot='les') ORDER BY date DESC LIMIT 0,40; > > +---+ > > | topic | > > +---+ > > | 11462 | > > > > | 12313 | > > | 9673 | > > | 11462 | > > > > | 12282 | > > | 9673 | > > | 12224 | > > +---+ > > 40 rows in set (0.06 sec) > > > > Hi! > > Thank you for posting about the above problem and for uploading the tables. > > The above is not a bug. > > Due to the presence of date column in ORDER BY clause, MySQL (and any > other RDBMS) has to do a silent addition to the select list, so your > query truly looks like this (I had to change table names to check the > ones you have sent us): > > > SELECT DISTINCT topic, date FROM searchmainhardwarefr8,searchjoinhardwarefr8 WHERE > searchmainhardwarefr8.numreponse=searchjoinhardwarefr8.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > > And a true output is truly like this : > > topic date > 2171 2002-04-19 20:13:10 > 2126 2002-04-19 04:54:38 > 2146 2002-04-18 21:39:12 > 2147 2002-04-18 12:44:30 > 2148 2002-04-18 09:41:56 > 2148 2002-04-17 15:13:44 > 2148 2002-04-16 15:59:50 > 1873 2002-04-15 10:02:20 > 2132 2002-04-13 18:18:44 > 2133 2002-04-13 15:56:57 > 2129 2002-04-12 17:43:29 > 2121 2002-04-12 17:00:03 > 2085 2002-04-12 15:07:49 > 2121 2002-04-12 13:26:14 > 1664 2002-04-12 00:45:30 > 2106 2002-04-10 00:33:03 > 741 2002-04-09 14:50:31 > 2093 2002-04-09 09:12:46 > 2045 2002-04-08 11:59:09 > 2089 2002-04-07 18:29:08 > 2089 2002-04-07 16:53:36 > 2067 2002-04-07 15:05:28 > 2079 2002-04-05 16:57:08 > 2021 2002-04-05 01:51:33 > 2054 2002-04-03 11:03:42 > 2049 2002-03-29 11:42:59 > 1993 2002-03-28 01:28:08 > 2040 2002-03-27 16:50:19 > 2021 2002-03-24 12:29:28 > 2005 2002-03-21 16:55:01 > 2005 2002-03-20 17:58:37 > 1935 2002-03-19 21:06:12 > 1935 2002-03-19 18:31:35 > 1998 2002-03-19 17:51:20 > 1990 2002-03-18 21:22:52 > 1934 2002-03-18 10:50:45 > 1983 2002-03-18 00:06:59 > 1934 2002-03-17 22:57:50 > 1968 2002-03-15 16:41:51 > 1971 2002-03-14 14:00:35 > > > So, simply, everything is as it should be. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > > - 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
Re: How does DISTINCT really work ?
Fournier Jocelyn [Presence-PC] writes: > Hi, > > I have the latest 4.0.2 source installed, and the problem is still here > AFAIK : > > mysql> \s > -- > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > <(13:29:52) > # This is a BitKeeper generated patch for the following project: > # Project Name: MySQL - fast and reliable SQL database > # This patch format is intended for GNU patch command version 2.5 or higher. > # This patch includes the following deltas: > # ChangeSet1.1235 -> 1.1236 > # Docs/manual.texi1.865 -> 1.866 > # > # The following is the BitKeeper ChangeSet Log > # > # 02/04/15 [EMAIL PROTECTED] 1.1236 > # Documentation fix. > # > > mysql> SELECT DISTINCT topic FROM > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > +---+ > | topic | > +---+ > | 11462 | > > | 12313 | > | 9673 | > | 11462 | > > | 12282 | > | 9673 | > | 12224 | > +---+ > 40 rows in set (0.06 sec) > Hi! Thank you for posting about the above problem and for uploading the tables. The above is not a bug. Due to the presence of date column in ORDER BY clause, MySQL (and any other RDBMS) has to do a silent addition to the select list, so your query truly looks like this (I had to change table names to check the ones you have sent us): SELECT DISTINCT topic, date FROM searchmainhardwarefr8,searchjoinhardwarefr8 WHERE searchmainhardwarefr8.numreponse=searchjoinhardwarefr8.numreponse AND (mot='les') ORDER BY date DESC LIMIT 0,40; And a true output is truly like this : topic date 21712002-04-19 20:13:10 21262002-04-19 04:54:38 21462002-04-18 21:39:12 21472002-04-18 12:44:30 21482002-04-18 09:41:56 21482002-04-17 15:13:44 21482002-04-16 15:59:50 18732002-04-15 10:02:20 21322002-04-13 18:18:44 21332002-04-13 15:56:57 21292002-04-12 17:43:29 21212002-04-12 17:00:03 20852002-04-12 15:07:49 21212002-04-12 13:26:14 16642002-04-12 00:45:30 21062002-04-10 00:33:03 741 2002-04-09 14:50:31 20932002-04-09 09:12:46 20452002-04-08 11:59:09 20892002-04-07 18:29:08 20892002-04-07 16:53:36 20672002-04-07 15:05:28 20792002-04-05 16:57:08 20212002-04-05 01:51:33 20542002-04-03 11:03:42 20492002-03-29 11:42:59 19932002-03-28 01:28:08 20402002-03-27 16:50:19 20212002-03-24 12:29:28 20052002-03-21 16:55:01 20052002-03-20 17:58:37 19352002-03-19 21:06:12 19352002-03-19 18:31:35 19982002-03-19 17:51:20 19902002-03-18 21:22:52 19342002-03-18 10:50:45 19832002-03-18 00:06:59 19342002-03-17 22:57:50 19682002-03-15 16:41:51 19712002-03-14 14:00:35 So, simply, everything is as it should be. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - 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
Re: How does DISTINCT really work ?
Fournier Jocelyn [Presence-PC] writes: > Hi, > > I have the latest 4.0.2 source installed, and the problem is still here > AFAIK : > > mysql> \s > -- > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > <(13:29:52) > # This is a BitKeeper generated patch for the following project: > # Project Name: MySQL - fast and reliable SQL database > # This patch format is intended for GNU patch command version 2.5 or higher. > # This patch includes the following deltas: > # ChangeSet1.1235 -> 1.1236 > # Docs/manual.texi1.865 -> 1.866 > # > # The following is the BitKeeper ChangeSet Log > # > # 02/04/15 [EMAIL PROTECTED] 1.1236 > # Documentation fix. > # > > mysql> SELECT DISTINCT topic FROM > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > +---+ > | topic | > +---+ > | 11462 | > > | 12313 | > | 9673 | > | 11462 | > > | 12282 | > | 9673 | > | 12224 | > +---+ > 40 rows in set (0.06 sec) > > > BTW I have a problem with the latest mysql client (12.1) : > > It seems to be locked when I make a 'use database' : > > > mysql> use forum > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > > > Regards, > > Jocelyn Hi! Can you upload a gzipped dump of your table, so that we could check the above ?? -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - 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
Re: How does DISTINCT really work ?
Hi! > "Heikki" == Heikki Tuuri <[EMAIL PROTECTED]> writes: Heikki> From: "SDiZ (UHome)" <[EMAIL PROTECTED]> >> 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. Heikki> Ok, thank you for noting this! Heikki> We should update the manual so that it mentions this exception to the rule Heikki> on the proper page http://www.mysql.com/doc/S/E/SELECT.html I think nothing this in the bugs section should be clear enough. It's very hard to keep the manual up to date if you duplicate everything at all places. Heikki> And maybe also change the processing of DISTINCT so that there is no such Heikki> exception to the rule. This should already be fixed in MySQL 4.0. Regards, Monty - 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
Re: How does DISTINCT really work ?
Hi, Sorry finally there is no lock with 'use database', it only takes a huge amont of time because of the number of table in my database :) Regards, Jocelyn - Original Message - From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Heikki Tuuri" <[EMAIL PROTECTED]> Sent: Monday, April 15, 2002 1:35 PM Subject: Re: How does DISTINCT really work ? > Hi, > > I have the latest 4.0.2 source installed, and the problem is still here > AFAIK : > > mysql> \s > -- > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > <(13:29:52) > # This is a BitKeeper generated patch for the following project: > # Project Name: MySQL - fast and reliable SQL database > # This patch format is intended for GNU patch command version 2.5 or higher. > # This patch includes the following deltas: > # ChangeSet1.1235 -> 1.1236 > # Docs/manual.texi1.865 -> 1.866 > # > # The following is the BitKeeper ChangeSet Log > # > # 02/04/15 [EMAIL PROTECTED] 1.1236 > # Documentation fix. > # > > mysql> SELECT DISTINCT topic FROM > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > +---+ > | topic | > +---+ > | 11462 | > > | 12313 | > | 9673 | > | 11462 | > > | 12282 | > | 9673 | > | 12224 | > +---+ > 40 rows in set (0.06 sec) > > > BTW I have a problem with the latest mysql client (12.1) : > > It seems to be locked when I make a 'use database' : > > > mysql> use forum > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > > > Regards, > > Jocelyn > - Original Message - > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, April 15, 2002 1:06 PM > Subject: Re: How does DISTINCT really work ? > > > > 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' co
Re: How does DISTINCT really work ?
Hi, I have the latest 4.0.2 source installed, and the problem is still here AFAIK : mysql> \s -- mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch <(13:29:52) # This is a BitKeeper generated patch for the following project: # Project Name: MySQL - fast and reliable SQL database # This patch format is intended for GNU patch command version 2.5 or higher. # This patch includes the following deltas: # ChangeSet1.1235 -> 1.1236 # Docs/manual.texi1.865 -> 1.866 # # The following is the BitKeeper ChangeSet Log # # 02/04/15 [EMAIL PROTECTED] 1.1236 # Documentation fix. # mysql> SELECT DISTINCT topic FROM forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND (mot='les') ORDER BY date DESC LIMIT 0,40; +---+ | topic | +---+ | 11462 | | 12313 | | 9673 | | 11462 | | 12282 | | 9673 | | 12224 | +---+ 40 rows in set (0.06 sec) BTW I have a problem with the latest mysql client (12.1) : It seems to be locked when I make a 'use database' : mysql> use forum Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Regards, Jocelyn - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, April 15, 2002 1:06 PM Subject: Re: How does DISTINCT really work ? > 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: > &g
Re: How does DISTINCT really work ?
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 > >
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 > > > > > > > > > > - Or
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 th
Re: How does DISTINCT really work ?
In fact I couldn't suppress the LIMIT as it would return a lot of rows ;) However the order by doesn't seem to have any effect. - Original Message - From: "DL Neil" <[EMAIL PROTECTED]> To: "Carsten Gehling" <[EMAIL PROTECTED]>; "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 7:52 PM Subject: Re: How does DISTINCT really work ? > Well done Carsten! > Which may bring us back to the relationships between the data/columns > being used to control the query. > Jocelyn: Did you try out my previous suggestions? > Please advise, > =dn > > - Original Message - > From: "Carsten Gehling" <[EMAIL PROTECTED]> > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "DL Neil" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: 14 April 2002 18:46 > Subject: SV: How does DISTINCT really work ? > > > > > Fra: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > > > Sendt: 14. april 2002 19:22 > > > > > In fact what it's odd is when I use EXPLAIN, I don't see anywhere > DISTINCT > > > in the EXPLAIN result : > > > > That's because the MySQL optimizer converts your distinct into a group > by. > > Read more here: > > > > http://www.mysql.com/doc/D/I/DISTINCT_optimisation.html > > > > - Carsten > > > > > > > > - 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
Re: How does DISTINCT really work ?
Well done Carsten! Which may bring us back to the relationships between the data/columns being used to control the query. Jocelyn: Did you try out my previous suggestions? Please advise, =dn - Original Message - From: "Carsten Gehling" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "DL Neil" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: 14 April 2002 18:46 Subject: SV: How does DISTINCT really work ? > > Fra: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > > Sendt: 14. april 2002 19:22 > > > In fact what it's odd is when I use EXPLAIN, I don't see anywhere DISTINCT > > in the EXPLAIN result : > > That's because the MySQL optimizer converts your distinct into a group by. > Read more here: > > http://www.mysql.com/doc/D/I/DISTINCT_optimisation.html > > - Carsten > > > - 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
Re: How does DISTINCT really work ?
Yes but when it's written "DISTINCT is converted to a GROUP BY on all columns", which columns are included in "all columns" ? :) Thanks, Jocelyn - Original Message - From: "Carsten Gehling" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "DL Neil" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 7:46 PM Subject: SV: How does DISTINCT really work ? > > Fra: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > > Sendt: 14. april 2002 19:22 > > > In fact what it's odd is when I use EXPLAIN, I don't see anywhere DISTINCT > > in the EXPLAIN result : > > That's because the MySQL optimizer converts your distinct into a group by. > Read more here: > > http://www.mysql.com/doc/D/I/DISTINCT_optimisation.html > > - Carsten > > > > - > 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
Re: How does DISTINCT really work ?
In fact what it's odd is when I use EXPLAIN, I don't see anywhere DISTINCT in the EXPLAIN result : mysql> EXPLAIN SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND date >='2002-03-24' AND (mot='test') ORDER BY date DESC LIMIT 0,20; +---++++ -+--+--+ --+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++++ -+--+--+ --+ | searchmainhardwarefr7 | ref| PRIMARY,numreponse | PRIMARY| 30 | const| 3597 | where used; Using index; Using temporary; Using filesort | | searchjoinhardwarefr7 | eq_ref | PRIMARY,numreponse | numreponse | 4 | searchmainhardwarefr7.numreponse |1 | where used | +---++++ -+--+--+ --+ 2 rows in set (0.00 sec) However if I remove the AND mot='test', DISTINCT comes back in the EXPLAIN result : mysql> EXPLAIN SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND date >='2002-03-24' ORDER BY date DESC LIMIT 0,20; +---+---+++- +--++--- ---+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+---+++- +--++--- ---+ | searchjoinhardwarefr7 | range | PRIMARY,numreponse | PRIMARY| 8 | NULL | 174020 | where used; Using index; Using temporary; Using filesort | | searchmainhardwarefr7 | ref | numreponse | numreponse | 4 | searchjoinhardwarefr7.numreponse | 9 | Using index; Distinct | +---+---+++- +--++--- ---+ 2 rows in set (0.00 sec) Jocelyn - Original Message - From: "DL Neil" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 6:58 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 | &g
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
Re: How does DISTINCT really work ?
Hi, 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
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
