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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0041785
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2019-09-09 16:432019-09-27 09:00
ReporteraferrazView Statuspublic 
Assigned Toalostale 
PrioritynormalResolutionfixedFixed in Version3.0PR19Q4
StatusclosedFix in branchFixed in SCM revision970f3f969462
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned ToAugustoMauch
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0041785: 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));
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to feature request 00301793.0PR15Q4 closedAugustoMauch DBSourceManager support indexes with functions 
related to defect 0041795 closedplatform Oracle only: multi-column index with function based column doesn't properly apply 

-  Notes
(0114494)
hgbot (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (administrator)
2019-09-27 09:00

 Code reviewed and verified

- Issue History
Date Modified Username Field Change
2019-09-09 16:43 aferraz New Issue
2019-09-09 16:43 aferraz Assigned To => platform
2019-09-09 16:43 aferraz Modules => Core
2019-09-09 16:43 aferraz Triggers an Emergency Pack => No
2019-09-09 16:56 aferraz Steps to Reproduce Updated View Revisions
2019-09-09 17:10 aferraz Summary Error with function index including a coalesce => Error with function index including a coalesce with a varchar
2019-09-09 17:10 aferraz Description Updated View Revisions
2019-09-09 17:10 aferraz Steps to Reproduce Updated View Revisions
2019-09-10 10:30 alostale Assigned To platform => alostale
2019-09-10 10:30 alostale Relationship added related to 0030179
2019-09-10 10:32 alostale Review Assigned To => AugustoMauch
2019-09-10 14:07 hgbot Checkin
2019-09-10 14:07 hgbot Note Added: 0114494
2019-09-10 14:07 hgbot Status new => resolved
2019-09-10 14:07 hgbot Resolution open => fixed
2019-09-10 14:07 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/6bc6f363024ebdaad0fe307f972440f24e870205 [^]
2019-09-10 14:07 hgbot Checkin
2019-09-10 14:07 hgbot Note Added: 0114495
2019-09-10 14:07 hgbot Checkin
2019-09-10 14:07 hgbot Note Added: 0114496
2019-09-10 14:07 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/6bc6f363024ebdaad0fe307f972440f24e870205 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/970f3f969462a63dcf01f1296eaf9a46efe18fcb [^]
2019-09-10 14:07 hgbot Checkin
2019-09-10 14:07 hgbot Note Added: 0114497
2019-09-10 18:12 vmromanos Relationship added related to 0041795
2019-09-10 19:28 hudsonbot Checkin
2019-09-10 19:28 hudsonbot Note Added: 0114516
2019-09-27 09:00 AugustoMauch Note Added: 0114827
2019-09-27 09:00 AugustoMauch Status resolved => closed
2019-09-27 09:00 AugustoMauch Fixed in Version => 3.0PR19Q4

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker