createIndex and indexExist different case for oracle

Alxspb commented 1 year ago

Search first

[X] I searched and no similar issues were found

Description

For oracledb index name in indexExist request is always in uppercase, but create index name is quoted. This changeset generates error after second run:

    <changeSet id="create-index" author="test" runAlways="true">
        <preConditions onFail="CONTINUE">
            <not>
                <indexExists tableName="test_table" indexName="index_-id"/>
            </not>
        </preConditions>
        <createIndex tableName="test_table" indexName="index_-id">
            <column name="name"/>
        </createIndex>
    </changeSet>

Steps To Reproduce

1) Create oracle db (I have used https://github.com/oracle/docker-images/tree/main/OracleDatabase/SingleInstance) 2) Create changelog_test.xml with content:

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <changeSet id="create-table" author="test">
        <createTable tableName="test_table">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(1024)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="create-index" author="test" runAlways="true">
        <preConditions onFail="CONTINUE">
            <not>
                <indexExists tableName="test_table" indexName="index_-id"/>
            </not>
        </preConditions>
        <createIndex tableName="test_table" indexName="index_-id">
            <column name="name"/>
        </createIndex>
    </changeSet>
</databaseChangeLog>

3) Run in latest liquibase container twice:

liquibase --changeLogFile="changelog_test.xml" --url="jdbc:oracle:thin:@oracle_db:1521/ORCLPDB1" --username=admin --password="sql"

Actual Behavior

Got output:

liquibase@e858c17c6a81:/liquibase$ liquibase --changeLogFile="changelog_test.xml" --url="jdbc:oracle:thin:@oracle_db:1521/ORCLPDB1" --username=admin --password="sql" --log-level=INFO update
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 21:35:34 (version 4.19.0 #6648 built at 2023-01-17 15:02+0000)
Liquibase Version: 4.19.0
Liquibase Open Source 4.19.0 by Liquibase
[2023-02-28 21:35:35] INFO [liquibase.lockservice] Successfully acquired change log lock
[2023-02-28 21:35:37] INFO [liquibase.changelog] Reading from ADMIN.DATABASECHANGELOG
Running Changeset: changelog_test.xml::create-table::test
[2023-02-28 21:35:37] INFO [liquibase.changelog] Table test_table created
[2023-02-28 21:35:37] INFO [liquibase.changelog] ChangeSet changelog_test.xml::create-table::test ran successfully in 52ms
Running Changeset: changelog_test.xml::create-index::test
[2023-02-28 21:35:38] INFO [liquibase.changelog] Index index_-id created
[2023-02-28 21:35:38] INFO [liquibase.changelog] ChangeSet changelog_test.xml::create-index::test ran successfully in 1023ms
[2023-02-28 21:35:38] INFO [liquibase.lockservice] Successfully released change log lock
Liquibase command 'update' was executed successfully.
liquibase@e858c17c6a81:/liquibase$ liquibase --changeLogFile="changelog_test.xml" --url="jdbc:oracle:thin:@oracle_db:1521/ORCLPDB1" --username=admin --password="sql" --log-level=INFO update
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 21:35:44 (version 4.19.0 #6648 built at 2023-01-17 15:02+0000)
Liquibase Version: 4.19.0
Liquibase Open Source 4.19.0 by Liquibase
[2023-02-28 21:35:45] INFO [liquibase.lockservice] Successfully acquired change log lock
[2023-02-28 21:35:46] INFO [liquibase.changelog] Reading from ADMIN.DATABASECHANGELOG
Running Changeset: changelog_test.xml::create-index::test
[2023-02-28 21:35:47] INFO [liquibase.lockservice] Successfully released change log lock
[2023-02-28 21:35:47] SEVERE [liquibase.integration] Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.command.CommandScope.execute(CommandScope.java:186)
    at liquibase.integration.commandline.Main.handleUpdateException(Main.java:2190)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1834)
    at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:396)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.integration.commandline.Main$1.run(Main.java:395)
    at liquibase.integration.commandline.Main$1.run(Main.java:217)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:165)
    at liquibase.integration.commandline.Main.run(Main.java:217)
    at liquibase.command.AbstractCliWrapperCommandStep.run(AbstractCliWrapperCommandStep.java:32)
    at liquibase.command.CommandScope.execute(CommandScope.java:172)
    at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
    at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
    at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
    at picocli.CommandLine.access$1500(CommandLine.java:148)
    at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
    at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
    at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
    at picocli.CommandLine.execute(CommandLine.java:2170)
    at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$1(LiquibaseCommandLine.java:352)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:165)
    at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:317)
    at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:84)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.base/java.lang.reflect.Method.invoke(Unknown Source)
    at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:107)
Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:127)
    at liquibase.Liquibase.lambda$null$0(Liquibase.java:291)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Scope.child(Scope.java:243)
    at liquibase.Liquibase.lambda$update$1(Liquibase.java:290)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Liquibase.runInScope(Liquibase.java:2431)
    at liquibase.Liquibase.update(Liquibase.java:237)
    at liquibase.Liquibase.update(Liquibase.java:222)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1832)
    ... 32 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:722)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:56)
    at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:114)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:113)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Scope.child(Scope.java:243)
    at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Scope.child(Scope.java:243)
    at liquibase.Scope.child(Scope.java:247)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
    ... 47 more
