MySQL Bugs: #1354: Sub-query returns incorrect result for count(*)

Bug #1354	Sub-query returns incorrect result for count(*)
Submitted:	19 Sep 2003 14:35	Modified:	1 Nov 2003 11:35
Reporter:	Kevin Oleniczak	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
It seems that an attempt to do a coorelated sub-query to get a "sub-count" returns something wrong.

In the example below, there are two tables. The first is the battles_rt table which is the parent to the battle_players table.

select battle_rt_id,
      (select count(distinct bp.player_id) 
         from battle_players_rt bp 
        where b.battle_rt_id = bp.battle_rt_id) as cnt_players
  from battles_rt b;

This should return the correct count of players for each battle record. It does it correctly for the first row in battles_rt and then returns null values for the other rows. In other words the coorelation for any row beyond the first, fails.

How to repeat:

CREATE TABLE battles_rt (
       battle_rt_id            INTEGER not null AUTO_INCREMENT,            
       server               VARCHAR(100) not null,
       map                  VARCHAR(50) not null,
       gamemode             VARCHAR(20) not null,
       rounds               INTEGER not null,
       timelimit            VARCHAR(20) not null,
       allied_score         INTEGER not null,
       axis_Score           INTEGER not null,
       server_ip_addr       VARCHAR(20) not null,
       create_date          DATETIME not null,
       last_date            DATETIME not null,
       status               varchar(20) not null,
       PRIMARY KEY (battle_rt_id) )
       TYPE = INNODB ;

CREATE TABLE battle_players_rt (
       battle_rt_id            INTEGER not null,       
       player_id            INTEGER not null,
       query_id             VARCHAR(20) not null,
       team                 VARCHAR(20) not null,
       score                INTEGER not null,
       frags                INTEGER not null,
       deaths               INTEGER not null,
       ping                 INTEGER not null,       
       create_date          DATETIME not null,
       PRIMARY KEY (battle_rt_id, player_id, query_id ) )
       TYPE = INNODB ;

Suggested fix:
look into correlation rounties for sub-quires... i guess.

Can you please provide some example data as well as output of query you assume is wrong compared to expected output.

With some random data inserted into your tables current 4.1 from source tree displays correct number of rows.

It is not very related, but same result can be achieved without subquery:

SELECT b.battle_rt_id, COUNT(bp.player_id) AS player_count 
FROM battles_rt AS b LEFT JOIN battle_players_rt AS bp 
     ON b.battle_rt_id = bp.battle_rt_id
GROUP BY b.battle_rt_id;

I appreciate the alternate approach but need the original query to work. I "think" it's fair to ask this as it is a very common method of doing sub-queries which 4.1 is supposed to provide. In the past week I actually have encountered a few other problems with sub-queries... using the same schema (plus another table or two) I tried this:

select sum(deaths) deaths, sum(score) score, sum(frags) frags 
from (
  select clan_id, bp.battle_rt_id, bp.player_id, max(deaths) deaths, max(score) score, max(frags) frags
   from clan_players cp, players p, battle_players_rt bp 
  where cp.person_id = p.person_id 
  and p.player_id = bp.player_id 
  and bp.create_date > DATE_SUB(SYSDATE(),INTERVAL 30 DAY) 
  and cp.clan_id =1
  group by clan_id, bp.battle_rt_id, bp.player_id 
) mysubquery

this query will aggregate player scores for a some game stats I am experimenting with. It's neccessary to have the sub-query as I only want to sum the "last" set of values containing the max points achieved. When I run this query two things happen:
-I get an error reported by windows xp that kills the mysql service 
-Returns the correct result

This doesn't work for me either as I would have to restart mysql everytime someone runs this via a web app. Please help.

here's some of the error info reported by widowz...

szAppName : mysqld-max-nt.exe     szAppVer : 0.0.0.0     
szModName : mysqld-max-nt.exe     szModVer : 0.0.0.0     offset : 0006a583

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".