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.