Hi,
While playing with LATERAL along with some aggregates in sub-query, I have
observed somewhat unusual behavior.
Consider following steps:
create table tab1(c1 int, c2 int);
insert into tab1 select id, 1 from generate_series(1, 3) id;
create function sum_tab1(extra int) returns setof bigint as $$
select sum(c1 + extra) sum from tab1 group by c1
$$ language sql;
-- This gives wrong output
select c1, c2, sum from tab1 t1, lateral
(select sum(t2.c1 + t1.c1) sum from tab1 t2 group by t2.c1) qry
order by 1, 2, 3;
-- This gives correct output
select c1, c2, sum from tab1 t1, lateral
(select sum_tab1 sum from sum_tab1(c1)) qry
order by 1, 2, 3;
I would expect same result from both these queries, but unfortunately not.
Per my understanding, second query involving function gives me correct result
where as first query's output seems wrong.
Is this an expected behavior OR we are giving wrong result in case of first
query?
Thanks