string_to_array with empty input

Steve Crawford <scrawford@pinpointresearch.com> writes:

I have a query that converts a string to an array with the
string_to_array function. Sometimes the input is an empty string (not a
null, but a string of zero-length). I had expected the result to be a
one-element array with an empty string as the first and only element but
instead it returned null. I looked at the docs and didn't find the
observed behavior documented.

The behavior is pretty intentional according to the source code:

/* return NULL for empty input string */
if (inputstring_len < 1)
{
text_position_cleanup(&state);
PG_RETURN_NULL();
}

I agree this seems less than consistent though, especially seeing
that you *don't* get a null for a zero-length separator, which if
anything is a more poorly defined case.

I doubt it'd be a good idea to back-patch a change for this,
but I could see altering the definition for 8.4.

Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)

regards, tom lane

Tom Lane wrote:

I agree this seems less than consistent though, especially seeing
that you *don't* get a null for a zero-length separator, which if
anything is a more poorly defined case.

I doubt it'd be a good idea to back-patch a change for this,
but I could see altering the definition for 8.4.

Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)

regards, tom lane

I like the array to contain single zero length string. A string was
passed in although empty, its still a string not a NULL.

Returning an empty array implies nothing was passed to the function
although something was. That seems kinda odd to me also, give back what
was sent in broken into an array.

I use this and split_part allot in our database to break apart part numbers

On Mar 30, 2009, at 8:26 PM, Tom Lane wrote:

Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)

Hrm. There seems to be some disagreement about this among some
languages:

% perl -le '@r = split /-/, ""; print length @r; print qq{"$r[0]"}'
1
""

% irb

puts ''.split('-')

=> nil

So Perl returns a single element as Steve had been expecting, while
Ruby returns nil. I'm used to the Perl way, but I guess there's room
for various interpretations, including the current implementation,
with which Ruby would seem to agree.

Best,

David

Sorry for top-posting--blame apple.

Hm my first instinct was indeed to make it a zero-length array. I was
thinking of the input as a "list" and surely there are no elements in
a list which empty. I had to think a while until a length-1 array made
sense.

I suppose the thinking was string_to_array is the inverse of an
array_to_string operation then there are multiple possible answers.
You might have joined a zero length or a singleton array of an empty
string....
and since it's unknown which was the original value null is the right
answer...

I agree that picking an arbitrary choice is going to be more useful in
practice though.

--
Greg

On 30 Mar 2009, at 23:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Tue, Mar 31, 2009 at 2:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Does anyone want to argue for keeping it the same?  Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string?  (It doesn't seem
like it to me, but maybe somebody thinks so.)

My first thought was that it should be a zero-element array, because
then the string_to_array() behaviour would conform to the notion that
it returns an array with 1 element per string fragment bounded by the
delimiter.

However, I note that if you provide an empty delimiter, or one which
doesn't occur anywhere in the source string, you get an array with one
element, being the entire source string.

# select string_to_array('1-2-3', '-');
{1,2,3}

# select string_to_array('1-2-3', 'x');
{1-2-3}

Given this behaviour, I would argue for consistent treatment for a
zero-length source string: it should return an array with one element,
being the entire source string, whenever there is no string splitting
to take place. And if the source string happens to be zero-length,
then the return value would be as expected by the OP.

Cheers,
BJ

On Tue, Mar 31, 2009 at 05:45:33PM +1100, Brendan Jurd wrote:

On Tue, Mar 31, 2009 at 2:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Does anyone want to argue for keeping it the same? �Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? �(It doesn't seem
like it to me, but maybe somebody thinks so.)

Given this behaviour, I would argue for consistent treatment for a
zero-length source string: it should return an array with one element,
being the entire source string, whenever there is no string splitting
to take place. And if the source string happens to be zero-length,
then the return value would be as expected by the OP.

I'd agree with this as well, just to be verbose:

