credativ team mailing list archive

[Daniel Stenlöv (XCLUDE) <912332@xxxxxxxxxxxxxxxxxx>]

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