DbUnit HSQLDB - invalid schema name: C

Question

When running a test with the following query, HSQLDB mistakes the table alias as a schema.

SELECT c.country_ml2country as CTRY_PK, c.name as CTRY_NAME,
l.name as LANGUAGE_NAME, l.code as LANGUAGE_CODE
FROM country_ml as c, language as l
WHERE c.language(+) = l.id and c.country_ml2country(+) = ?
ORDER BY l.name ASC;

Has anyone experienced this before? If "yes", then what is the fix?

Note that if I change FROM country_ml as c to FROM country_ml as bob, then the error message changes accordingly to invalid schema name: BOB.

Answer 1

The problem is the non-standard Oracle-style OUTER JOIN syntax, which is specific to Oracle and not supported by other SQL dialects.

WHERE c.language(+) = l.id and c.country_ml2country(+) = ?

You should use instead the following standard syntax, which Oracle also supports:

SELECT c.country_ml2country as CTRY_PK, c.name as CTRY_NAME,
l.name as LANGUAGE_NAME, l.code as LANGUAGE_CODE
FROM country_ml as c RIGHT OUTER JOIN language as l
ON c.language = l.id and c.country_ml2country = ?
ORDER BY l.name ASC