Union giving wrong column ordering even when using .values/.values_list

[Thiago Bellini Ribeiro]

This is a simplified example of a union I have in a project:

Product.objects.all().annotate(
    kind=Value("base_price", output_field=models.CharField()),
    priority=Value(0),
    product_id=F("id"),
    pos_id=Value(None, output_field=models.BigAutoField()),
).values(
    "kind",
    "priority",
    "price",
    "product_id",
    "pos_id",
).order_by().union(
    PriceTableProduct.objects.all().annotate(
        priority=Value(1),
        kind=Value("price-table", output_field=models.CharField()),
        pos_id=F("price_table__pos__id"),
    )
    .values(
        "kind",
        "priority",
        "price",
        "product_id",
        "pos_id",
    ),
)

On both sqlite and postgresql this produces wrong sql. On sqlite:

SELECT "product_product"."price" AS "col1",
       'base_price' AS "kind",
       0 AS "priority",
       "product_product"."id" AS "product_id",
       NULL AS "pos_id"
  FROM "product_product"
 UNION SELECT "product_pricetableproduct"."price" AS "col1",
       "product_pricetableproduct"."product_id" AS "col2",
       1 AS "priority",
       'price-table' AS "kind",
       "pos_pos"."id" AS "pos_id"
  FROM "product_pricetableproduct"
 INNER JOIN "product_pricetable"
    ON ("product_pricetableproduct"."price_table_id" = "product_pricetable"."id")
  LEFT OUTER JOIN "pos_pos"
    ON ("product_pricetable"."id" = "pos_pos"."price_table_id")
 LIMIT 21

On postgreql:

(
        SELECT "product_product"."price" AS "col1",
               'base_price' AS "kind",
               0 AS "priority",
               "product_product"."id" AS "product_id",
               NULL AS "pos_id"
          FROM "product_product"
       )
 UNION (
        SELECT "product_pricetableproduct"."price" AS "col1",
               "product_pricetableproduct"."product_id" AS "col2",
               1 AS "priority",
               'price-table' AS "kind",
               "pos_pos"."id" AS "pos_id"
          FROM "product_pricetableproduct"
         INNER JOIN "product_pricetable"
            ON ("product_pricetableproduct"."price_table_id" = "product_pricetable"."id")
          LEFT OUTER JOIN "pos_pos"
            ON ("product_pricetable"."id" = "pos_pos"."price_table_id")
       )
 LIMIT 21

You can see that the orders given to values are not preserved, and thus the returned value is wrong (e.g. "kind" is the second column in the base query, but "product_id" is the second column on the union query)

I'm using django 4.2, so not sure if this is a 4.2 regression or some old issue.

obs. the .order_by in the first query is also another issue. Since that model has an ordering defined in its meta, I get DatabaseError: ORDER BY not allowed in subqueries of compound statements. when running that query on sqlite (on postgresql it is fine and works without the empty order_by, but the main issue in this ticket still exists)

[Mariusz Felisiak]

Duplicate of #28900.