Re: array_to_string bug?

Folks,

Here's expected behavior:

davidfetter(at)postgres=# SELECT array(values(1),(null));
?column?
──────────
{1,NULL}
(1 row)

The next one is just plain unexpected. Either it's a bug, or it needs
more documentation in the function description in the docs, \df+
output, etc.

davidfetter(at)postgres=# SELECT array_to_string(array(values(1),(null)),'');
array_to_string
─────────────────
1
(1 row)

I would have expected it to come out as NULL.

What say?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> writes:
> The next one is just plain unexpected.

array_to_string ignores null elements. What do you think it should do
with them?

regards, tom lane

On Thu, Nov 12, 2009 at 11:20:26AM -0500, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > The next one is just plain unexpected.
>
> array_to_string ignores null elements. What do you think it should
> do with them?

It should concatenate them, i.e. return a NULL if the array includes
any NULLs. That, or it should explain that it doesn't do that because
there's nothing in the docs that would indicate this behavior.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
>
>> The next one is just plain unexpected.
>>
>
> array_to_string ignores null elements. What do you think it should do
> with them?
>
> regards, tom lane
>
>
This seems somewhat related to the long-running discussion from back in
February-April regarding string_to_array with empty input which faded
away somewhere around here:
http://archives.postgresql.org/pgsql-hackers/2009-04/msg00363.php. At
the time the decision was to defer any decision to after 8.4.

Perhaps there is a solution which can address both cases - ideally one
which would, to the extent practical, allow string_to_array to be the
inverse of array_to_string. This could be particularly useful when
dealing with clients that don't know how to directly deal with
PostgreSQL arrays but which can generally easily deal with strings.

Although it might cause a fair amount of backward-compatibility trouble,
the string representation could either use NULL to represent a null
element as is allowed in other contexts or require that empty-string
elements be represented as "" to differentiate ,"", (empty-string
element) from ,, (null element).

Cheers,
Steve

On Thu, Nov 12, 2009 at 1:28 PM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:
> Although it might cause a fair amount of backward-compatibility trouble, the
> string representation could either use NULL to represent a null element as
> is allowed in other contexts or require that empty-string elements be
> represented as "" to differentiate ,"", (empty-string element) from ,, (null
> element).

That would cause a substantial amount of grief to people who might not
want that behavior, though. I use these functions for creating
human-readable output, not for serialization. Simple, predictable
behavior is very important.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Nov 12, 2009 at 1:28 PM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)com> wrote:
>> Although it might cause a fair amount of backward-compatibility trouble, the
>> string representation could either use NULL to represent a null element as
>> is allowed in other contexts or require that empty-string elements be
>> represented as "" to differentiate ,"", (empty-string element) from ,, (null
>> element).

> That would cause a substantial amount of grief to people who might not
> want that behavior, though. I use these functions for creating
> human-readable output, not for serialization. Simple, predictable
> behavior is very important.

I agree --- we don't want to start introducing quoting rules into
array_to_string. I think the viable alternatives are the current
behavior, or treating a NULL element as if it were an empty string.
David's idea that the entire output should go to NULL might be sane
from a strict semantics point of view, but it also seems to make the
function just about entirely useless in practice.

regards, tom lane

On Thu, Nov 12, 2009 at 01:33:41PM -0500, Robert Haas wrote:
> On Thu, Nov 12, 2009 at 1:28 PM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)com> wrote:
> > Although it might cause a fair amount of backward-compatibility
> > trouble, the string representation could either use NULL to
> > represent a null element as is allowed in other contexts or
> > require that empty-string elements be represented as "" to
> > differentiate ,"", (empty-string element) from ,, (null element).
>
> That would cause a substantial amount of grief to people who might
> not want that behavior, though. I use these functions for creating
> human-readable output, not for serialization. Simple, predictable
> behavior is very important.

My question boils down to, "why is this string concatenation different
from all other string concatenations?"

For now, the answer can be, "it behaves differently with respect to
NULLs," and we just document this. We can later decide whether this
behavior should change.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Nov 12, 2009, at 10:46 AM, David Fetter wrote:

> My question boils down to, "why is this string concatenation different
> from all other string concatenations?"

Does it have something to do with afikoman?

David

On Thu, Nov 12, 2009 at 11:46:54AM -0800, David Wheeler wrote:
> On Nov 12, 2009, at 10:46 AM, David Fetter wrote:
>
> > My question boils down to, "why is this string concatenation
> > different from all other string concatenations?"
>
> Does it have something to do with afikoman?

I was wondering who'd comment on that first ;)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate