Self referential column may damage H2 database

Try the following on an H2DB that you don't mind having to delete. WARNING. THIS WILL DAMAGE THE DATABASE IRREVERSIBLY!

1. Create a table with self referential default column. E.g., use alter table to modify an ordinary table:

   create table if not exists BRICK_H2( ID tinyint );
   alter table BRICK_H2 alter column ID set default ifnull(
       (select max(ID) from BRICK_H2 for update)+1,0
   );
   

2. Close connections and shutdown database.

3. Start database again and try to connect. Observe connection failure at error:

Table "BRICK_H2" not found; SQL statement:
CREATE CACHED TABLE PUBLIC.BRICK_H开发者_Python百科2(
    ID TINYINT DEFAULT IFNULL(((SELECT
    MAX(ID)
FROM PUBLIC.BRICK_H2
    /* PUBLIC.BRICK_H2.tableScan */
FOR UPDATE) + 1), 0)
) [42102-155] 42S02/42102 (Help)

BTW. Please don't respond "use auto_increment". Yes, I know about auto_increment columns. Auto increment columns leave gaps after rollback and don't deal with all datatypes. For example how does one auto increment a column where the application uses an algorithm that is not "add a constant integer?" Also, there is no way to semsibly auto_increment a VARCHAR, but your application might have a perfectly sensible notion.

A solution is to use a Java function, such as:

drop all objects;
create table if not exists do_not_brick(id int);
create alias query as $$
String query(Connection conn, String sql) throws SQLException { 
  ResultSet rs = conn.createStatement().executeQuery(sql); 
  rs.next(); 
  return rs.getString(1);
}$$;
alter table do_not_brick alter column id set default
ifnull(query('select max(id) from do_not_brick for update')+1, 0);
insert into do_not_brick() values(), (), ();
select * from do_not_brick;

By the way, 'brick' is relative... you can still retrieve the data using the Recover tool. But of course it's not nice, and will be fixed in the next release. The problem is that H2 doesn't restrict what you can do in the default clause. Other databases simply don't allow any queries in the default clause, but I think that's timid (is this the right word?), I will try to find a better solution (possibly allow it). What do you suggest?