Using SELECT ... WHERE ... IN ( ) AND ... produces duplicate rows

[GoogleCodeExporter]

What steps will reproduce the problem?
We noticed that using queries of type
SELECT ID FROM TEST WHERE ID IN (1,2) AND AID=1;
results in duplicated rows.

CREATE TABLE TEST(
ID INT PRIMARY KEY,
AID INT
);
INSERT INTO TEST VALUES(1,1);
INSERT INTO TEST VALUES(2,1);
SELECT * FROM TEST;
SELECT ID FROM TEST WHERE ID IN (1,2);
SELECT ID FROM TEST WHERE ID IN (1,2) AND AID=1;
SELECT ID FROM TEST WHERE ID IN (1,2) AND AID=1 ORDER BY ID;

What is the expected output? What do you see instead?
SELECT * FROM TEST;
ID      AID  
1   1
2   1
(2 rows, 0 ms)

SELECT ID FROM TEST WHERE ID IN (1,2);
ID  
1
2
(2 rows, 0 ms)

SELECT ID FROM TEST WHERE ID IN (1,2) AND AID=1;
ID  
1
2
1
2
(4 rows, 0 ms)

SELECT ID FROM TEST WHERE ID IN (1,2) AND AID=1 ORDER BY ID;
ID  
1
2

What version of the product are you using? On what operating system, file
system, and virtual machine?
h2-1.2.129.jar

Do you know a workaround?
Use ORDER BY
e.g. SELECT ID FROM TEST WHERE ID IN (1,2) AND AID=1 ORDER BY ID;

How important/urgent is the problem for you?
very important

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

Best Regards

Original issue reported on code.google.com by gert.kre...@virtualforge.de on 25 Feb 2010 at 3:42

[GoogleCodeExporter]

Thanks a lot! This is a bug. It's really strange that there was no test case
yet for this kind of query. It is fixed in the trunk now.

Original comment by thomas.t...@gmail.com on 25 Feb 2010 at 11:32

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.2.130.

Original comment by thomas.t...@gmail.com on 26 Feb 2010 at 4:33

• Changed state: Fixed