SQLite limit is a string

Question

The Android API docs appear to suggest that the limit clause to provide when querying a SQLite database is a string.
This does not make much sense to me.
Presumably, it is converted internally to an integer?
Or are there other issues involved here?

Answer 1

I think they key thing to consider is that the parameter isn't solely for the single 1st part (see expr1 below) but for the entire LIMIT Clause.

This clause can be as as simple as just a single integer, but it can also be relatively complex; the full syntax of a LIMIT Clause is :-

LIMIT expr1 OFFSET (or ,) expr2; see - SELECT

Where :-

• expr1 should resolve to an integer specifying the maximum number of rows to be returned and
• expr2 is an integer that specifies the offset (where 1 is the 1st) from the start of the potential rows to be returned.

• either expression could, at least in theory, be a subquery e.g.

  • String limit_clause = "(SELECT numbertoshow FROM types WHERE id = (random() & 1)+1)";
  • P.S. not saying this is a useful example rather it is an example that works and is for illustration of a complex LIMIT clause and as such an example of why a String as opposed to an Integer is the more flexible/useful parameter type.

---

---

The complete SQL used for testing the above was :-

/*
DROP TABLE IF EXISTS basetable;
CREATE TABLE IF NOT EXISTS basetable (basename TEXT);
INSERT INTO basetable VALUES('test001');
INSERT INTO basetable VALUES('test002');
INSERT INTO basetable VALUES('test003');
INSERT INTO basetable VALUES('test004');
INSERT INTO basetable VALUES('test005');
INSERT INTO basetable VALUES('test006');
INSERT INTO basetable VALUES('test007');
INSERT INTO basetable VALUES('test008');
INSERT INTO basetable VALUES('test009');
INSERT INTO basetable VALUES('test010');
INSERT INTO basetable VALUES('test011');
INSERT INTO basetable VALUES('test012');
DROP TABLE IF EXISTS types;
CREATE TABLE IF NOT EXISTS types (id INTEGER PRIMARY KEY, typename TEXT, numbertoshow INTEGER);
INSERT INTO types VALUES(null,'type001',3);
INSERT INTO types VALUES(null,'type002',4);
*/
SELECT * FROM basetable LIMIT (SELECT numbertoshow FROM types WHERE id =  (random() & 1)+1);

• Note commented out statements are commented out as they are just needed the once.