Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033997Openbravo ERPY. DBSourceManagerpublic2016-09-15 01:112016-12-16 18:39
Reporterchelipf 
Assigned Tocaristu 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS30OS VersionUbuntu 16.04
Product Version3.0PR16Q3.1 
Target VersionFixed in Version3.0PR17Q1 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browserMozilla Firefox
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033997: dbsourcemanager export double precision as precision and impossible to cast in views
DescriptionCASE WHEN date_part('day', to_date(po.date_order)) > to_number(org.em_rh_day) THEN

... anything ...

ELSE

... anything ...

END

1) The problem is that date_part (the same occurs for extract function) returns 'double precision' then postgresql cast (to_number(org.em_rh_day)) as "double precision" (to_number(org.em_rh_day)::double precision) but it is exported like this


CASE WHEN date_part('day', to_date(po.date_order)) > to_number(org.em_rh_day) precision THEN
...
...
Note: take attention to the literal "precion" next to to_number(org.em_rh_day)

It is only set as precision, that in consecuense produces an error when updating database.


2)If I cast the extract or date_part by hand like this

cast(extract(DAY FROM to_date(po.fecha_papeleta)) AS NUMERIC)

That works, but when exported it gets ignored, that means when update.database is exected again it recreates de db without the cast and in consecuense in the next export.database it gets double precision again but like in point 1
Steps To ReproduceCreate a view with this code:

CASE WHEN date_part('day', to_date(po.date_order)) > to_number(org.em_rh_day) THEN

... anything ...

ELSE

... anything ...

END AS name_of_column

After that, export.database and update.database twice.
Proposed Solutionexport.database must cast double precision as TO_NUMBER (numeric in postgresql) or fix the only precision literal when exporting
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-09-15 01:11chelipfNew Issue
2016-09-15 01:11chelipfAssigned To => platform
2016-09-15 01:11chelipfWeb browser => Mozilla Firefox
2016-09-15 01:11chelipfModules => Core
2016-09-15 01:11chelipfTriggers an Emergency Pack => No
2016-09-15 01:55chelipfNote Added: 0089962
2016-09-29 09:16alostaleStatusnew => acknowledged
2016-12-01 13:25alostalePriorityhigh => urgent
2016-12-01 14:12alostaleAssigned Toplatform => caristu
2016-12-02 15:22hgbotCheckin
2016-12-02 15:22hgbotNote Added: 0092042
2016-12-02 15:22hgbotStatusacknowledged => resolved
2016-12-02 15:22hgbotResolutionopen => fixed
2016-12-02 15:22hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/7267d3ff1a33ab4631f70cec9da0f2e432d8a707 [^]
2016-12-02 15:33hgbotCheckin
2016-12-02 15:33hgbotNote Added: 0092043
2016-12-02 15:34caristuReview Assigned To => alostale
2016-12-02 15:34caristuWeb browserMozilla Firefox => Mozilla Firefox
2016-12-13 11:53alostaleNote Added: 0092310
2016-12-13 11:53alostaleStatusresolved => closed
2016-12-13 11:53alostaleFixed in Version => 3.0PR17Q1
2016-12-16 18:39hudsonbotCheckin
2016-12-16 18:39hudsonbotNote Added: 0092687

Notes
(0089962)
chelipf   
2016-09-15 01:55   
Tip: to_number(date_part(...) || '') in order to cast to numeric. It is the only solution that has worked to me.
(0092042)
hgbot   
2016-12-02 15:22   
Repository: erp/devel/dbsm-main
Changeset: 7267d3ff1a33ab4631f70cec9da0f2e432d8a707
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Fri Dec 02 15:21:39 2016 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/7267d3ff1a33ab4631f70cec9da0f2e432d8a707 [^]

fixes issue 33997: DBSM not removing double precision cast properly

DBSM was not removing the double precision cast when exporting PostgreSQL model elements like views. The PostgreSQLStandarization is the class in charge of standarizing the PostgreSQL code which contains castings. But until now it was not considering the double precision numeric type.

Together with the fix a new suite intended for testing the views has been created as part of the DBSM test suite.

---
M src-test/src/org/openbravo/dbsm/test/model/ModelSuite.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSQLStandarization.java
A src-test/model/views/BASE_MODEL.xml
A src-test/model/views/DOUBLE_PRECISION_CAST_TEST_MODEL.xml
A src-test/model/views/TEXT_CAST_TEST_MODEL.xml
A src-test/src/org/openbravo/dbsm/test/model/Views.java
---
(0092043)
hgbot   
2016-12-02 15:33   
Repository: erp/devel/pi
Changeset: 2218f18f1eaa3febdb75f5f7c8bb6466743f8258
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Fri Dec 02 15:32:28 2016 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/2218f18f1eaa3febdb75f5f7c8bb6466743f8258 [^]

related to issue 33997: updated dbsourcemanager library

---
M src-db/database/lib/dbsourcemanager.jar
---
(0092310)
alostale   
2016-12-13 11:53   
code reviewed and tested
(0092687)
hudsonbot   
2016-12-16 18:39   
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/dc8bf00badd0 [^]
Maturity status: Test