string_to_array(NULL,',') => NULL
string_to_array('',',') => {""}
string_to_array('a',',') => {a}
string_to_array('a,',',') => {a,""}
string_to_array('a,b',',') => {a,b}

However, I can see (nasty and hacky) reasons why the current behaviour
is there. You'd get the following error if this change was accepted:

string_to_array('',',')::INT[] => invalid input syntax for integer: ""

Which you don't get at the moment; although you do currently get it in
other common cases such as:

string_to_array('1,',',')::INT[]

If you want backwards compatible behaviour you could always bung a
NULLIF in there:

string_to_array(NULLIF('',''),',')::INT[] => NULL

To aid porting of code and general utility, I'd be tempted to add a pair
of functions like:

CREATE FUNCTION array_filter_blanks(TEXT[]) RETURNS TEXT[]
LANGUAGE SQL IMMUTABLE STRICT AS $$
ARRAY(SELECT s FROM unnest($1) AS s WHERE s <> '') $$;

CREATE FUNCTION array_nullif(ANYARRAY,ANYELEMENT) RETURNS ANYARRAY
LANGUAGE SQL IMMUTABLE AS $$
ARRAY(SELECT NULLIF(s,$2) FROM unnest($1) AS s) $$;

Although, this is obviously going above and beyond what you originally
asked for.

--
Sam http://samason.me.uk/

On Tue, Mar 31, 2009 at 7:45 AM, Brendan Jurd <direvus@gmail.com> wrote:

My first thought was that it should be a zero-element array, because
then the string_to_array() behaviour would conform to the notion that
it returns an array with 1 element per string fragment bounded by the
delimiter.

However, I note that if you provide an empty delimiter, or one which
doesn't occur anywhere in the source string, you get an array with one
element, being the entire source string.

Yeah, actually the more I think about it the more I think it would be
strange for most uses to get a singleton array for this case.

What do you really expect to be returned for things like

select count_elements(string_to_array('butter,tea,milk',','))
select count_elements(string_to_array('butter,tea',','))
select count_elements(string_to_array('butter',','))
select count_elements(string_to_array('',','))

...

I could see lists like this being stored when people gather data using
a web form or something and don't want to bother normalizing some
trivial bit of data collection which they'll never individually, but
have to unnest the list for some display purposes.

The cases where it makes more sense to return a singleton array are
going to be parsing things like /etc/password where there are specific
meanings for each element, but when some are optional. I can't think
of any examples offhand though.

--
greg

On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason <sam@samason.me.uk> wrote:

 string_to_array('',',')::INT[]  => invalid input syntax for integer: ""

Oof. That's a good point.

--
greg

On Tue, Mar 31, 2009 at 03:43:37PM +0100, Greg Stark wrote:

On Tue, Mar 31, 2009 at 7:45 AM, Brendan Jurd <direvus@gmail.com> wrote:

My first thought was that it should be a zero-element array, because
then the string_to_array() behaviour would conform to the notion that
it returns an array with 1 element per string fragment bounded by the
delimiter.

However, I note that if you provide an empty delimiter, or one which
doesn't occur anywhere in the source string, you get an array with one
element, being the entire source string.

Yeah, actually the more I think about it the more I think it would be
strange for most uses to get a singleton array for this case.

Really? I think it's strange not to!

What do you really expect to be returned for things like

select count_elements(string_to_array('butter,tea,milk',','))
select count_elements(string_to_array('butter,tea',','))
select count_elements(string_to_array('butter',','))
select count_elements(string_to_array('',','))

I'd expect 3,2,1 and 1.

That's also a disingenuous example; what would you expect back from:

select count_elements(string_to_array('butter,,milk',','))

I think the semantics you want is what you'd get from:

array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.

--
Sam http://samason.me.uk/

Greg Stark <stark@enterprisedb.com> writes:

On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason <sam@samason.me.uk> wrote:

string_to_array('',',')::INT[] �=> invalid input syntax for integer: ""

Oof. That's a good point.

Isn't that an argument in favor of the zero-size-array definition?

