8

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1192878
  • 704
  • 1
  • 10
  • 20
  • Related answers [here](http://stackoverflow.com/questions/12414750/is-there-something-like-a-zip-function-in-postgresql-that-combines-two-arrays/12414884#12414884) and [here](http://stackoverflow.com/questions/21782374/many-to-many-relation-in-operator-and-possibility-of-improper-results/21784881#21784881) and [here](http://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450). The last one with details about `LATERAL` and the upcoming `WITH ORDINALITY` in 9.4. – Erwin Brandstetter Apr 12 '14 at 13:07
  • Thank you @Erwin Brandstetter. – user1192878 Apr 14 '14 at 09:23

1 Answers1

9

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778