#34502: Union giving wrong column ordering even when using .values/.values_list
-------------------------------------+-------------------------------------
Reporter: Thiago | Owner: nobody
Bellini Ribeiro |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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)
--
Ticket URL: <https://code.djangoproject.com/ticket/34502>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018795807ad8-16b041e2-9344-4781-853b-508940b1645a-000000%40eu-central-1.amazonses.com.
