SQLite Forum

Bug(?) in columns available in 'offset' clause of sub-select
Login

Bug(?) in columns available in 'offset' clause of sub-select

(1.1) By Grunthos on 2021-03-16 04:18:58 edited from 1.0 [link] [source]

I don't know if this is a bug, but it is certainly not the behaviour I expected. The example below is much less complex that the real-life example that generated it, and is the minimal example I could construct. I have confirmed the bug(?) in 3.35.1.

It seems that columns from an outer 'from' clause are available in all parts of the column-select statement except the 'offset' and 'limit' parts.

It seems reasonable to me to allow limit/offset to be derived from outer columns, but perhaps I am naive!

create table foo(f1);
insert into foo values (1),(2),(3),(4);

create table off(o1);
insert into off values (0);

-- This works
select * from foo order by f1 desc limit 1 offset (select min(o1) from off);

-- This does NOT; the column in 'from' tables NOT recognised in 'OFFSET' statement
select  o.*,
        (select f1 from foo f order by f1 desc limit 1 offset o.o1)
    from off o;
-- Results in "Error: no such column: o.o1"

-- Nether does this (in LIMIT clause):
select  o.*,
        (select f1 from foo f order by f1 desc limit o.o1)
    from off o;
-- Results in "Error: no such column: o.o1"


-- This DOES; column in 'from' tables IS recognised in WHERE statement
select  o.*,
        (select f1 from foo f where f1 > (o.o1-100) order by f1 desc limit 1 offset 1)
    from off o;

(2) By Keith Medcalf (kmedcalf) on 2021-03-16 15:17:06 in reply to 1.1 [link] [source]

I believe this was discussed before and the <expression> following limit or offset currently needs to be a constant expression independent of the context in which it is used.

(3) By Grunthos on 2021-03-19 04:56:54 in reply to 2 [source]

Thanks. Sadly had to solve this by using a CTE and a window function...but now I can get nth rows as needed.