regards, tom lane

On Mar 31, 2009, at 8:34 AM, Sam Mason wrote:

What do you really expect to be returned for things like

select count_elements(string_to_array('butter,tea,milk',','))

3 = {butter,tea,milk}

select count_elements(string_to_array('butter,tea',','))

2 = {butter,tea}

select count_elements(string_to_array('butter',','))

1 = {butter}

select count_elements(string_to_array('',','))

1 = ARRAY['']

I'd expect 3,2,1 and 1.

That's also a disingenuous example; what would you expect back from:

select count_elements(string_to_array('butter,,milk',','))

3 = ARRAY['butter', '', 'milk']

I think the semantics you want is what you'd get from:

array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.

Yeah, if I wanted something like that in Perl, I'd do:

my @stuff = grep { $_ } split /,/, $string;

In no case would I ever expect a NULL, however, unless I was trying to
split on NULL.

NULL = string_to_array(NULL, ',');

Best,

David

On Tue, Mar 31, 2009 at 4:34 PM, Sam Mason <sam@samason.me.uk> wrote:

That's also a disingenuous example; what would you expect back from:

 select count_elements(string_to_array('butter,,milk',','))

I think the semantics you want is what you'd get from:

 array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.

I think you're still thinking from a programmers point of view
thinking about string processing.

From a functional point of view, if I give you a comma separated list
of items which is an empty string and ask you how many items are in it
nobody would normally say there's one empty thing in the list.

Both interpretations are clearly consistent but it depends on whether
you think it's a bunch of text strings concatenated together or if
it's a list of objects.

The example of string_to_array('',',')::int[] is relevant to this
point. The whole "there's one empty element" only makes sense if
you're thinking in terms of string processing. If it's a list of any
other kind of object it probably doesn't make sense; you can't say
there's one empty integer or one empty composite object or one empty
anything else.

--
greg

Sam Mason wrote:

I'd expect 3,2,1 and 1.

That's also a disingenuous example; what would you expect back from:

select count_elements(string_to_array('butter,,milk',','))

I think the semantics you want is what you'd get from:

array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.

I agree the function should not be changing values passed.
Stripping/Dropping empty strings is changing what was passed into the
function instead breaking it into a array.

Sam Mason wrote:

string_to_array('',',')::INT[] => invalid input syntax for integer: ""

Which you don't get at the moment; although you do currently get it in
other common cases such as:

string_to_array('1,',',')::INT[]

If you want backwards compatible behaviour you could always bung a
NULLIF in there:

string_to_array(NULLIF('',''),',')::INT[] => NULL

But consider this fails also

select string_to_array('1, , 3', ',' )::int[] => ERROR: invalid input
syntax for integer: " "

yet this works

select string_to_array('1, 2, 3',',')::int[]

On Tue, Mar 31, 2009 at 5:48 PM, justin <justin@emproshunts.com> wrote:

But consider  this fails also

select string_to_array('1, , 3', ',' )::int[] => ERROR:  invalid input
syntax for integer: " "

yet this works

select string_to_array('1, 2, 3',',')::int[]

Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.

The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.

I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.

--
greg

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Greg Stark wrote:
<blockquote
cite="mid:4136ffa0903311006v15eb3a25xf3fa94a8ca089fd6@mail.gmail.com"
type="cite">
<pre wrap="">On Tue, Mar 31, 2009 at 5:48 PM, justin <a class="moz-txt-link-rfc2396E" href="mailto:justin@emproshunts.com">&lt;justin@emproshunts.com&gt;</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">But consider &nbsp;this fails also

select string_to_array('1, , 3', ',' )::int[] =&gt; ERROR: &nbsp;invalid input
syntax for integer: " "

yet this works

select string_to_array('1, 2, 3',',')::int[]
</pre>
</blockquote>
<pre wrap=""><!---->

Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.

The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.

