Skip site navigation (1) Skip section navigation (2)

Re: Changed default ordering in tables

From: Erwin Brandstetter <brsaweda(at)gmail(dot)com>
To: Dave Page <dpage(at)pgadmin(dot)org>
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: Changed default ordering in tables
Date: 2011-07-29 05:10:08
Message-ID: 4E3240B0.3070701@gmail.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
On 29.07.2011 04:57, Erwin Brandstetter wrote:
> On 26.07.2011 23:20, Dave Page wrote:
>> On Tue, Jul 26, 2011 at 9:53 PM, Erwin Brandstetter
>> <brandstetter(at)falter(dot)at>  wrote:
>>> That may be the key here. When I open the simple test table from 
>>> above in
>>> v1.14 I get descending order. On opening the Sort/Filter dialog it 
>>> see an
>>> explicit ORDER BY test_id DESCENDING - which I did not set.
>>> Once I delete that, the sort order falls back to ascending - as it 
>>> should to
>>> begin with.

Actually, the sort order falls back to random, which happens to be 
ascending by chance in the test case.

I ran more tests. (on Windows XP Pro) The results for multiple-row pkeys 
might interest you:
Only the sort ordering for the _last_ index field is reversed.
Try these test case with 1,2 and three pkey columns:

     CREATE TABLE test1(id1 int PRIMARY KEY);
     INSERT INTO test1 VALUES (1),(2),(3),(4),(5),(6);

     CREATE TABLE test2(id1 int, id2 int, CONSTRAINT test2_pk PRIMARY 
KEY (id1, id2));
     INSERT INTO test2 VALUES 
(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3);

     CREATE TABLE test3(id1 int, id2 int, id3 int, CONSTRAINT test3_pk 
PRIMARY KEY (id1, id2, id3));
     INSERT INTO test3 VALUES 
(1,1,1),(1,1,2),(1,1,3),(1,2,1),(1,2,2),(1,2,3),(1,3,1),(1,3,2),(1,3,3);

If you open these tables, the server gets:
     SELECT * FROM public.test1 ORDER BY id1 DESC
     SELECT * FROM public.test2 ORDER BY id1, id2 DESC
     SELECT * FROM public.test3 ORDER BY id1, id2, id3 DESC

IOW, a single ' DESC' is appended to the SELECT, which is a bug in any case.
If DESC was intended, it would apply on all three columns.

It puzzles me, that Dave cannot reproduce it on his Mac. Can sombody 
else give it a shot and report back?
Do you want me to open a ticket?


-- 

Erwin Brandstetter


In response to

Responses

pgadmin-hackers by date

Next:From: Erwin BrandstetterDate: 2011-07-29 08:14:16
Subject: Re: Changed default ordering in tables
Previous:From: Erwin BrandstetterDate: 2011-07-29 02:57:51
Subject: Re: Changed default ordering in tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group