From: | "Jim C(dot) Nasby" <decibel(at)decibel(dot)org> |
---|---|
To: | "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: ORDER BY <field not in return list> |
Date: | 2005-07-25 22:10:45 |
Message-ID: | 20050725221045.GC29346@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Jul 25, 2005 at 07:06:46PM -0300, Marc G. Fournier wrote:
>
>
> On Mon, 25 Jul 2005, Jim C. Nasby wrote:
>
> >On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote:
> >>
> >>Just curious as to whether or not a warning or something should be issued
> >>in a case like:
> >>
> >> SELECT c.*
> >> FROM company c, company_summary cs
> >> WHERE c.id = cs.id
> >> AND cs.detail = 'test'
> >>ORDER BY cs.fullname;
> >>
> >>Unless I'm missing something, the ORDER BY clause has no effect, but an
> >>EXPLAIN shows it does take extra time, obviously ...
> >
> >Uh, I'd hope it had an effect. Note that RDBMSes have been moving
> >towards allowing fields in ORDER BY that aren't in the SELECT list,
> >though in the past it was common that anything in ORDER BY had to also
> >be in SELECT.
>
> 'k, in the test case I've been working with, the query always returns 1
> row, so my test case wouldn't have shown a difference ... but, if it does
> have an affect, how? The ORDER BY is on the final result set, and if
> there is no cs.fullname in that result, what exactly is it ordering?
decibel=# select usename, usesysid from pg_user;
usename | usesysid
----------+----------
postgres | 1
decibel | 100
(2 rows)
decibel=# select usesysid from pg_user order by usename;
usesysid
----------
100
1
(2 rows)
decibel=# explain analyze select usesysid from pg_user order by usename;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Sort (cost=1.02..1.02 rows=1 width=68) (actual time=0.182..0.183 rows=2 loops=1)
Sort Key: pg_shadow.usename
-> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 width=68) (actual time=0.060..0.077 rows=2 loops=1)
Total runtime: 0.518 ms
(4 rows)
decibel=#
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2005-07-25 22:15:25 | Re: [HACKERS] Patch to fix plpython on OS X |
Previous Message | Marc G. Fournier | 2005-07-25 22:08:10 | Re: ORDER BY <field not in return list> |