Bug #44562 Multiple join, duplicate result

• Submitted: 29 Apr 2009 21:41
• Modified: 30 Apr 2009 9:44
• Reporter: Sri Wahono
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.1.30
• OS: Windows (Vista)
• CPU Architecture: Any
• Tags: join, multiple

[29 Apr 2009 21:41] Sri Wahono

Description:
I create table and insert data with this syntax :

CREATE TABLE `purchase` (
  `purchaseid` char(15) NOT NULL DEFAULT '',
  `purchaseidref` char(15) NOT NULL DEFAULT '',
  `purchasedate` date DEFAULT NULL,
  `purchasetime` time DEFAULT NULL,
  `purchasetype` smallint(6) NOT NULL DEFAULT '0' COMMENT '0=CASH, 1=CASH ON DELIVERY, 2=CREDIT',
  `kind` int(11) NOT NULL DEFAULT '0',
  `updatetimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`purchaseid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `purchase` (`purchaseid`,`purchaseidref`,`purchasedate`,`purchasetime`,`purchasetype`,`kind`,`updatetimestamp`) VALUES 
 ('B000021','B000021','2009-03-28','12:10:44',0,1,'2009-03-28 12:11:37'),
 ('B000022','B000022','2009-03-28','12:48:27',0,1,'2009-03-28 12:48:47'),
 ('B000023','B000023','2009-04-14','02:22:18',0,1,'2009-04-14 02:22:42');

CREATE TABLE `purchasedetail` (
  `transid` bigint(20) NOT NULL AUTO_INCREMENT,
  `purchaseid` char(15) NOT NULL DEFAULT '',
  `grossamount` float NOT NULL DEFAULT '0',
  `purchasetax` float NOT NULL DEFAULT '0',
  `valuedisc` float NOT NULL DEFAULT '0',
  `netamount` float NOT NULL DEFAULT '0',
  `updatetimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transid`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

INSERT INTO `purchasedetail` (`transid`,`purchaseid`,`grossamount`,`purchasetax`,`valuedisc`,`netamount`,`updatetimestamp`) VALUES 
 (18,'B000021',176000,0,0,176000,'2009-03-28 12:11:37'),
 (19,'B000021',180000,18000,0,198000,'2009-03-28 12:11:37'),
 (20,'B000022',48000,4776,240,52536,'2009-03-28 12:48:47'),
 (21,'B000022',104000,9360,10400,102960,'2009-03-28 12:48:47'),
 (22,'B000023',48000,4800,0,52800,'2009-04-14 02:22:42');

CREATE TABLE `purchaseexpensesdetail` (
  `transid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `purchaseid` char(15) NOT NULL DEFAULT '',
  `transdate` date DEFAULT NULL,
  `debit` float NOT NULL DEFAULT '0',
  `credit` float NOT NULL DEFAULT '0',
  `updatetimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `purchaseexpensesdetail` (`transid`,`purchaseid`,`transdate`,`debit`,`credit`,`updatetimestamp`) VALUES 
 (1,'B000021',NULL,50000,0,'2009-04-17 06:17:55');

 CREATE TABLE `purchasepayments` (
  `transid` bigint(20) NOT NULL AUTO_INCREMENT,
  `purchaseid` char(15) NOT NULL DEFAULT '',
  `transdate` date DEFAULT NULL,
  `debit` float NOT NULL DEFAULT '0',
  `credit` float NOT NULL DEFAULT '0',
  `updatetimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `purchasepayments` (`transid`,`purchaseid`,`transdate`,`debit`,`credit`,`updatetimestamp`) VALUES 
 (8,'B000021','2009-03-28',374000,0,'2009-03-28 12:11:37'),
 (9,'B000022','2009-03-28',139946,0,'2009-03-28 12:48:47');

And I execute this query :

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 outer join purchasedetail on purchase.purchaseid = purchasedetail.purchaseid
left outer join purchaseexpensesdetail on purchase.purchaseid = purchaseexpensesdetail.purchaseid
left outer join purchasepayments on purchase.purchaseid = purchasepayments.purchaseid
where purchase.kind=1
group by
purchase.purchaseid,
purchase.purchasedate,
purchase.purchasetime,
purchase.purchasetype
order by
purchase.purchasedate,
purchase.purchasetime

The result in columns "sumgross", "sumdisc", "sumtax", and "sumnet" is correct.
But in column "sumexpenses" and "sumpayments" is double counting.

How to repeat:
Just execute query above

[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