Thread: Set returning functions in select column list
Can someone explain how set returning functions in a select clause work? It seems that it is doing some sort of implicit cross join. deliverance_development=# select id, generate_series(1, 3) from users; id | generate_series ----+----------------- 0 | 1 0 | 2 0 | 3 1 | 1 1 | 2 1 | 3 (6 rows) But if multiple set returning functions that return the same number of rows are in the same select it doesn't further cross join it. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 6) from users; id | generate_series | generate_series ----+-----------------+----------------- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 6 1 | 1 | 4 1 | 2 | 5 1 | 3 | 6 (6 rows) But if the set returning functions return a different number of rows then it goes back to a cross join. deliverance_development=# select id, generate_series(1, 3), generate_series(4, 5) from users; id | generate_series | generate_series ----+-----------------+----------------- 0 | 1 | 4 0 | 2 | 5 0 | 3 | 4 0 | 1 | 5 0 | 2 | 4 0 | 3 | 5 1 | 1 | 4 1 | 2 | 5 1 | 3 | 4 1 | 1 | 5 1 | 2 | 4 1 | 3 | 5 (12 rows) I really don't understand what is going on here. I have checked Google and the PostgreSQL docs, but it appears either I do not know the key words to search for or it is sparsely documented. -- Jack Christensen jackc@hylesanderson.edu
On Feb 16, 2012, at 14:19, Jack Christensen <jackc@hylesanderson.edu> wrote: > Can someone explain how set returning functions in a select clause work? You apparently just did...it CROSS JOINs except in the special case where multiple functions each return the same numberof records (generally due to them all operating on the same sized input - like unnest(array) - in which case it synchronizesthe generated rows. > > It seems that it is doing some sort of implicit cross join. > > deliverance_development=# select id, generate_series(1, 3) from users; > id | generate_series > ----+----------------- > 0 | 1 > 0 | 2 > 0 | 3 > 1 | 1 > 1 | 2 > 1 | 3 > (6 rows) > > But if multiple set returning functions that return the same number of rows are in the same select it doesn't further crossjoin it. > > deliverance_development=# select id, generate_series(1, 3), generate_series(4, 6) from users; > id | generate_series | generate_series > ----+-----------------+----------------- > 0 | 1 | 4 > 0 | 2 | 5 > 0 | 3 | 6 > 1 | 1 | 4 > 1 | 2 | 5 > 1 | 3 | 6 > (6 rows) > > > But if the set returning functions return a different number of rows then it goes back to a cross join. > > deliverance_development=# select id, generate_series(1, 3), generate_series(4, 5) from users; > id | generate_series | generate_series > ----+-----------------+----------------- > 0 | 1 | 4 > 0 | 2 | 5 > 0 | 3 | 4 > 0 | 1 | 5 > 0 | 2 | 4 > 0 | 3 | 5 > 1 | 1 | 4 > 1 | 2 | 5 > 1 | 3 | 4 > 1 | 1 | 5 > 1 | 2 | 4 > 1 | 3 | 5 > (12 rows) > > > I really don't understand what is going on here. I have checked Google and the PostgreSQL docs, but it appears either Ido not know the key words to search for or it is sparsely documented. > > -- > Jack Christensen > jackc@hylesanderson.edu > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
David Johnston <polobo@yahoo.com> writes:
> On Feb 16, 2012, at 14:19, Jack Christensen <jackc@hylesanderson.edu> wrote:
>> Can someone explain how set returning functions in a select clause work?
> You apparently just did...it CROSS JOINs except in the special case where multiple functions each return the same
numberof records (generally due to them all operating on the same sized input - like unnest(array) - in which case it
synchronizesthe generated rows.
I believe the number of rows you get is the least common multiple of the
lengths of the SRF results. This behavior is widely disliked, but we're
unlikely to change it for fear of breaking working apps. The long-term
plan is to implement LATERAL in FROM and then deprecate using SRFs in
target lists altogether.
regards, tom lane
On Thu, 16 Feb 2012 13:19:16 -0600, Jack Christensen wrote about [GENERAL] Set returning functions in select column list: [snip] > I really don't understand what is going on here. I have checked > Google and the PostgreSQL docs, but it appears either I do not know > the key words to search for or it is sparsely documented. The words to search for are "Cartesian product". This is the way RDBMSes have handled unconstrained implicit joins since the days of SQL/DS. -- Regards, Dave [RLU #314465] ====================================================================== dwnoon@ntlworld.com (David W Noon) ======================================================================
Attachment
On Thu, 16 Feb 2012 13:19:16 -0600, Jack Christensen wrote about [GENERAL] Set returning functions in select column list: [snip] > I really don't understand what is going on here. I have checked > Google and the PostgreSQL docs, but it appears either I do not know > the key words to search for or it is sparsely documented. The words to search for are "Cartesian product". This is the way RDBMSes have handled unconstrained implicit joins since the days of SQL/DS. -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================
On Fri, Feb 17, 2012 at 17:31, David W Noon <dwnoon@ntlworld.com> wrote: > The words to search for are "Cartesian product". This is the way > RDBMSes have handled unconstrained implicit joins since the days of > SQL/DS. Yeah, that's what Jack was confused about -- it's actually *not* a cartesian product. It simply stops enumerating when all the SRFs reach the end at the same time. Both of these queries generate 6 rows, although a cartesian product would mean 12 in the second query: select generate_series(1,2), generate_series(1,3); select generate_series(1,2), generate_series(1,6); Regards, Marti