Re: unicode and sorting(at least) - Mailing list pgsql-general

> All of the ISO 8xxx encodings and LATINX encodings can handle two langauges, English and at least one other.
Sometimesthey can handle several langauges besides English, and are actually designed to handle a family of langauges.  

ISO 8xxx series are not encodings but character sets. For example,
ISO-8859-1 can be expressed in 8-bit encoding form, it also can be
expressed in 7-bit encoding form. This is called ISO-2022. I know that
PostgreSQL treats ISO-8859-1 as an encoding but it's just a short hand
for "8-bit encoded ISO-8859-1".

Also, let's not mix together "languages" and "character
sets". Langugaes are defined by human, not by computers. While
character sets are perfectly definable by computers. More important
thing is that a language can be expressed in several character
sets. For example language Japanese can be expressed in EUC-JP of
cousrse. It also can be expressed in ASCII by using ROMAJI script.

What I want to say here is talking about "languages" is almost
useless and we have to talk about character sets and encodings.

> The ONLY encodings that can handle a significant amount of multiple langauges and character sets are the ISO/UTF/UCS
series.(UCS is giving way to UTF). In fact they can handle every human langauge ever used, plus some esoteric ones
postulated,and there is room for future languages. 
>
> So, for a column to handle multiple langauges/character sets, the languages/character sets have to be in the family
thatthe database's encoding was defined for(in postgres currently, choosing encoding down to the column level is
availableon several databases and is the SQL spec), OR, the encoding for the database has to be UTF8 (since we don't
haveUTF16 or UTF32 available) 
>
> Right now, the SORTING algorithm and functionality is fixed for the database cluster, which contains databases of any
kindof encodings. It really does not do much good to have a different locale than the encoding, except for UTF8, which
asan encoding is langauge/character set neutral, or SQL_ASCII and an ISO8xxx or LatinX encoding. Since a running
instanceof Postgres can only be connected to one cluster, a database engine has FIXED sorting, no matter what
language/characterset encoding is chosen for the database.  

The sorting order problem is not neccessary limited to "clutser
vs. locale" one. My example about ROMAJI above raises another question
"How to sort ROMAJI Japanese?" If we regard it just ASCII strings, we
could sort it in alphabetical order. But if we regard it as Japanaese,
probably sorting in alphabetical order is not appropreate. This
example shows that the sorting order should be defined by users or
applications, not by systems or DBMSs. This is why the SQL standard
has "COLLATION" concept IMO.

> It so happens that most non UTF encodings are designed to sort well in an extended ascii/8 bit environment, which is
whatthe ISO8xxxx and LatinX encodings actually are. I'm not sure that it's perfect though. So, if SQL_ASCII is chosen
forthe LOCALE, and the encoding is ISO8xxx or LATINx, it will probably sort OK. 
>
> UTF8/16/32 is built the same way. However, this only applies per character, and only works painlessly on UTF32, which
hasfixed width characters. UTF8/16 OTOH, have variable length characters (in multiples of 8 bits). Since SQL_ASCII
sortsin a binary fashion, UTF8/16 won't sort correctly under SQL_ASCII locale, I believe. 
> Tatsuo Ishii wrote:
>
> >>On Wed, 23 Jun 2004, Dennis Gearon wrote:
> >>
> >>
> >>>This is what has to be eventually done:(as sybase, and probably others do it)
> >>>
> >>>    http://www.ianywhere.com/whitepapers/unicode.html
> >>
> >>Actually, what probably has to be eventually done is what's in the SQL
> >>spec.
> >>
> >>Which is AFAICS basically:
> >> Allow multiple encodings
> >> Allow multiple character sets (within an encoding)
> >
> >
> > Could Please explain more details for above. In my understanding a
> > character set can have multiple encodings but...
> > --
> > Tatsuo Ishii
> >
> >
> >> Allow one or more collations per character set
> >> Allow columns to specify character set and collation
> >> Allow literals in multiple character sets
> >> Allow translations and encoding conversions (as supported)
> >> Allow explicit COLLATE clauses to control ordering and comparisons.
> >> Handle identifiers in multiple character sets
> >>
> >>plus some misc things like allowing sets that control the default
> >>character set for literals for this session and such.
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 9: the planner will ignore your desire to choose an index scan if your
> >>      joining column's datatypes do not match
> >>
> >
> >
>