Thread: ORDER BY
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 ...
On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: > On Mon, 2005-07-25 at 19:08 -0300, Marc G. Fournier wrote: >> >> On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: >> >>> On Mon, 2005-07-25 at 18:11 -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; >>> >>> Seems like it should work. Is it not returning in fullname order in >>> your tests? >> >> Full name isn't a field in the results, so how would it be ORDERing based >> on it? fullname is a field in the table being joined in order to restrict >> the results to just those with cs.detail = 'test' ... but company itself >> doesn't have a field fullname ... > > I'm still not seeing the problem. cs.fullname is in the product of the > join, and you can order the result thereby, and not return the column. That's what I was missing ... "the product of the join" ... I was seeing the end result as being all that an ORDER BY had to work with, vs the JOIN of all the tables ...
On Mon, 2005-07-25 at 19:08 -0300, Marc G. Fournier wrote: > > On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: > > > On Mon, 2005-07-25 at 18:11 -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; > > > > Seems like it should work. Is it not returning in fullname order in > > your tests? > > Full name isn't a field in the results, so how would it be ORDERing based > on it? fullname is a field in the table being joined in order to restrict > the results to just those with cs.detail = 'test' ... but company itself > doesn't have a field fullname ... I'm still not seeing the problem. cs.fullname is in the product of the join, and you can order the result thereby, and not return the column. -jwb
Am Montag, den 25.07.2005, 18:11 -0300 schrieb Marc G. Fournier: > 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 ... It just does the sorting as you requested. Check the order of the resulting c.id. See: experiment=# SELECT * FROM A;a_id | a_value ------+--------- 1 | abc 2 | bcd 3 | def (3 rows) experiment=# SELECT * FROM B;b_id | b_value ------+--------- 1 | xyz 2 | ijk 3 | abc (3 rows) experiment=# SELECT a.* FROM a,b WHERE a.a_id=b.b_id ORDER BY b_value;a_id | a_value ------+--------- 3 | def 2 | bcd 1 | abc (3 rows) So no reason for warnings :-)
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 ...
Why would it have no effect? If there is a one to many mapping between
fullname and id, and if the rows in c with the same fullname have
different rows in c.*, then it does matter.
For the casual observer, only seeing the output of the table, they would
see a consistent reporting order, but would be unable to derive how the
table rows were sorted. :-)
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness
bindthem...
http://mark.mielke.cc/
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 | 1decibel | 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; QUERYPLAN ----------------------------------------------------------------------------------------------------------Sort (cost=1.02..1.02rows=1 width=68) (actual time=0.182..0.183 rows=2 loops=1) Sort Key: pg_shadow.usename -> Seq Scan onpg_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@decibel.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?"
On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: > On Mon, 2005-07-25 at 18:11 -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; > > Seems like it should work. Is it not returning in fullname order in > your tests? Full name isn't a field in the results, so how would it be ORDERing based on it? fullname is a field in the table being joined in order to restrict the results to just those with cs.detail = 'test' ... but company itself doesn't have a field fullname ...
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?
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. -- Jim C. Nasby, Database Consultant decibel@decibel.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?"
> 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. Prior to SQL:1999, the spec required that any column referenced in an ORDER BY clause must also be referenced in the SELECT. SQL:1999 (feature E1210-02) relaxed this to allow columns to be specified in the ORDER BY clause but not in the SELECT. -- Matt Emmerton
On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: > On Mon, 2005-07-25 at 19:08 -0300, Marc G. Fournier wrote: >> >> On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: >> >>> On Mon, 2005-07-25 at 18:11 -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; >>> >>> Seems like it should work. Is it not returning in fullname order in >>> your tests? >> >> Full name isn't a field in the results, so how would it be ORDERing based >> on it? fullname is a field in the table being joined in order to restrict >> the results to just those with cs.detail = 'test' ... but company itself >> doesn't have a field fullname ... > > I'm still not seeing the problem. cs.fullname is in the product of the > join, and you can order the result thereby, and not return the column. 'k, this creates another question ... "product of the join" ... if I do a join on three tables, let's say that each has 10 fields in them (highly unlikely, but humor me) ... do the joins end up creating, in memory, a "temporary table" that includes all 30 fields? Basically, from how everyone has explained it, the ORDER BY will be done after all the JOINs are done, and the "product of the joins" are complete ... for it to be performed on a field not in the SELECT <field> clause, then those fields have to be "loaded into memory", *then* ORDERed, and then the query would return teh result set out of those "mega table" ... Is this correct? From what I've read, I believe this to be so ... but figured I'd double check to make sure I'm not missing something ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc, > Basically, from how everyone has explained it, the ORDER BY will be done > after all the JOINs are done, and the "product of the joins" are > complete ... for it to be performed on a field not in the SELECT <field> > clause, then those fields have to be "loaded into memory", *then* > ORDERed, and then the query would return teh result set out of those > "mega table" ... Pretty much, yes. We have to load the referenced rows anyway because that's the only way to locate visibility information, which is critical for an inner join. However, this discussion doesn't really belong on -hackers. Why don't you bring it over to IRC? -- --Josh Josh Berkus Aglio Database Solutions San Francisco