Caused by: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:446)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:78)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:161)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1270)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1252)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:687)
    ... 67 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:111)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1010)
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1530)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1310)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2162)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2117)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:442)
    ... 72 more
Caused by: Error : 955, Position : 19, Sql = CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name), OriginalSql = CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name), Error Msg = ORA-00955: name is already used by an existing object

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
    ... 85 more

Unexpected error running Liquibase: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]

liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.command.CommandScope.execute(CommandScope.java:186)
    at liquibase.integration.commandline.Main.handleUpdateException(Main.java:2190)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1834)
    at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:396)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.integration.commandline.Main$1.run(Main.java:395)
    at liquibase.integration.commandline.Main$1.run(Main.java:217)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:165)
    at liquibase.integration.commandline.Main.run(Main.java:217)
    at liquibase.command.AbstractCliWrapperCommandStep.run(AbstractCliWrapperCommandStep.java:32)
    at liquibase.command.CommandScope.execute(CommandScope.java:172)
    at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
    at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
    at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
    at picocli.CommandLine.access$1500(CommandLine.java:148)
    at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
    at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
    at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
    at picocli.CommandLine.execute(CommandLine.java:2170)
    at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$1(LiquibaseCommandLine.java:352)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:165)
    at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:317)
    at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:84)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.base/java.lang.reflect.Method.invoke(Unknown Source)
    at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:107)
Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:127)
    at liquibase.Liquibase.lambda$null$0(Liquibase.java:291)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Scope.child(Scope.java:243)
    at liquibase.Liquibase.lambda$update$1(Liquibase.java:290)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Liquibase.runInScope(Liquibase.java:2431)
    at liquibase.Liquibase.update(Liquibase.java:237)
    at liquibase.Liquibase.update(Liquibase.java:222)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1832)
    ... 32 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog_test.xml::create-index::test:
     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:722)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:56)
    at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:114)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:113)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Scope.child(Scope.java:243)
    at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
    at liquibase.Scope.lambda$child$0(Scope.java:180)
    at liquibase.Scope.child(Scope.java:189)
    at liquibase.Scope.child(Scope.java:179)
    at liquibase.Scope.child(Scope.java:158)
    at liquibase.Scope.child(Scope.java:243)
    at liquibase.Scope.child(Scope.java:247)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
    ... 47 more
Caused by: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
 [Failed SQL: (955) CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name)]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:446)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:78)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:161)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1270)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1252)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:687)
    ... 67 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:111)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1010)
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1530)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1310)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2162)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2117)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:442)
    ... 72 more
Caused by: Error : 955, Position : 19, Sql = CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name), OriginalSql = CREATE INDEX ADMIN."index_-id" ON ADMIN.test_table(name), Error Msg = ORA-00955: name is already used by an existing object

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
    ... 85 more

Expected/Desired Behavior

Every time changelog is processed, indexExist precondition checks that index exists and if it doesn't it should create it

