Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0032951Openbravo ERP04. Warehouse managementpublic2016-05-13 11:222018-01-26 10:51
Reporterngarcia 
Assigned Toalostale 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR16Q3 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0032951: Fix Stock process rounds the quantities to 6 decimals and it should not
DescriptionFix Stock process rounds the quantities to 6 decimals and it should not. The values in both tables should match without any rounding. Having wrong values in m_transaction table such as 15624.99949133737000000000 the m_storage_detail is updated to 15624.999491
Steps To Reproduce- Install the following module: https://code.openbravo.com/erp/pmods/org.openbravo.module.fixstock [^]
- Set a wrong movementqty like 123456789.0123456789 to any m_transaction record
- Check related m_storage_detail record has a precision of 9 decimals.
- As F&B Admin create a process request for Fix Stock process and run it.
- Check related m_storage_detail record has a precision of 6 decimals.
Proposed SolutionAttached a possible patch that should be reviewed
Additional Information
TagsFix Stock
Relationships
related to defect 0032943 closed markmm82 'Fix Stock QuantityOrder Process' should round the Order quantity of the opening physical inventory to the std precision 
related to defect 0032935 closed markmm82 Data is pending to be created in M_STORAGE_PENDING by the Fix Stock process 
related to defect 00150082.50MP24 closed adrianromero Wrong behaviour with 9 Decimals 
related to defect 0014769 closed AugustoMauch prescript-PostgreSql.sql: to_number(text) function returning wrong values for inputs in scientific notation 
related to defect 0030069 closed caristu Critical bug with decimals only in PostgreSQL 9.3 
depends on defect 0028459 closed eduardo_Argal It is necessary a modulescript to solve stock inconsistencies 
related to defect 0032934 closed markmm82 Wrong definition of convertUOM function in RestoreStockValue_data.xsql 
Attached Filesdiff FixStockCast.diff (1,206) 2016-05-13 11:22
https://issues.openbravo.com/file_download.php?file_id=9410&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-05-13 11:22ngarciaNew Issue
2016-05-13 11:22ngarciaAssigned To => Triage Finance
2016-05-13 11:22ngarciaFile Added: FixStockCast.diff
2016-05-13 11:22ngarciaModules => Core
2016-05-13 11:22ngarciaResolution time => 1466200800
2016-05-13 11:22ngarciaTriggers an Emergency Pack => No
2016-05-13 11:22ngarciaRelationship addeddepends on 0028459
2016-05-13 11:23ngarciaIssue Monitored: networkb
2016-05-16 13:25aferrazAssigned ToTriage Finance => markmm82
2016-05-16 13:26aferrazRelationship addedrelated to 0032943
2016-05-16 13:26aferrazRelationship addedrelated to 0032935
2016-05-16 17:39aferrazRelationship addedrelated to 0032934
2016-05-17 13:38ngarciaDescription Updatedbug_revision_view_page.php?rev_id=12132#r12132
2016-05-17 16:58markmm82Statusnew => scheduled
2016-05-23 14:49aferrazSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=12204#r12204
2016-05-23 16:33aferrazNote Added: 0086676
2016-05-23 16:33aferrazAssigned Tomarkmm82 => platform
2016-05-23 16:33aferrazStatusscheduled => feedback
2016-05-23 16:33aferrazStatusfeedback => new
2016-05-25 11:03alostaleRelationship addedrelated to 0015008
2016-05-25 11:05alostaleRelationship addedrelated to 0014769
2016-05-25 12:33alostaleReview Assigned To => caristu
2016-05-25 12:43alostaleStatusnew => scheduled
2016-05-25 12:43alostaleAssigned Toplatform => alostale
2016-05-25 14:48hgbotCheckin
2016-05-25 14:48hgbotNote Added: 0086745
2016-05-25 14:48hgbotStatusscheduled => resolved
2016-05-25 14:48hgbotResolutionopen => fixed
2016-05-25 14:48hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/ae61a0b425b3e86746040de0acfaeff9865fad9b [^]
2016-05-25 14:51alostaleNote Added: 0086746
2016-05-26 18:57caristuNote Added: 0086771
2016-05-26 18:57caristuStatusresolved => new
2016-05-26 18:57caristuResolutionfixed => open
2016-05-26 18:57caristuNote Edited: 0086771bug_revision_view_page.php?bugnote_id=0086771#r12240
2016-05-26 18:58caristuNote Edited: 0086771bug_revision_view_page.php?bugnote_id=0086771#r12241
2016-05-26 19:10caristuNote Edited: 0086771bug_revision_view_page.php?bugnote_id=0086771#r12242
2016-05-27 08:13caristuRelationship addedrelated to 0030069
2016-05-31 09:03hgbotCheckin
2016-05-31 09:03hgbotNote Added: 0086849
2016-05-31 09:03hgbotStatusnew => resolved
2016-05-31 09:03hgbotResolutionopen => fixed
2016-05-31 09:03hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/ae61a0b425b3e86746040de0acfaeff9865fad9b [^] => http://code.openbravo.com/erp/devel/pi/rev/447cf1f783343e22917fca9b7117de8ed8c01726 [^]
2016-05-31 10:45hgbotCheckin
2016-05-31 10:45hgbotNote Added: 0086853
2016-06-02 16:41caristuNote Added: 0086947
2016-06-02 16:41caristuStatusresolved => closed
2016-06-02 16:41caristuFixed in Version => 3.0PR16Q3
2016-06-17 19:37hudsonbotCheckin
2016-06-17 19:37hudsonbotNote Added: 0087569
2016-06-17 19:37hudsonbotCheckin
2016-06-17 19:37hudsonbotNote Added: 0087578
2016-06-17 19:38hudsonbotCheckin
2016-06-17 19:38hudsonbotNote Added: 0087579
2018-01-26 10:51ngarciaTag Attached: Fix Stock

