Strange result with LATERAL query

From: Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Strange result with LATERAL query
Date: 2016-08-24 05:51:43
Message-ID: CAM2+6=VY8ykfLT5Q8vb9B6EbeBk-NGuLbT6seaQ+Fq4zXvrDcA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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
--
Jeevan B Chalke

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Sharma 2016-08-24 06:08:38 Re: "Some tests to cover hash_index"
Previous Message Claudio Freire 2016-08-24 05:44:04 Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)