How do limit and offset work ?

[Maciek]

Hi,
I am looking for some comments on how "select .. limit .. offset .."
works in H2.
It looks like limit and offset accept integer values (not long), so
the max allowed limit is 2147483647.
However, setting limit to 2147483647, and offset to any value higher
than 0 causes select to return no rows.
The real upper bound on limit and offset seems to be: (limit_value +
offset_value) < 2147483647

Is there any other simple way in H2 to set specify "no upper limit" on
the selected rows ? Postgres has "limit ALL". What would be the
equivalent in H2 ?

See below for a test snippet that reproduces the bug. query3 and
query5 return no rows, although they should.
The problem only exists when running the Java code. The same queries
run from the H2 web console work fine.

---------------------- 8< ---------------------------
package h2test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class Main {
public static void main(String[] args) throws Exception {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:abc/
test.db");
for (String sql : new String[]{
"create table person(id bigint auto_increment, name
varchar(100))",
"insert into person(name) values ('a')",
"insert into person(name) values ('b')",
"insert into person(name) values ('c')",
}) {
conn.prepareStatement(sql).executeUpdate();
}

String[] queries = new String[]{
"select * from person",
"select * from person limit 2",
"select * from person limit 2 offset 1",
"select * from person limit " + Integer.MAX_VALUE + "
offset 1",
"select * from person limit " + (Integer.MAX_VALUE - 1) +
" offset 1",
"select * from person limit " + (Integer.MAX_VALUE - 1) +
" offset 2",
"select * from person limit " + (Integer.MAX_VALUE - 2) +
" offset 2"
};

for (int i = 0; i < queries.length; i++) {
String query = queries[i];
System.out.println("query" + i + ": " + query);

showResultSet(conn.prepareStatement(query).executeQuery());
}
}

private static void showResultSet(ResultSet rs) throws Exception {
int colCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
StringBuilder b = new StringBuilder(" ");
for (int i = 1; i < colCount + 1; i++) {
b.append(rs.getString(i));
b.append(' ');
}
System.out.println(b.toString());
}
}
}
---------------------- 8< ---------------------------
query0: select * from person
1 a
2 b
3 c
query1: select * from person limit 2
1 a
2 b
query2: select * from person limit 2 offset 1
2 b
3 c
query3: select * from person limit 2147483647 offset 1
query4: select * from person limit 2147483646 offset 1
2 b
3 c
query5: select * from person limit 2147483646 offset 2
query6: select * from person limit 2147483645 offset 2
3 c
---------------------- 8< ---------------------------

Cheers,
Maciek

[Thomas Mueller]

Hi,

Thanks for your help! Yes this is a bug. It is fixed in the trunk
(http://code.google.com/p/h2database/source), and will be available in
the next release. A workaround is using LIMIT 0, which means 'no
limit'.

Regards,
Thomas