Sort order in sub-select

pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Sort order in sub-select
3+ messages / 3 participants
[nested] [flat]

* Sort order in sub-select
@ 2006-06-25 18:15 Craig A. James <cjames@emolecules.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Craig A. James @ 2006-06-25 18:15 UTC (permalink / raw)
  To: pgsql-performance

Here is a subtle question about SQL.  I have a one-to-many pair of tables (call them "P" and "C" for parent and child).  For each row of P, there are many rows in C with data, and I want to sort P on the min(c.data).  The basic query is simple:

   select p_id, min(data) as m from c group by p_id order by m;

Now the problem: I also want to store this, in sorted order, as a "hitlist", so I have a table like this:

   create table hitlist(p_id integer, sortorder integer);

and a sequence to go with it.  The first thing I tried doesn't work:

   insert into hitlist(p_id, sortorder)
     (select p_id, nextval('hitlist_seq') from
        (select p_id, min(data) as m from c group by p_id order by m);

Apparently, the sort order returned by the innermost select is NOT maintained as you go through the next select statement -- the rows seem to come out in random order.  This surprised me.  But in thinking about the definition of SQL itself, I guess there's no guarantee that sort order is maintained across sub-selects.  I was caught by this because in Oracle, this same query works "correctly" (i.e. the hitlist ends up in sorted order), but I suspect that was just the luck of their implementation.

Can anyone confirm this, that the sort order is NOT guaranteed to be maintained through layers of SELECT statements?

The obvious solution is to make the hitlist.sortorder column have the nextval() as its default and eliminate the first sub-select.  But I thought the two would be equivalent.

Thanks,
Craig

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Sort order in sub-select
@ 2006-06-30 04:55 Craig A. James <cjames@modgraph-usa.com>
  2006-06-30 06:21 ` Re: Sort order in sub-select Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Craig A. James @ 2006-06-30 04:55 UTC (permalink / raw)
  To: pgsql-performance

Here is a question about SQL.  I have a one-to-many pair of tables (call them "P" and "C" for parent and child).  For each row of P, there are many rows in C with data, and I want to sort P on the min(c.data).  The basic query is simple:

  select p_id, min(data) as m from c group by p_id order by m;

Now the problem: I also want to store this, in sorted order, as a "hitlist", so I have a table like this:

  create table hitlist(p_id integer, sortorder integer);

and a sequence to go with it.  The first thing I tried doesn't work:

  insert into hitlist(p_id, sortorder)
    (select p_id, nextval('hitlist_seq') from
       (select p_id, min(data) as m from c group by p_id order by m);

Apparently, the sort order returned by the innermost select is NOT maintained as you go through the next select statement -- the rows seem to come out in random order.  This surprised me.  But in thinking about the definition of SQL itself, I guess there's no guarantee that sort order is maintained across sub-selects.  I was caught by this because in Oracle, this same query works "correctly" (i.e. the hitlist ends up in sorted order), but I suspect that was just the luck of their implementation.

Can anyone confirm this, that the sort order is NOT guaranteed to be maintained through layers of SELECT statements?

The apparent solution is to make the hitlist.sortorder column have nextval() as its default and eliminate the first sub-select.  But I thought the two would be equivalent.

Thanks,
Craig

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Sort order in sub-select
  2006-06-30 04:55 Sort order in sub-select Craig A. James <cjames@modgraph-usa.com>
@ 2006-06-30 06:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2006-06-30 06:21 UTC (permalink / raw)
  To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance

"Craig A. James" <cjames@modgraph-usa.com> writes:
>   insert into hitlist(p_id, sortorder)
>     (select p_id, nextval('hitlist_seq') from
>        (select p_id, min(data) as m from c group by p_id order by m);

> Apparently, the sort order returned by the innermost select is NOT
> maintained as you go through the next select statement -- the rows seem
> to come out in random order.  This surprised me.

It surprises me too.  This is outside the SQL spec, because the spec
doesn't allow ORDER BY in subselects, but Postgres definitely does and
we expect it to be honored.  Can you provide a complete example and the
EXPLAIN plan that you're getting?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2006-06-30 06:21 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-25 18:15 Sort order in sub-select Craig A. James <cjames@emolecules.com>
2006-06-30 04:55 Sort order in sub-select Craig A. James <cjames@modgraph-usa.com>
2006-06-30 06:21 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox