|
I have a table with a char(20) column called 'name'. This SELECT
returns the proper records:
SELECT * FROM table WHERE name = 'John';
But this one does not return the same records:
SELECT * FROM table WHERE UPPER(name) = '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 should
return a value of the same data type as its argument. The second SELECT
seems to be returning a VARCHAR(20) result which includes the 16 trailing
spaces. And those spaces are significant for the equality test. If
the string literal in the second SELECT has 16 trailing spaces added, the
correct records are returned.
Am I right that this is non-standard behavior? Is this a known
problem? What is its status in releases after 7.0.2?
BTW, the standard calls for CHARACTER SET and COLLATION support, which
include specifying if PAD characters are inserted to make the shorter comparand
as long as the longer one before the comparison is performed. One might
say 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 the
wrong result type here.
Thanks
Jim Ballard
|
