Обсуждение: BUG #14405: ORDER BY TABLENAME, possible bug

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQwNQpMb2dnZWQgYnk6ICAg
ICAgICAgIHVkdi5tYWlsQGdtYWlsLmNvbSB1ZHYubWFpbEBnbWFpbC5jb20K
RW1haWwgYWRkcmVzczogICAgICB1ZHYubWFpbEBnbWFpbC5jb20KUG9zdGdy
ZVNRTCB2ZXJzaW9uOiA5LjYuMApPcGVyYXRpbmcgc3lzdGVtOiAgIERlYmlh
bgpEZXNjcmlwdGlvbjogICAgICAgIAoKRS5nLiBxdWVyeToNCg0KU0VMRUNU
IGNvbDEsIGNvbDIsIGNvbDMNCkZST00gdGFibGUxDQpPUkRFUiBCWSB0YWJs
ZTENCg0KUG9zdGdyZXMgdXNlcyBjb2wxIGZvciBBU0Mgb3JkZXJpbmcsIGlm
IHdlIHdyaXRlICJPUkRFUiBCWSB0YWJsZTENCkRFU0MiIHRoZW4gREVTQy1v
cmRlcmluZy4gSSdtIG5vdCBzdXJlIHRoaXMgaXMgYSBidWcsIGJ1dCBkaWRu
J3QgZmluZA0KZGVzY3JpcHRpb24gZm9yIHN1Y2ggYmVoYXZpb3VyLg0KCgo=

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.

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