#24367: ORA-00918 (column ambiguously defined) when using a combination of
slicing,
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) | Keywords: ORA-00918 oracle
Severity: Normal | queryset
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
The steps to reproduce the problem are fairly simple.
First, you need at least 3 models :
- The first model has foreign keys to the other two
- The other two have a field with a similar name.
Here is a minimalistic example (pseudo-code):
{{{
class A (Model):
b = ForeignKey(B)
c = ForeignKey(C)
class B (Model):
name = CharField()
class C (Model)
name = CharField()
}}}
So model A has foreign keys to models B and C, and models B and C both
have a field called "name".
If we then select B's "name" field while ordering by C's "name" field in a
distinct and sliced query, it fails with an ORA-00918 error (column
ambiguously defined). In our example, the queryset would look something
like this:
{{{
A.objects.values("b__name").order_by("c__name").distinct()[:10]
or
A.objects.select_related("b").order_by("c__name").distinct()[:10]
}}}
If you remove either the order_by, the distinct(), or the slicing, it
works. If you have all 3, it breaks.
The generated SQL query looks something like this:
{{{
SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT DISTINCT
"B"."NAME" AS "NAME", "C"."NAME" FROM "A" INNER JOIN "B" ON ( "A"."A_ID" =
"B"."A_ID" ) INNER JOIN "C" ON ( "A"."C_ID" = "C"."C_ID" ) ORDER BY
"C"."NAME" ASC) "_SUB" WHERE ROWNUM <= 10) WHERE "_RN" > 0
}}}
I've reproduced the bug both on django 1.4 and on django 1.7, and it is
oracle specific (my application works fine on pgsql).
--
Ticket URL: <https://code.djangoproject.com/ticket/24367>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.2fa6368555661df72eaaa0b7ce2212f2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
