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 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 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 number of records (generally due to them all operating on the same sized input - like unnest(array) - in which case it synchronizes the generated rows.
Show quoted text
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--
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 number of records (generally due to them all operating on the same sized input - like unnest(array) - in which case it synchronizes the 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)
======================================================================
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