Thread: [BUGS] BUG #14800: substring produces different results with similar types

The following bug has been logged on the website:

Bug reference:      14800
Logged by:          Артём Костин
Email address:      kostin.artem@gmail.com
PostgreSQL version: 9.4.11
Operating system:   Win 10
Description:

These two commands produce different results with similar types
select substring(cast(' 2345           ' as character(16)), 1, 7) || '?',substring(cast(' 2345           ' as
varchar(16)),1, 7) || '?';
 

" 2345?";" 2345  ?"

Should it work in this way or not?


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

Kostin:

On Wed, Sep 6, 2017 at 3:21 PM,  <kostin.artem@gmail.com> wrote:
> These two commands produce different results with similar types
> select substring(cast(' 2345           ' as character(16)), 1, 7) || '?',
>         substring(cast(' 2345           ' as varchar(16)), 1, 7) || '?';
>
> " 2345?";" 2345  ?"
>
> Should it work in this way or not?

If you are not sure whether something is a bug, you should try asking
in the general list, to avoid noise.

Anyway, you may notice char() discards trailing blanks, varchar does not:

n=> select cast(' 2345           ' as character(16)) || '?';?column?
---------- 2345?
(1 row)

Plain char is STORED space padded and TRUNCATED if too long ( standard
required IIRC ), and trailing spaces are disregarded in many places,
such as when concatenating.

Francisco Olarte.


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

Francisco Olarte <folarte@peoplecall.com> writes:
> Anyway, you may notice char() discards trailing blanks, varchar does not:

More precisely, converting from char(n) to varchar or text discards
trailing blanks.  Since both substring() and the || operator take
text argument types, an implicit coercion to text is happening in
these examples ... and that's where the blanks went.
        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

Артём Костин <kostin.artem@gmail.com> writes:
> But documentation says different. So this behaviour is unxpected.

No it doesn't.
        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

Tom:

On Wed, Sep 6, 2017 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Francisco Olarte <folarte@peoplecall.com> writes:
>> Anyway, you may notice char() discards trailing blanks, varchar does not:
> More precisely, converting from char(n) to varchar or text discards
> trailing blanks.  Since both substring() and the || operator take
> text argument types, an implicit coercion to text is happening in
> these examples ... and that's where the blanks went.

I was suspecting that, but
https://www.postgresql.org/docs/9.6/static/functions-string.html
documents them as "string || string", returns text, and
"substring(string {several variants})", returns text and trying to
look where the conversion to text happened ( i.e., in the arguments,
or after aplying overloaded variants ) seemed a bit extreme. Not
surprising, anyway, of the space chopping, having worked with punched
cards I'm used to it.


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

Please, do not top-quote, it is hard to read.

On Wed, Sep 6, 2017 at 5:22 PM, Артём Костин <kostin.artem@gmail.com> wrote:
> But documentation says different. So this behaviour is unxpected.

If you think so you may post the relevant part of the documentation,
so we can see if it really does or try to tell you where we think you
are reading it wrong. This hand waving approach, "docs says
otherwise", won't help.

Francisco Olarte.


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

On Wed, Sep 6, 2017 at 7:10 PM, Артём Костин <kostin.artem@gmail.com> wrote:
> I'm sorry for this approach.

If it is for the top-quoting approach, just correct it.

> The main point is that behaviour are not applied to all character types
> described in docs. You can see it in my example.

It may, but that's not what we were discussing. Or may be. As I said,
your quoting style makes it extremely difficult to know what we are
replying to.

> For the second point, please, check chapter 8.3 for this
> "Trailing spaces are removed when converting a character value to one of the
> other string types. Note that trailing spaces are semantically significant
> in character varying and text values, and when using pattern matching, that
> is LIKE and regular expressions."
> According to the last sentence I expect spaces after characters. It would be
> strange first convert character to string and when string to text type,
> don't you think so?

It may, but as TL said the functions ( and operators, which are just
syntatic sugar for a function call ) work on text. You may interpreted
the sentence as "substring and concatenation do not convert as they
are not enumerated there". But, OTOH, char(n) fields work as people
caoming from punched cards and fixed lentght records are used to. In
this time you had to space-pad ( among other things space was
no-punch, no print, so you saved ink and increased structural
integrity by space-padding instead of usaing any other char ) and
every operation right-trimmed before aplying, as you had no var-length
fields.

This is why, when we got access to var-length fields, used them
everywhere to avoid this kind of surprises. I've been avoiding fixed
char fields since I can remember.

FOS


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