#393 HSQLDB & DBUnit, Cannot find column from ResultSetMetaData when using allias on SQL query

[David Hodeffi]

I am using maven to build a project and got an error message:

o.d.database.DatabaseTableMetaData - Cannot find column from ResultSetMetaData             
info via DatabaseMetaData. Returning null.
java.lang.IllegalStateException: Did not find column 'T2_PRF_VALUE' for
<schema.table> 'PUBLIC.CDD_PRF_TABLE4' in catalog 'PUBLIC' because names do
not exactly match

I tried to dive into the DBUNIT and the HSQLDB Driver and didn't find any way to et over this warning.

my setup is (using maven):

pom.xml

    <dependency>
        <groupId>org.dbunit</groupId>
        <artifactId>dbunit</artifactId>
        <version>2.5.2</version>
    </dependency>
    <dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>2.3.3</version>
        <scope>test</scope>
    </dependency>

hsqldb.script

SET SCHEMA PUBLIC
ALTER USER SA SET INITIAL SCHEMA PUBLIC
ALTER USER SA SET LOCAL TRUE
GRANT DBA TO SA
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE PUBLIC.CDD_APP_TABLE1 (id INTEGER, value VARCHAR(20));
CREATE MEMORY TABLE PUBLIC.CDD_APP_TABLE2 (id INTEGER, value VARCHAR(20));
CREATE MEMORY TABLE PUBLIC.CDD_APP_TABLE3 (id INTEGER, value TIMESTAMP);
CREATE MEMORY TABLE PUBLIC.CDD_APP_TABLE4 (id INTEGER, value TIMESTAMP);
CREATE MEMORY TABLE PUBLIC.CDD_PRF_TABLE1 (id INTEGER, value VARCHAR(20));
CREATE MEMORY TABLE PUBLIC.CDD_PRF_TABLE2 (id INTEGER, value VARCHAR(20));
CREATE MEMORY TABLE PUBLIC.CDD_PRF_TABLE3 (id INTEGER, value TIMESTAMP);
CREATE MEMORY TABLE PUBLIC.CDD_PRF_TABLE4 (id INTEGER, value TIMESTAMP);

query.sql

SELECT T1.VALUE as T1_PRF_VALUE,
   T2.VALUE as T2_PRF_VALUE
FROM CDD_PRF_TABLE3 AS T1 inner join CDD_PRF_TABLE4 AS T2
ON T1.ID= T2.ID

Main.java

final public static String  URL  =  "jdbc:hsqldb:res:hsqldb/hsqldb";
final public static String  DRIVER  =  "org.hsqldb.jdbcDriver";
final public static String  USERNAME  =  "sa";
final public static String  PASSWORD  =  "";
final public static String  SCHEMA_NAME  =  "PUBLIC";

public static JdbcDatabaseTester createHsqldbConnection() throws ClassNotFoundException {
    return new JdbcDatabaseTester(DRIVER, URL,  USERNAME, PASSWORD, SCHEMA_NAME);
}

thanks for anyone who knows how to solve this warning.
BTW I have saw many questions about this issue but had not found any good answer

[Jeff Jensen]

Thanks for the report.

If you have time and interest, please create a test based on dbUnit master reproducing the issue and attach as a patch or make a merge request. This will enable more easily diagnosing the problem; even better if you also find the cause and the fix.