Multiple unnests in query
Good night,
Is this first query expected behavior? If so, what is the rationale? I
would have expected nine rows returned (as in my second example).
Regards,
Aron Widforss
SELECT
unnest(ARRAY[1, 1, 2]) AS unnested1,
unnest(ARRAY[3, 3, 4]) AS unnested2
;
unnested1 | unnested2
-----------+-----------
1 | 3
1 | 3
2 | 4
(3 rows)
SELECT
unnest(ARRAY[1, 1, 2]) AS unnested1,
sec
FROM (SELECT 3 AS sec UNION ALL
SELECT 3 AS sec UNION ALL
SELECT 4 AS sec) test
;
unnested1 | sec
-----------+-----
1 | 3
1 | 3
2 | 3
1 | 3
1 | 3
2 | 3
1 | 4
1 | 4
2 | 4
(9 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Aron Widforss <pgsql-general@antarkt.is> writes:
Is this first query expected behavior? If so, what is the rationale?
The short answer is "because it's always worked that way". You
might find the last half of section 37.4.8 illuminating:
https://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
but if you're on a pre-v10 release, pay close attention to what it says
about the difference between v10 and pre-v10 behavior.
I would have expected nine rows returned (as in my second example).
Your second example has approximately nothing to do with your first.
It has only one SRF in the SELECT list, so there's not much doubt
about what ought to happen.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general