[Django] #24367: ORA-00918 (column ambiguously defined) when using a combination of slicing, distinct, and order_by
Django
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.
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
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
-------------------------------------+-------------------------------------
* 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
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by skoot):
I just checked and it's fixed in django 1.8.
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:2>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:3>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: fixed =>
Comment:
I have models like (result of inspectdb)
{{{
class Profile(models.Model):
id = models.BigIntegerField(primary_key=True)
name = models.CharField(max_length=1024, blank=True, null=True)
class Meta:
managed = False
db_table = 'cpe_profile'
class ResourceMgmt(models.Model):
id = models.BigIntegerField(primary_key=True)
profile = models.ForeignKey(Profile, models.DO_NOTHING,
related_name='cpe_mgmt_profile')
class Meta:
managed = False
db_table = 'cpe_resource_mgmt'
}}}
Serializer like
{{{
class ResourceMgmtSerializer(serializers.ModelSerializer):
profile_name = serializers.StringRelatedField(source='profile.name',
read_only=True)
class Meta:
model = ResourceMgmt
fields = (<tuple of fields including profile_name>)
}}}
View like
{{{
class RunningSchedules(generics.ListCreateAPIView):
serializer_class = ResourceMgmtSerializer
model = ResourceMgmt
filter_backends = (filters.OrderingFilter, filters.SearchFilter,)
ordering_fields = (<tuple of fields including profile_name as
profile__name>)
ordering = ('-schedule_start_time',)
search_fields = ordering_fields
}}}
Now when I hit url like `?ordering=profile__name` I get an error
`ORA-00918: column ambiguously defined`.
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:4>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Could you give a QuerySet that reproduces the problem rather than
something that requires Django REST Framework?
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:5>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Kishor Pawar):
Replying to [comment:5 Tim Graham]:
> Could you give a QuerySet that reproduces the problem rather than
something that requires Django REST Framework?
Sorry for delayed reply. I did not get notification for your reply.
Query =
{{{
ResourceMgmt.objects.filter(is_active='Y')\
.filter(schedule_next_run_time__lt=future)\ # future is calculated
date
.filter(schedule_end_time__gt=now + datetime.timedelta(minutes=1))\
.extra(where=["schedule_next_run_time > sysdate -
(schedule_frequency/1440)",
"owner_id IN ( \
select id from cpe_user \
where is_active='Y' \
connect by prior id = parent_id start with id = % s)"],
154)
}}}
the output looks like
{{{
{"count":4,"next":null,"previous":null,"results":[{"id":396511,"owner":385436,"owner_login":"s...@nv.com","resource_owner":"s...@nv.com","resource":396387,"resource_name":"20nov","profile":157,"profile_name":"some
string
here","run_remediate":"Y","schedule_start_time":"2017-11-20T06:05:00.578032Z","schedule_frequency":1,"schedule_create_time":"2017-11-20T06:02:35Z","schedule_end_time":"2017-11-20T06:05:00.578032Z","schedule_next_run_time":"2017-11-20T06:05:00.578032Z","is_active":"Y","run_cost_scan":"28.00","run_cost_remediate":"140.00","last_run":null,"created_by":"s...@nv.com","create_date":"2017-11-20T06:04:00Z","updated_by":"RAXAK3","update_date":"2017-11-20T06:05:00Z","overall_status":null,"overall_info":null,"resource_info":{"username":"raxak","os_version":"v6.3.9600","os_architecture":"6.3.9600","os_name":"some
string
here","ip_addr":"103.157.4.217"}},{"id":380200,"owner":379468,"owner_login":"sa...@nov.com","resource_owner":"sa...@nov.com","resource":379903,"resource_name
":"WIN-EOVV3Q2I5KK","profile":157,"profile_name":"some string
here","run_remediate":"N","schedule_start_time":"2017-11-17T09:49:03.235260Z","schedule_frequency":1,"schedule_create_time":"2017-11-17T09:46:38Z","schedule_end_time":"2017-11-17T09:49:03.235260Z","schedule_next_run_time":"2017-11-17T09:49:03.235260Z","is_active":"Y","run_cost_scan":"28.00","run_cost_remediate":"140.00","last_run":null,"created_by":"sa...@nov.com","create_date":"2017-11-17T09:48:03Z","updated_by":"RAXAK3","update_date":"2017-11-17T09:49:00Z","overall_status":null,"overall_info":null,"resource_info":{"username":"raxak","os_version":"v6.3.9600","os_architecture":"6.3.9600","os_name":"Microsoft
Windows Server 2012
R2","ip_addr":"13.126.155.240"}},{"id":367211,"owner":354149,"owner_login":"s...@amz.com","resource_owner":"s...@amz.com","resource":359474,"resource_name":"2012_1","profile":359771,"profile_name":"prof1","run_remediate":"N","schedule_start_time":"2017-11-16T08:47:24.278393Z","schedule_frequency":1,"schedule_create_time":"2017-11-16T08:45:00Z","schedule_end_time":"2017-11-16T08:47:24.278393Z","schedule_next_run_time":"2017-11-16T08:47:24.278393Z","is_active":"Y","run_cost_scan":"0.50","run_cost_remediate":"2.50","last_run":null,"created_by":"s...@amz.com","create_date":"2017-11-16T08:46:24Z","updated_by":"RAXAK3","update_date":"2017-11-16T08:47:00Z","overall_status":null,"overall_info":null,"resource_info":{"username":"raxak","os_version":"v6.3.9600","os_architecture":"6.3.9600","os_name":"Microsoft
Windows Server 2012
R2","ip_addr":"13.127.3.77"}},{"id":366341,"owner":354149,"owner_login":"s...@amz.com","resource_owner":"s...@amz.com","resource":359474,"resource_name":"2012_1","profile":157,"profile_name":"some
string
here","run_remediate":"Y","schedule_start_time":"2017-11-16T08:29:24.543755Z","schedule_frequency":1,"schedule_create_time":"2017-11-16T08:27:00Z","schedule_end_time":"2017-11-16T08:29:24.543755Z","schedule_next_run_time":"2017-11-16T08:29:24.543755Z","is_active":"Y","run_cost_scan":"28.00","run_cost_remediate":"140.00","last_run":null,"created_by":"s...@amz.com","create_date":"2017-11-16T08:28:24Z","updated_by":"RAXAK3","update_date":"2017-11-16T08:29:00Z","overall_status":null,"overall_info":null,"resource_info":{"username":"raxak","os_version":"v6.3.9600","os_architecture":"6.3.9600","os_name":"some
string here","ip_addr":"13.127.3.77"}}]}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:6>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
Hey Kishor, could you try reproducing without using `extra`? I suspect
this is the origin of your issue here as `id` could be ambiguous in your
subquery's context.
The `sysdate - (schedule_frequency/1440)` could be replaced by something
along `schedule_next_run_time__gt=(Func(template='sysdate') -
(F('schedule_frequency')/1440))` and the `owner_id IN` part by
`owner_id__in=...`.
I'll reset this ticket as fixed because while you have the same symptoms
as the initial report (`ORA-00918 (column ambiguously defined)`) you are
not using `slicing`, `distinct`, or `order_by` which is what this ticket
is about.
If you manage to reproduce a similar failure without using `extra()`
please submit a new ticket with reproduction steps.
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:7>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Kishor Pawar):
Hey Simon, I got what you are saying. I see now what I missed. Also ,
thank you for improvisation tips. I will update you with results of your
suggestion.
Replying to [comment:7 Simon Charette]:
> Hey Kishor, could you try reproducing without using `extra`? I suspect
this is the origin of your issue here as `id` could be ambiguous in your
subquery's context.
>
> The `sysdate - (schedule_frequency/1440)` could be replaced by something
along `schedule_next_run_time__gt=(Func(template='sysdate') -
(F('schedule_frequency')/1440))` and the `owner_id IN` part by
`owner_id__in=...`.
>
> I'll reset this ticket as fixed because while you have the same symptoms
as the initial report (`ORA-00918 (column ambiguously defined)`) you are
not using `slicing`, `distinct`, or `order_by` which is what this ticket
is about.
>
> If you manage to reproduce a similar failure without using `extra()`
please submit a new ticket with reproduction steps.
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:8>
Django
distinct, and order_by
-------------------------------------+-------------------------------------
Reporter: skoot | Owner: nobody
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ORA-00918 oracle | Triage Stage:
queryset | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Kishor Pawar):
Hey Simon,
Thank you for pointer. When I tried without `extra` clause, It worked. But
`id` wasn't the issue, issue was `owner_id`. So I added the dotted
notation like `cpe_resource_mgmt.owner_id` and it worked perfect.
Replying to [comment:7 Simon Charette]:
> Hey Kishor, could you try reproducing without using `extra`? I suspect
this is the origin of your issue here as `id` could be ambiguous in your
subquery's context.
>
> The `sysdate - (schedule_frequency/1440)` could be replaced by something
along `schedule_next_run_time__gt=(Func(template='sysdate') -
(F('schedule_frequency')/1440))` and the `owner_id IN` part by
`owner_id__in=...`.
>
> I'll reset this ticket as fixed because while you have the same symptoms
as the initial report (`ORA-00918 (column ambiguously defined)`) you are
not using `slicing`, `distinct`, or `order_by` which is what this ticket
is about.
>
> If you manage to reproduce a similar failure without using `extra()`
please submit a new ticket with reproduction steps.
--
Ticket URL: <https://code.djangoproject.com/ticket/24367#comment:9>