Oracle: descending text_steps_doc_id_id_index blocks every later schema migration #9439

Observed: upgrade 32 → 33 on Oracle 23 stopped in core Version33000Date20250819110529 with the error above. The index had been created with the workaround statement from #9393's description.

Cause

• Oracle stores a DESC key as a hidden virtual column, SYS_NCnnnnn$. user_ind_columns returns that name for the index, while user_tab_columns doesn't list it.
• It looks like OracleMigrator::getDiff rebuilds every table through addIndex(), and Doctrine's Table::_createIndex() requires each index column to exist, so any step whose changeSchema returns a schema throws. Steps that return null pass, so the failure surfaces one or more migrations after the index is created. This part is from reading the code; corrections welcome.
• Fresh installs run app migrations schema-only (Installer.php#L548), so postSchemaChange, where the raw CREATE INDEX lives, never runs on them. If that's right, phpunit-oci never executes that statement.

Oracle 23.26.3, oc_text_steps with the index created ("document_id", "id" DESC)

Recreated ("document_id", "id"): the index columns read back as document_id, id, and the table has no hidden column. Plans on 200k rows with statistics, for the StepMapper::getLatestVersion and getBeforeVersion query shapes:

Repro (Oracle, as the Nextcloud schema user; creates the index in its #9393 form; fresh installs have no index to drop):

CREATE INDEX text_steps_doc_id_id_index ON "oc_text_steps" ("document_id", "id" DESC);
SELECT index_name, column_name, descend FROM user_ind_columns WHERE column_name LIKE 'SYS_NC%';

Then run any migration that returns a schema.

Workaround for affected instances:

DROP INDEX text_steps_doc_id_id_index;
CREATE INDEX text_steps_doc_id_id_index ON "oc_text_steps" ("document_id", "id");

A repair step in text can't fully replace this: core migrations run before app migrations, so on a major upgrade core's first schema-changing step fails before text's repair runs.

Server side

Server is the only place that runs early enough to cover instances that already have the index, and to stop one app's index blocking core's and every other app's migrations. Two options:

• A pre-upgrade repair step (Repair::getBeforeUpgradeRepairSteps(), which runs before doCoreUpgrade()) that recreates DESC-only indexes ascending on Oracle.
• OracleMigrator tolerating expression index columns. Skipping such an index only in the rebuilt target would probably make the comparison emit DROP INDEX for it, so it would likely also have to be ignored on the introspected source side.

Related, not addressed here: fresh installs on PostgreSQL and SQLite never get this index, since it is only created in postSchemaChange.