BUG #13934: wrong result of split_part with char value

On Monday, February 8, 2016, <dominik.kosiorek@infobright.com> wrote:

The following bug has been logged on the website:

Bug reference: 13934
Logged by: Dominik Kosiorek
Email address: dominik.kosiorek@infobright.com <javascript:;>
PostgreSQL version: 9.2.2
Operating system: Ubuntu 14
Description:

create table string1postgres(
a1 int,
a2 char(65),
a3pattern char(15),
a4field int
)
insert into string1postgres values(72,'abcd defg poir abcde m ert g d c v b
gthb',' ',1);

select split_part(a2,a3pattern,a4field) from string1postgres;

-------------------
the result of split part is:
-------------------
abcd defg poir abcde m ert g d c v b gthb

-------------------
instead of:
-------------------
abcd

-------------------
This defect is only with char type. On varchar result is correct.

This calls for another round of "don't use char” advice...

It is not a bug but a result of the fact that trailing white space in char
values is able to be trimmed away thus leaving you the empty string and no
splitting.

You may read the documentation for details:

http://www.postgresql.org/docs/9.5/static/datatype-character.html

And search the Internet for numerous postings as to why you should avoid
char.

Use to text or varchar instead.

David J.

On 02/08/2016 06:00 PM, David G. Johnston wrote:

On Monday, February 8, 2016, <dominik.kosiorek@infobright.com
This calls for another round of "don't use char” advice...

It is not a bug but a result of the fact that trailing white space in
char values is able to be trimmed away thus leaving you the empty string
and no splitting.

In other words, when you insert ' ' into string1postgres.a3pattern,
which is defined as char(15), the single space is trimmed leaving an
empty string:

test=# select '***' || a3pattern || '!!!' from string1postgres;
?column?
----------
***!!!
(1 row)

And if you feed an actual space to split_part(), it works as expected.

test=# select split_part(a2,' ',a4field) from string1postgres;
split_part
------------
abcd
(1 row)

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Joe Conway <mail@joeconway.com> writes:

In other words, when you insert ' ' into string1postgres.a3pattern,
which is defined as char(15), the single space is trimmed leaving an
empty string:

test=# select '***' || a3pattern || '!!!' from string1postgres;
?column?
----------
***!!!
(1 row)

Actually, I believe the space-trimming happens when the char(n) value
is coerced to type text in preparation for passing it to the || operator
(which takes text). Since trailing spaces are considered insignificant
in char(n), whereas they definitely are significant in text, this is a
reasonable thing to do, at least in some contexts.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Feb 9, 2016 at 8:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joe Conway <mail@joeconway.com> writes:

In other words, when you insert ' ' into string1postgres.a3pattern,
which is defined as char(15), the single space is trimmed leaving an
empty string:

test=# select '***' || a3pattern || '!!!' from string1postgres;
?column?
----------
***!!!
(1 row)

Actually, I believe the space-trimming happens when the char(n) value
is coerced to type text in preparation for passing it to the || operator
(which takes text). Since trailing spaces are considered insignificant
in char(n), whereas they definitely are significant in text, this is a
reasonable thing to do, at least in some contexts.

​And in the OP:

​
select split_part(a2,a3pattern,a4field) from string1postgres;

​split_part likewise takes text, not char, and so an implicit conversion
and trimming takes place.

David j.
​