Thank you Jayaram for the patch.
My first general comment would be for the creation and cleanup of schema objects, you use CleanDatabaseTestSetup and decorateSQL to create the objects such as tables and views up front and then let them get cleaned up automatically at the end of the test. You can then remove the drop statements from the test. If any table names are reused in the test you will have to choose distinct names so there is no collision. One big advantage of doing it this way is that if the test fails for some reason we are assured that things get cleaned up properly and also declutters the test. Take a look at GroupByTest for an example of how to do this.
My second would be that it would be good to break the test up into multiple fixtures instead of just one big one, for example maybe testIdentity testIdentityWithStartValue, testIdentityWithIncrement, etc. Just go down through the autoincrement.out file and group it into logical chunks and make sure each fixture has javadoc comments.
I did not check the out file against the new test, but noticed that the lock checking seems to be missing. You need to add the creation of the lock_table view and check the locks where they are being checked in the original test. The master/autoincrement.out file has the expected output.
Also a small thing, instead of doing:
Connection conn = getConnection();
Statement s = conn.createStatement();
to get a statement, just do
Statement s - getStatement();
Here again the statement will get cleaned up automatically, so you don't have to put the cleanup code in the test.
There are other built in helper methods too, like
conn.commit(); -> commit()
conn.setAutoCommit(true); -> setAutoCommit(true);
Take a look at BaseJDBCTestCase to see what is available.