'afterColumn' and 'position' not working in Firebird and Oracle databases

[kuhjunge]

Environment

Liquibase Version: 3.8.5 - 4.1

Liquibase Integration & Version: gradle

Liquibase Extension(s) & Version:

Database Vendor & Version: firebird 2.5, oracle

Operating System Type & Version: win 10 java 8

Description

The 'addColumn' tag supports 'afterColumn' and 'position', but these tags are ignored in the SQL generation in Firebird and Oracle ( https://docs.liquibase.com/concepts/advanced/column.html ). Therefore it is not possible to insert a column at a specific position in Firebird or after a column in Oracle.

There is also no documentation on which dbms supports 'afterColumn' or 'position'.

Steps To Reproduce

JUnit Test:

 @Test
  void testLiquibaseOrderBug() throws IOException {
    DatabaseChangeLog log = new DatabaseChangeLog();
    ChangeSet set = new ChangeSet("Test", "test", Boolean.FALSE, Boolean.FALSE, null, "context",
                                  "firebird", log);
    AddColumnChange addcol = new AddColumnChange();
    addcol.setTableName("MI6");
    AddColumnConfig addColumnConfig = new AddColumnConfig();
    addColumnConfig.setType("int");
    addColumnConfig.setValueNumeric(0);
    addColumnConfig.setName("James");
    addColumnConfig.setPosition(2);
    addcol.addColumn(addColumnConfig);
    set.addChange(addcol);

    String[] changesets = getXmlChangelogAsArray(set);
    assertEquals("<column name=\"James\" position=\"2\" type=\"int\" valueNumeric=\"0\"/>",changesets[4].trim());

    assertEquals("ALTER TABLE MI6 ADD James INT POSITION 2;", getLiquiSql("firebird", set));
  }

  @Test
  void testLiquibaseAfterBug() throws IOException {
    DatabaseChangeLog log = new DatabaseChangeLog();
    ChangeSet set = new ChangeSet("Test", "test", Boolean.FALSE, Boolean.FALSE, null, "context",
                                  "oracle", log);
    AddColumnChange addcol = new AddColumnChange();
    addcol.setTableName("MI6");
    AddColumnConfig addColumnConfig = new AddColumnConfig();
    addColumnConfig.setType("int");
    addColumnConfig.setValueNumeric(0);
    addColumnConfig.setName("James");
    addColumnConfig.setAfterColumn("Bond");
    addcol.addColumn(addColumnConfig);
    set.addChange(addcol);

    String[] changesets = getXmlChangelogAsArray(set);
    assertEquals("<column afterColumn=\"Bond\" name=\"James\" type=\"int\" valueNumeric=\"0\"/>",changesets[4].trim());

    assertEquals("ALTER TABLE MI6 ADD James INTEGER AFTER Bond;", getLiquiSql("oracle", set));
  }

  private String[] getXmlChangelogAsArray(ChangeSet set) throws IOException {
    List<ChangeLogChild> changes = new ArrayList<>();
    changes.add(set);
    ByteArrayOutputStream stream = new ByteArrayOutputStream();
    ChangeLogSerializer serializer = new XMLChangeLogSerializer();
    serializer.write(changes, stream);
    String[] changesets = new String(stream.toByteArray()).replaceAll("\r", "")
      .split("\n");
    return changesets;
  }
  
  private String getLiquiSql(String dbms, ChangeSet set) throws IOException {
    ByteArrayOutputStream stream = new ByteArrayOutputStream();
    ChangeLogSerializer serializer = new FormattedSqlChangeLogSerializer();
    set.setFilePath("export."+dbms+".sql");
    List<ChangeLogChild> changes = new ArrayList<>();
    changes.add(set);
    serializer.write(changes, stream);
    return new String(stream.toByteArray()).split("\n")[3];
  }

Actual Behavior

Positions and afterColumn tags are ignored.
An afterTable tag in Oracle leads to following sql: ALTER TABLE MI6 ADD James INTEGER
A position tag in Firebird leads to following sql: ALTER TABLE MI6 ADD James INT

Expected/Desired Behavior

<changeSet author="test" context="context" dbms="firebird" id="Test" objectQuotingStrategy="LEGACY"> <addColumn tableName="MI6"> <column name="James" position="2" type="int" valueNumeric="0"/> </addColumn> </changeSet>

should create in Firebird something like

ALTER TABLE MI6 ADD James INT POSITION 2

and

<changeSet author="test" context="context" dbms="firebird,oracle,mssql" id="Test" objectQuotingStrategy="LEGACY"> <addColumn tableName="MI6"> <column afterColumn="Bond" name="James" type="int" valueNumeric="0"/> </addColumn> </changeSet>

should create in Oracle something like

ALTER TABLE MI6 ADD James INTEGER AFTER Bond

[chenmeister]

@molivasdat I like to work on this issue.

[molivasdat]

HI @chenmeister Thanks for signing up to help. I will assign this to you and look for a PR from you.

[theovanessen]

It also does not work on Postgresql for the properties position, afterColumn and beforeColumn.
Used Liquibase Version: 3.8.5 also.

[chenmeister]

@theovanessen There is no way to add the properties position, afterColumn, and beforeColumn in Postgres. You can only append it at the end of the table. See link below for explanation.

https://www.postgresqltutorial.com/postgresql-add-column/

[theovanessen]

@chenmeister Thanks for the replay. I learned something again today.

[molivasdat]

Hi @kuhjunge Can you share where you found that Oracle supports the after and before statements for column adds?
I could not find anywhere in the Oracle docs that showed
ALTER TABLE MI6 ADD James INTEGER AFTER Bond
was valid Oracle SQL supported code.
I tested the code that @chenmeister created for you and I get this error in Oracle

ORA-01735: invalid ALTER TABLE option
 [Failed SQL: (1735) ALTER TABLE JENKINSCI.MI6 ADD James INTEGER AFTER BOND]

I do see the docs that Firebird does support position based alters
https://firebirdsql.org/refdocs/langrefupd20-alter-table.html#langrefupd20-at-position
And we can fix that.

[molivasdat]

Hi @kuhjunge I also tried to recreate the error you are seeing in firebird.
I get an error when trying to use alter table add column in position x

ALTER TABLE MI6 ADD James INT POSITION 2;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 31
-POSITION
SQL> ALTER TABLE MI6 ADD James INT;
SQL> alter table mi6 alter james position 2;

using isql.
The only way that I saw this working is to add the column and then move the column as shown above by issuing 2 alter table statements. Do you have firebird working differently?

[kuhjunge]

Hi @molivasdat, it seems that I confused oracle commands with MySQL commands. I cannot find anything official now either. :(

I tested a firebird 2.5 database with the tool DBeaver and alter table mi6 alter james position 2 works fine there:
https://user-images.githubusercontent.com/2418297/103618895-0329e080-4f31-11eb-9b40-d539068182db.mp4

It should be noted in the liquibase documentation (here maybe?: https://docs.liquibase.com/concepts/advanced/column.html ) that 'position' and 'after' tag only works for certain Databases (Firebird and MySQL?) and not with for example Oracle, MS SQL and Postgresql. Otherwise I would expect, that these tags would work on every database.

[molivasdat]

Hi @kuhjunge Thanks for doing the research.
I found that the same alter table mi6 alter james position 2 works as the way you describe on Firebird 3.0 too.

but that means
<changeSet author="test" context="context" dbms="firebird" id="Test" objectQuotingStrategy="LEGACY"> <addColumn tableName="MI6"> <column name="James" position="2" type="int" valueNumeric="0"/> </addColumn> </changeSet>

is two sql statements
ALTER TABLE MI6 ADD James INT;
and
ALTER TABLE MI6 alter James position 2;

which means 2 alter table statements are needed for the single changeset on Firebird.
The first adds the column and the second moves it.

Where MYSQL is only one statement.
ALTER TABLE MI6 ADD James INT after bond;
Is that what you see? If so, we need to rethink the PR that was used to fix this issue.