List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 4 2006 3:38pm
Subject:Re: MySQL 5 Calculate Field query
View as plain text  
In the last episode (May 04), Hendro Suryawan said:
> Hi all,
> I found something strange with calculate field query in MySQL 5.
> 
> My base table structure like this :
> CREATE TABLE `BrgIn2` (                             
>          `Qty` decimal(10,3) NOT NULL default '0.000',     
>          `Price` decimal(19,4) NOT NULL default '0.0000',  
>          `Kurs` decimal(10,4) default NULL              
>        ) ENGINE=MyISAM DEFAULT CHARSET=latin1              
> 
> Create Table testig Select PO,BrgId,Qty,Price,Kurs, Qty*Price*Kurs AS 
> Amount from BrgIn2
> 
> Result in MySQL 4.1x
> 
> CREATE TABLE `testig` (                            
>          `Qty` decimal(10,3) NOT NULL default '0.000',    
>          `Price` decimal(19,4) NOT NULL default '0.0000', 
>          `Kurs` decimal(10,4) default NULL,               
>          `Amount` double(21,4) default NULL               
>        ) ENGINE=MyISAM DEFAULT CHARSET=latin1             
> 
> 
> Result in MySQL 5.0.20a
> 
> CREATE TABLE `testig` (                            
>          `Qty` decimal(10,3) NOT NULL default '0.000',    
>          `Price` decimal(15,4) NOT NULL default '0.0000', 
>          `Kurs` decimal(10,4) default NULL,               
>          `Amount` decimal(35,11) default NULL             
>        ) ENGINE=MyISAM DEFAULT CHARSET=latin1             
> 
> The result field is different size. Is it normal behaviour for MySQL 5 
> or bug? Because i have trouble with this behaviour in my apps. I use 
> Delphi 5/6 for my development tool.

MySQL 5's result is correct.  MySQL 4 converts "decimal" values to
"double" when doing math, where MySQL 5 has a true fixed-point math
package to process "double" values.

http://dev.mysql.com/doc/refman/5.0/en/precision-math.html

-- 
	Dan Nelson
	dnelson@stripped
Thread
MySQL 5 Calculate Field queryHendro Suryawan4 May
  • Re: MySQL 5 Calculate Field queryDan Nelson4 May