HSQLDB No such table Exception

I've encountered a strange problem using HSQLDB which became totally weird when using that database in conjunction with Hibernate formulas. Here is the problem and the specific issue.

I've a table named group (lower case) and a table named property  (lower case) in a schema named auth (lower case too, for naming convention) and I want to create them both on HSQLDB. I know group is a reserved word in SQL so I've created my DDL statements accordingly:

    create schema auth authorization DBA;

    create table auth."group" (
        "id" bigint generated by default as identity (start with 1),
        "description" longvarchar,
        primary key ("id")
    );

    create table auth."property" (
        id bigint generated by default as identity (start with 1),
        handler varchar(255),
        primary key (id)
    );

As you can see I've double quoted the structure element names in table group to avoid the reserved word problem (I could limit myself to the table name, but this doesn't make any difference) and I've used the same notation for the table property name too (not needed but this clarifies my example). Now I wish to query that database with a query like

select * from auth."group"

which correctly executes and returns the results, but a query like

select * from auth.property

fails with a No such table exception !?!

Well the problem is HSQLDB converts all identifiers to upper case unless you use the double quote notation!!!! The query then should be issued as

select * from auth."property"

If you query the database meta data you can see the problem in the auth schema name: it's real name is AUTH, all uppercase letters!

The problem here is HSQLDB is case sensitive but implicitly converts all your table names and column names to upper case! Yes the problem occur on column names too, in fact the following query fails with a No such column exception:

select "id" from auth."property"

Thats because the id column was implicitly renamed to ID... sigh!

Ok, this is a problem, but it's still not a great problem, you just use double quotes consistently through all your project (I had no choice to use double quotes everywhere) and you can forget the problem just treating HSQLDB as a case sensitive database.

If you wish to use Hibernate to query such a database you have to use the special single quote character ` (sorry, I haven't found a better name for it) instead of double quotes inside your HBMs to let Hibernate substitute the ` char with the " char (to avoid XML issues).

Well, still no unresolvable problem until now, but if you want to write an Hibernate formula property... BANG! With an Hibernate formula property in fact you can write your own SQL statement which will be executed to populate that property, but you can't use nor double quotes nor the ` char to escape a column name there! Well the last statement is not completely true as you can use the ` tinstead of the double quotes, but in this case you can use only fields of the table your class is mapped onto... which makes formulas quite unuseful.

I'm actually trying to help the Hibernate developers to solve the problem... I'll update this post if I found a solution as Hibernate user or developer.