Issue is explained here
http://stackoverflow.com/q/35274539/243233
Essentially queries such as:
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)
do not return data.
Copy-pasting the entire post below:
---------------------------------------
Consider the following table with data:
CREATE TABLE USERS (NAME VARCHAR(255), AGE INT, SAL INT,CHECK(AGE >=
0),CHECK(SAL >= 0));
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
3000025000200001800010000
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
2025301928
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)
5943
(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).
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
