MySQL bug?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

It's not wrong. You really need to read and understand the W3C standards I linked you to for SQL.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL bug?

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL bug?

Post 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 :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL bug?

Post by VladSun »

Weirdan wrote:I missed epic thread it seems :)
Definitely :) :P
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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL bug?

Post by Eran »

Very good article, thanks Vlad. A lot of indepth information
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL bug?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply