Understanding behavior of SELECT with multiple unnested columns

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Understanding behavior of SELECT with multiple unnested columns
Дата	27 марта 2013 г. 09:34:35
Msg-id	CAD3a31X7ZGAgzOuUnfu3UUy1Do9UVu7CVAKem1yTHpbL3WQBCg@mail.gmail.com обсуждение исходный текст
Ответы	Re: Understanding behavior of SELECT with multiple unnested columns Re: Understanding behavior of SELECT with multiple unnested columns
Список	pgsql-general

Дерево обсуждения

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

Throw an unnested null column in and you get zero rows, which I also didn't expect:

SELECT unnest2(array['a','b','c']),unnest2(array['1','2']),unnest(NULL::varchar[]);

unnest2 | unnest2 | unnest

---------+---------+--------

(0 rows)

After some head scratching, I think I understand what to expect from these unnests, but I'm unclear of the logic behind what is going on. I'm hoping someone can explain it a bit. Also, on a practical level, would anyone know how to query so that SELECT unnest2(array['a','b','c']),unnest2(array['1','2']) would return three rows instead of six, like so:

a 1

b 2

c (NULL)

As that would be perfect for my purposes. Thanks in advance!

Ken