QueryBuilder's limit(0) returns all rows for some database types?

[stefanofornari]

Hello, based on the javadoc

public QueryBuilder<T,ID> limit(Long maxRows)

Limit the output to maxRows maximum number of rows. Set to null for no limit (the default).

I expect that limit(0) returns 0 rows. Is my expectation correct? If so, I think there is a bug because

QueryBuilder qb = dao.queryBuilder().offset(0).limit(0);

returns all values.

[j256]

Hrm. Can you take a look at the generated query in the logs to see if the query is correct? In looking at the code, I don't see any specific logic around the limit value of 0 but this may be a problem with your database? What type are you using?

I just wrote a test for this and all of my test database types passed fine but Derby and Hsql returned all results if limit(0). Derby doesn't support the limit (or at least my database type class doesn't) so the limit argument is ignored. Maybe I should throw an exception if the limit is used? Certainly there should be some docs on limit to say that it is database specific.

Also HSQL doesn't seem to work either although I think ORMLite is generating the right query:

StatementExecutor query of 'SELECT LIMIT 0 0 * FROM "FOOTABLE"' with 0 args returned 2 results

[stefanofornari]

Hi, attached my sample project and logs. It looks like HSQL supports it, but LIMIT 0 0 returns all rows.

Specifying it in the doc would be good indeed; Maybe instead of an exception it could log a WARNING? (is there a general policy in ormlite for similar cases?).

What about a WARNING + a default implementation for the DBs that do not support it? Not efficient, but helpful.

BTW see the test, limit(x) works as expected with the exception of limit(0). log.txt ormtest.zip