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.

Sollars

Related answers here and here and here. The last one with details about LATERAL and the upcoming WITH ORDINALITY in 9.4. – Khaki

Thank you @Erwin Brandstetter. – Sollars

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.

Montford

but in the FROM clause they can't have aggregates unnest(array_agg(id)) – Weimer

Note that this weird behaviour may change in a future PostgreSQL version. Do not rely on it. – Montford

@EvanCarroll Right, you need to handle that in a subquery. (Please post a new question and put a link to it in the comments here.) – Montford

Note that the weird behavior ended with Postgres 10 (just like Craig predicted). See: https://mcmap.net/q/28586/-what-is-the-expected-behaviour-for-multiple-set-returning-functions-in-select-clause – Khaki