primaryKeyExists pre condition with table name and primary key name is malfunctioning on Oracle

[Vampire]

Environment

Liquibase Version: 4.3.1

Liquibase Integration & Version: n/a

Liquibase Extension(s) & Version: n/a

Database Vendor & Version: Oracle broken, MSSQL works

Operating System Type & Version: n/a

Description

Given these change sets:

   <changeSet id="xxx-1" author="n/a">
      <preConditions onFail="MARK_RAN">
         <not>
            <primaryKeyExists primaryKeyName="PK_ON_TABLE_X"/>
         </not>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-2" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists primaryKeyName="NON_EXISTING_PK"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-3" author="n/a">
      <preConditions onFail="MARK_RAN">
         <not>
            <primaryKeyExists tableName="TABLE_X_WITH_PK"/>
         </not>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-4" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="TABLE_WITHOUT_PK"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-5" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="NON_EXISTING_TABLE"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-6" author="n/a">
      <preConditions onFail="MARK_RAN">
         <not>
            <primaryKeyExists tableName="TABLE_X_WITH_PK" primaryKeyName="PK_ON_TABLE_X"/>
         </not>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-7" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="TABLE_X_WITH_PK" primaryKeyName="NON_EXISTING_PK"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-8" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="TABLE_WITHOUT_PK" primaryKeyName="PK_ON_TABLE_X"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-9" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="TABLE_WITHOUT_PK" primaryKeyName="NON_EXISTING_PK"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-10" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="NON_EXISTING_TABLE" primaryKeyName="PK_ON_TABLE_X"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-11" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="NON_EXISTING_TABLE" primaryKeyName="NON_EXISTING_PK"/>
      </preConditions>
      <stop/>
   </changeSet>

   <changeSet id="xxx-12" author="n/a">
      <preConditions onFail="MARK_RAN">
         <primaryKeyExists tableName="TABLE_Y_WITH_PK_BUT_NOT_THE_ONE_IN_THE_OTHER_ATTRIBUTE" primaryKeyName="PK_ON_TABLE_X"/>
      </preConditions>
      <stop/>
   </changeSet>

I would expect all to be marked as ran and none to fail due to the stop change. This works properly on MSSQL, but it fails on Oracle. On Oracle xxx-7 and xxx-12 reach the stop change. It seems on Oracle any PK satisfies the pre-condition despite a name being specified.

[molivasdat]

Hi @Vampire Thanks for writing up the issue. We will add this to the list of changes that we are processing. The assumption is that this is running on an empty schema? Does Oracle xxx-7 mean Oracle 7 and xxx-12 mean Oracle 12?

[Vampire]

No, 1 to 12 are just running number, no semantics. And no, not an empty database, but with the tables and pks existing (or non-existing) like they are named.

[Jukien]

Hello, same issue here:

<changeSet id="${project.artifactId}-19-renamePrimaryKey" author="JDI">
    <preConditions onFail="MARK_RAN">
        <primaryKeyExists tableName="BODY_STRUCTURE" primaryKeyName="PK_LOCATION"/>
    </preConditions>
    <sql>
        ALTER TABLE BODY_STRUCTURE RENAME CONSTRAINT PK_LOCATION TO PK_BODY_STRUCTURE
    </sql>
</changeSet>

Error:

liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog.xml::test-19-renamePrimaryKey::JDI:
     Reason: liquibase.exception.DatabaseException: ORA-23292: La contrainte n'existe pas
 [Failed SQL: (23292) ALTER TABLE BODY_STRUCTURE RENAME CONSTRAINT PK_LOCATION TO PK_BODY_STRUCTURE]

Have you made progress on this issue?

[molivasdat]

@Jukien We are going to add this back into the queue to take a deeper look at.

[Jukien]

@molivasdat Noted

[AchrafBelkahla]

Hello some issue with liquibase 4.8.0 and Oracle 12

<changeSet author="user" id="1"> 
               <preConditions onFail="MARK_RAN" onError="HALT">
            <not>
                <primaryKeyExists primaryKeyName="PK_TESTTABLE" tableName="TESTTABLE" />
            </not>
        </preConditions>    
        <dropPrimaryKey constraintName="PK_TESTTABLE" dropIndex="true" tableName="TESTTABLE"/>
</changeSet>

and even with removing the primaryKeyName attribute :

    <changeSet author="user" id="1"> 
        <preConditions onFail="MARK_RAN" onError="HALT">
            <not>
                <primaryKeyExists  tableName="TESTTABLE" />
            </not>
        </preConditions>    
        <dropPrimaryKey constraintName="PK_TESTTABLE" dropIndex="true" tableName="TESTTABLE"/>
    </changeSet>`

Any idea ?

[AchrafBelkahla]

Hello some issue with liquibase 4.8.0 and Oracle 12

<changeSet author="user" id="1"> 
               <preConditions onFail="MARK_RAN" onError="HALT">
          <not>
              <primaryKeyExists primaryKeyName="PK_TESTTABLE" tableName="TESTTABLE" />
          </not>
      </preConditions>    
      <dropPrimaryKey constraintName="PK_TESTTABLE" dropIndex="true" tableName="TESTTABLE"/>
</changeSet>

and even with removing the primaryKeyName attribute :

  <changeSet author="user" id="1"> 
      <preConditions onFail="MARK_RAN" onError="HALT">
          <not>
              <primaryKeyExists  tableName="TESTTABLE" />
          </not>
      </preConditions>    
      <dropPrimaryKey constraintName="PK_TESTTABLE" dropIndex="true" tableName="TESTTABLE"/>
  </changeSet>`

Any idea ?

EDIT : my bad, was using the not condition ....

[FBurguer]

Still an issue on liquibase 4.15.