Re: Changed default ordering in tables

On 29.07.2011 07:10, Erwin Brandstetter wrote:
> 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@falter.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?

Actually, the feature "View data" .. "Last 100 rows" is broken for
multi-column pkeys. It send a query of the form (same as above):
     SELECT * FROM public.test3 ORDER BY id1, id2, id3 DESC
Where it should be:
     SELECT * FROM public.test3 ORDER BY id1 DESC, id2 DESC, id3 DESC

So, that's two distinct issue:
- "Last 100 rows" broken for multi-column pkeys.
- Default sort order DESC for edit grid, where it should be ASC.

--

Erwin Brandstetter