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