Liquibase Version

4.19.0

Database Vendor & Version

Oracle 19.3.0 EE

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Ubuntu 22.04 + liquibase docker image

Additional Context

No response

Are you willing to submit a PR?

[ ] I'm willing to submit a PR (Thank you!)

nvoxland commented 1 year ago

Thanks, the issue makes sense. We auto-quote index names that need to be quoted like index_-id in the create etc, but must not have that same logic for the query we come up with in indexExists. We just switch it to the database-stored capitalization and do an equality check in there.

You can work around the issue for now by switching indexExists to a sqlCheck where you write a custom query against the metadata and correctly case the index name.

Otherwise, if someone is looking to provide a fix, the problem code is likely in how IndexExistsPrecondition just does a correctObjectName() on the index instead of checking the database.mustQuoteObjectName() function

rh-id commented 1 year ago

Hello, I will try to look at it. Hi @nvoxland I'm new to liquibase source code and can't seemed to find maven command to execute liquibase using project source code, is there any of this command?

Alxspb commented 10 months ago

This patch actually works in our fork:

Index: liquibase-core/src/main/java/liquibase/database/AbstractJdbcDatabase.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/liquibase-core/src/main/java/liquibase/database/AbstractJdbcDatabase.java b/liquibase-core/src/main/java/liquibase/database/AbstractJdbcDatabase.java
--- a/liquibase-core/src/main/java/liquibase/database/AbstractJdbcDatabase.java (revision 9553d6a9b01a79118e66ec4cb0c7aa8eedeaaaf7)
+++ b/liquibase-core/src/main/java/liquibase/database/AbstractJdbcDatabase.java (revision 569fac2686463ccb0eac9337764406eed95a3e6f)
@@ -277,6 +277,10 @@

     @Override
     public String correctObjectName(final String objectName, final Class<? extends DatabaseObject> objectType) {
+        if (objectType == Index.class && mustQuoteObjectName(objectName, objectType)) {
+            return objectName;
+        }
+
         if (quotingStrategy == ObjectQuotingStrategy.QUOTE_ALL_OBJECTS || unquotedObjectsAreUppercased == null
                 || objectName == null || (objectName.startsWith(quotingStartCharacter) && objectName.endsWith(
                 quotingEndCharacter))) {
@@ -998,7 +1002,8 @@
     }

     protected boolean mustQuoteObjectName(String objectName, Class<? extends DatabaseObject> objectType) {
-        return objectName.contains("-") || startsWithNumeric(objectName) || isReservedWord(objectName) || objectName.matches(".*\\W.*");
+        return objectName != null &&
+                (objectName.contains("-") || startsWithNumeric(objectName) || isReservedWord(objectName) || objectName.matches(".*\\W.*"));
     }

     public String quoteObject(final String objectName, final Class<? extends DatabaseObject> objectType) {

mpvvliet commented 3 months ago

I took a look at this issue and after some investigation it seems the existence of the index is checked using a query on ALL_IND_COLUMNS which stores index names in a VARCHAR2field, unquoted and case-sensitive. When checking whether the index exists in JdbcDatabaseSnapshot the index name has been converted to uppercase (though indeed not quoted), so the index is not found.

I tried running the test with index name INDEX_-ID (so uppercase) which works fine, as well as using index_id(lowercase, but without the - to prevent quoting) which also works.

My conclusion is that the combination of lowercase with quoting causes the issue.

The solution is unclear to me though. When passing the index name down the call stack to be used in the SQL query on ALL_IND_COLUMNS there are various places where the index name is converted to uppercase using a call to database.correctObjectName. I don’t see an easy way to use the original index name in the query, plus am not sure whether changing this will work on all databases.

Another option would be to change the way we check for index existence to use the actual object rather than query a table, in which case we can presumably use the escaped and quoted index name. A comment in the relevant code suggests querying the ALL_IND_COLUMNSview was implemented to work around a bug in Oracle’s getIIndexInfocode so that also does not seem like a good option.

My recommendation would be to document that the combination of a lowercase index name with the need to quote the name don’t work with the IndexExistsPrecondition.

liquibase / liquibase