List:General Discussion« Previous MessageNext Message »
From:Tom Crimmins Date:February 28 2005 10:39pm
Subject:RE: ODD COUNT(*) Results on Self-Join (Bug?)
View as plain text  
On Monday, February 28, 2005 14:54, Van wrote:

> Tom:
> 
> I see your point, but the group by is necessary so I can walk through
> all Song Title groups and get the total number of unique versions of
> that song.  If I do this:
> SELECT DISTINCT file_details.Title,
>          file_details_1.CD,
>          file_details_1.mp3Name,
>         COUNT(*) AS cnt
>  FROM file_details LEFT JOIN file_details AS file_details_1
>          ON file_details.Title = file_details_1.Title
>  WHERE (((file_details.Type) Like 'Song%')
>          AND file_details.CD = 'Wasted Tears'
>          AND file_details_1.Type LIKE 'Song%'
>          AND file_details_1.FileName NOT LIKE '%_ds.php3'
>          AND file_details.Title = 'Seems I\'ll')
> GROUP BY file_details.Title
>  ORDER BY file_details_1.Title;
> 
> I get this:
> +------------+--------------+-----------------------+-----+
>> Title      | CD           | mp3Name               | cnt |
> +------------+--------------+-----------------------+-----+
>> Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 |   6 |
> +------------+--------------+-----------------------+-----+
> 
> COUNT = 6; And, it's distinct.  Watch what happens when I do a row
> listing without the grouping;
> 
> SELECT DISTINCT file_details.Title,
>          file_details_1.CD,
>          file_details_1.mp3Name
>  FROM file_details LEFT JOIN file_details AS file_details_1
>          ON file_details.Title = file_details_1.Title
>  WHERE (((file_details.Type) Like 'Song%')
>          AND file_details.CD = 'Wasted Tears'
>          AND file_details_1.Type LIKE 'Song%'
>          AND file_details_1.FileName NOT LIKE '%_ds.php3'
>          AND file_details.Title = 'Seems I\'ll')
>  ORDER BY file_details_1.Title;
> +------------+-----------------+-------------------------------------+
>> Title      | CD              | mp3Name                             |
> +------------+-----------------+-------------------------------------+
>> Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3       |
>> Seems I'll | n/a             | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 |
>> Seems I'll | Wasted Tears    | mp3/mp3s/seemsill.mp3               |
> +------------+-----------------+-------------------------------------+
> 
> So, here the listing is the group of records I want and there are 3,
> and it's distinct.
> 
> Why when I put the group on this query (which is what I need) does it
> double the count?
> 
> I think it's a bug.
> 

If you displayed all the fields from both tables, I think you would 
understand the join better. This is somewhat difficult to explain, but 
because you are only saying file_details_1.FileName NOT LIKE '%_ds.php3' 
and not file_details.FileName NOT LIKE '%_ds.php3' as well, this will in 
effect double all of your groupings since you are not grouping by filename, 
and two rows from table 1 will be left after the where clause to be joined 
to the three rows left in table 2. This in effect gives you your six rows,
or
3 groups of 2 with you group by clause.

Like I said above, I would suggest showing all of the fields 
ie. SELECT file_details.*, file_details_1.* FROM ....., so you can get a 
better idea of what is going on here. Also, there really isn't any reason 
to do a left join here. An inner join would work just fine since you are 
joining a table with itself on the same field there will always be a match. 

By the way this is a horrible explaination, maybe someone else can do a
better 
job of it.