I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.
</pre>
</blockquote>
<br>
I disagree.&nbsp;&nbsp; Casting a string to something else can be a very error
prone to begin with. <br>
Having&nbsp; string_to_array() to deal with that possibility&nbsp; is out of its
scope IMHO.<br>
<br>
Consider this.&nbsp; I have intelligent part numbers&nbsp; that need to be split
apart to simplify searching&nbsp; and do math with. <br>
<br>
string_to_array(' F-2500-50 ', '-' ) ::int[]<br>
<br>
Still fails with an error as expected.&nbsp; what is the difference between
' '&nbsp; and 'F'&nbsp; <br>
<br>
So before doing any thing a test needs to be done to verify the
contents, so it can be casted to something else.<br>
</body>
</html>

On Tue, Mar 31, 2009 at 6:44 PM, justin <justin@emproshunts.com> wrote:

Consider this.  I have intelligent part numbers  that need to be split apart
to simplify searching  and do math with.

string_to_array(' F-2500-50 ', '-' ) ::int[]

Yeah, that's what I've said about three times. If you're building a
parser and think of it in terms of string parsing then yes, a
singletone array makes sense.

Still fails with an error as expected.  what is the difference between ' '
and 'F'

I don't know what you're talking about. The question is how many
elements are in ''. No space. And no separators. To repeat for the
last time. If you think in terms of string processing then the answer
1 is reasonable. But if you think it's a list of separate items then
anyone will say that's an empty list and contains no elements.

Nobody has ever suggested filtering out empty elements or dealing
specially with spaces or anything else like that. If you're talking
about that then you've missed the original question.

One last try.

If there's a column called "shopping list" which is a comma-separated
list of items to buy in the store and I store '' in it. How many items
do you think that means you should go shopping for? Do you think
that's one item that should be rejected because it's an empty string?
Or do you think that's an empty list with zero items listed?

What would it look like if it was a zero-length list? You can ask what
would it look like if it was a shopping list of one item called ''.
But I agree both are theoretically consistent, but one is actually
useful in 99% of use cases. The other is only useful in unusual cases.
--
greg

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font color="#ff0000">This thread being cross posted has made it a bit
confusing </font><br>
<br>
Greg Stark wrote:
<blockquote
cite="mid:4136ffa0903311053x6f80e0bbl384167a71005aece@mail.gmail.com"
type="cite">
<pre wrap="">

Nobody has ever suggested filtering out empty elements or dealing
specially with spaces or anything else like that. If you're talking
about that then you've missed the original question.
</pre>
</blockquote>
<br>
<pre wrap="">"Does anyone want to argue for keeping it the same? Or perhaps
argue that a <big><big><u><b>zero-element array</b></u></big></big> is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)"

<small><font color="#ff0000"><big><big><font face="Arial">That means to me dropping empty strings or removing values that don't make sense. Then the argument begins what values make since to drop. Just zero length strings or include strings with million white spaces???</font></big></big></font></small>

</pre>
<blockquote
cite="mid:4136ffa0903311053x6f80e0bbl384167a71005aece@mail.gmail.com"
type="cite">
<pre wrap="">
One last try.

If there's a column called "shopping list" which is a comma-separated
list of items to buy in the store and I store '' in it. How many items
do you think that means you should go shopping for? Do you think
that's one item that should be rejected because it's an empty string?
Or do you think that's an empty list with zero items listed?
</pre>
</blockquote>
<font color="#ff0000"><big>It can't be rejected,&nbsp; Its an Empty shopping
list&nbsp; although a worthless shopping list .&nbsp;&nbsp; </big></font><br>
<blockquote
cite="mid:4136ffa0903311053x6f80e0bbl384167a71005aece@mail.gmail.com"
type="cite">
<pre wrap="">
What would it look like if it was a zero-length list? You can ask what
would it look like if it was a shopping list of one item called ''.
But I agree both are theoretically consistent, but one is actually
useful in 99% of use cases. The other is only useful in unusual cases.
</pre>
</blockquote>
<br>
<font color="#ff0000">I'm still confused which one you want here</font><br>
</body>
</html>

