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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0030069
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformcriticalalways2015-06-02 09:292015-06-10 15:38
ReportervmromanosView Statuspublic 
Assigned Tocaristu 
PriorityimmediateResolutionfixedFixed in Version3.0PR15Q3
StatusclosedFix in branchFixed in SCM revision780731085f0c
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase version9.3Ant version
Product VersionSCM revision 
Review Assigned Toalostale
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0030069: Critical bug with decimals only in PostgreSQL 9.3

DescriptionIt seems that the way decimal numbers are managed by PostgreSQL 9.3 is different from PostgreSQL 9.1, and may create important issues in several flows.

The same query [select to_number('1.53')] returns different results depending on the PG version:

PG 9.1: 1.53
PG 9.3: 153
Steps To ReproduceLogin as Openbravo admin

Go to Sales Order and create a new record:
  Org: España Norte
  BP: Alimentos y Supermercados
  Invoice Terms: Immediate
Go to Lines and create a new record:
  Quantity: 10
  Product: Agua sin gas
 Verify unit price = 1.53
Book the sales order

Go to Sales Invoice and create a new record:
  Org: España Norte
  BP: Alimentos y SuperMercados
Press Create Lines From:
  In the sales order select the previously created
  In the grid select the 2 order lines
  Press OK
Go to lines and check that:
  the unit price is 153, when it should be 1.53
  the line net amount is 1530, when it should be 15.30
TagsNo tags attached.
Attached Filesxml file icon Format.xml [^] (3,243 bytes) 2015-06-02 09:30
? file icon postgresql.conf [^] (20,710 bytes) 2015-06-02 09:30
diff file icon postgresql_91vs93_conf.diff [^] (5,986 bytes) 2015-06-02 11:38 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]
depends on backport 00301133.0PR15Q2.2 closedcaristu Critical bug with decimals only in PostgreSQL 9.3 
depends on backport 00301143.0PR15Q1.5 closedcaristu Critical bug with decimals only in PostgreSQL 9.3 
related to defect 0032951 closedalostale Fix Stock process rounds the quantities to 6 decimals and it should not 

-  Notes
(0077969)
vmromanos (manager)
2015-06-02 09:32

Attached for your reference the Format.xml and postgresql.conf. Note that the same configuration works fine in PG 9.1.

Assigned initial to platform, but feel free to forward to Finance team if necessary
(0077972)
shuehner (administrator)
2015-06-02 10:41

Hi,
i just tested it cannot reproduce that. I get the expected 1.53 in both 9.1 & 9.3

But that sounds very similar to another config problem Eduardo reported just a few days ago.

Please compare all 'lc_*' lines in postgresql.conf in the bost systems where you see the difference.
(0077974)
vmromanos (manager)
2015-06-02 11:37

@shuehner: lc_* are exactly the same in both 9.1 and 9.3.
I have attached the diff of postgresql.conf file between the 2 versions.
In general they are quite similar.

The problem might be here:
< #timezone = '(defaults to server environment setting)'
---
> #timezone = 'localtime'

As you can see timezone is commented in both files, but maybe the way PG gets the default value might be different creating the problem
(0077980)
shuehner (administrator)
2015-06-02 13:28

timezone has nothing to do with number formatting at all so highly unlikely.

But as said alo has a idea already and is looking into it.

If you want to test his idea, change lc_monetary to en_US.UTF-8
and maybe your issue will go away.
(0077987)
alostale (manager)
2015-06-02 15:11

This is caused by lc_numeric setting together with a behavior change introduced in PostgreSQL 9.3:

[1] Fix to_number() to properly handle a period used as a thousands separator (Tom Lane)

Previously, a period was considered to be a decimal point even when the locale says it isn't and the D format code is used to specify use of the locale-specific decimal point. This resulted in wrong answers if FM format was also used.

This causes:

======
PG 9.1
======

to_number result is independent from locale

pi91=# set lc_numeric='es_ES.UTF-8';
SET
pi91=# show lc_numeric;
 lc_numeric
-------------
 es_ES.UTF-8
(1 row)

pi91=# select to_number('1.2');
 to_number
-----------
       1.2
(1 row)

pi91=# set lc_numeric='en_US.UTF-8';
SET
pi=# select to_number('1.2');
 to_number
-----------
       1.2
(1 row)


======
PG 9.3
======

to_number result DEPENDS on locale

pi=# set lc_numeric='es_ES.UTF-8';
SET
pi=# show lc_numeric;
 lc_numeric
-------------
 es_ES.UTF-8
(1 row)

pi=# select to_number('1.2');
 to_number
-----------
        12
(1 row)

pi=# select to_number('1,2');
 to_number
-----------
       1.2
(1 row)

pi=# set lc_numeric='en_US.UTF-8';
SET
pi=# select to_number('1.2');
 to_number
-----------
       1.2
(1 row)


==========
Workaround
==========

Set lc_numeric = 'en_US.UTF-8' in postgreqsl.conf and reload server.


