Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0034488Openbravo ERPY. DBSourceManagerpublic2016-11-14 12:212023-12-06 08:24
Reportervmromanos 
Assigned Tocaristu 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q1 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0034488: Unsolvable dbconsistency problems in index functions
DescriptionI have a custom function developed in the prescript* files (not sure if this really matters).
This function, which has 2 parameters, is used as part of a function index.

The export database process always modifies the way the function parameters in the index definition are written: it either adds or removes a blank space.

Example I (In Oracle):

Index definition in XML before exporting database (note the blank space between the parameters (GENERATED_CREDIT, USED_CREDIT):
<index name="FIN_PAYMENT_BP_CREDIT_IX" unique="false">
  <index-column name="C_BPARTNER_ID"/>
  <index-column name="functionBasedColumn" functionExpression="OBEQUALS(GENERATED_CREDIT, USED_CREDIT)"/>
</index>

Index definition in XML after exporting database (note the blank space has been removed):
<index name="FIN_PAYMENT_BP_CREDIT_IX" unique="false">
  <index-column name="C_BPARTNER_ID"/>
  <index-column name="functionBasedColumn" functionExpression="OBEQUALS(GENERATED_CREDIT,USED_CREDIT)"/>
</index>

Note that the previous example is not valid as the mentioned blank space should not be present on the XML file

Example II (In PostgresSQL):

Index definition in XML before exporting database (note there is NO blank space between the parameters (GENERATED_CREDIT,USED_CREDIT):
<index name="FIN_PAYMENT_BP_CREDIT_IX" unique="false">
  <index-column name="C_BPARTNER_ID"/>
  <index-column name="functionBasedColumn" functionExpression="OBEQUALS(GENERATED_CREDIT,USED_CREDIT)"/>
</index>

Index definition in XML after exporting database (note the blank space between parameters is added):
<index name="FIN_PAYMENT_BP_CREDIT_IX" unique="false">
  <index-column name="C_BPARTNER_ID"/>
  <index-column name="functionBasedColumn" functionExpression="OBEQUALS(GENERATED_CREDIT, USED_CREDIT)"/>
</index>

Steps To ReproduceIn PostgreSQL instance
Apply patch attached to 0033581 (without space between parameters)
Set core in development.
Export database. See the diff in FIN_PAYMENT.xml file (it adds a space in parameters)
Run smarbuild -Dlocal=no
Export database again. See the FIN_PAYMENT.xml file (the space between parameters have been removed)


Tested in try:
https://code.openbravo.com/erp/devel/try/graph/7cec3fd4e1346b7f05d55fdf0eafc7e72f8e25df [^]
https://ci.openbravo.com//job/try-dbcons-pgsql/4691/ [^]

https://code.openbravo.com/erp/devel/try/graph/f840a525ce1c8fc45680e7028788c0b7bfde6618 [^]
https://ci.openbravo.com//job/try-dbcons-oracle/4696/ [^]
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
has duplicate defect 0034209 closed platform DB consistency error with function index with 2 or more parameters separated by blank space or not 
blocks defect 00335813.0PR17Q2 closed vmromanos slow defaults retrieval in add payment 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-11-14 12:21vmromanosNew Issue
2016-11-14 12:21vmromanosAssigned To => platform
2016-11-14 12:21vmromanosModules => Core
2016-11-14 12:21vmromanosTriggers an Emergency Pack => No
2016-11-14 12:21vmromanosRelationship addedblocks 0033581
2016-11-15 18:27caristuAssigned Toplatform => caristu
2016-11-16 11:05hgbotCheckin
2016-11-16 11:05hgbotNote Added: 0091598
2016-11-16 16:04caristuSummaryUnsolvable dbconsistency problems in index functions (Oracle only) => Unsolvable dbconsistency problems in index functions
2016-11-16 16:04caristuDescription Updatedbug_revision_view_page.php?rev_id=13780#r13780
2016-11-16 16:04caristuSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=13782#r13782
2016-11-16 16:04caristuDescription Updatedbug_revision_view_page.php?rev_id=13783#r13783
2016-11-16 16:21hgbotCheckin
2016-11-16 16:21hgbotNote Added: 0091604
2016-11-16 17:10hgbotCheckin
2016-11-16 17:10hgbotNote Added: 0091607
2016-11-16 17:10hgbotStatusnew => resolved
2016-11-16 17:10hgbotResolutionopen => fixed
2016-11-16 17:10hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a3c30b683b0a40e2ca67f12dd68765175b259f10 [^]
2016-11-16 17:10caristuReview Assigned To => alostale
2016-11-22 09:52inigosanchezRelationship addedhas duplicate 0034209
2016-11-25 09:53alostaleRelationship addedrelated to 0030179
2016-11-25 09:55alostaleNote Added: 0091816
2016-11-25 09:55alostaleStatusresolved => closed
2016-11-25 09:55alostaleFixed in Version => 3.0PR17Q1
2016-12-16 18:38hudsonbotCheckin
2016-12-16 18:38hudsonbotNote Added: 0092637
2023-12-06 08:24dnesemrNote Added: 0157980
2023-12-06 13:23priyamNote View State: private: 157980
2023-12-06 13:24priyamNote Deleted: 0157980

Notes
(0091598)
hgbot   
2016-11-16 11:05   
Repository: erp/devel/dbsm-main
Changeset: 2d1eea31f8c2bbeaa4d6c0fbc0f8554d936b6228
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Wed Nov 16 11:05:14 2016 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/2d1eea31f8c2bbeaa4d6c0fbc0f8554d936b6228 [^]

related to issue 34488: added new test case

---
M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java
A src-test/model/indexes/FUNCTION_INDEX_TWO_ARGUMENTS.xml
---
(0091604)
hgbot   
2016-11-16 16:21   
Repository: erp/devel/dbsm-main
Changeset: 1eb928c7f69fb95fc1c1e7df9e8bf7f880a2bf04
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Wed Nov 16 16:20:15 2016 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/1eb928c7f69fb95fc1c1e7df9e8bf7f880a2bf04 [^]

related to issue 34488: Unsolvable dbconsistency problems in index functions

The problem was reproducible in PostgresSQL, because the expression of function based indexes is stored in database separating the arguments with a comma followed by a blank space. When exporting this kind of expressions into XML, changes were being detected because those blank spaces were not being deleted when the expression contained a function using uncasted arguments like for example: COALESCE(COL1, COL2).

To solve the problem now the blank spaces are trimmed in this case also. In Oracle, this problem can not be reproduced because this kind of expressions are stored without blank spaces after the commas that separate the function arguments.

Together with this, the regular expressions used to remove casting expressions and extra white spaces have been improved in order to consider all alphanumeric characters.

---
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
(0091607)
hgbot   
2016-11-16 17:10   
Repository: erp/devel/pi
Changeset: a3c30b683b0a40e2ca67f12dd68765175b259f10
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Wed Nov 16 16:55:16 2016 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a3c30b683b0a40e2ca67f12dd68765175b259f10 [^]

fixes issue 34488: Unsolvable dbconsistency problems in index functions

---
M src-db/database/lib/dbsourcemanager.jar
---
(0091816)
alostale   
2016-11-25 09:55   
code reviewed

tested in Oracle and PostgreSQL, in both cases index is consistently exported without blank spaces separating parameters.
(0092637)
hudsonbot   
2016-12-16 18: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/dc8bf00badd0 [^]
Maturity status: Test