#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) |
Severity: Normal | Resolution:
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by skoot):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
The bug is actually easier to spot when using this queryset:
{{{
A.objects.values("b__name", "c__name").order_by("c__name").distinct()[:10]
}}}
which generates this query:
{{{
SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (
SELECT DISTINCT "B"."NAME" AS "NAME", "C"."NAME" AS Col1, "C"."NAME"
FROM (...)
ORDER BY "C"."NAME" ASC
) "_SUB" WHERE ROWNUM <= 10) WHERE "_RN" > 0
}}}
IMO, the broken code is in SQLCompiler.get_ordering():
{{{
for table, col, order in self.find_ordering_name(field,
self.query.model._meta, default_order=asc):
if (table, col) not in processed_pairs:
elt = '%s.%s' % (qn(table), qn2(col))
processed_pairs.add((table, col))
if distinct and elt not in select_aliases:
ordering_aliases.append(elt)
result.append('%s %s' % (elt, order))
group_by.append((elt, []))
}}}
It's from django 1.4 code, but it's pretty similar in 1.7.
"elt not in select_aliases" will never be False, because get_columns() was
called with the "with_aliases" parameter set to True, and so the entry in
select_aliases for C's "name" is "Col1" and not "C"."NAME".
Which predicts that this query should works despite the bug:
{{{
A.objects.values("b__name", "c__name").order_by("b__name").distinct()[:10]
}}}
And it does: B's name being in first position, il will be aliased as
"NAME", not "Col1". And so "elt not in select_aliases" will find it, and
won't add the column again in the SELECT part of the statement.
I got a patch, but I don't like it. It solves my problem, but it doesn't
prevent get_ordering form adding in the SELECT part of the statement a
column that's already there. All it does is alias it unconditionally using
a name (hopefully) won't be used anywhere else:
{{{
ord = 1
(...)
if distinct and elt not in select_aliases:
ordering_aliases.append("%s AS Ord%d" % (elt,
ordn))
ordn += 1
}}}
Definitely not the way it should be fixed, but it may help whoever works
on this understand what happens and why.
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:1>
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/063.af7c09644719793bbfc8e0ba4701d7b7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