---
[1] http://www.postgresql.org/docs/9.3/static/release-9-3.html [^]
(0078099)
vmromanos (manager)
2015-06-04 18:12

More info:

1. Tried changing lc_monetary = en_US.UTF-8 (or en_US) gave the following error when restarting database:

[....] Restarting PostgreSQL 9.3 database server: main[....] The PostgreSQL server failed to start. Please check the log output: 2015-06-04 17:51:00 CEST [27343-1] LOG: 22023: valor no válido para el parámetro «lc_monetary»: «en_US.UTF-8» 2015-06-04 17:51:00 CEST [27343-2] UBICACIÓN: call_string_check_hook, guc.c:8332 2015-06-04 17:51:01 CEST [27343-3] FATAL: F0000: el archivo de configuración «/etc/postgresql/9.3/main/postgresql.conf» contiene errores 2015-06-04 17:51:[FAILST [27343-4] UBICACIÓN: ProcessConfigFile, guc-file.l:352 ... failed!
 failed!

2. If I delete the following lines in the postgresql.conf, the issue is not reproducible:

lc_messages = 'es_ES.UTF-8' # locale for system error message
                                        # strings
lc_monetary = 'es_ES.UTF-8' # locale for monetary formatting
lc_numeric = 'es_ES.UTF-8' # locale for number formatting
lc_time = 'es_ES.UTF-8' # locale for time formatting
(0078122)
caristu (developer)
2015-06-05 15:54

Fix pushed to try
(0078163)
hgbot (developer)
2015-06-09 18:03

Repository: erp/devel/pi
Changeset: 780731085f0c277f8c3f6778a328c38fc5dca667
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Tue Jun 09 18:01:36 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/780731085f0c277f8c3f6778a328c38fc5dca667 [^]

Fixes issue 30069: Critical bug with decimals only in PostgreSQL 9.3

A new build validation has been added. This way, when executing update.database the result returned by the to_number() procedure is evaluated.
In case it does not return the expected result, the task will fail, showing a message with the link to the documentation

---
A src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck.class
A src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DatabaseDecimalNumberCheckData.class
A src-util/buildvalidation/src/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck.java
A src-util/buildvalidation/src/org/openbravo/buildvalidation/DatabaseDecimalNumberCheck_data.xsql
---
(0078164)
caristu (developer)
2015-06-09 18:09

The documentation has been updated to inform about the behavior change introduced in PostgreSQL 9.3, along with how to configure the lc_numeric setting: http://wiki.openbravo.com/wiki/Installation/Custom/PostgreSQL_Database [^]
(0078177)
hudsonbot (developer)
2015-06-10 15:15

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/ec9a491fe07d [^]
Maturity status: Test
(0078181)
alostale (manager)
2015-06-10 15:38

code reviewed

tested in pg9.3 having es_ES as lc_numeric build validation fails not allowing to continue

- Issue History
Date Modified Username Field Change
2015-06-02 09:29 vmromanos New Issue
2015-06-02 09:29 vmromanos Assigned To => platform
2015-06-02 09:29 vmromanos Modules => Core
2015-06-02 09:29 vmromanos Triggers an Emergency Pack => No
2015-06-02 09:30 vmromanos File Added: Format.xml
2015-06-02 09:30 vmromanos File Added: postgresql.conf
2015-06-02 09:32 vmromanos Note Added: 0077969
2015-06-02 09:33 vmromanos Priority normal => immediate
2015-06-02 09:35 vmromanos Steps to Reproduce Updated View Revisions
2015-06-02 10:41 shuehner Note Added: 0077972
2015-06-02 11:37 vmromanos Note Added: 0077974
2015-06-02 11:38 vmromanos File Added: postgresql_91vs93_conf.diff
2015-06-02 13:28 shuehner Note Added: 0077980
2015-06-02 15:12 alostale Note Added: 0077987
2015-06-04 18:12 vmromanos Note Added: 0078099
2015-06-05 11:42 alostale Assigned To platform => caristu
2015-06-05 11:42 alostale Review Assigned To => alostale
2015-06-05 11:43 alostale Status new => scheduled
2015-06-05 15:54 caristu Note Added: 0078122
2015-06-09 18:02 caristu Issue Monitored: alostale
2015-06-09 18:03 hgbot Checkin
2015-06-09 18:03 hgbot Note Added: 0078163
2015-06-09 18:03 hgbot Status scheduled => resolved
2015-06-09 18:03 hgbot Resolution open => fixed
2015-06-09 18:03 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/780731085f0c277f8c3f6778a328c38fc5dca667 [^]
2015-06-09 18:09 caristu Note Added: 0078164
2015-06-10 15:15 hudsonbot Checkin
2015-06-10 15:15 hudsonbot Note Added: 0078177
2015-06-10 15:38 alostale Note Added: 0078181
2015-06-10 15:38 alostale Status resolved => closed
2015-06-10 15:38 alostale Fixed in Version => 3.0PR15Q3
2016-05-27 08:13 caristu Relationship added related to 0032951


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker