BUG #14405: ORDER BY TABLENAME, possible bug

Hi:

On Sat, Oct 29, 2016 at 2:19 PM, <udv.mail@gmail.com> wrote:

The following bug has been logged on the website:
E.g. query:

SELECT col1, col2, col3
FROM table1
ORDER BY table1

Postgres uses col1 for ASC ordering, if we write "ORDER BY table1
DESC" then DESC-ordering. I'm not sure this is a bug, but didn't find
description for such behaviour.

That piked my curiosity, and I found doing the equivalent of (select
table1 from table1) sends back a single column output of record
values, with one record in each one, so if col1 is the first column in
table1 as rows seem to sort lexicographically that will explain the
behaviour. Try it yourself. ( Note, not the same as select table1.*
from table1, which gives n column )

Then, I do not remember whether that's a bug or a feature, and have
not been able to see it in the docs, do not even know how to do it (
tried some places without luck ), so someone more knowledgeable can
point us in the right direction.

Francisco Olarte.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Francisco Olarte <folarte@peoplecall.com> writes:

Then, I do not remember whether that's a bug or a feature, and have
not been able to see it in the docs, do not even know how to do it (
tried some places without luck ), so someone more knowledgeable can
point us in the right direction.

Yeah: really, "tablename" is a whole-row variable, and "tablename.colname"
is an application of a field-selection operation to a composite value,
but you're not required to perform such a selection after mentioning
a whole-row variable, if it's in a context where the system can do
something sensible with a composite value. Another way to say it is
that "tablename" and "tablename.*" are interchangeable notations for
a composite value representing the table's current row, everywhere except
at the top level of a SELECT list, where the standard mandates that "*"
references be burst into individual column references.

I had thought this was documented someplace, but after trawling our SGML
docs the closest thing I can find is the discussion in
https://www.postgresql.org/docs/current/static/sql-expressions.html#FIELD-SELECTION
and that's not terribly explicit. There's also some related material in
https://www.postgresql.org/docs/current/static/rowtypes.html
but that doesn't seem to state this straight out either.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs