Thread: BUG #1783: A function using char variables does not behave the same way between 7.4 and 8.0

The following bug has been logged online:

Bug reference:      1783
Logged by:          Laurent HERVE
Email address:      laurent-herve@club-internet.fr
PostgreSQL version: 8.0.1
Operating system:   Linux Mandriva LE 2005 x86_64
Description:        A function using char variables does not behave the same
way between 7.4 and 8.0
Details:

I am upgrading a 7.4 database to PostgreSQL 8.0.
During my tests, I found that the results of a function weren't like I
expected to.
I am using a 8.0.1 but I didn't find anything related to that problem on the
release note for later versions.

ez33=# select version();
                                                                 version
----------------------------------------------------------------------------
--------------------------------------------------------------
 PostgreSQL 8.0.1 on x86_64-mandrake-linux-gnu, compiled by GCC
x86_64-mandrake-linux-gnu-gcc (GCC) 3.4.3 (Mandrakelinux 10.2 3.4.3-7mdk)
(1 ligne)

my fonction is :

create or replace function ctb_remove_extra_spaces (text) returns text as '
declare
  p_string alias for $1;
  tmp_string text :='''';
  cln_string text :=''''; /* cleaned string */
  i integer :=0;
  pre_car char(1) :='''';
  car char(1) :='''';
a varchar;
begin
  tmp_string:=trim(p_string);
  for i in 1 .. length(tmp_string)
  loop
    car := substring(tmp_string, i ,1);
raise notice ''% / % % %'',i,pre_car,car,cln_string;
-- raise notice ''%'',car;
    if (car = '' '' and pre_car <> '' '')
    then
        a:=ascii(car);
        raise notice ''ASCII = %'',a;
       cln_string:=cln_string||car;
    end if;
    if (car <> '' '')
    then
       cln_string:=cln_string||car;
    end if;
    pre_car:=car;
  end loop;
  return cln_string;
end;
' language 'plpgsql';

A select ctb_remove_extra_spaces('   abc   defghi     '); should give 'abc
defghi'

Running it on PG 8 gives :

ez33=# select ctb_remove_extra_spaces('   abc   defghi     ');
NOTICE:  1 /   a
NOTICE:  2 / a b a
NOTICE:  3 / b c ab
NOTICE:  4 / c   abc
NOTICE:  ASCII = 0
NOTICE:  5 /     abc
NOTICE:  6 /     abc
NOTICE:  7 /   d abc
NOTICE:  8 / d e abcd
NOTICE:  9 / e f abcde
NOTICE:  10 / f g abcdef
NOTICE:  11 / g h abcdefg
NOTICE:  12 / h i abcdefgh
 ctb_remove_extra_spaces
-------------------------
 abcdefghi
(1 ligne)

which is a wrong result.

But when I change the datatypes char(1) to varchar, I get :

ez33=# select ctb_remove_extra_spaces('   abc   defghi     ');
NOTICE:  1 /  a
NOTICE:  2 / a b a
NOTICE:  3 / b c ab
NOTICE:  4 / c   abc
NOTICE:  ASCII = 32
NOTICE:  5 /     abc
NOTICE:  6 /     abc
NOTICE:  7 /   d abc
NOTICE:  8 / d e abc d
NOTICE:  9 / e f abc de
NOTICE:  10 / f g abc def
NOTICE:  11 / g h abc defg
NOTICE:  12 / h i abc defgh
 ctb_remove_extra_spaces
-------------------------
 abc defghi
(1 ligne)

which is right.

Well it looks like a bug ... but probably I had better using varchar in my
applications...

"Laurent HERVE" <laurent-herve@club-internet.fr> writes:
> I am upgrading a 7.4 database to PostgreSQL 8.0.
> During my tests, I found that the results of a function weren't like I
> expected to.
> I am using a 8.0.1 but I didn't find anything related to that problem on the
> release note for later versions.

This is intentional --- trailing spaces in char(N) data are now
considered insignificant in more contexts than before, including
concatenation.

            regards, tom lane