Значения NULL теряются при наложении результата unnest()

Я наткнулся на очень странное поведение с unnest(), при приведении после расширения массива.

Вступление

Существует три основных варианта синтаксиса для использования unnest ():

1) SELECT unnest('{1,NULL,4}'::int[]) AS i;
2) SELECT i FROM unnest('{2,NULL,4}'::int[]) AS i;
3) SELECT i FROM (SELECT unnest('{3,NULL,4}'::int[])) AS t(i);

Все они включают ряд с NULL в результате, как и ожидалось

 i
---
 1
(null)
 4

Чтобы привести элементы массива к другому типу, можно привести элементы к базовому типу сразу после расширения массива или преобразовать сам массив в другой тип массива перед расширением. Первый вариант показался мне немного проще и короче:

A) SELECT unnest('{4,NULL,1}'::int[])::text;
B) SELECT unnest('{4,NULL,2}'::int[]::text[]);

 i
---
 4
(null)
 1

Странное поведение

Возможны все комбинации, кроме 2A)

Почему-то нельзя совмещать 2) с A)

SELECT * FROM unnest('{2,NULL,1}'::int[])::text;

ОШИБКА: синтаксическая ошибка в или около "::"

Я могу принять это. Редкий угловой случай, который по какой-то причине не был реализован.
Все остальные комбинации летают, хотя:

1А) SELECT unnest('{1,NULL,1}'::int[])::text AS i;
2A) SELECT i FROM unnest('{2,NULL,1}'::int[])::text AS i;
3A) SELECT i FROM (SELECT unnest('{3,NULL,1}'::int[])::text) AS t(i);
1B) SELECT unnest('{1,NULL,2}'::int[]::text[]) AS i;
2B) SELECT i FROM unnest('{2,NULL,2}'::int[]::text[]) AS i;
3B) SELECT i FROM (SELECT unnest('{3,NULL,2}'::int[]::text[])) AS t(i);

Тот же результат, что и выше.

Действительно странное поведение

Следующие наблюдения касаются A) исключительно. Можно избежать проблемы, подставив B),

Как и ожидалось, мы увидели NULL элемент в массиве, в результате чего строка с NULL значение во всех запросах до сих пор. Однако это не тот случай, когда приводятся результаты из некоторых типов массивов к некоторым базовым типам.

Здесь строка со значением NULL внезапно исчезает (!):

SELECT unnest('{1,NULL,4}'::int[])::int8;

 i
---
 1
 4

Примеры

Я пошел посмотреть, как глубоко заходит кроличья нора. Вот некоторые примеры:

NULL исчезает:

SELECT unnest('{1,NULL,1}'::int[])::int2;
SELECT unnest('{1,NULL,2}'::int[])::int8;
SELECT unnest('{1,NULL,3}'::int[])::real;
SELECT unnest('{1,NULL,4}'::int[])::float8;
SELECT unnest('{1,NULL,5}'::int[])::numeric;
SELECT unnest('{1,NULL,6}'::numeric[])::int2;
SELECT unnest('{1,NULL,7}'::numeric[])::int8;
SELECT unnest('{1,NULL,8}'::numeric[])::real;
SELECT unnest('{1,NULL,9}'::numeric[])::float8;
SELECT unnest('{1,NULL,a}'::text[])::char;
SELECT unnest('{1,NULL,b}'::text[])::char(1);
SELECT unnest('{1,NULL,c}'::text[])::varchar(10);      -- !!!
SELECT unnest('{1,NULL,d}'::varchar[])::varchar(10);   -- !!!
SELECT unnest('{2013-1-1,NULL,2013-1-1}'::date[])::timestamp;
SELECT unnest('{2013-1-1,NULL,2013-1-1}'::timestamp[])::date;
SELECT unnest('{23:11,NULL,23:11}'::time[])::interval;
SELECT unnest('{23:11,NULL,23:11}'::interval[])::time;

NULL остается:

SELECT unnest('{1,NULL,1}'::int[])::int4;    -- is really from int to int
SELECT unnest('{1,NULL,2}'::int[])::text;
SELECT unnest('{1,NULL,3}'::int8[])::text;
SELECT unnest('{1,NULL,4}'::numeric[])::text;
SELECT unnest('{1,NULL,5}'::text[])::int;
SELECT unnest('{1,NULL,6}'::text[])::int8;
SELECT unnest('{1,NULL,7}'::text[])::numeric;
SELECT unnest('{1,NULL,8}'::text[])::varchar;    -- !!!
SELECT unnest('{1,NULL,9}'::varchar[])::text;    -- !!!
SELECT unnest('{2013-1-1,NULL,2013-1-1}'::date[])::text;
SELECT unnest('{2013-1-1,NULL,2013-1-1}'::text[])::date;
SELECT unnest('{23:11,NULL,23:11}'::time[])::text;
SELECT unnest('{23:11,NULL,23:11}'::text[])::time;

Это кажется неприемлемым.

После тестирования довольно многих комбинаций картина выглядит так:

Приведение между связанными типами приводит к NULL элементы теряются.
Приведение между несвязанными типами приводит к NULL элементы сохраняются.
Кроме этого varchar[] -> text и наоборот опровергает эту мою маленькую гипотезу. Или же varchar а также text отличаются больше, чем я думал.

Протестировано с PostgreSQL 9.1 и 9.2. Одинаковые результаты.
-> SQLfiddle

Вопросы

Я что-то здесь упускаю? Может кто-нибудь объяснить это поведение?
Если нет, то возникает вопрос: Должен ли я отправлять файл с сообщением об ошибке?

Источник

1 ответ

Решение

Приведение функции SRF (в предложении FROM) не поддерживается - вы не можете использовать там какой-либо оператор. Разрешен только вызов функции.

приведение возможно только в списке столбцов:

postgres=# SELECT * FROM unnest('{2,NULL,1}'::int[])::text;
ERROR:  syntax error at or near "::"
LINE 1: SELECT * FROM unnest('{2,NULL,1}'::int[])::text;
                                                 ^
postgres=# SELECT v::text FROM unnest('{2,NULL,1}'::int[]) g(v);
   v    
────────
      2
 [null]
      1
(3 rows)

Пропущенная строка из NULL, вероятно, ошибка

postgres=# SELECT unnest('{1,NULL,4}'::int[])::text;
 unnest 
────────
      1
 [null]
      4
(3 rows)

postgres=# SELECT unnest('{1,NULL,4}'::int[])::numeric;
 unnest 
────────
      1
      4
(2 rows)

Я думаю, что нет причин, по которым NULL-строки должны быть удалены.

Источник
Другие вопросы по тегам