Forum OpenACS Q&A: Case insensitive sort in bboard??

Collapse
1: Case insensitive sort in bboard??
Posted by Barry McMullin on 10/19/01 05:08 PM
Hi Folks -

My apologies if this has been asked (& answered before): I
trawled the archives but couldn't locate anything relevant so
far...

I have OpenACS 3.2.5 running on RH 6.2, postgres 7.0.3.  It's
really a prototype system, not heavily used.

I have a bboard configured for the Q&A interface. Configured to
display new messages at the top.

We just recently noticed that new messages are no longer
appearing at the top, but some way down.  Examining it a little
closer, it seems that we have just rolled the msg_id over from
00000Z to 00000a; but the sorting (on sort_key) seems to be
case-insensitive - so that 00000a is actually appearing along with
0000A rather than before/after 00000Z.

Any pointers on how to "sort" (!) this out???

Thanks,

- B.

Collapse
2: Response to Case insensitive sort in bboard?? (response to 1)
Posted by Jonathan Ellis on 10/19/01 06:12 PM
Turn sql logging on (either in the db pool config, or by turning debug on) and see what it is actually comparing on. All I saw was "order by sort_key" which should be case sensitive: 
bf2=# select 'a' as a union select 'A' as a union select 'Z' as a order by a;
 a
---
 A
 Z
 a
Collapse
3: Response to Case insensitive sort in bboard?? (response to 1)
Posted by Don Baccus on 10/19/01 07:04 PM
Yeah, this seems very strange.  We're not seeing this on the openacs.org  site which is running something close to the same software (i.e. an older version).

Have you got locale enabled and set to something other than "C" in your database?  This can/will screw up sorting of sortkeys depending on which  locale you choose...

Collapse
4: Response to Case insensitive sort in bboard?? (response to 1)
Posted by Barry McMullin on 10/19/01 10:59 PM
Hmmm yes, thanks Don ... the locale setting is surely implicated here.

The setting is thus:

  LC_ALL=en_IE

which *should* be right for where I am (i.e., Ireland).

So I created a dummy text file with lines starting a, b, c, A, B, C
and sorted it with the standard sort command; works as expected (order is A, B, C, a, b, c); but now I try with "sort -l" (which,
according to man, tells sort to use locale support).  Now the
order comes out as A, a, B, b, C, c.

This suggests the problem is with that particular locale setting; but I went on to try en_GB and en_US (!); they gave exactly the same
behaviour.  Finally I tried just "en" and then the order reverted to
the expected!

So OK, I guess it is clear that this is not really an OpenACS issue
... but if anyone has any comments on what is going on here (i.e.
why the locales might have been designed to sort in such a harebrained
way) I'd still appreciate hearing about it...

Thanks again,

- B.

Collapse
5: Response to Case insensitive sort in bboard?? (response to 1)
Posted by Don Baccus on 10/19/01 11:21 PM
Check this thread for more information ...