Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0041795Openbravo ERPA. Platformpublic2019-09-10 18:122019-09-19 09:02
Reportervmromanos 
Assigned Toplatform 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutioninvalid 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0041795: Oracle only: multi-column index with function based column doesn't properly apply
DescriptionIt seems that when you have a multi-column index where any of the columns has a function, the install.source/update.database processes fail in Oracle (in PG works fine).
Steps To ReproduceIn an Oracle instance clone the following modules:
https://code.openbravo.com/erp/pmods/org.openbravo.oms [^]
https://code.openbravo.com/erp/mods/org.openbravo.api.ticket [^]

Note the OBOMS_STOCK_BY_WAREHOUSE table has the following index:
  <index name="OBOMS_SBW_AVAILABLEPROD_IX" unique="false">
        <index-column name="functionBasedColumn" functionExpression="(QTYONHAND-RESERVEDQTY)>0"/>
        <index-column name="M_PRODUCT_ID"/>
  </index>

Run ant install.source (with smartbuild -Dlocal=no it is also reproducible).
The following error is thrown:

[createdatabase] 168019 [main] WARN org.apache.ddlutils.platform.OracleStandardBatchEvaluator - SQL Command failed with: ORA-00907: falta el paréntesis derecho
[createdatabase]
[createdatabase] 168019 [main] WARN org.apache.ddlutils.platform.OracleStandardBatchEvaluator - -- END
[createdatabase] CREATE INDEX OBOMS_SBW_AVAILABLEPROD_IX ON OBOMS_STOCK_BY_WAREHOUSE (((QTYONHAND-RESERVEDQTY)>0), M_PRODUCT_ID)
[createdatabase] 210228 [main] INFO org.apache.ddlutils.platform.OracleStandardBatchEvaluator - Executed 14045 SQL command(s) with 1 error(s) in 141956 ms
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to defect 0041785 closed alostale Error with function index including a coalesce with a varchar 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2019-09-10 18:12vmromanosNew Issue
2019-09-10 18:12vmromanosAssigned To => platform
2019-09-10 18:12vmromanosModules => Core
2019-09-10 18:12vmromanosTriggers an Emergency Pack => No
2019-09-10 18:12vmromanosRelationship addedrelated to 0041785
2019-09-10 18:15vmromanosSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=19374#r19374
2019-09-10 18:16vmromanosSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=19375#r19375
2019-09-19 09:02alostaleNote Added: 0114664
2019-09-19 09:02alostaleStatusnew => closed
2019-09-19 09:02alostaleResolutionopen => invalid

Notes
(0114664)
alostale   
2019-09-19 09:02   
This is a limitation on how Oracle supports functions on indexes, therefore there is no solution that can be implemented in dbsm.

> CREATE TABLE TEST
   ( TEST_ID VARCHAR2(32) NOT NULL ENABLE,
AMT1 NUMBER,
AMT2 NUMBER,
CONSTRAINT "TEST_ID" PRIMARY KEY ("TEST_ID"));
Table TEST created.
     
> CREATE INDEX TEST_I1 ON TEST (AMT1-AMT2);
Index TEST_I1 created.
     
> CREATE INDEX TEST_I2 ON TEST (AMT1-AMT2>0);
Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"

> CREATE INDEX TEST_I2 ON TEST (AMT1>0);
Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"