android sqlite query with offset doesn't return desired rows

Question

I'm using the following code to get a list of notifications (total rows are 21):

List<Notification> list = new ArrayList<Notification>();
Cursor c = _db.query(TABLE_NAME, COL_ALL, null, null, null, null, order, get_limitStr(offset));
if(c != null && c.moveToFirst())
{
    while(!c.isAfterLast())
    {
        Notification model = cursorToModel(c);
        if(model != null)
        {
            list.add(model);
        }
        c.moveToNext();
    }
    c.close();
}

and the generated query for offset = 0 is

SELECT Id, Token, Title, Read, Message, Image, CreateDate, CreateDateFA FROM Notifications ORDER BY CreateDate DESC LIMIT 20,0

and it works as it's supposed to and returns 20 rows, when I increase offset by 1 (offset = 1) it returns only 1 row which is correct but the problem is when the offset is bigger than 1 then the query will be

SELECT Id, Token, Title, Read, Message, Image, CreateDate, CreateDateFA FROM Notifications ORDER BY CreateDate DESC LIMIT 20,2

and I thought it supposed to skip 20 * 2 rows and then starts taking rows from there, which either my thought or my query is wrong. What am I doing wrong here? Thanks

Answer 1

LIMIT 20,2

and I thought it supposed to skip 20 * 2 rows and then starts taking rows from there, which either my thought or my query is wrong.

LIMIT 20,2 skips first 20 rows and returns at most 2 remaining rows. It's the same as LIMIT 2 OFFSET 20.

Even the documentation says it's counter-intuitive:

Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma. In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression. This is counter-intuitive, as when using the OFFSET clause the second of the two expressions is the OFFSET and the first the LIMIT. This reversal of the offset and limit is intentional - it maximizes compatibility with other SQL database systems. However, to avoid confusion, programmers are strongly encouraged to use the form of the LIMIT clause that uses the "OFFSET" keyword and avoid using a LIMIT clause with a comma-separated offset.

If you want to achieve sort of result paging with page size of 20, use something like OFFSET k*20 LIMIT 20 where k is your zero-based page number.