Not null sqlCheck in Liquibase precondition

Question

I'm wondering if there is an easy way to create liquibase precondition checking if particular value is NULL or NOT NULL. My approach doesn't work.

I created the following test changeset:

<?xml version="1.0" encoding="UTF-8"?>
<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="1" author="test">
      <createTable tableName="test_table">
         <column name="id" type="int">
            <constraints primaryKey="true" nullable="false" />
         </column>
         <column name="test_text" type="varchar(50)">
            <constraints nullable="true" />
         </column>
      </createTable>
   </changeSet>

   <changeSet id="2" author="test">
      <insert tableName="test_table">
         <column name="id" value="1" />
         <column name="test_text" value="test1" />
      </insert>
      <insert tableName="test_table">
         <column name="id" value="2" />
         <column name="test_text" value="null" />
      </insert>
   </changeSet>

   <changeSet id="3" author="test">
      <preConditions onFail="HALT">
         <sqlCheck expectedResult="test1">select test_text from test_table where id=1</sqlCheck>
      </preConditions>
      <insert tableName="test_table">
         <column name="id" value="3" />
         <column name="test_text" value="foo" />
      </insert>
   </changeSet>

   <changeSet id="4" author="test">
      <preConditions onFail="HALT">
         <sqlCheck expectedResult="NULL">select test_text from test_table where id=2</sqlCheck>
      </preConditions>
      <insert tableName="test_table">
         <column name="id" value="4" />
         <column name="test_text" value="bar" />
      </insert>
   </changeSet>

</databaseChangeLog>

Everything works fine except the changeset 4, which is failing with surprising error message "No rows returned from SQL Precondition". However the data is there:

$ psql -U test -c 'select * from test_table'
 id | test_text 
----+-----------
  1 | test1
  2 | 
  3 | foo
(3 rows)

It seems the result is interpreted as empty. In fact it returns one row with one column containing NULL value. Any ideas how to make this working?

I'm using Liquibase 3.3.5 with PostgreSQL 9.3 on Ubuntu 14.04.

Here is the full output (info level):

$ ./liquibase --driver=org.postgresql.Driver  --url="jdbc:postgresql://localhost:5432/test" --username=test --password="" --changeLogFile=/tmp/test.changeset.xml  --logLevel=info update
Liquibase Home is not set.
INFO 02.06.15 09:57: liquibase: Successfully acquired change log lock
INFO 02.06.15 09:57: liquibase: Creating database history table with name: public.databasechangelog
INFO 02.06.15 09:57: liquibase: Reading from public.databasechangelog
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::1::test: Table test_table created
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::1::test: ChangeSet /tmp/test.changeset.xml::1::test ran successfully in 15ms
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::2::test: New row inserted into test_table
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::2::test: New row inserted into test_table
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::2::test: ChangeSet /tmp/test.changeset.xml::2::test ran successfully in 10ms
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::3::test: New row inserted into test_table
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::3::test: ChangeSet /tmp/test.changeset.xml::3::test ran successfully in 3ms
SEVERE 02.06.15 09:57: liquibase: /tmp/test.changeset.xml: /tmp/test.changeset.xml::4::test: Change Set /tmp/test.changeset.xml::4::test failed.  Error: Migration failed for change set /tmp/test.changeset.xml::4::test:
     Reason: 
          /tmp/test.changeset.xml : No rows returned from SQL Precondition

liquibase.exception.MigrationFailedException: Migration failed for change set /tmp/test.changeset.xml::4::test:
     Reason: 
          /tmp/test.changeset.xml : No rows returned from SQL Precondition

    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:485)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:43)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
    at liquibase.Liquibase.update(Liquibase.java:200)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1044)
    at liquibase.integration.commandline.Main.run(Main.java:175)
    at liquibase.integration.commandline.Main.main(Main.java:94)
Caused by: liquibase.exception.PreconditionFailedException: Preconditions Failed
    at liquibase.precondition.core.AndPrecondition.check(AndPrecondition.java:51)
    at liquibase.precondition.core.PreconditionContainer.check(PreconditionContainer.java:201)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:471)
    ... 6 more
INFO 02.06.15 09:57: liquibase: /tmp/test.changeset.xml::4::test: Successfully released change log lock
Unexpected error running Liquibase: Preconditions Failed

SEVERE 02.06.15 09:57: liquibase: /tmp/test.changeset.xml::4::test: Preconditions Failed
liquibase.exception.MigrationFailedException: Migration failed for change set /tmp/test.changeset.xml::4::test:
     Reason: 
          /tmp/test.changeset.xml : No rows returned from SQL Precondition

    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:485)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:43)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
    at liquibase.Liquibase.update(Liquibase.java:200)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1044)
    at liquibase.integration.commandline.Main.run(Main.java:175)
    at liquibase.integration.commandline.Main.main(Main.java:94)
Caused by: liquibase.exception.PreconditionFailedException: Preconditions Failed
    at liquibase.precondition.core.AndPrecondition.check(AndPrecondition.java:51)
    at liquibase.precondition.core.PreconditionContainer.check(PreconditionContainer.java:201)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:471)
    ... 6 more

Answer 1

This workaround might work

<sqlCheck expectedResult="1">select COUNT(id) from test_table where id=2 AND test_text IS NULL</sqlCheck>

Answer 2

Might not be the most simple or elegant way but you could use a <customPrecondition>.

You would write your own class that has to implement CustomPrecondition.

Checkout this example class: ExampleCustomPrecondition as a reference.

You get a Database object at hand and could do a query to the database yourself and throw a CustomPreconditionFailedException.

(Alternatively create a Jira-Ticket to file a request for changing this behavior or adding a way to achieve the null preCondition check.)