EDIT: This appears to be a bug in H2 version 1.4.190. I have updated my post below. The problem does not happen in version 1.3.176 or 1.4.191.
(Suggestions for optimizing the queries also welcome)
Consider the following table with data:
CREATE TABLE USERS (NAME VARCHAR(255), AGE INT, SAL INT);
INSERT INTO USERS VALUES('alice', 20, 25000);
INSERT INTO USERS VALUES('bob', 25, 20000);
INSERT INTO USERS VALUES('carol', 30, 30000);
INSERT INTO USERS VALUES('jim', 19, 10000);
INSERT INTO USERS VALUES('max', 59, 50000);
INSERT INTO USERS VALUES('alice', 28, 18000);
INSERT INTO USERS VALUES('bob', 43, 48000);
Consider the following queries:
A. Find SAL
s of all users skipping first two when sorted descending
SELECT
USERS.SAL AS user_sal
FROM
USERS
ORDER BY USERS.SAL DESC LIMIT 10 OFFSET 2
30000
25000
20000
18000
10000
B. I want to find the AGE
of those users that match query A. So I have the following query:
SELECT
USERS.AGE AS user_age
FROM
(SELECT
USERS.SAL AS user_sal
FROM
USERS
ORDER BY SAL DESC LIMIT 10 OFFSET 2) AS T,
USERS
WHERE
USERS.SAL = user_sal
20
25
30
19
28
This seems to give me the right data.
C. I want to rewrite (B) using IN
so I tried:
SELECT
USERS.AGE AS user_age
FROM
USERS
WHERE
USERS.SAL IN (SELECT
USERS.SAL AS user_sal
FROM
USERS
ORDER BY SAL DESC LIMIT 10 OFFSET 2)
It does not return anything, while I expect it to return the same data as (B).
D. I tried rewriting (B) as:
SELECT
USERS.AGE AS user_age
FROM
USERS
WHERE
USERS.SAL = ANY (SELECT
USERS.SAL AS user_sal
FROM
USERS
ORDER BY SAL DESC LIMIT 10 OFFSET 2)
It also does not return anything, while I expect it to return the same data as (B).
E. Finally, I want to find the complement of (A) so I use:
SELECT
USERS.AGE AS user_age
FROM
USERS
WHERE
USERS.SAL <> ALL (SELECT
USERS.SAL AS user_sal
FROM
USERS
ORDER BY SAL DESC LIMIT 10 OFFSET 2)
59
43
(E) gives me the right data. So why not (C) and (D)?
H2 version 1.4.190
EDIT: Seems to be a bug in H2. Version 1.3.176 gives the correct results for (C) and (D).