Pasted from comments in one my database project files:
-----
--SQLite 3.12.2 SCALARS NOT VISIBLE TO CORRELATED SUBQUERY LIMIT CLAUSE.
--Observe that "s.off" is a scalar constant with respect to subquery "v" 's
scope in the following example.
with data(v) as (values ('A'),('B'),('C')), selector(off) as (values
(2),(1))
select *,(select v from data limit 1 offset s.off)v from selector s;
--OUTPUT: 'Error: no such column: s.off'
--Expected output would be the two rows {(2,B),(1,A)}, with column names
(off,v).
--It is worthwhile to note that column expressions, WHERE clauses, and
HAVING clauses
--have the expected correlated subquery scalar scoping including at
arbitrary depth of
--nested correlated FROM clause. It is only the LIMIT and ORDER BY clauses
which
--are mysteriously globally scoped with respect to outer scalars.
-----
Yes, I do appreciate my example is contrived. But the place in my code
where the
expected capability would have been better than the workaround data caching
table, trigger,
and view 'warts' is not contrived or trivial. The example is merely the
briefest bug/QA
regression case I could think of to write in the comment above the ugly
workaround.
I am eager to read comments. I would call this a bug report rather than
feature request
since the expected result is reasonable and the choice of subquery scalar
scoping
for LIMIT seems arbitrary.
Is there some documentation I may read which explains this design choice
for the LIMIT clause of correlated subqueries?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
