Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0032951
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 04. Warehouse managementmajoralways2016-05-13 11:222018-01-26 10:51
ReporterngarciaView Statuspublic 
Assigned Toalostale 
PriorityurgentResolutionfixedFixed in Version3.0PR16Q3
StatusclosedFix in branchFixed in SCM revision447cf1f78334
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tocaristu
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0032951: 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
TagsFix Stock
Attached Filesdiff file icon FixStockCast.diff [^] (1,206 bytes) 2016-05-13 11:22 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0032943 closedmarkmm82 'Fix Stock QuantityOrder Process' should round the Order quantity of the opening physical inventory to the std precision 
related to defect 0032935 closedmarkmm82 Data is pending to be created in M_STORAGE_PENDING by the Fix Stock process 
related to defect 00150082.50MP24 closedadrianromero Wrong behaviour with 9 Decimals 
related to defect 0014769 closedAugustoMauch prescript-PostgreSql.sql: to_number(text) function returning wrong values for inputs in scientific notation 
related to defect 0030069 closedcaristu Critical bug with decimals only in PostgreSQL 9.3 
depends on defect 0028459 closededuardo_Argal It is necessary a modulescript to solve stock inconsistencies 
related to defect 0032934 closedmarkmm82 Wrong definition of convertUOM function in RestoreStockValue_data.xsql 

-  Notes
(0086676)
aferraz (manager)
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 (developer)
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 (manager)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
2016-06-02 16:41

 Code review + testing OK.

Verified in pi@c265570e1b9d
(0087569)
hudsonbot (developer)
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 (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2016-05-13 11:22 ngarcia New Issue
2016-05-13 11:22 ngarcia Assigned To => Triage Finance
2016-05-13 11:22 ngarcia File Added: FixStockCast.diff
2016-05-13 11:22 ngarcia Modules => Core
2016-05-13 11:22 ngarcia Resolution time => 1466200800
2016-05-13 11:22 ngarcia Triggers an Emergency Pack => No
2016-05-13 11:22 ngarcia Relationship added depends on 0028459
2016-05-13 11:23 ngarcia Issue Monitored: networkb
2016-05-16 13:25 aferraz Assigned To Triage Finance => markmm82
2016-05-16 13:26 aferraz Relationship added related to 0032943
2016-05-16 13:26 aferraz Relationship added related to 0032935
2016-05-16 17:39 aferraz Relationship added related to 0032934
2016-05-17 13:38 ngarcia Description Updated View Revisions
2016-05-17 16:58 markmm82 Status new => scheduled
2016-05-23 14:49 aferraz Steps to Reproduce Updated View Revisions
2016-05-23 16:33 aferraz Note Added: 0086676
2016-05-23 16:33 aferraz Assigned To markmm82 => platform
2016-05-23 16:33 aferraz Status scheduled => feedback
2016-05-23 16:33 aferraz Status feedback => new
2016-05-25 11:03 alostale Relationship added related to 0015008
2016-05-25 11:05 alostale Relationship added related to 0014769
2016-05-25 12:33 alostale Review Assigned To => caristu
2016-05-25 12:43 alostale Status new => scheduled
2016-05-25 12:43 alostale Assigned To platform => alostale
2016-05-25 14:48 hgbot Checkin
2016-05-25 14:48 hgbot Note Added: 0086745
2016-05-25 14:48 hgbot Status scheduled => resolved
2016-05-25 14:48 hgbot Resolution open => fixed
2016-05-25 14:48 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/ae61a0b425b3e86746040de0acfaeff9865fad9b [^]
2016-05-25 14:51 alostale Note Added: 0086746
2016-05-26 18:57 caristu Note Added: 0086771
2016-05-26 18:57 caristu Status resolved => new
2016-05-26 18:57 caristu Resolution fixed => open
2016-05-26 18:57 caristu Note Edited: 0086771 View Revisions
2016-05-26 18:58 caristu Note Edited: 0086771 View Revisions
2016-05-26 19:10 caristu Note Edited: 0086771 View Revisions
2016-05-27 08:13 caristu Relationship added related to 0030069
2016-05-31 09:03 hgbot Checkin
2016-05-31 09:03 hgbot Note Added: 0086849
2016-05-31 09:03 hgbot Status new => resolved
2016-05-31 09:03 hgbot Resolution open => fixed
2016-05-31 09:03 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/ae61a0b425b3e86746040de0acfaeff9865fad9b [^] => http://code.openbravo.com/erp/devel/pi/rev/447cf1f783343e22917fca9b7117de8ed8c01726 [^]
2016-05-31 10:45 hgbot Checkin
2016-05-31 10:45 hgbot Note Added: 0086853
2016-06-02 16:41 caristu Note Added: 0086947
2016-06-02 16:41 caristu Status resolved => closed
2016-06-02 16:41 caristu Fixed in Version => 3.0PR16Q3
2016-06-17 19:37 hudsonbot Checkin
2016-06-17 19:37 hudsonbot Note Added: 0087569
2016-06-17 19:37 hudsonbot Checkin
2016-06-17 19:37 hudsonbot Note Added: 0087578
2016-06-17 19:38 hudsonbot Checkin
2016-06-17 19:38 hudsonbot Note Added: 0087579
2018-01-26 10:51 ngarcia Tag Attached: Fix Stock

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker