Thread: ORDER BY TABLENAME, possible bug
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. Best regards, Dmitry.
SGkNCg0KMjAxNi0xMC0yOSAxNDoxMyBHTVQrMDI6MDAgZHYgPHVkdi5tYWlsQGdtYWlsLmNvbT46 DQoNCj4gRS5nLiBxdWVyeToNCj4NCj4gU0VMRUNUIGNvbDEsIGNvbDIsIGNvbDMNCj4gRlJPTSB0 YWJsZTENCj4gT1JERVIgQlkgdGFibGUxDQo+DQo+IFBvc3RncmVzIHVzZXMgY29sMSBmb3IgQVND IG9yZGVyaW5nLCBpZiB3ZSB3cml0ZSAiT1JERVIgQlkgdGFibGUxDQo+IERFU0MiIHRoZW4gREVT Qy1vcmRlcmluZy4gSSdtIG5vdCBzdXJlIHRoaXMgaXMgYSBidWcsIGJ1dCBkaWRuJ3QgZmluZA0K PiBkZXNjcmlwdGlvbiBmb3Igc3VjaCBiZWhhdmlvdXIuDQo+DQoNCkl0IGlzIG5vdCBidWcuIFBv c3RncmVzcWwncyB0YWJsZSBoYXMgZmljdGl2ZSBjb2x1bW4gd2l0aCBzYW1lIG5hbWUgYXMNCnRh YmxlbmFtZSB0aGF0IGlzIGNvbXBvc2l0ZSBvZiBhbGwgY29sdW1ucw0KDQpwb3N0Z3Jlcz0jIHNl bGVjdCAqIGZyb20gZm9vOw0K4pSM4pSA4pSA4pSA4pSA4pSs4pSA4pSA4pSA4pSA4pSQDQrilIIg YSAg4pSCIGIgIOKUgg0K4pWe4pWQ4pWQ4pWQ4pWQ4pWq4pWQ4pWQ4pWQ4pWQ4pWhDQrilIIgMTAg 4pSCIDIwIOKUgg0K4pSU4pSA4pSA4pSA4pSA4pS04pSA4pSA4pSA4pSA4pSYDQooMSByb3cpDQoN ClRpbWU6IDAuODM3IG1zDQpwb3N0Z3Jlcz0jIHNlbGVjdCBmb28gZnJvbSBmb287DQrilIzilIDi lIDilIDilIDilIDilIDilIDilIDilIDilJANCuKUgiAgIGZvbyAgIOKUgg0K4pWe4pWQ4pWQ4pWQ 4pWQ4pWQ4pWQ4pWQ4pWQ4pWQ4pWhDQrilIIgKDEwLDIwKSDilIINCuKUlOKUgOKUgOKUgOKUgOKU gOKUgOKUgOKUgOKUgOKUmA0KKDEgcm93KQ0KDQogUmVnYXJkcw0KDQpQYXZlbA0KDQoNCj4gQmVz dCByZWdhcmRzLCBEbWl0cnkuDQo+DQo+DQo+IC0tDQo+IFNlbnQgdmlhIHBnc3FsLWJ1Z3MgbWFp bGluZyBsaXN0IChwZ3NxbC1idWdzQHBvc3RncmVzcWwub3JnKQ0KPiBUbyBtYWtlIGNoYW5nZXMg dG8geW91ciBzdWJzY3JpcHRpb246DQo+IGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFpbHBy ZWYvcGdzcWwtYnVncw0KPg0K
On Sat, Oct 29, 2016 at 3:23 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hi > > 2016-10-29 14:13 GMT+02:00 dv <udv.mail@gmail.com>: > >> 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. >> > > It is not bug. Postgresql's table has fictive column with same name as > tablename that is composite of all columns > > > Is this somewhere in the documentation? The only place I could find where there is a hint of this use, is the Note in Row Constructors in https://www.postgresql.org/docs/current/static/sql-expressions.html that uses a table alias without the .* in an expression: ROW(t, 42) Pantelis Theodosiou
On Fri, Nov 18, 2016 at 3:35 PM, Pantelis Theodosiou <ypercube@gmail.com> wrote: > > > On Sat, Oct 29, 2016 at 3:23 PM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: > >> Hi >> >> 2016-10-29 14:13 GMT+02:00 dv <udv.mail@gmail.com>: >> >>> 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. >>> >> >> It is not bug. Postgresql's table has fictive column with same name as >> tablename that is composite of all columns >> >> >> > Is this somewhere in the documentation? The only place I could find wher= e > there is a hint of this use, is the Note in Row Constructors in > https://www.postgresql.org/docs/current/static/sql-expressions.html that > uses a table alias without the .* in an expression: ROW(t, 42) > =E2=80=8Bhttps://www.postgresql.org/docs/9.6/static/rowtypes.html =E2=80=8B"Whenever you create a table, a composite type is also automatical= ly created, with the same name as the table, to represent the table's row type." So, its documented and in technically correct location. I'm not sure if introducing this material in a "tutorial" would be a gain or just confuse the student. It seems to be something one picks up somehow (trial and error, mailing list, stumbling upon it in the docs or elsewhere on the Internet) as one increases their knowledge of SQL to an intermediate level. David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Fri, Nov 18, 2016 at 3:35 PM, Pantelis Theodosiou <ypercube@gmail.com> > wrote: >> Is this somewhere in the documentation? > âhttps://www.postgresql.org/docs/9.6/static/rowtypes.html > â"Whenever you create a table, a composite type is also automatically > created, with the same name as the table, to represent the table's row > type." > So, its documented and in technically correct location. I'm not sure if > introducing this material in a "tutorial" would be a gain or just confuse > the student. It seems to be something one picks up somehow (trial and > error, mailing list, stumbling upon it in the docs or elsewhere on the > Internet) as one increases their knowledge of SQL to an intermediate level. There's a whole bunch of behaviors around composite values that are documented in scattered places, some of which are completely not where you'd expect to look. In this example, the fact that you can use a table name/alias to represent the composite value of the current row is something that isn't exactly obvious, much less how that relates to other possible spellings such as "TABLENAME.*". We had a related question just a couple weeks ago, which caused me to wonder (not for the first time) whether we could pull together some sort of unified presentation. I haven't done anything about it though. regards, tom lane
David G. Johnston schrieb am 18.11.2016 um 23:48: > > Is this somewhere in the documentation? The only place I could find > where there is a hint of this use, is the Note in Row Constructors in > https://www.postgresql.org/docs/current/static/sql-expressions.html > <https://www.postgresql.org/docs/current/static/sql-expressions.html> > that uses a table alias without the .* in an expression: ROW(t, 42) > > âhttps://www.postgresql.org/docs/9.6/static/rowtypes.html > > â"Whenever you create a table, a composite type is also automatically created, with the same name as the table, to representthe table's row type." > > So, its documented and in technically correct location. I'm not sure > if introducing this material in a "tutorial" would be a gain or just > confuse the student. It seems to be something one picks up somehow > (trial and error, mailing list, stumbling upon it in the docs or > elsewhere on the Internet) as one increases their knowledge of SQL to > an intermediate level. I think the chapter about "Select Lists"[1] would be the approriate place to explain this. I would also love to see an explanation there on why "select (a,b,c)" is something different then "select a,b,c" I see far too many people putting the columns of the select list between parentheses and then being confused about the output. I don't know if the Postgres behaviour is mandated by the SQL standard. If it's not, that should be documented, maybe in the chapter I mentioned above or in the compatibility section of SELECT. If that _is_ mandated by the standard, then I think some small note/warning might be useful (especially to newcomers) Thomas [1] https://www.postgresql.org/docs/current/static/queries-select-lists.html