Notes
(0086676)
aferraz   
2016-05-23 16:33   
The problem is the pattern used in to_number(text) postgresql function: S99999999999999D999999

If you run the following query it will return 123456789.012345:
select to_number('123456789.0123456789') from dual
(0086745)
hgbot   
2016-05-25 14:48   
Repository: erp/devel/pi
Changeset: ae61a0b425b3e86746040de0acfaeff9865fad9b
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed May 25 12:42:09 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/ae61a0b425b3e86746040de0acfaeff9865fad9b [^]

fixed bug 32951: in pg to_number truncates decimal to 6 positions

  To_Number function in PostgreSQL (created to emulate the same from Oracle),
  truncates the number to 6 decimal positions. Internally the conversion text ->
  numeric is implemetned with a text mask with 6 decimal positions.

  This is fixed by not using pg's to_number with mask but to directly cast, in
  this way precission is not lost. Tested also to properly work with scientific
  notation.

---
M src-db/database/model/prescript-PostgreSql.sql
---
(0086746)
alostale   
2016-05-25 14:51   
Tested in PG 9.1 -> 9.5. For numbers with:
* many decimal positions (as reported in issue)
* many integer positions (it returned null before)
* scientific notation

In all cases working fine.

Results compared with Oracle are the same.
(0086771)
caristu   
2016-05-26 18:57   
(edited on: 2016-05-26 19:10)
Reopened to consider this case: the DatabaseDecimalNumberCheck validation verifies whether the decimal numbers are retrieved properly from the database: this ensures that OB works with the correct locale[1].

With this change in TO_NUMBER(), the validation stops detecting the "wrong" locale.

Steps to reproduce this problem:

1) In postgresql.conf file set lc_numeric = 'locale es_ES' (bad configuration)
2) Execute select to_number('1.2') (without having the fix) --> 12 we detect the bad configuration.
3) Execute select to_number('1.2') (having the fix) --> 1.2, we are NOT detecting the bad configuration.

A possible solution could be to change the validation and use TO_NUMBER with a fixed mask: TO_NUMBER('1.2', '9D9') as we are always checking the same value (1.2)


[1] http://wiki.openbravo.com/wiki/Installation/Custom/PostgreSQL_Database [^]
(0086849)
hgbot   
2016-05-31 09:03   
Repository: erp/devel/pi
Changeset: 447cf1f783343e22917fca9b7117de8ed8c01726
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue May 31 09:03:00 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/447cf1f783343e22917fca9b7117de8ed8c01726 [^]

fixed bug 32951: in pg to_number truncates decimal to 6 positionsi

  To_Number function now does not use masks to do the conversion, so now it
  considers always dot as decimal separator regardless DB's locale.

  In any case build validation should prevent locales with other decimal separator
  than dot, so mask is used to do the validation now.

---
M src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DatabaseDecimalNumberCheckData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck_data.xsql
---
(0086853)
hgbot   
2016-05-31 10:45   
Repository: erp/devel/pi
Changeset: f62e834ae3c26f01c3c95e647af26fcbfa1542ab
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue May 31 10:44:19 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/f62e834ae3c26f01c3c95e647af26fcbfa1542ab [^]

related to issue 32951: fixed build validation for Oracle

  Used mask as special case for PG is not valid in Oracle, using standard
  to_number when in Oracle.

---
M src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck.class
M src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DatabaseDecimalNumberCheckData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck.java
M src-util/buildvalidation/src/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck_data.xsql
---
(0086947)
caristu   
2016-06-02 16:41   
Code review + testing OK.

Verified in pi@c265570e1b9d
(0087569)
hudsonbot   
2016-06-17 19:37   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/0dc7be081b1c [^]
Maturity status: Test
(0087578)
hudsonbot   
2016-06-17 19:37   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/0dc7be081b1c [^]
Maturity status: Test
(0087579)
hudsonbot   
2016-06-17 19:38   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/0dc7be081b1c [^]
Maturity status: Test