Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0014769Openbravo ERPA. Platformpublic2010-10-05 18:042023-07-11 05:13
Reporterchristiancorrodi 
Assigned ToAugustoMauch 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version2.40 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0014769: prescript-PostgreSql.sql: to_number(text) function returning wrong values for inputs in scientific notation
DescriptionGiven a number x as a String. When x >= 1E7 or x <= 1E-4 the value stored to the database is wrong.

This error comes for example from calling Double.toString(). Under Oracle this function returns a value of the type numeric for this case.

1E7 -> 17 instead of 10000000
1E-4 -> 14 instead of .0001
Steps To Reproduce
Proposed SolutionCREATE OR REPLACE FUNCTION to_number
(
text
)
RETURNS NUMERIC AS '
DECLARE
  v_Pos INTEGER;
  v_Mant NUMERIC;
  v_Exp NUMERIC;
  v_Res NUMERIC;
BEGIN
  v_Pos := position(''E'' in upper($1));
  IF v_Pos = 0 THEN
    -- this is the old behaviour
    RETURN to_number($1, ''S99999999999999D999999'');
  ELSE
    v_Mant := substring($1 from 1 for v_Pos - 1); -- Mantissa, implicit cast to data type NUMERIC
    v_Exp := substring($1 from v_Pos + 1); -- Exponent, implicit cast to data type NUMERIC
    v_Res := v_Mant * power(10, v_Exp);
    RETURN v_Res;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
' LANGUAGE 'plpgsql' IMMUTABLE
/-- END
Additional Information
TagsNo tags attached.
Relationships
related to defect 0032951 closed alostale Fix Stock process rounds the quantities to 6 decimals and it should not 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2010-10-05 18:04christiancorrodiNew Issue
2010-10-05 18:04christiancorrodiAssigned To => alostale
2010-10-11 09:05alostalePrioritynormal => urgent
2010-10-11 09:06alostaleStatusnew => scheduled
2011-11-16 16:30alostaleAssigned Toalostale => marvintm
2012-02-13 12:08marvintmAssigned Tomarvintm => AugustoMauch
2012-02-13 16:31hgbotCheckin
2012-02-13 16:31hgbotNote Added: 0045073
2012-02-13 16:31hgbotStatusscheduled => resolved
2012-02-13 16:31hgbotResolutionopen => fixed
2012-02-13 16:31hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/34727a4bec62fdee0e39b43a4b0d79605cd4a6b7 [^]
2012-02-13 16:39AugustoMauchNote Added: 0045075
2012-02-21 17:26marvintmStatusresolved => closed
2012-03-02 15:36hudsonbotCheckin
2012-03-02 15:36hudsonbotNote Added: 0045836
2016-05-25 11:05alostaleRelationship addedrelated to 0032951
2023-07-11 05:13thielmikaylaNote Added: 0152297
2023-07-11 13:48shuehnerNote Deleted: 0152297

Notes
(0045073)
hgbot   
2012-02-13 16:31   
Repository: erp/devel/pi
Changeset: 34727a4bec62fdee0e39b43a4b0d79605cd4a6b7
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Feb 13 16:30:57 2012 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/34727a4bec62fdee0e39b43a4b0d79605cd4a6b7 [^]

Fixes issue 14769: to_text function now supports scientific notation

---
M src-db/database/model/prescript-PostgreSql.sql
---
(0045075)
AugustoMauch   
2012-02-13 16:39   
If the number to be converted is written in scientific notation (e.g. 1e5), the convertion now returns 10000 (prior to the fix, it would have returned 15).

If the number to be converted is not written in scientific notation, the convertion is the same as before, so I think that this is not a risky fix.
(0045836)
hudsonbot   
2012-03-02 15:36   
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/544d64e0c159 [^]

Maturity status: Test