Sergi, thanks for advice, I've added ORDER BY, but it doesn't change the results. My problem is that record count doesn't reduce when I am icnrementing offset. Updated 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 ORDER BY filter1 offset 0) ORDER BY id; //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 ORDER BY filter1 offset 1) ORDER BY id; //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 ORDER BY filter1 offset 2) ORDER BY id; //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 ORDER BY filter1 offset 3) ORDER BY id; //expected: empty ouptut //actual: empty output SELECT id FROM test_offset WHERE filter1 IN(SELECT DISTINCT filter1 FROM test_offset ORDER BY filter1 offset 4) ORDER BY id; On Tuesday, October 4, 2016 at 2:40:18 PM UTC+6, Sergi Vladykin wrote: > > 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] > <javascript:>>: > >> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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.
