[Bug] Using Limit and Offset causes SQL exception.

[wojciechsura]

I have the following code:

public Operation getLocalOperation(int mostRecentOperation, int skip) {

        try {

            Operation operation = databaseHelper.getOperationDao().queryBuilder()
                    .orderBy(Operation.ID_FIELD, true)
                    .offset((long)skip)
                    .where()
                    .isNull(Operation.FOREIGN_USER_FIELD)
                    .and()
                    .ge(Operation.ID_FIELD, mostRecentOperation)
                    .queryForFirst();

            return operation;

        } catch (SQLException e) {

            throw new RuntimeException("Cannot get local operation");
        }
    }

Running this code on Android 30 yields the following exception:

SELECT * FROM Operations WHERE (ForeignUserIS NULL ANDId>= 0) ORDER BYId OFFSET 0 LIMIT 1

However, the query fails with the following exception and cause:

Problems executing Android query: SELECT * FROM `Operations` WHERE (`ForeignUser` IS NULL AND `Id` >= 0) ORDER BY `Id` OFFSET 0 LIMIT 1
near "OFFSET": syntax error (code 1 SQLITE_ERROR[1]): , while compiling: SELECT * FROM `Operations` WHERE (`ForeignUser` IS NULL AND `Id` >= 0) ORDER BY `Id` OFFSET 0 LIMIT 1

I'm using com.j256.ormlite:ormlite-android:6.1 on Android 30 (Samsung Galaxy S9+).

[noordawod]

LIMIT should appear before OFFSET, ie:

SELECT * FROM `Operations` WHERE (`ForeignUser` IS NULL AND `Id` >= 0) ORDER BY `Id` LIMIT 1 OFFSET 0

@j256 this is strange, ehh?

[j256]

Huh. The code definitively appends the limit before the offset:

https://github.com/j256/ormlite-core/blob/master/src/main/java/com/j256/ormlite/stmt/QueryBuilder.java#L551

Seems like it has been this way for a long time too – at least 4 years. Hey @wojciechsura , can we get the entire exception?

[j256]

Wait. I see a possible problem. The SqliteDatabaseType in JDBC has the limit being LIMIT offset-number, limit-number. I wonder if this code should be in the BaseSqliteDatabaseType.

https://github.com/j256/ormlite-jdbc/blob/master/src/main/java/com/j256/ormlite/jdbc/db/SqliteDatabaseType.java#L35

I can't figure out if Android supports OFFSET keyword or the LIMIT o,l format. Or both. Regardless I don't understand how OFFSET got ahead of LIMIT. Maybe something is translating the query somehow?