ORDER BY <field not in return list>

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, 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, 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, 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@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?"

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, 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

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, 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 bind them...

http://mark.mielke.cc/

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