createIndex does not work for function based indexes anymore

Description

With Liquibase 2.0.5 ist was possible to create function based indexes with the createIndex tag:

<createIndex indexName="IX_MYINDEX" tableName="TABLE1" unique="false">
<column name="lower(anystring)"/>
</createIndex>

With Liquibase 3.0.7 the column name is escaped and Oracle returns 'ORA-9984: invalid identifier', since the function is contained in quotes.

What is the suggested solution for this problem?

Environment

Oracle 11g, Windows 7

Activity

Show:
Martin Aberle
December 16, 2013, 9:24 AM
Edited

I can't see a change in the behaviour, there is still this error message:

Caused by: liquibase.exception.DatabaseException: Error executing SQL CREATE INDEX HPA_TMP.M_SFAZ_FZID4_ACTIVE ON HPA_TMP.M_SFAZ("substr(fahrzeug_id, -4, 4)", VERSION_ACTIVE_FLAG) TABLESPACE HPA_IDX: ORA-00904: "substr(fahrzeug_id, -4, 4)": invalid identifier

Also I can't find any check in comment regarding this issue.

Nathan Voxland
January 6, 2014, 3:48 PM

Fixed.

The problem in your case was the - in substr(fahrzeug_id, -4, 4). Your example in the description was working fine before.

Martin Aberle
January 13, 2014, 8:18 AM

Thanks, it is working fine now. Hint: Indexes with columns including the constant '1' (Oracle force of indexing null key values) will not generated correctly.

Workaround: Enclosing the 1 in parenthesises: "(1)".

alexandre
August 6, 2014, 8:50 AM

Hello
I've the same problem in 3.2.2

ORA-00972: l'identificateur est trop long
<changeSet author="jenkins (generated)" id="1407267384170-2061">
<createIndex indexName="ACT_UNIQ_HI_BUS_KEY" tableName="ACT_HI_PROCINST" unique="true">
<column name="CASE WHEN BUSINESS_KEY_ IS NULL THEN NULL ELSE PROC_DEF_ID_ END"/>
</createIndex>
</changeSet>

any idee ?
thanks

Nathan Voxland
October 21, 2014, 7:55 PM

3.3.0 adds a new "computed" attribute to <column> to better differentiate between function/computed columns and actual columns

Fixed

Reporter

Martin Aberle

Components

Fix versions

Affects versions

Priority

Major