Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035474Openbravo ERPA. Platformpublic2017-03-09 18:352017-03-17 09:54
Reporteraaroncalero 
Assigned Toalostale 
PriorityurgentSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q2 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035474: Function based indexes do not support arithmetic operations on columns
DescriptionCurrently the dbsourcemanager supports exporting function based indexes.
This works fine when the indexed part is just a call to a function (e.g. indexes defined here[1]).
However if the index is compound of an arithmetic operation, this requires an extra set of parenthesis when the index is defined, but then at the time of exporting this index, the parentheses are removed.


[1] https://code.openbravo.com/erp/pmods/org.openbravo.retail.highvolumesconfig/file/tip/src-db/database/model/modifiedTables/C_BPARTNER.xml [^]
Steps To ReproduceCreate the following index:
create index EM_OBPOS_ORDLINE_QTYPENDING on c_orderline ((QTYORDERED-QTYDELIVERED))

Try exporting the index. The xml will show the following:
+ <index name="EM_OBPOS_ORDLINE_QTYPENDING" unique="false">
+ <index-column name="functionBasedColumn" functionExpression="QTYORDERED-QTYDELIVERED"/>
+ </index>
(notify that the parentheses are missing).

After executing ant update.database, the following warning will be raised:
SQL Command failed with: ERROR: syntax error at or near "-"
  Position: 78
-- END
CREATE INDEX EM_OBPOS_ORDLINE_QTYPENDING ON C_ORDERLINE (QTYORDERED-QTYDELIVERED)
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
related to design defect 0029492 closed alostale Wrong export of a view 
related to design defect 0035519 closed alostale can only have one function column in db indexes 
related to defect 0035543 closed alostale incorrect export of indexes including CASE expressions 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-03-09 18:35aaroncaleroNew Issue
2017-03-09 18:35aaroncaleroAssigned To => platform
2017-03-09 18:35aaroncaleroModules => Core
2017-03-09 18:35aaroncaleroTriggers an Emergency Pack => No
2017-03-10 13:47alostaleRelationship addedrelated to 0035467
2017-03-10 13:48alostaleRelationship addedrelated to 0030179
2017-03-10 13:49alostaleRelationship addedrelated to 0029492
2017-03-10 13:50alostaleRelationship deletedrelated to 0035467
2017-03-15 10:53alostalePrioritynormal => urgent
2017-03-15 10:53alostaleStatusnew => acknowledged
2017-03-15 10:53alostaleTypefeature request => defect
2017-03-15 10:53alostaleAssigned Toplatform => alostale
2017-03-15 10:54alostaleReview Assigned To => caristu
2017-03-15 14:08hgbotCheckin
2017-03-15 14:08hgbotNote Added: 0095034
2017-03-15 14:08hgbotStatusacknowledged => resolved
2017-03-15 14:08hgbotResolutionopen => fixed
2017-03-15 14:08hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7 [^]
2017-03-15 14:09hgbotCheckin
2017-03-15 14:09hgbotNote Added: 0095036
2017-03-15 14:09hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/2686a1fd45b975ea70ca9eb413a38775bff31190 [^]
2017-03-15 20:22hudsonbotCheckin
2017-03-15 20:22hudsonbotNote Added: 0095302
2017-03-17 09:54caristuNote Added: 0095355
2017-03-17 09:54caristuStatusresolved => closed
2017-03-17 09:54caristuFixed in Version => 3.0PR17Q2
2017-03-17 09:59caristuRelationship addedrelated to 0035519
2017-03-17 10:02caristuRelationship addedrelated to 0035543

Notes
(0095034)
hgbot   
2017-03-15 14:08   
Repository: erp/devel/pi
Changeset: a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Mar 15 14:08:12 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a62a7a94d7eefa6c84ef3fb5eb2748c5172171e7 [^]

fixed bug 35474, fixed issue 35519: function based indexes improvements

  Improved the way function definitions in indexes are read from DB, now they are
  read per column, using in PG the index specific function pg_get_indexdef. This
  allows to:

   * properly read indexes based on arithmethic expressions ie (col1-col2)
   * support more than one index column based on function

---
M src-db/database/lib/dbsourcemanager.jar
---
(0095036)
hgbot   
2017-03-15 14:09   
Repository: erp/devel/dbsm-main
Changeset: 2686a1fd45b975ea70ca9eb413a38775bff31190
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Mar 15 13:35:00 2017 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/2686a1fd45b975ea70ca9eb413a38775bff31190 [^]

fixed bug 35474, fixed issue 35519: function based indexes improvements

  Improved the way function definitions in indexes are read from DB, now they are
  read per column, using in PG the index specific function pg_get_indexdef. This
  allows to:

   * properly read indexes based on arithmethic expressions ie (col1-col2)
   * support more than one index column based on function

---
M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
A src-test/model/indexes/FUNCTION_INDEX_MULTI_COL.xml
A src-test/model/indexes/FUNCTION_INDEX_OPERATOR.xml
---
(0095302)
hudsonbot   
2017-03-15 20:22   
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/54e102bef53e [^]
Maturity status: Test
(0095355)
caristu   
2017-03-17 09:54   
Code reviewed + testing OK.