I was implementing a Query system. I implemented unnest function. Now user was asking about using multiple unnest in a single select statement. I was using PostgreSQL as kind of guideline since most users was using it before our query system.
PostgreSQL has such strange behavior:
postgres=# select unnest(array[1,2]), unnest(array[1,2]);
unnest | unnest
--------+--------
1 | 1
2 | 2
(2 rows)
postgres=# select unnest(array[1,2]), unnest(array[1,2,3]);
unnest | unnest
--------+--------
1 | 1
2 | 2
1 | 3
2 | 1
1 | 2
2 | 3
(6 rows)
My implementation was always generate as Cartesian product. I'm wondering, what's the correct logic behind this? Is PostgreSQL doing right thing or just a bug? I didn't find clear description in ANSI document or PostgreSQL document.
This isn't about unnest as such, but about PostgreSQL's very weird handling of multiple set-returning functions in the SELECT
list. Set-returning functions in SELECT
aren't part of the ANSI SQL standard.
You will find behaviour much saner with LATERAL
queries, which should be preferred over using a a set-returning function in FROM
as much as possible:
select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
e.g.
regress=> select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
a | b
---+---
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
(6 rows)
The only time I still use multiple set-returning functions in SELECT
is when I want to pair up values from functions that both return the same number of rows. The need for that will go away in 9.4, with multi-argument unnest
and with support for WITH ORDINALITY
.