sql - IN and ANY not working as expected in H2 (with examples) -

edit: appears bug in h2 version 1.4.190. have updated post below. problem not happen in version 1.3.176 or 1.4.191.

(suggestions optimizing queries welcome)

consider following table data:

create table users (name varchar(255), age int, sal int);  insert users values('alice', 20, 25000); insert users values('bob', 25, 20000); insert users values('carol', 30, 30000); insert users values('jim', 19, 10000); insert users values('max', 59, 50000); insert users values('alice', 28, 18000); insert users values('bob', 43, 48000); 

consider following queries:

a. find sals of users skipping first 2 when sorted descending

select      users.sal user_sal       users  order users.sal desc limit 10 offset 2  30000 25000 20000 18000 10000 

b. want find age of users match query a. have following query:

select      users.age user_age      (select          users.sal user_sal               users      order sal desc limit 10 offset 2) t,      users       users.sal = user_sal   20 25 30 19 28 

this seems give me right data.

c. want rewrite (b) using in tried:

select      users.age user_age       users       users.sal in (select                      users.sal user_sal                                         users                    order sal desc limit 10 offset 2)  

it not return anything, while expect return same data (b).

d. tried rewriting (b) as:

select      users.age user_age       users       users.sal = (select                          users.sal user_sal                                                users                       order sal desc limit 10 offset 2)  

it not return anything, while expect return same data (b).

e. finally, want find complement of (a) use:

select     users.age user_age      users      users.sal <> (select                          users.sal user_sal                                                users                       order sal desc limit 10 offset 2)   59 43 

(e) gives me right data. why not (c) , (d)?

h2 version 1.4.190

edit: seems bug in h2. version 1.3.176 gives correct results (c) , (d).