Re: Changed default ordering in tables

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