Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ordering of results in query templates involving UNION

Ordering of results in query templates involving UNION

From
Edmund Horner
Date:
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 ).

Cheers,
Edmund Horner

Re: Ordering of results in query templates involving UNION

From
Dave Page
Date:
Hi,

On Sun, Jun 12, 2016 at 11:18 PM, Edmund Horner <ejrh00@gmail.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