Hello! I am using h2 version - 1.4.192 and faced problem with query results.
I am trying to use query that includes subquery with offset inside IN() function. Here is test_script: CREATE TABLE IF NOT EXISTS test_offset (id int, filter1 char(50), filter2 char(50)); TRUNCATE TABLE test_offset; INSERT INTO test_offset VALUES (1, 'filter1_1', 'filter2_1'); INSERT INTO test_offset VALUES (2, 'filter1_2', 'filter2_2'); INSERT INTO test_offset VALUES (3, 'filter1_3', 'filter2_3'); INSERT INTO test_offset VALUES (4, 'filter1_4', 'filter2_4'); SELECT * FROM test_offset; //expected: all 4 ids to return. //actual: matches expected. SELECT id FROM test_offset WHERE filter1 IN(SELECT DISTINCT filter1 FROM test_offset offset 0); //expected: ids 2-4 to return //actual: all 4 ids are returned SELECT id FROM test_offset WHERE filter1 IN(SELECT DISTINCT filter1 FROM test_offset offset 1); //expected: ids 3-4 to return //actual: all 4 ids are returned SELECT id FROM test_offset WHERE filter1 IN(SELECT DISTINCT filter1 FROM test_offset offset 2); //expected: one id - 4 to return //actual: all 4 ids are returned SELECT id FROM test_offset WHERE filter1 IN(SELECT DISTINCT filter1 FROM test_offset offset 3); //expected: empty ouptut //actual: empty output SELECT id FROM test_offset WHERE filter1 IN(SELECT DISTINCT filter1 FROM test_offset offset 4); As you see offset in subquery is applied with some strange logic, it works only for all or empty results. I realize that query could be rewritten using INNER JOIN. But project specific forced us to use subquery with offset inside IN() function and this functionality seems to be broken. Please, help with resolving this issue. -- 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.
