NOT IN Subquery

[Paul]

Hello,

I currently have a very simple database and i seem to be running into
a problem that i believe might be a bug. I am using H2 version 1.3.151
(2011-02-12).

The table is set up as follows;
CREATE TABLE UserDetails (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT
UserDetails_PK PRIMARY KEY,
UID CHAR(32) NOT NULL,
Agent VARCHAR(200),
Number VARCHAR(30)
);

The database is then populated with arbitrary data with lots of
identical data in the agent column.

I have written multiple statements as follows;
SELECT * FROM USERDETAILS WHERE AGENT IN (SELECT TOP 2 AGENT FROM
USERDETAILS ORDER BY AGENT );
SELECT * FROM USERDETAILS WHERE AGENT NOT IN (SELECT TOP 2 AGENT FROM
USERDETAILS ORDER BY AGENT );

My problem is that i am not getting the expected results. When i
remove the "ORDER BY AGENT" from the subquery i get the expected
result. The first statement returns all the results from the table not
filtering out anything and the second statement returns absolutely
nothing.

At first my subquery statements were allot more complicated with GROUP
BY's etc but i simplified it to try and explain my situation.
I have run tests on SQL server just to confirm its not my stupidity
and all is well there.

Any input or feedback is appreciated.

Paul

[Thomas Mueller]

Hi,

Could you try with the newest version of H2? I think this problem is fixed.

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>