ORDER BY TABLENAME, possible bug

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

postgres=# select * from foo;
┌────┬────┐
│ a │ b │
╞════╪════╡
│ 10 │ 20 │
└────┴────┘
(1 row)

Time: 0.837 ms
postgres=# select foo from foo;
┌─────────┐
│ foo │
╞═════════╡
│ (10,20) │
└─────────┘
(1 row)

Regards

Pavel

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

​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.

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

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

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&gt;
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 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.

I think the chapter about "Select Lists"[1]https://www.postgresql.org/docs/current/static/queries-select-lists.html 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

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