Query with OFFSET or LIMIT without ORDER BY makes no sense, it can fetch data in any random order, thus any result is possible. Try applying ORDER BY in subquery for predictable results.
Sergi 2016-10-04 11:25 GMT+03:00 Artem Dippel <[email protected]>: > 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. > -- 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.
