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
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! |