MySQL Bugs: #17974: INSERT ... SELECT with GROUP BY causes truncation of DECIMAL

Bug #17974	INSERT ... SELECT with GROUP BY causes truncation of DECIMAL
Submitted:	6 Mar 2006 16:16	Modified:	21 Mar 2006 10:08
Reporter:	Mark Leith	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.18	OS:	Any (Any)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
Using INSERT ... SELECT with GROUP BY causes truncation of DECIMAL values, whilst using a normal INSERT ... SELECT does not.

How to repeat:
CREATE TABLE `dest` (
`field1` decimal(3,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `source` (
`src1` smallint(6) default NULL,
`src2` smallint(6) default NULL,
`src3` smallint(6) default NULL,
`src4` smallint(6) default NULL,
`dec1` decimal(18,2) default NULL,
`dec2` decimal(18,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `source` VALUES (1,1,2006,1,'2828.00','2196.00'),(1,1,2006,2,'3450.00','0.00');

insert into dest select SUM(dec2/dec1) from source group by src1,src2,src3,src4;

show warnings;

select * from dest;

insert into dest select SUM(dec2/dec1) from source;

show warnings;

select * from dest;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3808

Yes, this bug is too hard to fix properly in 4.1
Here is similar one http://bugs.mysql.com/bug.php?id=10719