Problem with character results

From: "Jim Ballard" <jballard(at)netezza(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Problem with character results
Date: 2001-03-29 18:50:45
Message-ID: 024801c0b881$29cd3460$8300a8c0@planet
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Gaszewski 2001-03-29 21:04:30 LATIN2 and wrong upper() and lower() functions output
Previous Message Bruce Momjian 2001-03-29 13:39:04 Re: Please, fix!!! The backend can crash on your system!