Bug(?) in columns available in 'offset' clause of sub-select
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;
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.