Left outer joins producing different results in memory vs on disk

[GoogleCodeExporter]

Using H2 1.1.119, our tests (in Apache Cayenne) can correctly produce the join, 
but using H2 1.2.143 and 1.2.142 the tests fail.  H2 1.2.143 produces one 
record if you are using an on-disk database, but produces three records if 
using an in-memory database (using the SQL below).  H2 1.1.119 produces one 
record for on-disk and in-memory.

What is the expected output?  What do you see instead?

We were expecting one record in our tests.  Saw three.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.2.143 on OS X and Ubuntu.

Do you know a workaround?

Downgrade H2 versions.

How important/urgent is the problem for you?

Not urgent, since we downgraded to fix the build.

In your view, is this a defect or a feature request?

Defect.  Worked in an earlier version of H2.

Please provide any additional information below.

Here is the SQL that will illustrate the problem:

CREATE TABLE ARTIST (ARTIST_ID BIGINT NOT NULL, ARTIST_NAME CHAR(254) NOT NULL, 
DATE_OF_BIRTH DATE NULL, PRIMARY KEY (ARTIST_ID));
CREATE TABLE PAINTING (ARTIST_ID BIGINT NULL, ESTIMATED_PRICE DECIMAL(10, 2) 
NULL, GALLERY_ID INTEGER NULL, PAINTING_DESCRIPTION VARCHAR(255) NULL, 
PAINTING_ID INTEGER NOT NULL, PAINTING_TITLE VARCHAR(255) NOT NULL, PRIMARY KEY 
(PAINTING_ID));

INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (33001, 'B');
INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (33002, 'A');
INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (33003, 'D');
INSERT INTO PAINTING (PAINTING_ID, ARTIST_ID, PAINTING_TITLE, ESTIMATED_PRICE) 
VALUES (33009, 33001, 'X', 5000);
INSERT INTO PAINTING (PAINTING_ID, ARTIST_ID, PAINTING_TITLE, ESTIMATED_PRICE) 
VALUES (33010, 33001, 'Y', 5000);
INSERT INTO PAINTING (PAINTING_ID, ARTIST_ID, PAINTING_TITLE, ESTIMATED_PRICE) 
VALUES (33011, 33002, 'Z', 5000);

SELECT t0.DATE_OF_BIRTH AS ec0_0, t0.ARTIST_ID AS ec0_2, t0.ARTIST_NAME AS 
ec0_1 FROM ARTIST t0 LEFT OUTER JOIN PAINTING t1 ON (t0.ARTIST_ID = 
t1.ARTIST_ID) WHERE t1.PAINTING_ID IS NULL;

Original issue reported on code.google.com by blackn...@gmail.com on 9 Oct 2010 at 5:02

[GoogleCodeExporter]

Thanks a lot! I can reproduce the problem and will fix it for the next release.

Original comment by thomas.t...@gmail.com on 11 Oct 2010 at 5:43

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.2.144

Original comment by thomas.t...@gmail.com on 15 Oct 2010 at 6:29

• Changed state: Fixed

[GoogleCodeExporter]

I switched our POM over to 1.2.144 and all of our test cases run perfectly now. 
 Thanks for the fast fix!

Original comment by blackn...@gmail.com on 22 Oct 2010 at 12:57