pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Max Pyziur <pyz@panix.com>
To: pgsql-admin@postgresql.org
To: pgsql-sql@postgresql.org
To: pgsql-bugs@hub.org
Subject: "ORDER BY" issue - is this a bug?
Date: Sun, 10 Sep 2000 14:19:08 -0400
Message-ID: <39BBD09C.C8F6B3F8@panix.com> (raw)

Greetings, 

I've been using postgresql for some time and currently am using it in three
different environments (specific details below) w/ the first two being 6.5.x
releases and the third  7.0.2-2:

I've come across what I consider an odd sorting anomaly in 7.0.2-2 where running
the following query:
select headline from headlines where headline like 'Alb%' order by 1 ;

The results in the 7.0.2-2 install come back case-insensitive and oblivious to
punctuation.  This seems to me to be a bug; case-insensitive ordering can be
achieved with the use of UPPER() or LOWER() functions, otherwise why have the
functions.  Also, I've created three test databases on the 7.0.2-2 system each
with a different specified encoding  - 
         List of databases
  Database  |  Owner   | Encoding  
------------+----------+-----------
 headlines  | deckard  | WIN
 headlines2 | deckard  | SQL_ASCII
 headlines3 | deckard  | LATIN1

to see if the results are different.  Each of the encodings produces the same
anomalous results.

Any help in this regard as well as resolving it would be appreciated.  Details
of systems and schema are appended below.

-- 
Max Pyziur                                     BRAMA - Gateway Ukraine
pyz@brama.com                                  http://www.brama.com/


The Details:

On the first two systems the results are as follows:
headline                                                     
-------------------------------------------------------------
Albright Arrives in Ukraine to Boost Reforms                 
Albright Calls on Ukraine to Announce Chernobyl Closure      
Albright Calls on Ukraine to Speed up Military Reforms       
Albright Hopeful on Chernobyl Cover                          
Albright Meets Ukraine Officials                             
Albright Reschedules Ukraine Visit                           
Albright Throws Weight Behind Ukraine's Kuchma               
Albright To Hold Talks in Ukraine                            
Albright plans to show support for reform in visit to Ukraine
Albright plans to show support for reform in visit to Ukraine
Albright rushes to kyiv ahead of putin                       
Albright says U.S. still backs Kuchma                        
Albright to perform balancing act in Central Asia            
Albright, in Kiev, Hails Russian START-2 Vote                
(14 rows)
#####################################################################

On the 7.0.2-2 system it is:
                           headline                            
---------------------------------------------------------------
 Albright Arrives in Ukraine to Boost Reforms 
 Albright Calls on Ukraine to Announce Chernobyl Closure
 Albright Calls on Ukraine to Speed up Military Reforms
 Albright Hopeful on Chernobyl Cover 
 Albright, in Kiev, Hails Russian START-2 Vote
 Albright Meets Ukraine Officials 
 Albright plans to show support for reform in visit to Ukraine
 Albright plans to show support for reform in visit to Ukraine
 Albright Reschedules Ukraine Visit 
 Albright rushes to kyiv ahead of putin
 Albright says U.S. still backs Kuchma
 Albright Throws Weight Behind Ukraine's Kuchma 
 Albright To Hold Talks in Ukraine 
 Albright to perform balancing act in Central Asia
(14 rows)
#####################################################################



1 - Production - Solaris 2.6 running Postgresql 6.5.2 on a small Sparc 2
compiled from source
2 - Development - RH5.2 Linux 2.0.36 running Postgresql 6.5.3 installed from
rpms
3 - Development - RH6.2 Linux 2.2.14-5.0smp running Postgresql 7.0.2-2 installed
from stock rpms

Database table schema:
headlines=> \d headlines
Table    = headlines
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| headline_id                      | int4 not null default nextval (  |     4 |
| headline                         | text                             |   var |
| url                              | text                             |   var |
| postdate                         | date                             |     4 |
| source                           | text                             |   var |
| flags                            | text                             |   var |
| posttime                         | timestamp                        |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  headlines_headline_id_key
          hl_pdate
          hl_s_pd
          hl_srce



-- 
Max Pyziur                                     BRAMA - Gateway Ukraine
pyz@brama.com                                  http://www.brama.com/



view thread (7+ messages)  latest in thread

Message-ID: <39BBD09C.C8F6B3F8@panix.com>
Permalink:  ../39BBD09C.C8F6B3F8@panix.com/
Also on:    postgresql.org/message-id/39BBD09C.C8F6B3F8@panix.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pyz@panix.com, pgsql-admin@postgresql.org, pgsql-bugs@hub.org
  Subject: Re: "ORDER BY" issue - is this a bug?
  In-Reply-To: <39BBD09C.C8F6B3F8@panix.com>
  References: <39BBD09C.C8F6B3F8@panix.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox