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

514 Views Asked by At

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 SALs 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).

0

There are 0 best solutions below