Re: Default order on displaying data without order clause

Done. #4092

Thanks,
Dave

On Mon, Mar 18, 2019 at 5:57 AM Dave Page <dpage(at)pgadmin(dot)org> wrote:

> If someone wants to log a feature request for this, it'll probably get
> done relatively soon. It's not an unreasonable request for the View/Edit
> data tool (obviously not the Query Tool mode, as discussed).
>
> On Fri, Mar 15, 2019 at 6:36 PM Dave Caughey <caugheyd(at)gmail(dot)com> wrote:
>
>> I think you're confusing my point.
>>
>> You're right that you never want to add an *implicit* default to the
>> order clause. That would be changing the SQL standard which, as you've
>> noted, would be really bad.
>>
>> But, we're talking about a tool that constructs *explicit* queries in
>> order to display the data in the table... I.e., when you right-click on a
>> table in pgAdmin and select "View/Edit Data...", pgAdmin is fully in
>> control of whether it generates either
>>
>> SELECT * FROM public.sometable
>>
>>
>> or
>>
>> SELECT * FROM public.sometable ORDER BY somecolumn ASC
>>
>>
>> In the second case, there is no *implicit* default... it's very
>> explicitly specified, and does not in anyway change/contradict the SQL
>> standard. It's just the tool stepping in and doing what I end up having to
>> do manually. every. frickin'. time.
>>
>> Another UI concept that could be applied here is to simply remember my
>> previous settings, on a table-by-table basis. E.g., if I do a "View/Edit
>> Data..." once, and *explicitly* change the ORDER BY clause to be "ORDER BY
>> price DESC" then it's very a very smart thing to add to SELECT the next
>> time I do a "View/Edit Data..." on that table. And if I subsequently
>> change to be "ORDER BY qty ASC" then that becomes a smart default to
>> continue to use on subsequent "View/Edit Data..." queries. Or, if I
>> actually remove the default ORDER BY, then it's smart thing to remember
>> that choice and continue to use no ORDER BY.
>>
>> But certainly, if the user explicitly indicates that they *do* want an
>> ORDER BY clause, then continuously defaulting to no ORDER BY clause each
>> time they do a "View/Edit Data...", on the same table, is arbitrarily
>> picking a behaviour that you clearly know that the user doesn't prefer.
>> Why not just do for the user the same thing they explicitly asked you to do
>> for last time? From a UX perspective, this approach requires the least
>> amount of interaction by the user in order to get what exactly they want.
>>
>> And from a UX design perspective, the advantage of the this approach is
>> that it doesn't require yet-another option to support/document.
>>
>> Cheers,
>> Dave
>>
>>
>> On Fri, Mar 15, 2019 at 1:59 PM Doug Easterbrook <doug(at)artsman(dot)com>
>> wrote:
>>
>>> I’m disagreeing with dave.
>>>
>>> to be clear. you NEVER want to add an implicit default to the order
>>> clause. EVER.
>>>
>>> Postgres returns data in the order that it returns it (sql standard)
>>> unless you specify otherwise.
>>>
>>> the python mantra: it is better to be EXPLICIT than IMPLICIT.
>>>
>>> If you do not provide an explicit sort order and rely on some implicit
>>> ordering, then your code will break in the future if you are relying on the
>>> order from the engine… and its harder to find problems if pgadmin has
>>> different behaviour than sending the same query through psql or the
>>> postgres libraries.
>>>
>>> final point is that PGadmin just gives the database your query. if
>>> Pgadmin altered my SQL code for me, I’d be angry since i’ve wanted to know
>>> what order postgres felt it wanted to return the data.
>>>
>>> its not pgadmin's job to fix my mistakes. it my job to fix my mistakes.
>>>
>>>
>>> do I agree that people want a natural order in a gui. Absolutely
>>> but you need to be explicit which one.
>>>
>>>
>>>
>>> *Doug Easterbrook*
>>> *Arts Management Systems Ltd.*
>>> mailto:doug(at)artsman(dot)com <doug(at)artsman(dot)com>
>>> http://www.artsman.com
>>> Phone (403) 650-1978
>>>
>>> On Mar 15, 2019, at 8:24 AM, Dave Caughey <caugheyd(at)gmail(dot)com> wrote:
>>>
>>> Yeah, but that's just what the spec says about how the SELECT works.
>>> It's not a statement about how a user interface should display information.
>>>
>>> Presenting unordered information is basically guaranteed to be never
>>> what the user wants. In this case, the user has to *almost always*
>>> edit the query to add a sensible "order by" clause.
>>>
>>> On the other hand, picking a default column (e.g., PK, or insertion
>>> order) is probably going to be the desirable behaviour most of the time
>>> (but rarely, not). In this case, the user has *almost never* edit the
>>> query to remove/alter the default "order by" clause.
>>>
>>> So you've got a UI decision of doing something that'll be "right" (i.e.,
>>> what the user wants) most of the time, or "wrong" (i.e., what the user
>>> doesn't want) most of the time. This is an easy win.
>>>
>>> Cheers,
>>> Dave
>>>
>>>
>>> On Fri, Mar 15, 2019 at 9:19 AM Wim Bertels <wim(dot)bertels(at)ucll(dot)be> wrote:
>>>
>>>> Janus schreef op vr 15-03-2019 om 12:20 [+0100]:
>>>> > In pgAdmin 3, an implicit default order by primary key is used when
>>>> > displaying data without an otherwise stated order. This is not the
>>>> > case in pgAdmin 4. The order seems somewhat arbitrary and the
>>>> > following statement from this site is definitely not true: "When you
>>>> > query data from a table, PostgreSQL returns the rows in the order
>>>> > that they were inserted into the table."
>>>>
>>>> Hallo Janus,
>>>>
>>>> this standard behaviour, as described by the ISO standard
>>>> :: ie unless you specify an ORDER BY clause, the order can be anything,
>>>> like a (unordered) set;
>>>>
>>>> the example of "the way there were inserted", this might often be the
>>>> case, but it's not a rule
>>>> more background: see planner, shared mem and cache
>>>>
>>>> >
>>>> > I do understand that some order of course exists, but from a
>>>> > human perspective it would make sense that default ordering is by PK,
>>>> > unless stated otherwise. In PgAdmin 4, is it possible to set a
>>>> > default ordering, at least so that displaying a table's contents
>>>> > using the "View Data" button, i.e. the query being run is altered
>>>> > from "SELECT oid, * FROM public.mytable" to "SELECT oid, * FROM
>>>> > public.mytable ORDER BY <primary key(s)>"?
>>>>
>>>> i only see the filter/sort option
>>>>
>>>> >
>>>> > Thanks!
>>>> --
>>>> mvg,
>>>> Wim Bertels
>>>> --
>>>> Lector
>>>> UC Leuven-Limburg
>>>> --
>>>> Let me take you a button-hole lower.
>>>> -- William Shakespeare, "Love's Labour's Lost"
>>>>
>>>>
>>>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>