我偶然发现了非常奇怪的行为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
要将数组元素转换为另一种类型,可以将elements到基本类型的权利扩大后数组,或投射array本身为不同的数组类型扩展前。第一个变体对我来说似乎稍微简单和简短:
A) SELECT unnest('{4,NULL,1}'::int[])::text;
B) SELECT unnest('{4,NULL,2}'::int[]::text[]);
i
---
4
(null)
1
奇怪的行为
所有组合都可能,除了2A)
由于某种原因无法结合2)
with A)
SELECT * FROM unnest('{2,NULL,1}'::int[])::text;
错误:“::”处或附近的语法错误
我可以接受。由于某种原因尚未实施的罕见极端情况。
不过,所有其他组合都可以:
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);
与上面的结果相同。
行为真的很奇怪
以下意见涉及A)
只。人们可以通过替换来避免这个问题B)
.
正如所料,我们看到了NULL
数组中的元素产生一行NULL
到目前为止所有查询的价值。然而,当转换结果时,情况并非如此some数组类型为some基本类型。
这里的行是NULL值突然消失 (!):
SELECT unnest('{1,NULL,4}'::int[])::int8;
i
---
1
4
Examples
我去看兔子洞有多深。这里有些例子:
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:
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
and text
与我想象的不同。
使用 PostgreSQL 9.1 和 9.2 进行测试。相同的结果。
-> SQLfiddle
问题
我在这里错过了什么吗?有人可以解释这种行为吗?
如果没有,问题就变成:我应该继续提交错误报告吗?