Re: Understanding behavior of SELECT with multiple unnested columns

On 2013-03-27, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:
> --047d7b5da657ecd54004d8e23a90
> Content-Type: text/plain; charset=ISO-8859-1
>
> I've been working on some queries involving multiple unnested columns. At
> first, I expected the number of rows returned would be the product of the
> array lengths, so that this query would return 4 rows:
>
> SELECT unnest2(array['a','b']),unnest2(array['1','2']);
>
> when in fact it returns 2:
>
> unnest2 | unnest2
> ---------+---------
> a | 1
> b | 2
>
> Which is all well and good. (Better, in fact, for my purposes.) But then
> this query returns 6 rows:
>
> SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
> unnest2 | unnest2
> ---------+---------
> a | 1
> b | 2
> c | 1
> a | 2
> b | 1
> c | 2
>

check out the documentation for generate_series, it behaves similarly.

I think basically the problem is caused by using SRFs between SELECT
and FROM

to see that it's not the cartesion product try it with sets of length
4 and 6

SELECT unnest(array['a','b','c','d']),unnest(array['1','2','3','4','5','6']);

what you want is possible, but it's not pretty - you have to number
the results and join explicitly.

with
a as ( select u,row_number() over ()
from (select unnest(array['a','b']) as u ) as x),
b as ( select u,row_number() over ()
from (select unnest(array['1','2','3']) as u ) as x)
select a.u as a, b.u as b
from a full outer join b on a.row_number=b.row_number;

⚂⚃ 100% natural