BUG #8150: NULL emements lost when casting result of unnest()

From: brandstetter(at)falter(dot)at
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8150: NULL emements lost when casting result of unnest()
Date: 2013-05-11 12:22:48
Message-ID: E1Ub8p6-0006NC-Ja@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8150
Logged by: Erwin Brandstetter
Email address: brandstetter(at)falter(dot)at
PostgreSQL version: 9.2.4
Operating system: Multiple
Description:

PostgreSQL allows to cast the result of unnest() directly. However, I found
inconsistent results with NULL elements in the array. With some array types
NULL element are lost, while they are retained with others:

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

i
---
4
(null)
1

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

i
---
1
4

The NULL element is lost in the second case, which is unwarranted as far as
I can tell.

Tested with the latest PostgreSQL 9.1.9 and 9.2.4. Identical results.

I presented the case with more details in this question on Stackoverflow:
http://stackoverflow.com/questions/16452501/null-emements-lost-when-casting-result-of-unnest

Accompanied by this demo on SQLfiddle:
http://www.sqlfiddle.com/#!12/d41d8/930

As advised by Pavel, I am reporting this as possible bug now.

Regards
Erwin

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2013-05-11 15:13:44 Re: BUG #8150: NULL emements lost when casting result of unnest()
Previous Message Euler Taveira 2013-05-10 22:17:31 Re: Inconsistency between TO_CHAR() and TO_NUMBER()