MySQL Bugs: #22614: improper results in select statements when multiple tables are used

Bug #22614	improper results in select statements when multiple tables are used
Submitted:	22 Sep 2006 19:49	Modified:	26 Nov 2006 16:34
Reporter:	Ksheera Puritipati	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.11-beta	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
Select ALL TableB.description, sum(1) totalCount from TableA, TableB where TableA.ID = TableB.IDName

the above query is returning double the record count. Suppose 6 rows exists, returning 12. The query works perfectly in Oracle. When I am trying to migrate from Oracle, this problem occured. 

If I remove tableB and check with same where clause, getting exact record count.. but if I use tableB, count doubles.

How to repeat:
SELECT ALL motor_veh_inv_with_dimension.description mviw,
motor_veh_inv_with_dimension.sort_order,
accident_fact.cnty_city_loc,
SUM(1) tot, 
SUM(if(accident_fact.collision_severity='1',1,0)) fatal, 
SUM(case accident_fact.collision_severity 
when '2' then 1 
when '3' then 1 
when '4' then 1 
else 0 end ) injury,
SUM(if(accident_fact.collision_severity='0',1,0)) pdo,
SUM(accident_fact.NUMBER_killed) killed,  
SUM(accident_fact.number_injured) injured, 
SUM(accident_fact.count_severe_inj) severe, SUM(accident_fact.count_visible_inj) visible, 
SUM(accident_fact.count_complaint_pain) complaint_pain 
FROM accident_fact, motor_veh_inv_with_dimension
WHERE accident_fact.mviw = motor_veh_inv_with_dimension.code 
AND accident_fact.not_private_property='Y'
AND accident_fact.juris= $P{P_JURIS}  
AND accident_fact.cnty_city_loc = $P{P_LOC} 
AND accident_fact.collision_date >= $P{P_BEGIN_DATE} 
AND accident_fact.collision_date <= $P{P_END_DATE} 
GROUP BY juris,description, cnty_city_loc, sort_order ORDER BY sort_order

this query returns double the values in select statement. This works fine in Oracle (Did modifications for Oracle decode())

Thank you for the bug report. Could you please provide a complete test
case table definition/insert data/query and wrong result. Thanks in
advance.

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".

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".