How to avoid liquibase evaluating columns inside an IF EXISTS BEGIN block even when false

General Discussion
1

We are using liquibase formatted SQL (and need to stay in this format for the time being) and it has been going well, but I just encountered an issue that I cannot figure out how to get around.

We have had a lot of IF EXISTS (proc) DROP PROC statements and those seem to work fine.

But, one of the developers just added a check for a column on a table; the SQL executes properly directly against MSSQL but fails when ran as Liquibase.


The logic is if the table contains a given column, then update the values in that column. Liquibase appears to be properly capturing the result of the IF statement, but it seems it still tries to evaluate the validity of the condition inside of it.

Here is the desired Query:

  1. – changeset damon.overboe:test-if-exists-3 endDelimiter:\nGO runOnChange:true
  2. IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='good_table_name AND COLUMN_NAME=‘bad_column_name’)
  3. BEGIN
  4.     UPDATE good_table_name SET bad_column_name = 1
  5. END
  6. GO
I’ve tried with BEGIN and BEGIN; and both give the same output:

 Reason: liquibase.exception.DatabaseException: Error executing SQL IF …
%< ----- snip ---- >%
 Invalid column name ‘bad_column_name’.


I also tried using
  1. IF (SELECT count(*) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=‘good_table_name’ AND COLUMN_NAME=‘bad_column_name’) <> 0
  2. …
And get the same result.

And I have tried dropping the BEGIN and END blocks:

  1. – changeset damon.overboe:test-if-exists-4 endDelimiter:\nGO runOnChange:true
  2. IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='good_table_name AND COLUMN_NAME=‘bad_column_name’)
  3.     UPDATE good_table_name SET bad_column_name = 1
  4. GO


So, to prove it’s not the IF statement’s syntax, I added two tables, damon_test_table_should_persist and damon_test_you_shouldnt_see_me, then ran the following two changesets

  1. – changeset damon.overboe:test-if-exists-1 endDelimiter:\nGO runOnChange:true
  2. IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=‘good_table_name’ AND COLUMN_NAME=‘bad_column_name’)
  3. BEGIN
  4.     – IF should be false, so this table should not be dropped
        DROP TABLE damon_test_table_should_persist
  5. END
  6. GO

  8. – changeset damon.overboe:test-if-exists-2 endDelimiter:\nGO runOnChange:true
  9. IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=‘good_table_name’ AND COLUMN_NAME=‘bad_column_name’)
  10. BEGIN
  11.     – IF should be true, so this table should be dropped
        DROP TABLE damon_test_you_shouldnt_see_me
  12. END
  13. GO

Both of those ran successfully, so I don’t think Liquibase is having an issue with the syntax, I think it’s just evaluating the code inside the IF block.

This command errs as expected (table does not exist, it was properly dropped):
  1. $ sqlcmd -Q “use mydb; select * from damon_test_you_shouldnt_see_me”
  2. Changed database context to ‘mydb’.
  3. Msg 208, Level 16, State 1, Server *****, Line 1
  4. Invalid object name ‘damon_test_you_shouldnt_see_me’.

and this command passes as expected (table does exist):

  1. $ sqlcmd -Q “use mydb; select * from damon_test_table_should_persist”
  2. Changed database context to ‘mydb’.
  3. ID        
  4. -----------
  5. (0 rows affected)

SUMMARY:
It seems that Liquibase is properly evaluating and running the IF blocks when needed, but, it seems it’s also diving into the IF blocks and evaluating them, whether they should be ran. How can I avoid this?

2

It may be a problem with how liquibase tries to break up the SQL statement into separate blocks to feed to the database. Liquibase should just be dividing them up with a line separator regular expression on the GO but if it was dividing it up so the IF is one statement and the inner block is another it may still always evaluate the inside block.  If that was the case, though, I would think the IF portion would give a syntax error since it is probably not a full statement.


To be sure, you could use splitStatements: false in your changeSet definition. That will cause liquibase to send the whole string to the database as is.


Nathan

3

I forgot about the splitStatements; I had used it once or twice before but back when I first started putting this in place. Thanks!