Re: interesting finding on order by behaviour

I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL
9.0.4 and found something interesting...

set up
=====
drop table t1
create table t1 (f1 varchar(100))
insert into t1 (f1) values ('AbC')
insert into t1 (f1) values ('CdE')
insert into t1 (f1) values ('abc')
insert into t1 (f1) values ('ABc')
insert into t1 (f1) values ('cde')

test
===
select * from t1 order by f1
select min(f1) as min, max(f1) as max from t1

results
=====
SQL Server 2008 R2 (with case insensitive data, the ordering follows
ASCII order)

f1
---
AbC
abc
ABc
cde
CdE

min max
------ -------
AbC CdE

Oracle 10 (data is case-sensitive, the ordering follows ASCII order)

f1
---
ABc
AbC
CdE
abc
cde

min max
------ -------
ABc cde

PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ...
DIFFERENT)

f1
---
abc
AbC
ABc
cde
CdE

min max
------ -------
abc CdE

On Fri, 2011-07-22 at 10:11 -0700, Samuel Hwang wrote:

> I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL
> 9.0.4 and found something interesting...
> results
> =====
> SQL Server 2008 R2 (with case insensitive data, the ordering follows
> ASCII order)
>
> Oracle 10 (data is case-sensitive, the ordering follows ASCII order)
>
> PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ...
> DIFFERENT)
>
>

perhaps http://www.postgresql.org/docs/9.1/static/charset.html will
provide an answer

On Jul 22, 2011, at 11:11 AM, Samuel Hwang wrote:

> results
> =====
> SQL Server 2008 R2 (with case insensitive data, the ordering follows
> ASCII order)
>
> f1
> ---
> AbC
> abc
> ABc
> cde
> CdE

Well, if it's case insensitive, then AbC & abc & ABc are all equal, so any order for those 3 would be correct...

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Jul 22, 12:20 pm, scott_r(dot)(dot)(dot)(at)elevated-dev(dot)com (Scott Ribe) wrote:
> On Jul 22, 2011, at 11:11 AM, Samuel Hwang wrote:
>
> > results
> > =====
> > SQL Server 2008 R2 (with case insensitive data, the ordering follows
> > ASCII order)
>
> > f1
> > ---
> > AbC
> > abc
> > ABc
> > cde
> > CdE
>
> Well, if it's case insensitive, then AbC & abc & ABc are all equal, so any order for those 3 would be correct...
>
> --
> Scott Ribe
> scott_r(dot)(dot)(dot)(at)elevated-dev(dot)comhttp://www.elevated-dev.com/
> (303) 722-0567 voice
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Sorry I didn't make it clear, the interesting part is how PostgreSQL
sorts data.

The server encoding is set to UTF8 and collation is united states.1252
The client encoding is Unicode.

Samuel Hwang wrote:
> I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL
> 9.0.4 and found something interesting...
>
> set up
> =====
> drop table t1
> create table t1 (f1 varchar(100))
> insert into t1 (f1) values ('AbC')
> insert into t1 (f1) values ('CdE')
> insert into t1 (f1) values ('abc')
> insert into t1 (f1) values ('ABc')
> insert into t1 (f1) values ('cde')
>
> test
> ===
> select * from t1 order by f1
> select min(f1) as min, max(f1) as max from t1
>
> results
> =====
> SQL Server 2008 R2 (with case insensitive data, the ordering follows
> ASCII order)
>
> f1
> ---
> AbC
> abc
> ABc
> cde
> CdE
>
> min max
> ------ -------
> AbC CdE
>
> Oracle 10 (data is case-sensitive, the ordering follows ASCII order)
>
> f1
> ---
> ABc
> AbC
> CdE
> abc
> cde
>
> min max
> ------ -------
> ABc cde
>
> PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ...
> DIFFERENT)
>
> f1
> ---
> abc
> AbC
> ABc
> cde
> CdE
>
> min max
> ------ -------
> abc CdE
>

> The server encoding is set to UTF8 and collation is united states.1252
> The client encoding is Unicode.

I can only speak about Oracle and PostgreSQL.

The problem is that they use different collations.

I don't know what NLS_LANGUAGE is set to in your Oracle session, but I
assume
that it is AMERICAN. You can check with
SELECT value FROM nls_session_parameters WHERE
parameter='NLS_LANGUAGE';

Sorting in Oracle is controled by the NLS_SORT parameter, which by
default
is set to BINARY if NLS_LANGUAGE is AMERICAN, which is why you get ASCII
ordering (in GERMAN, it would be different :^/ ).

PostgreSQL uses the operating system's collation, which in your case
gives
you linguistic ordering.

In Oracle, try something like
ALTER SESSION SET NLS_SORT = 'GENERIC_M';
for a non-binary sorting order, and in PostgreSQL (before 9.1), create
your
database with C collation for binary sorting order.

You can force binary order in PostgreSQL with
SELECT * FROM t1 ORDER BY f USING ~<~;

Yours,
Laurenz Albe

Thanks for the info. That clarify things :)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/interesting-finding-on-order-by-behaviour-tp4623884p4632301.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.