Broken migrations for indices on TIMESTAMP WITH TIMEZONE Oracle Database columns

[gabrielkim13]

Issue description

When creating indices for columns of type TIMESTAMP WITH TIMEZONE, on Oracle Database, a virtual column named 'SYS_NC0000*$' will be created in order for the DB to be able to index the actual column. Since the index will reference this virtual column, any further TypeORM migrations will be broken, attempting to: drop the index; drop the virtual column - which fails; re-create the previously dropped index.

Expected Behavior

After running the first migration, which creates the index on the TIMESTAMP WITH TIMEZONE column, further migrations shouldn't attempt to re-create it and just ignore the virtual column.

Actual Behavior

A second run of the MCVE will result in the following error:

❯ npm run start

> typeorm-mcve@0.0.1 start
> ts-node src/index.ts

QueryFailedError: ORA-00942: table or view does not exist
    at OracleQueryRunner.query (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:274:19)
    at async /home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:2576:49
    at async Promise.all (index 5)
    at async /home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:2385:33
    at async Promise.all (index 0)
    at async OracleQueryRunner.loadTables (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:2368:
16)
    at async OracleQueryRunner.getTables (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/query-runner/BaseQueryRunner.ts:158:29)
    at async RdbmsSchemaBuilder.build (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-builder/RdbmsSchemaBuilder.ts:92:13)
    at async DataSource.synchronize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:335:9)
    at async DataSource.initialize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:273:43) {
  query: 'SELECT * FROM "TEST"."typeorm_metadata" "t" WHERE "type" = :1 AND "name" = :2 AND "table" = :3',
  parameters: [ 'GENERATED_COLUMN', 'SYS_NC00006$', 'user' ],
  driverError: Error: ORA-00942: table or view does not exist
      at async OracleQueryRunner.query (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:210:25)
      at async /home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:2576:49
      at async Promise.all (index 5)
      at async /home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:2385:33
      at async Promise.all (index 0)
      at async OracleQueryRunner.loadTables (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:236
8:16)
      at async OracleQueryRunner.getTables (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/query-runner/BaseQueryRunner.ts:158:29)
      at async RdbmsSchemaBuilder.build (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-builder/RdbmsSchemaBuilder.ts:92:13
)
      at async DataSource.synchronize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:335:9)
      at async DataSource.initialize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:273:43) {
    errorNum: 942,
    offset: 21
  },
  errorNum: 942,
  offset: 21
}

Even after manually creating the "typeorm_metadata" table, the example will fail:

CREATE TABLE "typeorm_metadata" (
    "type" VARCHAR2(255) NOT NULL,
    "database" VARCHAR2(255) DEFAULT NULL,
    "schema" VARCHAR2(255) DEFAULT NULL,
    "table" VARCHAR2(255) DEFAULT NULL,
    "name" VARCHAR2(255) DEFAULT NULL,
    "value" CLOB
);

❯ npm run start

> typeorm-mcve@0.0.1 start
> ts-node src/index.ts

QueryFailedError: ORA-00904: "SYS_NC00006$": invalid identifier
    at OracleQueryRunner.query (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:274:19)
    at async OracleQueryRunner.executeQueries (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/query-runner/BaseQueryRunner.ts:659:
13)
    at async OracleQueryRunner.dropColumn (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:1693:
9)
    at async OracleQueryRunner.dropColumns (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:1707
:13)
    at async RdbmsSchemaBuilder.dropRemovedColumns (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-builder/RdbmsSchemaBuild
er.ts:777:13)
    at async RdbmsSchemaBuilder.executeSchemaSyncOperationsInProperOrder (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-bu
ilder/RdbmsSchemaBuilder.ts:225:9)
    at async RdbmsSchemaBuilder.build (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-builder/RdbmsSchemaBuilder.ts:95:13)
    at async DataSource.synchronize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:335:9)
    at async DataSource.initialize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:273:43) {
  query: 'ALTER TABLE "user" DROP COLUMN "SYS_NC00006$"',
  parameters: undefined,
  driverError: Error: ORA-00904: "SYS_NC00006$": invalid identifier
      at async OracleQueryRunner.query (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:210:25)
      at async OracleQueryRunner.executeQueries (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/query-runner/BaseQueryRunner.ts:65
9:13)
      at async OracleQueryRunner.dropColumn (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:169
3:9)
      at async OracleQueryRunner.dropColumns (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/driver/oracle/OracleQueryRunner.ts:17
07:13)
      at async RdbmsSchemaBuilder.dropRemovedColumns (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-builder/RdbmsSchemaBui
lder.ts:777:13)
      at async RdbmsSchemaBuilder.executeSchemaSyncOperationsInProperOrder (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-
builder/RdbmsSchemaBuilder.ts:225:9)
      at async RdbmsSchemaBuilder.build (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/schema-builder/RdbmsSchemaBuilder.ts:95:13
)
      at async DataSource.synchronize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:335:9)
      at async DataSource.initialize (/home/gabrielkim13/Desktop/github.com/gabrielkim13/typeorm-mcve/src/data-source/DataSource.ts:273:43) {
    errorNum: 904,
    offset: 31
  },
  errorNum: 904,
  offset: 31
}

Notice the error message: "ORA-00904: "SYS_NC00006$": invalid identifier" - TypeORM attempts to drop the virtual column, which fails.

Steps to reproduce

MCVE: https://github.com/gabrielkim13/typeorm-mcve/tree/oracle-timestamp-with-timezone-index

My Environment

Dependency	Version
Operating System	Ubuntu 22.04.3 LTS (WSL)
Node.js version	20.9.0
Typescript version	4.5.2
TypeORM version	0.3.17

Additional Context

When TypeORM lists a table's columns with:

SELECT *
FROM "ALL_TAB_COLS" "C"
WHERE ("C"."OWNER" = 'TEST' AND "C"."TABLE_NAME" = 'user')

The virtual column "SYS_NC00006$" shows up. Since it isn't explicitly declared on any schema, TypeORM will attempt to drop it on future migrations / schema syncs.

Furthermore, when it loads all indices with the following query:

SELECT "C"."INDEX_NAME",
       "C"."OWNER",
       "C"."TABLE_NAME",
       "C"."UNIQUENESS",
       LISTAGG("COL"."COLUMN_NAME", ',') WITHIN GROUP (ORDER BY "COL"."COLUMN_NAME") AS "COLUMN_NAMES"
FROM "ALL_INDEXES" "C"
         INNER JOIN "ALL_IND_COLUMNS" "COL"
                    ON "COL"."INDEX_OWNER" = "C"."OWNER" AND "COL"."INDEX_NAME" = "C"."INDEX_NAME"
         LEFT JOIN "ALL_CONSTRAINTS" "CON" ON "CON"."OWNER" = "C"."OWNER" AND "CON"."CONSTRAINT_NAME" = "C"."INDEX_NAME"
WHERE (("C"."OWNER" = 'TEST' AND "C"."TABLE_NAME" = 'user'))
  AND "CON"."CONSTRAINT_NAME" IS NULL
GROUP BY "C"."INDEX_NAME", "C"."OWNER", "C"."TABLE_NAME", "C"."UNIQUENESS";

The index references the virtual column:

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, and I know how to start.