Search code examples
hsqldbderbymybatissap-ase

Do Derby or HSQLDB support Sybase syntax for cross-schema joins?

I have a set of database schemas that looks like so, on Sybase ASE 15:

Schema S1:

CREATE TABLE T1 (T1KEY INT)

Schema S2:

CREATE TABLE T2 (T2KEY INT, T1FK INT)

T2's T1FK is basically a FK to T1's T1KEY column (not enforced by actual database business rules, but the values match).

In Sybase, it's legal to execute a join across these schemas like so:

SELECT a.T1KEY, b.T2KEY
  FROM S1..T1 a
  LEFT OUTER JOIN S2..T2 AS b ON (a.T1KEY = b.T1FK)

I haven't yet found any embedded database that will support this syntax. Derby and HSQLDB both seem to balk at the double-dot syntax, and even when that syntax is replaced by, for instance, S1.dbo.T1, neither DB is a go. I am using MyBatis 3.1.1 to run the queries, which work fine in actual deployment against the ASE server.

I'd like to be able to stand up something light at unit test time that supports queries like this, but I know Sybase syntax tends to be ill-supported. I know for Oracle, HSQLDB supports a flag to enable compatibility with ORA-specific syntax. Are there some syntax flags I'm missing that would enable either of these DBs to process Sybase query syntax like the above? Is there any alternative product that supports this use case?

Thanks very much!

Solution

  • The syntax supported by HSQLDB is Standard SQL, which uses a single dot.

    SELECT a.T1KEY, b.T2KEY
  FROM S1.T1 a
  LEFT OUTER JOIN S2.T2 AS b ON (a.T1KEY = b.T1FK)

    As double dot never occurs in Standard SQL syntax, you can safely replace all occurences of double dot with single dot when you run the queries against HSQLDB.