转换unfst（）的结果时丢失了NULL数字

I stumbled upon very odd behavior with unnest(), when casting after expanding an array.

在扩展数组后进行转换时，我偶然发现了使用unexst（）的非常奇怪的行为。

Introduction

There are three basic syntax variants to use 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);

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）;

All of them include a row with NULL in the result as expected

所有这些都包含一个在结果中按预期方式为NULL的行

 i
---
 1
(null)
 4

To cast the array elements to a different type, one can cast the elements to a basic type right after expanding the array, or cast the array itself to a different array type before expanding. The first variant seemed slightly simpler and shorter to me:

要将数组元素转换为其他类型，可以在扩展数组后立即将元素转换为基本类型，或者在展开之前将数组本身转换为其他数组类型。对我来说，第一个变体似乎更简单，更短：

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

A）SELECT unnest（'{4，NULL，1}':: int []）:: text; B）SELECT unnest（'{4，NULL，2}':: int [] :: text []）;

 i
---
 4
(null)
 1

Odd behaviour

All combinations possible except 2A)

For some reason one cannot combine 2) with A)

由于某种原因，人们无法将2）与A）结合起来

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

ERROR: syntax error at or near "::"

错误：“::”处或附近的语法错误

I can accept that. A rare corner case that has not been implemented for some reason.
All other combinations fly, though:

我可以接受。一个罕见的角落案例，由于某种原因尚未实施。然而，所有其他组合飞行：

1A) 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);

1A）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）;

Same result as above.

与上面的结果相同。

Really odd behaviour

The following observations concern A) exclusively. One can avoid the problem by substituting with B).

以下观察仅涉及A）。用B）代替可以避免这个问题。

As expected, we have seen the NULL element in the array resulting in a row with a NULL value in all queries so far. However, this is not the case when casting the results from some array types to some base types.

正如所料，我们已经看到数组中的NULL元素导致到目前为止在所有查询中都有一个具有NULL值的行。但是，将某些数组类型的结果转换为某些基类型时，情况并非如此。

Here the row with the NULL value suddenly disappears (!):

这里具有NULL值的行突然消失（！）：

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

 i
---
 1
 4

Examples

I went to see how deep the rabbit hole goes. Here are some examples:

我去看看兔子洞有多深。这里有些例子：

NULL disappears:

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 stays:

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;

This seems unacceptable.

这似乎是不可接受的。

After testing quite a few combinations, the pattern seems to be:

在测试了很多组合后，模式似乎是：

Cast between related types results in NULL elements being lost.
Cast between unrelated types results in NULL elements being preserved.
Except that varchar[] -> text and vice versa trashes this little hypothesis of mine. Or varchar and text differ more than I thought.

在相关类型之间转换会导致NULL元素丢失。在不相关的类型之间转换会导致保留NULL元素。除了varchar [] - >文本，反之亦然，这是我的这个小假设。或varchar和文本的差异比我想象的要多。

Tested with PostgreSQL 9.1 and 9.2. Identical results.
-> SQLfiddle

使用PostgreSQL 9.1和9.2进行测试。相同的结果。 - > SQLfiddle

Questions

Am I missing something here? Can someone explain this behaviour?
If not, the question becomes: Should I go ahead an file a bug report?

我在这里错过了什么吗？有人可以解释这种行为吗？如果没有，问题就变成了：我应该在文件中提交错误报告吗？

1 个解决方案

#1

Casting SRF function (in FROM clause) is not supported - you cannot use any operator there. Only function call is allowed.

不支持转换SRF函数（在FROM子句中） - 您不能在那里使用任何运算符。只允许函数调用。

a cast is possible only in column list:

只能在列列表中进行强制转换：

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)

Missing row from NULL is probably bug and should be reported

从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)

There is not reason, why NULL rows should be dropped, I think

我认为没有理由，为什么应该删除NULL行