Project:
View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
ID | ||||||||
0030069 | ||||||||
Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
defect | [Openbravo ERP] A. Platform | critical | always | 2015-06-02 09:29 | 2015-06-10 15:38 | |||
Reporter | vmromanos | View Status | public | |||||
Assigned To | caristu | |||||||
Priority | immediate | Resolution | fixed | Fixed in Version | 3.0PR15Q3 | |||
Status | closed | Fix in branch | Fixed in SCM revision | 780731085f0c | ||||
Projection | none | ETA | none | Target Version | ||||
OS | Any | Database | PostgreSQL | Java version | ||||
OS Version | Database version | 9.3 | Ant version | |||||
Product Version | SCM revision | |||||||
Review Assigned To | alostale | |||||||
Web browser | ||||||||
Modules | Core | |||||||
Regression level | ||||||||
Regression date | ||||||||
Regression introduced in release | ||||||||
Regression introduced by commit | ||||||||
Triggers an Emergency Pack | No | |||||||
Summary | 0030069: Critical bug with decimals only in PostgreSQL 9.3 | |||||||
Description | It 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 Reproduce | Login 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 | |||||||
Tags | No tags attached. | |||||||
Attached Files | Format.xml [^] (3,243 bytes) 2015-06-02 09:30 postgresql.conf [^] (20,710 bytes) 2015-06-02 09:30 postgresql_91vs93_conf.diff [^] (5,986 bytes) 2015-06-02 11:38 [Show Content] | |||||||
Relationships [ Relation Graph ] [ Dependency Graph ] | ||||||||||||||||||||||
|
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 |