Does "*" ensure a specific column ordering?

Question

I'm curious about a query I saw written "by a friend" (LOL) and I was surprised by it. Here's the simplified case:

create table my_table (
  a int,
  b int,
  c int,
  d int,
  primary key (a, b, c)
);

insert into my_table (a, b, c, d) values (1, 2, 3, 4);
insert into my_table (a, b, c, d) values (5, 6, 7, 8);

select *, count(*), sum(d)
from my_table
group by 1, 2, 3

This actually works in PostgreSQL, and translates in the last line 1, 2, 3 to a, b, c, and never to c, b, a or other.

Result (PostgreSQL 11.1):

a  b  c  d  count  sum
-  -  -  -  -----  ---
5  6  7  8      1    8
1  2  3  4      1    4

So, does * actually preserve column ordering?

I won't probably write a confusing query like this ever, but still wanted to know if the SQL Standard said anything about it.

This does NOT work in Postgres -- and I would think you would know that -- https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2f5eab2719cc470cf9d9e5a8d15a02e3. — Gordon Linoff, Jan 30 '20 at 18:13
2 things: 1, this errors. 2, I believe the `*` just expands to the columns based on ordinal. So I think it would keep order. — dfundako, Jan 30 '20 at 18:14
@GordonLinoff: it works if the columns include the primary key: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e3b18892572cda42c4f9752765dac39d — , Jan 30 '20 at 18:17
Added the primary key definition and now it works everywhere. — The Impaler, Jan 30 '20 at 18:20
@GSerg Perfect. "...The columns are referenced in the ascending sequence of their ordinal position within T...". That answers it clearly. Please mark as duplicate to close. — The Impaler, Jan 30 '20 at 18:24
Syntax error. A single `*` must be alone in the select list. — jarlh, Jan 30 '20 at 20:12
@jarlh Why? You can add other things after a comma. You can click the dbfiddle links in [comments above](https://stackoverflow.com/questions/59992091/does-ensure-a-specific-column-ordering#comment106097345_59992091) and see it working. — GSerg, Jan 30 '20 at 20:51
@GSerg, ISO/IEC 9075-2:2016(E), 7.16 " ::= SELECT [ ] ::= ** | }... ]". — jarlh, Jan 30 '20 at 20:59
@GSerg, yes, I know. It seems to work with _most_ dbms products, Oracle and a few others excluded. — jarlh, Jan 30 '20 at 21:26

score 1 · Answer 1 · answered Jan 30 '20 at 18:18

I'm going to ignore your example code and address the question:

Does * actually preserve column ordering?

In general, I have not found specific documentation or reference that the column ordering is guaranteed to be preserved. I would be very happy if someone produced such a reference, either in the standard or even in a particular database.

However, I think it is safe to assume that the ordering is preserved. Following are three reasons.

The first is that all databases that I have experience with have always preserved the ordering. Even more, for multiple tables the ordering is in the ordering specified in the FROM clause.

Second is that the ordering is known -- at least in databases that provide metadata schemas. The column INFORMATION_SCHEMA.COLUMNS.ORDINAL_NUMBER has this information.

And finally, the following works and I think is guaranteed to work in any database:

insert into t
    select *
    from t;

In order for this to work, the columns need to be in the specified order. I don't think a SELECT can behave differently in an INSERT . . . SELECT than it would by itself (other than the order of the rows).

`I would be very happy if someone produced such a reference` - https://stackoverflow.com/a/11737363/11683? — GSerg, Jan 30 '20 at 18:23

Does "*" ensure a specific column ordering?

1 Answers1