SQL multiple UNNEST in single select list

Question

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.

Answer 1

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.