Re: Ordering of results in query templates involving UNION

Hi,

On Sun, Jun 12, 2016 at 11:18 PM, Edmund Horner <ejrh00(at)gmail(dot)com> wrote:
> Hi,
>
> I was trying out the beta download and noticed that the SQL definition for
> some of my constraints had the columns in the wrong order. I traced this to
> https://git.postgresql.org/gitweb/?p=pgadmin4.git;a=blob;f=web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/get_constraint_cols.sql;hb=702609517f23be7c14e2f9d655f66ea8ac43dd9c
> which has the form of a SELECT ... UNION SELECT ... for each column in the
> constraint. I was able to fix this one case by appending the loop index as
> a new column and ordering by that:
>
> {% for n in range(colcnt|int) %}
> {% if loop.index != 1 %}
> UNION SELECT pg_get_indexdef({{ cid|string }}, {{ loop.index|string }},
> true) AS column, {{ loop.index|string }} AS idx
> {% else %}
> SELECT pg_get_indexdef({{ cid|string }} , {{ loop.index|string }} ,
> true) AS column, {{ loop.index|string }} AS idx
> {% endif %}
> {% endfor %}
> ORDER BY idx
>
> There are a few other templates where it looks like the same pattern
> happens. In a lot of them you use UNION ALL, which may coincidentally
> return rows the same order as the individual SELECT statements. In the one
> above you use UNION so hashing is used and rows are returned in a less
> predictable order. I think that even in the first case an ORDER BY clause
> is required for correctness -- or the rows need to be sorted in the client
> code before generating the SQL.
>
> I have not attached a patch as I've not been involved on pgAdmin
> development. But if the problem and the fix makes sense I would be happy to
> work on one to try to add ordering to UNION-based query templates (e.g. the
> ones on this list
> https://git.postgresql.org/gitweb/?p=pgadmin4.git&a=search&h=HEAD&st=grep&s=UNION
> ).

Good catch - a patch would be very welcome, thanks!

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company