Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0041785Openbravo ERPA. Platformpublic2019-09-09 16:432019-09-27 09:00
Reporteraferraz 
Assigned Toalostale 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR19Q4 
Merge Request Status
Review Assigned ToAugustoMauch
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0041785: Error with function index including a coalesce with a varchar
DescriptionError with function index including a coalesce with a varchar
Steps To ReproduceCreate following index:
CREATE INDEX c_order_test ON public.c_order USING btree (COALESCE(c_order_id, 'X'));

Export database.
Check database is correctly exported and index is exported as follows:
<index name="C_ORDER_TEST" unique="false">
  <index-column name="functionBasedColumn" functionExpression="COALESCE(C_ORDER_ID,'X' varying)"/>
</index>

Remove index from database and run ant update.database -Dlocal=yes in order to create the index from the xml.
Check you get following error:
SQL Command failed with: ERROR: error de sintaxis en o cerca de «varying»
CREATE INDEX C_ORDER_TEST ON C_ORDER ((COALESCE(C_ORDER_ID,'X' varying)))

Realize similar index including a coalesce with a decimal works fine:
CREATE INDEX c_order_test ON public.c_order USING btree (COALESCE(grandtotal, 0));
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
related to defect 0041795 closed platform Oracle only: multi-column index with function based column doesn't properly apply 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2019-09-09 16:43aferrazNew Issue
2019-09-09 16:43aferrazAssigned To => platform
2019-09-09 16:43aferrazModules => Core
2019-09-09 16:43aferrazTriggers an Emergency Pack => No
2019-09-09 16:56aferrazSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=19365#r19365
2019-09-09 17:10aferrazSummaryError with function index including a coalesce => Error with function index including a coalesce with a varchar
2019-09-09 17:10aferrazDescription Updatedbug_revision_view_page.php?rev_id=19367#r19367
2019-09-09 17:10aferrazSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=19368#r19368
2019-09-10 10:30alostaleAssigned Toplatform => alostale
2019-09-10 10:30alostaleRelationship addedrelated to 0030179
2019-09-10 10:32alostaleReview Assigned To => AugustoMauch
2019-09-10 14:07hgbotCheckin
2019-09-10 14:07hgbotNote Added: 0114494
2019-09-10 14:07hgbotStatusnew => resolved
2019-09-10 14:07hgbotResolutionopen => fixed
2019-09-10 14:07hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/6bc6f363024ebdaad0fe307f972440f24e870205 [^]
2019-09-10 14:07hgbotCheckin
2019-09-10 14:07hgbotNote Added: 0114495
2019-09-10 14:07hgbotCheckin
2019-09-10 14:07hgbotNote Added: 0114496
2019-09-10 14:07hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/6bc6f363024ebdaad0fe307f972440f24e870205 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/970f3f969462a63dcf01f1296eaf9a46efe18fcb [^]
2019-09-10 14:07hgbotCheckin
2019-09-10 14:07hgbotNote Added: 0114497
2019-09-10 18:12vmromanosRelationship addedrelated to 0041795
2019-09-10 19:28hudsonbotCheckin
2019-09-10 19:28hudsonbotNote Added: 0114516
2019-09-27 09:00AugustoMauchNote Added: 0114827
2019-09-27 09:00AugustoMauchStatusresolved => closed
2019-09-27 09:00AugustoMauchFixed in Version => 3.0PR19Q4

Notes
(0114494)
hgbot   
2019-09-10 14:07   
Repository: erp/devel/pi
Changeset: 6bc6f363024ebdaad0fe307f972440f24e870205
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Sep 10 13:57:18 2019 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/6bc6f363024ebdaad0fe307f972440f24e870205 [^]

fixed bug 41785: function indexes fail with character varying columns

  When exporting indexes with functions castings added in PostgreSQL catalog are
  removed to keep ORA<->PG compatiliblity. This is done through a regexp, which
  incorrectly assumed cast is always a single word.

  As character varying is the only type with two words, the regexp has been changed
  to take this specific case into account, no more complex potential cases have
  been included as this would add extra complexity for castings followed by other
  expressions (ie. CASE col1 when col2 then 'Y'::text else 'N'::text).

---
M src-db/database/lib/dbsourcemanager.jar
---
(0114495)
hgbot   
2019-09-10 14:07   
Repository: erp/devel/dbsm-main
Changeset: 88e357247291addb12a5bcb9824f17d9d28a20d2
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Sep 10 10:31:14 2019 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/88e357247291addb12a5bcb9824f17d9d28a20d2 [^]

related to issue 41785: added test case

---
M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java
A src-test/model/indexes/FUNCTION_INDEX_COALESCE.xml
---
(0114496)
hgbot   
2019-09-10 14:07   
Repository: erp/devel/dbsm-main
Changeset: 970f3f969462a63dcf01f1296eaf9a46efe18fcb
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Sep 10 10:44:19 2019 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/970f3f969462a63dcf01f1296eaf9a46efe18fcb [^]

fixed bug 41785: function indexes fail with character varying columns

  When exporting indexes with functions castings added in PostgreSQL catalog are
  removed to keep ORA<->PG compatiliblity. This is done through a regexp, which
  incorrectly assumed cast is always a single word.

  As character varying is the only type with two words, the regexp has been changed
  to take this specific case into account, no more complex potential cases have
  been included as this would add extra complexity for castings followed by other
  expressions (ie. CASE col1 when col2 then 'Y'::text else 'N'::text).

---
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
(0114497)
hgbot   
2019-09-10 14:07   
Repository: erp/devel/dbsm-main
Changeset: cf22a41569f8f5a18eb1299a56e26dcfe80d36c1
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Sep 10 14:03:02 2019 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/cf22a41569f8f5a18eb1299a56e26dcfe80d36c1 [^]

related to issue 41785: refactor index test cases

  Making now use of standard export assertions instead of ad-hoc one.

---
M src-test/src/org/openbravo/dbsm/test/model/CheckIndexFunctionInPrescripts.java
M src-test/src/org/openbravo/dbsm/test/model/ContainsSearchIndexes.java
M src-test/src/org/openbravo/dbsm/test/model/FunctionBasedIndexes.java
M src-test/src/org/openbravo/dbsm/test/model/IndexBaseTest.java
M src-test/src/org/openbravo/dbsm/test/model/IndexParallelization.java
M src-test/src/org/openbravo/dbsm/test/model/OperatorClassIndexes.java
M src-test/src/org/openbravo/dbsm/test/model/PartialIndexes.java
---
(0114516)
hudsonbot   
2019-09-10 19:28   
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/34e5cbc290bf [^]
Maturity status: Test
(0114827)
AugustoMauch   
2019-09-27 09:00   
Code reviewed and verified