Re: Very puzzling sort behavior - Mailing list pgsql-general

Hi.  In a table that includes these columns:

my_db=> \d tbl_client

...

 name_last                   | character varying(40)          | not null

 name_first                  | character varying(30)          | not null

...

I am extremely puzzled by the sorting of the "CLARKE"s in this list:

my_db=> SELECT name_last,length(name_last),name_last='CLARK' AS clark,name_last='CLARKE' AS clarke FROM tbl_client WHERE name_last ILIKE 'CLARK%' OR name_last ILIKE 'ADAMS%' ORDER BY name_last || ', ' || name_first;

 name_last | length | clark | clarke 

-----------+--------+-------+--------

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMSON   |      7 | f     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARKE    |      6 | f     | t

 CLARKE    |      6 | f     | t

 CLARKE    |      6 | f     | t

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

(17 rows)

The ADAMS are included just to show a similar example is ordering correctly.  I put the length and equality test columns in to try to make sure there weren't some bizarre characters in the data.  This is only happening on one particular database.  I did a reindex on the table just for good measure.  If I remove the name_first piece of the ORDER BY (which doesn't seem like it should matter), it sorts as expected:

my_db=> SELECT name_last,length(name_last),name_last='CLARK' AS clark,name_last='CLARKE' AS clarke FROM tbl_client WHERE name_last ILIKE 'CLARK%' OR name_last ILIKE 'ADAMS%' ORDER BY name_last || ', '               

;

 name_last | length | clark | clarke 

-----------+--------+-------+--------

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMS     |      5 | f     | f

 ADAMSON   |      7 | f     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARK     |      5 | t     | f

 CLARKE    |      6 | f     | t

 CLARKE    |      6 | f     | t

 CLARKE    |      6 | f     | t

(17 rows)

I tried selecting those 17 rows from tbl_client into a new table, and get the same odd behavior.  However, if I run with new data I get an expected order:

CREATE TEMP TABLE test (

  name_first VARCHAR(40),

  name_last VARCHAR(30)

);

INSERT INTO test VALUES ('JOE','CLARKE'),('BILL','CLARK');

SELECT * FROM test ORDER BY name_last;

SELECT * FROM test ORDER BY name_last || ', ' || name_first;

Any thoughts about what's going on, what to do about it, or what obvious point I missing?  Thanks in advance!

my_db=> SELECT version();

                                                    version                                                     

----------------------------------------------------------------------------------------------------------------

 PostgreSQL 9.2.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit

(1 row)

Ken

--

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://agency-software.org/demo/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.