Actually it is wrong to use LIMIT/OFFSET without ORDER BY clause. I'm not sure may be there is still a bug, but for your exact query any result may be considered as valid. Just because each time the subquery executed it is allowed to fetch the table in any random order.
Sergi 2016-09-28 13:29 GMT+03:00 Artem Dippel <[email protected]>: > Hi, I am using h2 version 1.4.192. > I am trying to filter query records by subquery values, using offset. > I've got test script > > > CREATE TABLE test_offset( >> id VARCHAR(50), >> filter VARCHAR(50) >> ); >> INSERT INTO test_offset VALUES('2', 'filter2'); >> INSERT INTO test_offset VALUES('3', 'filter3'); >> INSERT INTO test_offset VALUES('4', 'filter4'); >> INSERT INTO test_offset VALUES('5', 'filter5'); >> INSERT INTO test_offset VALUES('6', 'filter6'); >> > > >> select id >> from test_offset >> where filter in >> (select filter >> from test_offset >> limit 10 offset 0); >> > > >> select id >> from test_offset >> where filter in >> (select filter >> from test_offset >> limit 10 offset 1); >> > > >> select id >> from test_offset >> where filter in >> (select filter >> from test_offset >> limit 10 offset 2); > > > select id >> from test_offset >> where filter in >> (select filter >> from test_offset >> limit 10 offset 3); >> > > >> select id >> from test_offset >> where filter in >> (select filter >> from test_offset >> limit 10 offset 4); > > > So I go table TEST_OFFSET with records: > ID > <http://127.0.1.1:8082/query.do?jsessionid=a0421a5bb211b20229a967b46bbea83f#> > FILTER > <http://127.0.1.1:8082/query.do?jsessionid=a0421a5bb211b20229a967b46bbea83f#> > 2 filter2 > 3 filter3 > 4 filter4 > 5 filter5 > 6 filter6 > > > I expected results of queries will be different and depend on offset value > (decreased by one record for each next query), but actually all select > queries results are the same: > > ID > <http://127.0.1.1:8082/query.do?jsessionid=a0421a5bb211b20229a967b46bbea83f#> > 2 > 3 > 4 > 5 > 6 > It might look like offset parameter is not applied at all, but if we > execute query: > > select id >> from test_offset >> where filter in >> (select filter >> from test_offset >> limit 10 offset 5); > > > > No rows are returned. > > So I am wondering how offset parameter works in subqueries in my case? > > > -- > 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.