> 
> Tom Crimmins wrote:
> 
>> On Sunday, February 27, 2005 19:20, Van wrote:
>> 
>> Hi Van,
>> 
>> 
>> 
>>> Greetings:
>>> 
>>> I've got a table that has the following fields that are relevant to
>>> my self-join: FileName  | varchar(100)  |      | MUL |
>>>               |
>>> Title     | varchar(45)   |      | MUL |
>>>               |
>>> Type      | varchar(20)   |      |     | HTML
>>>               |
>>> mp3Name   | varchar(100)  |      |     |
>>>               |
>>> CD        | varchar(25)   |      |     |
>>>               |
>>> 
>>> Here are the relevant values for the fields for the song Seems I'll:
>>> +------------------------------------+------------+--------+----------
>>> FileName                           | Title      | Type   |
>>> mp3Name                             | CD              |
>>> +------------------------------------+------------+--------+----------
>>> mp3/php3/seemsill.php3             | Seems I'll | Song   |
>>> mp3/mp3s/seemsill.mp3               | Wasted Tears    |
>>> mp3/php3/SeemsIllUnplugged.php3    | Seems I'll | Song   |
>>> mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a             |
>>> mp3/php3/SeemsIll20031029.php3     | Seems I'll | Song   |
>>> mp3/mp3s/SeemsIll20031029.mp3       | Fear of Success |
>>> lyrics/seemsill.php3               | Seems I'll | Lyrics |
>>> mp3/php3/SeemsIll20031029_ds.php3   | Wasted Tears    |
>>> mp3/php3/seemsill_ds.php3          | Seems I'll | Song   |
>>> mp3/mp3s/seemsill.mp3               | Wasted Tears    |
>>> mp3/php3/SeemsIll20031029_ds.php3  | Seems I'll | Song   |
>>> mp3/mp3s/SeemsIll20031029.mp3       | Fear of Success |
>>> mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song   |
>>> mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a             |
>>> +------------------------------------+------------+--------+----------
>>> 
>>> Here is the query in question (I'm trying to get the count of all
>>> versions of Seems I'll songs, which was originally on the CD Wasted
>>> Tears, so I can display the other versions, including the one on
>>> Wasted Tears {mp3/mp3s/seemsill.mp}):
>>> SELECT file_details.Title,
>>>        file_details.Type,
>>>        file_details_1.CD,
>>>        file_details_1.mp3Name,
>>>        COUNT(*) AS cnt
>>> FROM file_details LEFT JOIN file_details AS file_details_1
>>>        ON file_details.Title = file_details_1.Title
>>> WHERE (((file_details.Type) Like 'Song%')
>>>        AND file_details.CD = 'Wasted Tears'
>>>        AND file_details_1.Type LIKE 'Song%'
>>>        AND file_details_1.FileName NOT LIKE '%_ds.php3'
>>>        AND file_details.Title = 'Seems I\'ll')
>>> GROUP BY file_details.Title, file_details.Type, file_details_1.CD,
>>> file_details_1.mp3Name ORDER BY file_details_1.Title;
>>> 
>>> But, oddly here is the result of this query:
>>> +------------+------+-----------------+-------------------------
>>> Title      | Type | CD              |
>>> mp3Name                             | cnt |
>>> +------------+------+-----------------+-------------------------
>>> Seems I'll | Song | Fear of Success |
>>> mp3/mp3s/SeemsIll20031029.mp3       |   2 |
>>> Seems I'll | Song | n/a             |
>>> mp3/mp3s/SeemsIllUnpluggedMixed.mp3 |   2 |
>>> Seems I'll | Song | Wasted Tears    |
>>> mp3/mp3s/seemsill.mp3               |   2 |
>>> +------------+------+-----------------+-------------------------
>>> 
>>> The count should be 3, right?  What gives?
>>> 
>>> 
>> 
>> The cnt field looks to be correct here based on your data. The count
>> here is the number of results in that grouping not the total number
>> of rows returned. If you remove the group by clause from your query,
>> you should see 6 rows returned (2 of each).

Regards,

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
Thread
Mysql tuning - server Crash 1Deluxe Web26 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri27 Feb
  • Re: Mysql tuning - server Crash 1Deluxe Web27 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri27 Feb
  • RE: Mysql tuning - server Crash 1Donny Simonton28 Feb
    • ODD COUNT(*) Results on Self-Join (Bug?)Van28 Feb
    • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
    • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
RE: ODD COUNT(*) Results on Self-Join (Bug?)Tom Crimmins28 Feb
  • Re: ODD COUNT(*) Results on Self-Join (Bug?)Van28 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri28 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri28 Feb
  • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri28 Feb
  • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
RE: ODD COUNT(*) Results on Self-Join (Bug?)Tom Crimmins28 Feb
  • Re: ODD COUNT(*) Results on Self-Join (Bug?)Van28 Feb