#1398 Where exists using an temp table with join has no results

[Bjarni van Berkum]

In a specific case, when using a 'where exists' clause the hsqldb should return results, but it doesn't. The next query is used:
SELECT *
FROM (
SELECT T1.ID AS ID
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON ( T1.ID = T2.CASEID )
) TABLE_ALIAS
WHERE EXISTS (
SELECT 1
FROM TABLE3 T3
WHERE (TABLE_ALIAS.ID = T3.CASEID)
)

Strange thing is: when the 'left outer join' clause on TABLE2 is removed, the results are correct.

[Fred Toussi]

You need to provide CREATE TABLE statements. Please also test with the HSQLDB 2.3.3 Release Candidate (avialable from hsqldb.org).

[Bjarni van Berkum]

We've found the issue on the 2.3.2 and tested it on the 2.3.3 RC which resulted in the same problem.

Hereby the create and insert statements to reproduce the problem:
CREATE TABLE TABLE1 ( ID BIGINT NOT NULL, PRIMARY KEY(ID));
CREATE TABLE TABLE2 ( ID BIGINT NOT NULL, CASEID BIGINT NOT NULL, PRIMARY KEY(ID));
CREATE TABLE TABLE3 ( ID BIGINT NOT NULL, CASEID BIGINT NOT NULL, PRIMARY KEY(ID));

INSERT INTO TABLE1(ID) VALUES ('1');
INSERT INTO TABLE2(ID, CASEID) VALUES ('1','1');
INSERT INTO TABLE3(ID, CASEID) VALUES ('1','1');

[Fred Toussi]

This has been fixed and the example works with the latest SVN code. You can checkout the SVN /base/trunk and use the Gradle GUI build, or the Ant build.