SUMMERY OF ENVIROMENT Ubuntu 11.04 Python: 2.7 Postgre: 9.0 OpenERP Server: 6.0.3 OpenERP Client: 6.0.2 and 6.0.3.
The extra cast doesn't have any real value in the code. I does however gives some extra problems explained below. Code and solution was provided in first post. What is needed to be done is to accept and test the fix for implementation. There is two main reasons why this is problematic: 1) The extra cast is only putting extra load on the SQL server. The bug might not pop up because of the data included in the database. 2) Float Point Accuracy Problem. For every cast that is done this problem is increased. For more information about float point accuracy problem see, http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems and below for more explaination. First of all I will present some basic information, so it's easier to understand the problem. FLOATING POINT STANDARD, IEEE754-2008 A quick overview of the standard, http://en.wikipedia.org/wiki/IEEE_754-2008 Single-precision floating-point format, http://en.wikipedia.org/wiki/Single_precision_floating-point_format POSTGRE DATA TYPES, http://www.postgresql.org/docs/9.0/static/datatype- numeric.html numeric [(p, s)] Alias: decimal [(p, s)] Storage size: variable Description: Exact numeric with selectable precision Range: No limit real Alias: float4 Storage size: 4 bytes Description: Inexact, single precision floating-point number Range: 6 decimals digits precision double precision Alias: float8 Storage size: 8 bytes Description: Inexact, double precision floating-point number Range: 15 decimals digits precision PYTHON DATA TYPE, http://starship.python.net/crew/theller/ctypes/tutorial.html#fundamental-data-types float (is the same as float in C) Storage size: 4 bytes Description: Inexact, single precision floating-point number Range: 6 decimals OPENERP see bin/osv/fields.py and class float for more information PROBLEM EXPLAINED What is specific about real and double precision is that they are inexact which means they are approximations of the original value. Since you are casting the value you will get an approximation of the original value that is then recast in python. So you might end up with dual approximation error because of how float is handled. This will result of some values returning incorrectly because of float point limitation, so to minimize this issue transformation should be keeped to a minimum. I'm going to try to explain the big issues. Since you are doing a round, the answer will be returned with 2 decimals in Numeric postgre data type. Float data type in python is the same as float data type in C. It uses 4 bytes for representing itself. So it's not the same size as double precision in postgre that uses 8 bytes. So flow can actually be described as this: The module takes two 6 decimal precision values minus each other. Then cast them into exact precision and rounds off the value to 2 decimal precision and the cast it inexact to 15 decimals precision to try to force it down into a inexact 6 decimal precision. There are some issues with float-point handling in Python that is good to know about. More information about that could be found here: http://docs.python.org/tutorial/floatingpoint.html If you are going to use float then you should use more decimals then the final value and by casting it multiple times will make the end result more faulty then casting it as few times as possible. Switching between float types will not help. It would actually be better to use the python data type decimal introduced in python 2.6. I provided a quick fix to the issue in the first post, even if you can't recreate it. My guess is that it depends on the data in Postgre. -- You received this bug notification because you are a member of OpenERP Framework Experts, which is subscribed to OpenERP Addons. https://bugs.launchpad.net/bugs/912332 Title: hr_timesheet_sheet: cannot change data type of view column "total_difference" from numeric to double precision Status in OpenERP Addons (modules): Incomplete Bug description: Server: Ubuntu Server v11.04 OpenERP-Server version: 6.0.3. Postgre version: 9.0 Updating the module hr_timesheet_sheet brings up the following error: ---- CUT ---- Environment Information : System : Windows-Vista-6.1.7601-SP1 OS Name : nt Operating System Release : Vista Operating System Version : 6.1.7601 Operating System Architecture : 32bit Operating System Locale : sv_SE.cp1252 Python Version : 2.5.2 OpenERP-Client Version : 6.0.2 Last revision No. & ID :Bazaar Package not Found !Traceback (most recent call last): File "/opt/openerp/server/bin/netsvc.py", line 489, in dispatch result = ExportService.getService(service_name).dispatch(method, auth, params) File "/opt/openerp/server/bin/service/web_services.py", line 599, in dispatch res = fn(db, uid, *params) File "/opt/openerp/server/bin/osv/osv.py", line 122, in wrapper return f(self, dbname, *args, **kwargs) File "/opt/openerp/server/bin/osv/osv.py", line 176, in execute res = self.execute_cr(cr, uid, obj, method, *args, **kw) File "/opt/openerp/server/bin/osv/osv.py", line 167, in execute_cr return getattr(object, method)(cr, uid, *args, **kw) File "/opt/openerp/server/bin/addons/base/module/wizard/base_module_upgrade.py", line 98, in upgrade_module _db, pool = pooler.restart_pool(cr.dbname, update_module=True) File "/opt/openerp/server/bin/pooler.py", line 60, in restart_pool return get_db_and_pool(db_name, force_demo, status, update_module=update_module) File "/opt/openerp/server/bin/pooler.py", line 39, in get_db_and_pool addons.load_modules(db, force_demo, status, update_module) File "/opt/openerp/server/bin/addons/__init__.py", line 883, in load_modules processed_modules.extend(load_module_graph(cr, graph, status, report=report, skip_modules=processed_modules)) File "/opt/openerp/server/bin/addons/__init__.py", line 719, in load_module_graph init_module_objects(cr, package.name, modules) File "/opt/openerp/server/bin/addons/__init__.py", line 420, in init_module_objects obj.init(cr) File "/opt/openerp/server/bin/addons/hr_timesheet_sheet/hr_timesheet_sheet.py", line 712, in init )) AS bar""") File "/opt/openerp/server/bin/sql_db.py", line 78, in wrapper return f(self, *args, **kwargs) File "/opt/openerp/server/bin/sql_db.py", line 131, in execute res = self._obj.execute(query, params) ProgrammingError: cannot change data type of view column "total_difference" from numeric to double precision ---- CUT ---- The error seems to be located in the hr_timesheet_sheet.py file on line 657 for 6.0 branch and on line 667 for Trunk branch. Show below: ---- CUT (6.0 BRANCH) ---- 641 _columns = { 642 'name': fields.date('Date', readonly=True), 643 'sheet_id': fields.many2one('hr_timesheet_sheet.sheet', 'Sheet', readonly=True, select="1"), 644 'total_timesheet': fields.float('Total Timesheet', readonly=True), 645 'total_attendance': fields.float('Attendance', readonly=True), 646 'total_difference': fields.float('Difference', readonly=True), 647 } 648 649 def init(self, cr): 650 cr.execute("""create or replace view hr_timesheet_sheet_sheet_day as 651 SELECT 652 id, 653 name, 654 sheet_id, 655 total_timesheet, 656 total_attendance, 657 cast(round(cast(total_attendance - total_timesheet as Numeric),2) as Double Precision) AS total_difference ---- CUT ---- ---- CUT (TRUNK BRANCH) ---- 651 _columns = { 652 'name': fields.date('Date', readonly=True), 653 'sheet_id': fields.many2one('hr_timesheet_sheet.sheet', 'Sheet', readonly=True, select="1"), 654 'total_timesheet': fields.float('Total Timesheet', readonly=True), 655 'total_attendance': fields.float('Attendance', readonly=True), 656 'total_difference': fields.float('Difference', readonly=True), 657 } 658 659 def init(self, cr): 660 cr.execute("""create or replace view hr_timesheet_sheet_sheet_day as 661 SELECT 662 id, 663 name, 664 sheet_id, 665 total_timesheet, 666 total_attendance, 667 cast(round(cast(total_attendance - total_timesheet as Numeric),2) as Double Precision) AS total_difference ---- CUT ---- The columns description says that the total_difference is of type float (python data type), but the SQL will try to cast it from Numeric (postgre data type) to Double Precision (postgre data type). This bug will disappear if SQL query is modified to look like this instead: ---- CUT ---- 657/667 round(cast(total_attendance - total_timesheet as Numeric),2) AS total_difference ---- CUT ---- The postgre function round will return it as Numeric and the error will disappear. To manage notifications about this bug go to: https://bugs.launchpad.net/openobject-addons/+bug/912332/+subscriptions -- Mailing list: https://launchpad.net/~credativ Post to : [email protected] Unsubscribe : https://launchpad.net/~credativ More help : https://help.launchpad.net/ListHelp
