Order by in a sub query when aggregating the main query

[Federico <cfederico87@xxxxxxxxx>]

Hi all,

I have a question related to the order by clause used in a subquery of
a main query that uses one or more aggregation functions with a group
by.
A basic example of the type of query in question is the following (see
below for the actual query):

  select w, array_agg(x)
  from (
    select v, v / 10 as w
    from pg_catalog.generate_series(25, 0, -1) as t(v)
    order by v
  ) as t(x)
  group by w

This query will return an ordered array as specified by the order by
clause.in the subquery.
Can this behaviour be relied upon?
>From what I could find from searching in SQL the order by in a
subquery could be ignored by the engines, but I've found that
postgresql will always respect it.

The context of the question is the updated reflection logic that will
be introduced in version 2 of SQLAlchemy, that makes use of orderby in
subqueries to, for example, match column index of a constraint with
the column name of a table. This query and other similar one return
the correct result, and they seem stable in their output (ie the CI is
not randomly failing because the order has changed). For more
information this potential issue with the current query is traket in
the issue https://github.com/sqlalchemy/sqlalchemy/issues/8561
Below is the full query that will be used in sqlalchemy to reflect
constraints given the constraint type and on a list of table oids:

  select
    attr.conrelid,
    array_agg(attr.attname) as cols,
    attr.conname,
    min(attr.description) as description
  from (
    select
      con.conrelid as conrelid,
      con.conname as conname,
      con.description as description,
      pg_catalog.pg_attribute.attname as attname
    from pg_catalog.pg_attribute
    join (
      select
        pg_catalog.pg_constraint.conrelid as conrelid,
        pg_catalog.pg_constraint.conname as conname,
        unnest(pg_catalog.pg_constraint.conkey) as attnum,
        generate_subscripts(pg_catalog.pg_constraint.conkey,
%(generate_subscripts_1)s) as ord,
        pg_catalog.pg_description.description as description
      from pg_catalog.pg_constraint
      left outer join pg_catalog.pg_description on
        pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid
      where
        pg_catalog.pg_constraint.contype = :contype
        and pg_catalog.pg_constraint.conrelid in (:oids)
      ) as con on
      pg_catalog.pg_attribute.attnum = con.attnum
      and pg_catalog.pg_attribute.attrelid = con.conrelid
    order by con.conname, con.ord
  ) as attr
  group by attr.conrelid, attr.conname
  order by attr.conrelid, attr.conname

The other reflection queries that use order by in subqueries are
similar to the above, I can post them here if they may prove useful.

Thank you
Federico