Bug #44562 | Multiple join, duplicate result | ||
---|---|---|---|
Submitted: | 29 Apr 2009 21:41 | Modified: | 30 Apr 2009 9:44 |
Reporter: | Sri Wahono | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 5.1.30 | OS: | Windows (Vista) |
Assigned to: | CPU Architecture: | Any | |
Tags: | join, multiple |
[29 Apr 2009 21:41]
Sri Wahono
[29 Apr 2009 22:35]
MySQL Verification Team
Thank you for the bug report. Could you please print here the actual result and the expected one also please do a try with the decimal data type instead of these float you have in your tables definition. Thanks in advance.
[29 Apr 2009 23:00]
Sri Wahono
This is the result : purchaseid purchasedate purchasetime transactiontype sumgross sumdisc sumtax sumexpenses sumpayments sumnet B000021 2009-03-28 12:10:44 Cash 356000 0 18000 100000 748000 374000 B000022 2009-03-28 12:48:27 Cash 152000 10640 14136 279892 155496 B000023 2009-04-14 02:22:18 Cash 48000 0 4800 52800 And IMO, the result should like this : purchaseid purchasedate purchasetime transactiontype sumgross sumdisc sumtax sumexpenses sumpayments sumnet B000021 2009-03-28 12:10:44 Cash 356000 0 18000 50000 374000 374000 B000022 2009-03-28 12:48:27 Cash 152000 10640 14136 139946 155496 B000023 2009-04-14 02:22:18 Cash 48000 0 4800 52800 Thanks
[30 Apr 2009 6:45]
Sveta Smirnova
Thank you for the feedback. This is really not a bug. If simplify query and remove GROUP BY: SELECT purchase.purchaseid, purchaseexpensesdetail.debit, purchaseexpensesdetail.credit, purchasepayments.debit, purchasepayments.credit from purchase left join purchasedetail on purchase.purchaseid = purchasedetail.purchaseid left join purchaseexpensesdetail on purchase.purchaseid = purchaseexpensesdetail.purchaseid left join purchasepayments on purchase.purchaseid = purchasepayments.purchaseid ; purchaseid debit credit debit credit B000021 50000 0 374000 0 B000021 50000 0 374000 0 B000022 NULL NULL 139946 0 B000022 NULL NULL 139946 0 B000023 NULL NULL NULL NULL You see there are 2 rows with B000021. If count sum(purchaseexpensesdetail.debit-purchaseexpensesdetail.credit) for each of them result is 50000, but when you use GROUP BY result is 50000 + 50000 = 100000. Same for sum(purchasepayments.debit-purchasepayments.credit). So this is not a bug.
[30 Apr 2009 7:31]
Sri Wahono
I need to group purchasedetail, purchaseexpensesdetail and purchaseexpenses to create summary and grouping by purchase.purchaseid. IMO, purchasedetail, purchaseexpensesdetail and purchaseexpenses is in same level, as table with "left join" to purchase. Why the result in purchasedetail is true and in "purchaseexpensesdetail and purchaseexpenses" is false? I try again with another way : SELECT purchase.purchaseid, purchase.purchasedate, purchase.purchasetime, case purchase.purchasetype when 0 then 'Cash' when 1 then 'Cash On Delivery' when 2 then 'Credit' end as transactiontype, sum(purchasedetail.grossamount) as sumgross, sum(purchasedetail.valuedisc) as sumdisc, sum(purchasedetail.purchasetax) as sumtax, sum(purchaseexpensesdetail.debit-purchaseexpensesdetail.credit) as sumexpenses, sum(purchasepayments.debit-purchasepayments.credit) as sumpayments, sum(purchasedetail.netamount) as sumnet from purchase left join (purchasedetail,purchaseexpensesdetail,purchasepayments) on (purchase.purchaseid = purchasedetail.purchaseid and purchase.purchaseid =purchaseexpensesdetail.purchaseid and purchase.purchaseid = purchasepayments.purchaseid) where purchase.kind=1 group by purchase.purchaseid, purchase.purchasedate, purchase.purchasetime, purchase.purchasetype order by purchase.purchasedate, purchase.purchasetime But the result doesn't correct too. Please you read again my report. Thanks
[30 Apr 2009 9:44]
Sri Wahono
And I can get correct result when I split with sub query. I think it doesn't consistent. Or any maximum limit in join syntax? This syntax produces correct result : select t1.purchaseid, t1.purchasedate, t1.purchasetime, t1.transactiontype, t1.sumnet, sum(t2.debit-t2.credit) as sumexpenses, sum(t3.debit-t3.credit) as sumpayments from (SELECT purchase.purchaseid, purchase.purchasedate, purchase.purchasetime, purchase.kind, case purchase.purchasetype when 0 then 'Cash' when 1 then 'Cash On Delivery' when 2 then 'Credit' end as transactiontype, sum(purchasedetail.grossamount) as sumgross, sum(purchasedetail.valuedisc) as sumdisc, sum(purchasedetail.purchasetax) as sumtax, sum(purchasedetail.netamount) as sumnet from purchase left join purchasedetail on purchase.purchaseid=purchasedetail.purchaseid group by purchase.purchaseid, purchase.purchasedate, purchase.purchasetime, purchase.purchasetype) as t1 left join purchaseexpensesdetail as t2 on t1.purchaseid=t2.purchaseid left join purchasepayments as t3 on t1.purchaseid=t3.purchaseid where t1.kind=1 group by t1.purchaseid, t1.purchasedate, t1.purchasetime, t1.transactiontype order by t1.purchasedate, t1.purchasetime