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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0034488
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] Y. DBSourceManagermajoralways2016-11-14 12:212023-12-06 08:24
ReportervmromanosView Statuspublic 
Assigned Tocaristu 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q1
StatusclosedFix in branchFixed in SCM revisiona3c30b683b0a
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt 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

0034488: 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/ [^]
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to feature request 00301793.0PR15Q4 closedAugustoMauch DBSourceManager support indexes with functions 
has duplicate defect 0034209 closedplatform DB consistency error with function index with 2 or more parameters separated by blank space or not 
blocks defect 00335813.0PR17Q2 closedvmromanos slow defaults retrieval in add payment 

-  Notes
(0091598)
hgbot (developer)
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 (developer)
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 (developer)
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 (manager)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2016-11-14 12:21 vmromanos New Issue
2016-11-14 12:21 vmromanos Assigned To => platform
2016-11-14 12:21 vmromanos Modules => Core
2016-11-14 12:21 vmromanos Triggers an Emergency Pack => No
2016-11-14 12:21 vmromanos Relationship added blocks 0033581
2016-11-15 18:27 caristu Assigned To platform => caristu
2016-11-16 11:05 hgbot Checkin
2016-11-16 11:05 hgbot Note Added: 0091598
2016-11-16 16:04 caristu Summary Unsolvable dbconsistency problems in index functions (Oracle only) => Unsolvable dbconsistency problems in index functions
2016-11-16 16:04 caristu Description Updated View Revisions
2016-11-16 16:04 caristu Steps to Reproduce Updated View Revisions
2016-11-16 16:04 caristu Description Updated View Revisions
2016-11-16 16:21 hgbot Checkin
2016-11-16 16:21 hgbot Note Added: 0091604
2016-11-16 17:10 hgbot Checkin
2016-11-16 17:10 hgbot Note Added: 0091607
2016-11-16 17:10 hgbot Status new => resolved
2016-11-16 17:10 hgbot Resolution open => fixed
2016-11-16 17:10 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a3c30b683b0a40e2ca67f12dd68765175b259f10 [^]
2016-11-16 17:10 caristu Review Assigned To => alostale
2016-11-22 09:52 inigosanchez Relationship added has duplicate 0034209
2016-11-25 09:53 alostale Relationship added related to 0030179
2016-11-25 09:55 alostale Note Added: 0091816
2016-11-25 09:55 alostale Status resolved => closed
2016-11-25 09:55 alostale Fixed in Version => 3.0PR17Q1
2016-12-16 18:38 hudsonbot Checkin
2016-12-16 18:38 hudsonbot Note Added: 0092637
2023-12-06 08:24 dnesemr Note Added: 0157980
2023-12-06 13:23 priyam Note View State: private: 157980
2023-12-06 13:24 priyam Note Deleted: 0157980

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker