Thread: BUG #9541: Result of TRIM function has changed

The following bug has been logged on the website:

Bug reference:      9541
Logged by:          Kazuki Uehara
Email address:      uehara.kazuki@po.ntts.co.jp
PostgreSQL version: 9.3.3
Operating system:   CentOS release 5.6
Description:

Depending on the version, the result of the TRIM function is different.

I did the following steps.

1. Change the setting of search_path.
---------------------------------------------
search_path='test,pg_catalog,"$user",public'
---------------------------------------------

2. Run the following query.
-------------------------------------------
CREATE FUNCTION LTRIM(CHAR,text)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT;
SELECT '|' || TRIM(LEADING 'a' FROM 'abcd'::char(7)) || '|';
--------------------------------------------


Execution result of each version

PostgreSQL9.3.1
| postgres=# SELECT '|' || TRIM(LEADING 'a' FROM 'abcd'::char(7)) || '|';
|  ?column?
| ----------
|  |bcd|
| (1 row)

PostgreSQL9.3.2：
| postgres=# SELECT '|' || TRIM(LEADING 'a' FROM 'abcd'::char(7)) || '|';
|  ?column?
| ----------
|  |bcd|
| (1 row)

PostgreSQL9.3.3：
| postgres=# SELECT '|' || TRIM(LEADING 'a' FROM 'abcd'::char(7)) || '|';
|  ?column?
| ----------
|  |bcd   |
| (1 row)


Thank you very much.

uehara.kazuki@po.ntts.co.jp writes:
> Depending on the version, the result of the TRIM function is different.

I'm not sure that anyone would consider this a supported thing to do:

> CREATE FUNCTION LTRIM(CHAR,text)
> RETURNS text
> AS 'ltrim'
> LANGUAGE internal
> STRICT;

My attitude towards overriding system functions like that is "if it
breaks, you get to keep both pieces".  (BTW, is there a reason you
omitted IMMUTABLE from this?)

However,

> PostgreSQL9.3.3：
> | postgres=# SELECT '|' || TRIM(LEADING 'a' FROM 'abcd'::char(7)) || '|';
> |  ?column?
> | ----------
> |  |bcd   |
> | (1 row)

I get "|bcd|" from this example, in both 9.3 and HEAD branch.  I would
be surprised to get anything different, because the grammar converts
that syntax into "pg_catalog.ltrim(..., ...)" and so your override
function cannot be selected as the expansion.  At least not if the
override function went into schema "public", which would be the default.

I suspect that in 9.3.3 you forced the function into the pg_catalog
schema, but omitted to do that in the other two examples.

            regards, tom lane

I'm sorry.
This is my mistake. My way was wrong.

PostgreSQL is processing in accordance with the specification

Thank you for your reply despite being so busy.


On Wed, 12 Mar 2014 10:29:25 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

| uehara.kazuki@po.ntts.co.jp writes:
| > Depending on the version, the result of the TRIM function is different.
|
| I'm not sure that anyone would consider this a supported thing to do:
|
| > CREATE FUNCTION LTRIM(CHAR,text)
| > RETURNS text
| > AS 'ltrim'
| > LANGUAGE internal
| > STRICT;
|
| My attitude towards overriding system functions like that is "if it
| breaks, you get to keep both pieces".  (BTW, is there a reason you
| omitted IMMUTABLE from this?)
|
| However,
|
| > PostgreSQL9.3.3：
| > | postgres=# SELECT '|' || TRIM(LEADING 'a' FROM 'abcd'::char(7)) || '|';
| > |  ?column?
| > | ----------
| > |  |bcd   |
| > | (1 row)
|
| I get "|bcd|" from this example, in both 9.3 and HEAD branch.  I would
| be surprised to get anything different, because the grammar converts
| that syntax into "pg_catalog.ltrim(..., ...)" and so your override
| function cannot be selected as the expansion.  At least not if the
| override function went into schema "public", which would be the default.
|
| I suspect that in 9.3.3 you forced the function into the pg_catalog
| schema, but omitted to do that in the other two examples.
|
|             regards, tom lane

--
Kazuki Uehara
uehara.kazuki@po.ntts.co.jp