On Tue, Mar 31, 2009 at 05:08:45PM +0100, Greg Stark wrote:

Both interpretations are clearly consistent but it depends on whether
you think it's a bunch of text strings concatenated together or if
it's a list of objects.

The example of string_to_array('',',')::int[] is relevant to this
point. The whole "there's one empty element" only makes sense if
you're thinking in terms of string processing. If it's a list of any
other kind of object it probably doesn't make sense; you can't say
there's one empty integer or one empty composite object or one empty
anything else.

I think this is about the only sensible option, but my reasoning is
somewhat different.

My original intuition was that array_to_string and string_to_array
should be (perfect) inverses of each other. Unfortunately I can't see
any way to get this to happen; zero length arrays or NULL elements
in the array mean than array_to_string isn't injective. This means
that the composition of the two functions won't result in an injective
function and my original premise is false. Note that as far as I can
tell string_to_array is injective. I'm assuming that the delimiter
won't appear as part of an element of the array; e.g. an array of
integers and space as a delimiter is OK, but using the same delimiter
with unconstrained text is not OK, a blank delimiter is never OK as it's
always part of a string.

"Injective" means there exists more than one array that encodes to the
same string. The examples being how do you sanely encode '{}' and
'{NULL}' in a unique way; '{""}' is a bad example because it's just
an artifact of how strings are represented. The complications needed
to allow this to happen would make it a very similar function as the
array's normal output_function function and hence wouldn't serve a
useful purpose. All of this implies that we have to make a compromise
somewhere.

The semantics that most closely match the existing behaviour would be;
for array_to_string:

1) remove NULL values from input array
2) call output_function on remaining elements
3) intersperse[1]as in the intersperse function in Haskell http://www.haskell.org/onlinereport/list.html#sect17.3 intersperse "#" ["a", "bar"] == ["a", "#", "bar"] note that here we're working with arrays of string, rather than arrays of characters as in the report. the delimiter between the remaining elements
4) concatenate the resulting array

for string_to_array:

1) check if input is zero length; return empty array
2) split array based on delimiter and return

Note that both functions are STRICT; i.e. a NULL for either parameter
should cause the function to return NULL. Arguably in string_to_array
it could be non-strict if the input string is empty, but it's probably
worth keeping it strict to simplify the semantics.

Here are some examples:

array_to_string('{}'::TEXT[],',') => ''
array_to_string('{NULL}'::TEXT[],',') => ''
array_to_string('{NULL,NULL}'::TEXT[],',') => ''
array_to_string('{a,NULL}'::TEXT[],',') => 'a'
array_to_string('{NULL,a}'::TEXT[],',') => 'a'
array_to_string('{a,b}'::TEXT[],',') => 'a,b'
array_to_string('{a,NULL,b}'::TEXT[],',') => 'a,b'

string_to_array('',',') => '{}'
string_to_array(' ',',') => '{" "}'
string_to_array(',',',') => '{"",""}'
string_to_array('a',',') => '{a}'
string_to_array('a,',',') => '{a,""}'
string_to_array(',a',',') => '{"",a}'
string_to_array('a,b',',') => '{a,b}'

My thinking before was that it should be doing:

string_to_array('',',') => '{""}'

instead, but I now think that Greg has a point and these are nicer/more
generally useful semantics.

Hum, that all got a bit more verbose than I was expecting. Ah well, I
hope it's somewhat useful.

--
Sam http://samason.me.uk/

[1]: as in the intersperse function in Haskell http://www.haskell.org/onlinereport/list.html#sect17.3 intersperse "#" ["a", "bar"] == ["a", "#", "bar"] note that here we're working with arrays of string, rather than arrays of characters as in the report.
http://www.haskell.org/onlinereport/list.html#sect17.3
intersperse "#" ["a", "bar"] == ["a", "#", "bar"]
note that here we're working with arrays of string, rather than
arrays of characters as in the report.