Thread: Problem with character results

Problem with character results

From
"Jim Ballard"
Date:
I have a table with a char(20) column called 'name'.  This SELECT returns t=
he proper records:

SELECT * FROM table WHERE name =3D 'John';

But this one does not return the same records:

SELECT * FROM table WHERE UPPER(name) =3D 'JOHN';

I believe this is contrary to the SQL standard, which (as I understand it) =
says that a scalar function applied to a single character field argument sh=
ould return a value of the same data type as its argument.  The second SELE=
CT seems to be returning a VARCHAR(20) result which includes the 16 trailin=
g spaces.  And those spaces are significant for the equality test.  If the =
string literal in the second SELECT has 16 trailing spaces added, the corre=
ct records are returned.

Am I right that this is non-standard behavior?  Is this a known problem?  W=
hat is its status in releases after 7.0.2?

BTW, the standard calls for CHARACTER SET and COLLATION support, which incl=
ude specifying if PAD characters are inserted to make the shorter comparand=
 as long as the longer one before the comparison is performed.  One might s=
ay that PG behaves as though PAD were turned off.  But I think that misses =
the root of this problem, which is that UPPER and other functions return th=
e wrong result type here.


Thanks
Jim Ballard