CODEWITHSUNDEEP
sqlite
LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

OFFSET and LIMIT expressions scope

I found following limitation(?) in SQLite (3.7.16.2):

Imagine following schema:

CREATE TABLE t1 (v);
CREATE TABLE t2 (v);

If a have a sub-query like this,

SELECT *, (SELECT COUNT() FROM t2 WHERE t2.v = t1.v) FROM t1;

it works, meaning that in sub-query on t2 you can refer outer query (on t1) columns.

However,

SELECT *, (SELECT COUNT() FROM t2 LIMIT t1.v) FROM t1;

or

SELECT *, (SELECT COUNT() FROM t2 LIMIT 1 OFFSET t1.v) FROM t1;

fails with error Error: no such column: t1.v.

This means that expressions within OFFSET and LIMIT clauses can't refer columns from outer queries.

Is this behaviour a limitation on SQLite engine?

Or am I missing some point?

Upvotes: 2

Views: 204

Answers (1)

CL.
CL.

Reputation: 180172

This is indeed a limitation in all SQLite versions so far. (OFFSET/LIMIT is a non-standard extension, so it is not specified whether this should be allowed.)

Upvotes: 2

Related Questions