order by not working in view ?
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
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
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.