Page 3 of 3
Posted: Thu Aug 16, 2007 6:12 am
by superdezign
VladSun wrote:Using DISTINCT is pointless in this query - all of my estate_pictures records have distinct name field. So every row in this query will be unique. As a result I'll have multiple rows for each estates.id due to LEFT JOINing tables. I.e. with or without DISTINCT the result remains still the same.
Note that you are grouping by estates.id, not the picture name. When you join the picture table, you would get multiple entries of different pictures with the same estates.id. However, using GROUP BY, you would only get one result per estates.id. I haven't tested it, but I'm fairly sure that this is the case.
Posted: Thu Aug 16, 2007 10:08 am
by VladSun
superdezign wrote:VladSun wrote:Using DISTINCT is pointless in this query - all of my estate_pictures records have distinct name field. So every row in this query will be unique. As a result I'll have multiple rows for each estates.id due to LEFT JOINing tables. I.e. with or without DISTINCT the result remains still the same.
Note that you are grouping by estates.id, not the picture name. When you join the picture table, you would get multiple entries of different pictures with the same estates.id. However, using GROUP BY, you would only get one result per estates.id. I haven't tested it, but I'm fairly sure that this is the case.
It was a post about using DISTINCT in this query - I haven't mentioned GROUP BY...
All of the posts in this topic should have been discusing whether using GROUP BY without agregate function is correct while keeping in mind what is the purpose of GROUP BY.
I think it is clear that its purpose is not to limit the number of rows when joining two tables. Its only purpose is to have an agregate function applied to each individual group of column values. Nothing else.
I gave an example with PGSQL because I really believe that it is closer to SQL standard than MySQL. It does not indeeed guarantee that in this case MySQL has wrong aporach, but the possibility is higher.
Posted: Thu Aug 16, 2007 5:13 pm
by superdezign
Well if you were to use DISTINCT on estates.id, would it only return one picture name per result, or is GROUP BY the only way to do so?
Posted: Thu Aug 16, 2007 5:43 pm
by VladSun
superdezign wrote:Well if you were to use DISTINCT on estates.id, would it only return one picture name per result,
DISTINCT means that the *whole* row (i.e. the set of columns' values in a row) must be different from any of the other ones returned by the query. You can not have DISTINCT modifier applied to just one of the columns in the query.
superdezign wrote:or is GROUP BY the only way to do so?
I'm a little bit tired of repeating it - I think that using GROUP BY in my first query example must be considered MySQL misbehave according to SQL standard. I think it is wrong nevertheless it is working.
I've shown you in my second query one way of how it should be accomplished however complying SQL standard.
Posted: Thu Aug 16, 2007 5:49 pm
by AKA Panama Jack
It's not wrong. You really need to read and understand the W3C standards I linked you to for SQL.
Posted: Thu Aug 16, 2007 6:07 pm
by VladSun
Now I am totally tired ...
Also, I should tell my friend to make his high payed team of DB designers, admins and developers to go to W3School and refresh their knowledge. It is ridiculous.
The only argument you have is that using LEFT JOIN has the same effect as using aggregate functions in a query and thus using GROUP BY is permitted and can always be a solution.
Posted: Thu Aug 16, 2007 6:25 pm
by AKA Panama Jack
Just because they are high paid developers doesn't mean they know what they are talking about. I have met many a college educated programmer that didn't know squat but the degree entitled them to a fat salary.
Posted: Thu Aug 16, 2007 6:39 pm
by VladSun
AKA Panama Jack wrote:Just because they are high paid developers doesn't mean they know what they are talking about. I have met many a college educated programmer that didn't know squat but the degree entitled them to a fat salary.
In my country degree means almost NOTHING! It is the expirience and knowledge that is paid for!
For me this topic is over.
Have a nice day!
Re: MySQL bug?
Posted: Tue Feb 17, 2009 8:43 am
by VladSun
Sorry for waking up this thread, but I finally found a really good article on the topic discussed here.
I think that everyone who use MySQL should have read this:
http://dev.mysql.com/tech-resources/art ... myths.html
If I was right or wrong - I think it's still an open question

Re: MySQL bug?
Posted: Tue Feb 17, 2009 2:31 pm
by Weirdan
I missed epic thread it seems

Though any thread APJ participated in is worth reading, even if only from moderator's point of view.
PS: VladSun, your English is much better now

Re: MySQL bug?
Posted: Tue Feb 17, 2009 3:03 pm
by VladSun
Weirdan wrote:I missed epic thread it seems

Definitely
Weirdan wrote:PS: VladSun, your English is much better now

Thanks, I hope so. This forum is the only place where I can practise and improve my poor English

I'm not kidding

Re: MySQL bug?
Posted: Tue Feb 17, 2009 3:55 pm
by Eran
Very good article, thanks Vlad. A lot of indepth information
Re: MySQL bug?
Posted: Tue Feb 17, 2009 5:44 pm
by VladSun
pytrin wrote:Very good article, thanks Vlad. A lot of indepth information
Yeah, it's good

I couldn't believe this is the exact article I've been looking for ages.