Postgres Pro
Downloads
Home   >   mailing lists

Thread: order by not working in view ?

order by not working in view ?

From
David Gauthier
Date:
psql (9.6.7, server 11.3) on linux

In the copy/paste below, first 2 lines returned by a select on the view, why didn't it sort on start_datetime correctly ?  I would think that the one started on 04-08 would come before the one on 04-09 ?

dvdb=> \d sim_phases;
                Table "dvm.sim_phases"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 sj_id          | integer                  |
 sim_phase_name | character varying        |
 status         | character varying        |
 error_message  | character varying        |
 start_datetime | timestamp with time zone |
 end_datetime   | timestamp with time zone |
 duration_hrs   | numeric(5,2)             |
Check constraints:
    "sim_phases_name_check" CHECK (sim_phase_name::text = ANY (ARRAY['presim'::character varying::text, 'runsim'::character varying::text, 'postsim'::character varying::text, NULL::character varying::text]))
    "sim_phases_status_check" CHECK (status::text = ANY (ARRAY['in_progress'::character varying, 'completed'::character varying, 'passed'::character varying, 'failed'::character varying, NULL::character varying]::text[]))
Foreign-key constraints:
    "sim_phases_sj_id_fkey" FOREIGN KEY (sj_id) REFERENCES sim_jobs(sj_id) ON DELETE CASCADE

dvdb=> create or replace view spview as (select sj_id,sim_phase_name,status,start_datetime,end_datetime,duration_hrs from dvm.sim_phases order by sj_id,start_datetime);
CREATE VIEW
dvdb=> select * from spview where sj_id in (select sj_id from sjview where dvm_id = 1102);
 sj_id | sim_phase_name |   status    |     start_datetime     |      end_datetime      | duration_hrs
-------+----------------+-------------+------------------------+------------------------+--------------
  6269 | runsim         | in_progress | 2020-04-09 03:39:13-04 |                        |            
  6269 | presim         | completed   | 2020-04-08 23:11:21-04 | 2020-04-09 03:39:13-04 |         4.46
  6267 | runsim         | in_progress | 2020-04-09 02:21:38-04 |                        |            
  6267 | presim         | completed   | 2020-04-08 23:11:21-04 | 2020-04-09 02:21:38-04 |         3.17

Re: order by not working in view ?

From
Tom Lane
Date:
David Gauthier <davegauthierpg@gmail.com> writes:
> In the copy/paste below, first 2 lines returned by a select on the view,
> why didn't it sort on start_datetime correctly ?

Putting an ORDER BY in a view is a bit dangerous (last I looked,
it wasn't even legal in standard SQL).  Yeah, the view will sort,
but there is nothing compelling the calling query to preserve
the ordering.

EXPLAIN would give you more info, but I'm betting that the IN is being
converted to a semijoin and then done with a non-order-preserving join
method.

            regards, tom lane

Re: order by not working in view ?

From
"David G. Johnston"
Date:
On Thursday, April 9, 2020, David Gauthier <davegauthierpg@gmail.com> wrote:
psql (9.6.7, server 11.3) on linux

In the copy/paste below, first 2 lines returned by a select on the view, why didn't it sort on start_datetime correctly ?  I would think that the one started on 04-08 would come before the one on 04-09 ?
[...]
dvdb=> create or replace view spview as (select sj_id,sim_phase_name,status,start_datetime,end_datetime,duration_hrs from dvm.sim_phases order by sj_id,start_datetime);
CREATE VIEW
dvdb=> select * from spview where sj_id in (select sj_id from sjview where dvm_id = 1102);

You should be including Explain output when posting questions like this.

Just because the from clause relation is ordered does not mean the final result will be.  In this case the system fetched rows from the ordered view out of order during fulfillment of the where expression (this may not be true implementation but it is seemingly what happened).  The optimizations the planner is allowed to make are not constrained by order by).

In short, adding order by to views is misleading to the user unless the user only writes (select * from viewname;)  Queries that export data and want ordering need to specify it themselves.

David J.