Problem: Oracle Case-Sensitive Columns Liquibase v3.0.7

General Discussion
1

I’ve switched from Liquibase Version 3.0.2 to the new 3.0.7. With the new Version i’m not able to create a “password” column and add a check constraint to it. Normally oralce should be case-insensitive and create the Table with no problem. But with the new Version the column will be created case-sensitive!

Database description of the table:

ID                   NOT NULL NUMBER(38)
…  
USERID         NOT NULL VARCHAR2(255)
password                              VARCHAR2(255)
FIRSTNAME NOT NULL VARCHAR2(255)
LASTNAME  NOT NULL VARCHAR2(255)
…


Expected Database description of the table:

ID                   NOT NULL NUMBER(38)
…  
USERID         NOT NULL VARCHAR2(255)
PASSWORD                        VARCHAR2(255)
FIRSTNAME NOT NULL VARCHAR2(255)
LASTNAME  NOT NULL VARCHAR2(255)
…



In the XML File the createTable Statement is defined like this:
  1.  
  2.     User Table
  3.    
  4.        
  5. …
  6.        
  7.        
  8.        
  9.        
  10. …
  11.    
  12.  
  13.  
  14.     Primary key erstellen
  15.    
  16.  

If I write the definition in UPPER-CASE everything works like expected.


Check Constraint definition:


  1.     Password LowerCase Check Constraint
       
            alter table t_users add constraint chk_users_passwordlc check(password = lower(password))
       
       
           
                alter table t_users drop constraint chk_users_passwordlc
           
       
     


Is this a Bug or did I miss something?


Best regards

André

2

Password was added to what liquibase knows are reserved words in Oracle which causes it to be quoted in the SQL which them makes it case sensitive in oracle.


It is on the list of reserved words http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm, so I wouldn’t think you should have been able to create the column without the quotes before. It was working in 3.0.2 though?


Nathan

3

Yes it was working with 3.0.2. And it even works with v3.0.7 if I use the uppercase “PASSWORD” definition. What will be the future of this behavior? I mean you’re right, it is a reserved word by oracle but it works for a column name.

Best regards

André

4

I’m not sure what the right approach is. Since it is a reserved word, I feel like it should be quoted to ensure compatibility, even if it happens to work now. However, if people aren’t expecting it to be quoted and you suddenly get unexpected case sensitivity issues, that is a problem too.

Probably the best approach is to uppercase reserved words that get quoted. That will probably do what people want most of the time.


I created https://liquibase.jira.com/browse/CORE-1574 to track the change.


Nathan



5

Hard to tell if you did something wrong without seeing what you did. Have you debugged through it to see what the code is doing?

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

6

I would like to be able to extend the liquibase.database.core.OracleDatabase Class to override the “isReservedWord” Method to work around this issue without modifying my changeSet statements.

My current extension seems to not be picked up by Liquibase, as it defaults to it’s own OracleDatabase Class.

Is it not possible to do this kind of workaround in current Liquibase (v3.8) or did i do something wrong?

7

Turns out I did something wrong. My database extension was placed in the package liquibase.ext.database instead of liquibase.database.ext as specified in https://liquibase.jira.com/wiki/spaces/CONTRIB/pages/2424845/Database

Thanks for trying to help!