BUG #6743: BETWEEN operator does not work for char(1)
The following bug has been logged on the website:
Bug reference: 6743
Logged by: Spătărel Dan
Email address: spatarel1@yahoo.com
PostgreSQL version: 9.1.4
Operating system: Windows Vista SP2
Description:
I use "UTF8" charset and "Romania, Romanian" locale.
I came across this as I wanted to test if a symbol was a letter:
SELECT 'a' BETWEEN 'a' AND 'z'; -- true
SELECT 'z' BETWEEN 'a' AND 'z'; -- true
SELECT 'A' BETWEEN 'a' AND 'z'; -- true
SELECT 'Z' BETWEEN 'a' AND 'z'; -- false (!)
SELECT 'a' BETWEEN 'A' AND 'Z'; -- false (!)
SELECT 'z' BETWEEN 'A' AND 'Z'; -- true
SELECT 'A' BETWEEN 'A' AND 'Z'; -- true
SELECT 'Z' BETWEEN 'A' AND 'Z'; -- true
It seems that the intent is for the comparison to be case-insensitive, but
in some limit-cases it fails.
Please let me know if this turns out to be a real bug on not.
spatarel1@yahoo.com writes:
I use "UTF8" charset and "Romania, Romanian" locale.
I came across this as I wanted to test if a symbol was a letter:
SELECT 'a' BETWEEN 'a' AND 'z'; -- true
SELECT 'z' BETWEEN 'a' AND 'z'; -- true
SELECT 'A' BETWEEN 'a' AND 'z'; -- true
SELECT 'Z' BETWEEN 'a' AND 'z'; -- false (!)
SELECT 'a' BETWEEN 'A' AND 'Z'; -- false (!)
SELECT 'z' BETWEEN 'A' AND 'Z'; -- true
SELECT 'A' BETWEEN 'A' AND 'Z'; -- true
SELECT 'Z' BETWEEN 'A' AND 'Z'; -- true
It seems that the intent is for the comparison to be case-insensitive, but
in some limit-cases it fails.
I dunno exactly what sorting rule is implemented by Windows' Romanian
locale, but these examples are consistent with the theory that the
sort order is like aAbBcC ... zZ. In any case, you need to complain
to Microsoft if you don't like the locale's sort order. We just do
what the platform's strcoll() function tells us to.
regards, tom lane