Error When Creating Index IDX_UM_PERMISSION_ID_LOWER_UM_USER_NAME_UM_TENANT_ID

[aaujayasena]

Description

The database script for oracle rac attempts to create a unique index on the UM_SYSTEM_USER table using a column (UM_PERMISSION_ID) that does not exist in the table. This results in the following error during script execution:

ORA-00904: "UM_PERMISSION_ID": invalid identifier
Error at Line: 589 Column: 0

Steps to Reproduce

1. Execute the following CREATE TABLE statement to create the UM_SYSTEM_USER table:CREATE TABLE UM_SYSTEM_USER (
   UM_ID INTEGER,
   UM_USER_NAME VARCHAR(255) NOT NULL,
   UM_USER_PASSWORD VARCHAR(255) NOT NULL,
   UM_SALT_VALUE VARCHAR(31),
   UM_REQUIRE_CHANGE NUMBER(1) DEFAULT 0,
   UM_CHANGED_TIME TIMESTAMP NOT NULL,
   UM_TENANT_ID INTEGER DEFAULT 0,
   PRIMARY KEY (UM_ID, UM_TENANT_ID)
   );

2. Run the following CREATE UNIQUE INDEX statement:
   CREATE UNIQUE INDEX IDX_UM_PERMISSION_ID_LOWER_UM_USER_NAME_UM_TENANT_ID
   ON UM_SYSTEM_USER(UM_PERMISSION_ID, LOWER(UM_USER_NAME), UM_TENANT_ID);

3. Observe the error:

Version

IS 7.1.0-m8

Environment Details (with versions)

Oracle rac 23ai

[aaujayasena]

https://github.com/wso2/carbon-kernel/blame/4.10.x/distribution/kernel/carbon-home/dbscripts/oracle_rac.sql#L440
wso2/carbon-kernel#4185
@sumedhe can you please check on this

[aaujayasena]

DB scrips were verified with the wso2is7.1.0-alpha. sql outputs are as below, no issue observed.

sdw_output_250128_0935.txt
sdw_output_250128_0934.txt
sdw_output_250128_0933.txt